c#将文件写入mysql中和从mysql中读数据_c#把文件路经放到mysql表中

主代码

这样需要自定义mysql的连接参数;主要代码如下

MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = sql;//这句需传参数;
cmd.CommandType = CommandType.Text;

cmd.Parameters.Add("@id", MySql.Data.MySqlClient.MySqlDbType.Int16);
cmd.Parameters.Add("@fileName", MySql.Data.MySqlClient.MySqlDbType.VarChar);
cmd.Parameters.Add("@filecontent", MySql.Data.MySqlClient.MySqlDbType.Blob);//文件在mysql中存为Blob

cmd.Parameters[0].Value = curIndex;//这句需传参数
cmd.Parameters[1].Value = fileName;
cmd.Parameters[2].Value = bytes;//文件传入的参数应该是字节数组;
cmd.Connection = conn;

cmd.ExecuteNonQuery();
1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql封装

我们把mysql的操作抽象成如下的代码;其中MySql.Data这个命名空间其实是一个外在的mysqldata.dll的引用;
这里提供下载

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using MySql.Data.MySqlClient;
    using System.Data;
    public class MySQLDataHelper : IDisposable
    {

        string host = "127.0.0.1";
        int port = 3306;
        string uid = "root";
        string password = "";
        string dbname = "testforfileopert";

        MySqlConnection conn = null;


        // 构造函数 私有化
        private MySQLDataHelper()
        {
            string connStr = "server=" + host
                            + ";port=" + port
                            + ";uid=" + uid
                            + ";password=" + password
                            + ";database=" + dbname;
            conn = new MySqlConnection(connStr); //数据库连接 
        }
        // 单例模式
        public static readonly MySQLDataHelper Instance = new MySQLDataHelper();

        // 连接数据库
        public Boolean ConnectToDB()
        {
            if (conn == null)
                return false;
            if (conn.State == System.Data.ConnectionState.Open)
                return true;
            try
            {
                conn.Open();
            }
            catch (Exception e)
            {
                return false;
            }
            return true;
        }
        //轻易不要这样用
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }

        public DataTable GetDataTable(string sql)
        {
            if (conn != null)
            {
                if (conn.State == System.Data.ConnectionState.Open || ConnectToDB())
                {
                    MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                    DataSet MyDataSet = new DataSet();
                    da.Fill(MyDataSet);
                    return MyDataSet.Tables[0];
                }
            }
            return null;
        }

        //运行sql代码，只用于insert、update和delete，返回影响的行数
        public int runSql(String sql)
        {
            if (conn != null)
            {
                if (conn.State == System.Data.ConnectionState.Open || ConnectToDB())
                {
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    return cmd.ExecuteNonQuery();
                }
            }
            return 0;
        }


        /// <summary>
        /// 运行SQL代码,这句话供Dragon来上传文件到表中；
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int upFile(String sql, Byte[] bytes,int curIndex,string fileName)
        {
            if (conn != null)
            {
                if (conn.State == System.Data.ConnectionState.Open || ConnectToDB())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = sql;//这句需传参数;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@id", MySql.Data.MySqlClient.MySqlDbType.Int16);
                    cmd.Parameters.Add("@fileName", MySql.Data.MySqlClient.MySqlDbType.VarChar);
                    cmd.Parameters.Add("@filecontent", MySql.Data.MySqlClient.MySqlDbType.Blob);//这句需传参数;
                    cmd.Parameters[0].Value = curIndex;
                    cmd.Parameters[1].Value = fileName;
                    cmd.Parameters[2].Value = bytes;
                    cmd.Connection = conn;


                    return cmd.ExecuteNonQuery();


                }
            }
            return 0;
        }
        /// <summary>
        /// 用来从数据库下载文件
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="buffer">输出的字节数组，内含文件</param>
        /// <returns></returns>
        public long downFile(String sql, out byte[] buffer)
        {
            if (conn != null)
            {
                if (conn.State == System.Data.ConnectionState.Open || ConnectToDB())
                {
                    MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    cmd.Connection = conn;
                    System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
                    buffer = null;
                    if (reader.HasRows)
                    {
                        reader.Read();
                        long len = reader.GetBytes(0, 0, null, 0, 0);//1是picture
                        buffer = new byte[len];
                        len = reader.GetBytes(0, 0, buffer, 0, (int)len);
                        //System.IO.MemoryStream ms = new System.IO.MemoryStream(buffer);
                        //System.Drawing.Image iamge = System.Drawing.Image.FromStream(ms);
                        //pictureBox1.Image = iamge;
                        return len;
                    }
                }
            }
            buffer = null;
            return 0;

        }

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

测试代码

下面是一个小程序用来保存word文件到数据库和从数据库中下载word文件

        static void saveWord()
        {
            //MySQLDataHelper mdh = MySQLDataHelper.Instance;
            //打开word文件并保存为字节数组;
            string filePath = @"E:\资料\onedrive\文档\2015E8013261145_邓力.docx";
            string fileName = filePath.Split('\\')[4];
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            byte[] buffByte = new byte[fs.Length];
            fs.Read(buffByte, 0, (int)fs.Length);
            fs.Close();
            fs = null;
            int curIndex = 1;
            string sql = "insert into testforfileopert.t_filesave values(?id,?fileName,?filecontent)";
            MySQLDataHelper msdl = MySQLDataHelper.Instance;
            int num = msdl.upFile(sql, buffByte, curIndex, fileName);
            Console.WriteLine("num="+num);

        }
        /// <summary>
        /// 下载word文件到本地;
        /// </summary>
        static void downLoadWord()
        {
            //先从服务器下载数据;
            string sql = "select fileContent from testforfileopert.t_filesave where id = 1";
            byte[] bytes = null;
            MySQLDataHelper mdhl = MySQLDataHelper.Instance;
            long num=mdhl.downFile(sql, out bytes);
            FileStream fs = new FileStream(@"D:\test.docx", FileMode.OpenOrCreate, FileAccess.Write);
            fs.Write(bytes, 0,(int)num);
            Console.WriteLine("下载上传num=" + num);
            fs.Close();
        }
        static void Main(string[] args)
        {
            saveWord();

            downLoadWord();
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

这样程序也可以读取其它文件格式。