In the process of development, we often encounter the form of storing files in the database. The common method is to upload files to the server. The database only needs to save the file path address. However, many internal window applications are not linked to the network and are local running services. At this time, we need to use another form to store files, That is, the file stream operation converts the file into bytes and stores it in the database in the form of file stream. When downloading, it can be downloaded in the corresponding conversion.
This time, we will provide two database operation modes, PostgresSql and Sqlite.
PostgresSql
The Psql database provides the type of storage bytes, that is, @ bytea. Therefore, when creating the database, set the corresponding field to @ bytea
1 CREATE TABLE tb_flypath 2 ( 3 ID SERIAL PRIMARY KEY , 4 FileName TEXT NOT NULL, 5 FileContent bytea NOT NULL, 6 CreateTime timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 7 )
Then, after setting it to bytea, you can convert the file into bytes for storage. Use the following code
1 /// <summary> 2 /// Convert file to bytes 3 /// </summary> 4 /// <param name="path">File path</param> 5 /// <returns></returns> 6 public byte[] getContent(string path) 7 { 8 System.IO.FileStream fs = new System.IO.FileStream(@path, System.IO.FileMode.Open); 9 fs.Position = 0; 10 byte[] content = new byte[fs.Length]; 11 fs.Read(content, 0, (int)fs.Length); 12 fs.Close(); 13 return content; 14 }
We directly use SQL statements to convert bytes into strings and store them in the database
1 /// <summary> 2 /// Byte to string 3 /// </summary> 4 /// <param name="inputBytes">byte</param> 5 /// <returns></returns> 6 public string ByteToString(byte[] inputBytes) 7 { 8 StringBuilder temp = new StringBuilder(2048); 9 foreach (byte tempByte in inputBytes) 10 { 11 temp.Append(tempByte > 15 ? 12 Convert.ToString(tempByte, 2) : '0' + Convert.ToString(tempByte, 2)); 13 } 14 return temp.ToString(); 15 } 16 17 string bstr = ByteToString(buffer); 18 string insertSql = string.Format(@"INSERT INTO tb_flyPath (FileContent) VALUES ('{0}')", bstr);
The above is the operation of writing files to the database. However, if this method is adopted, there will be problems. Because INSERT SQL cannot recognize the type of bytes, we store the string in the field of the corresponding byte type.
The subsequent problem is that when downloading a file, the downloaded file cannot be opened by converting FileContent anyway. If it is text, the content will be converted binary data.
As for why this happens, it has not been fully studied. Please leave a message.
Therefore, I also adopt the following methods for data storage, and use the database Parameter to execute SQL. This method can directly convert the data you pass in to the corresponding type dbtype Bytea, this type can transfer in byte type data for storage. After using this method, the stored data can be downloaded, converted and used, and various files can be opened.
1 /// <summary> 2 /// File storage into table 3 /// </summary> 4 /// <param name="filePath">File path</param> 5 /// <param name="id">node ID</param> 6 /// <returns></returns> 7 public bool FileInsert(string filePath, string id) 8 { 9 string strErrMsg = ""; 10 //read file 11 FileStream fs = File.OpenRead(filePath); 12 byte[] buffer = new byte[fs.Length]; 13 fs.Read(buffer, 0, buffer.Length); 14 15 if (!CreateCommand(out strErrMsg)) 16 return false; 17 31 NpgsqlParameter paramFileContent = _Command.CreateParameter(); 32 paramFileContent.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea; 33 paramFileContent.ParameterName = "FileContent"; 34 paramFileContent.Direction = ParameterDirection.Input; 35 paramFileContent.Value = buffer; 36 _Command.Parameters.Add(paramFileContent); 37 38 NpgsqlParameter paramFileName = _Command.CreateParameter(); 39 paramFileName.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar; 40 paramFileName.ParameterName = "FileName"; 41 paramFileName.Direction = ParameterDirection.Input; 42 paramFileName.Value = id; 43 _Command.Parameters.Add(paramFileName); 44 45 string sqlInsert = "INSERT INTO tb_flypath (filename,filecontent) VALUES (:FileName, :FileContent)"; 46 _Command.CommandText = sqlInsert; 47 _Command.CommandType = CommandType.Text; 48 49 _Connection.Open(); 50 int result = _Command.ExecuteNonQuery(); 51 _Connection.Close(); 52 53 return result > 0 ? true : false; 54 } 55 56 /// <summary> 57 /// Get the file from the database according to the file name 58 /// </summary> 59 /// <param name="fileName">File name in database</param> 60 /// <param name="savePath">The save path of the file, including the file name, such as D:\test.fly</param> 61 public bool DownloadFile(string fileName, string savePath) 62 { 63 string strErrMsg = ""; 64 65 if (!CreateCommand(out strErrMsg)) 66 return false;70 71 string sqlSelect = "select filename, filecontent from tb_flypath where filename=:FileName"; 72 _Command.CommandText = sqlSelect; 73 _Command.CommandType = CommandType.Text; 74 75 NpgsqlParameter paramFileName = _Command.CreateParameter(); 76 paramFileName.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar; 77 paramFileName.ParameterName = "FileName"; 78 paramFileName.Direction = ParameterDirection.Input; 79 paramFileName.Value = fileName; 80 _Command.Parameters.Add(paramFileName); 81 82 _Connection.Open(); 83 NpgsqlDataReader dr = _Command.ExecuteReader(); 84 dr.Read(); 85 byte[] buffer = (byte[])dr["filecontent"]; 86 dr.Close(); 87 _Connection.Close(); 88 89 //Save the file to the specified path 90 File.WriteAllBytes(savePath, buffer); 91 92 return true; 93 }
Sqlite
Let's talk about the operation mode of Sqlite database. In fact, the methods are the same. There is no big difference, but the storage data types of each database are different.
Sqlite provides BLOB type to store byte data, so create a table.
1 CREATE TABLE tb_flyPath 2 ( 3 ID INTEGER primary key autoincrement , 4 FileName VARCHAR not null , 5 FileContent BLOB not null , 6 CreateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 7 )
There is no difference in the methods of accessing files. PostgresSql uses the bytea type, so Sqlite needs to be set to dbtype Binary type.
1 /// <summary> 2 /// File storage into table 3 /// </summary> 4 /// <param name="filePath">File path</param> 5 /// <param name="id">node ID</param> 6 /// <returns></returns> 7 public bool FileInsert(string filePath, string id) 8 { 9 string strErrMsg = ""; 10 //read file 11 FileStream fs = File.OpenRead(filePath); 12 byte[] buffer = new byte[fs.Length]; 13 fs.Read(buffer, 0, buffer.Length); 14 15 if (!CreateCommand(out strErrMsg)) 16 return false; 17 31 SQLiteParameter paramFileContent = _Command.CreateParameter(); 32 paramFileContent.DbType = DbType.Binary; 33 paramFileContent.ParameterName = "FileContent"; 34 paramFileContent.Direction = ParameterDirection.Input; 35 paramFileContent.Value = buffer; 36 _Command.Parameters.Add(paramFileContent); 37 38 SQLiteParameter paramFileName = _Command.CreateParameter(); 39 paramFileName.DbType = DbType.String; 40 paramFileName.ParameterName = "FileName"; 41 paramFileName.Direction = ParameterDirection.Input; 42 paramFileName.Value = id; 43 _Command.Parameters.Add(paramFileName); 44 45 string sqlInsert = "INSERT INTO tb_flyPath (filename,filecontent) VALUES (:FileName, :FileContent)"; 46 _Command.CommandText = sqlInsert; 47 _Command.CommandType = CommandType.Text; 48 49 _Connection.Open(); 50 int result = _Command.ExecuteNonQuery(); 51 _Connection.Close(); 52 53 return result > 0 ? true : false; 54 } 55 56 /// <summary> 57 /// Get the file from the database according to the file name 58 /// </summary> 59 /// <param name="fileName">File name in database</param> 60 /// <param name="savePath">The save path of the file, including the file name, such as D:\test.fly</param> 61 public bool DownloadFile(string fileName, string savePath) 62 { 63 string strErrMsg = ""; 64 65 if (!CreateCommand(out strErrMsg)) 66 return false; 67 71 string sqlSelect = "select filename, filecontent from tb_flyPath where filename=:FileName"; 72 _Command.CommandText = sqlSelect; 73 _Command.CommandType = CommandType.Text; 74 75 SQLiteParameter paramFileName = _Command.CreateParameter(); 76 paramFileName.DbType = DbType.String; 77 paramFileName.ParameterName = "FileName"; 78 paramFileName.Direction = ParameterDirection.Input; 79 paramFileName.Value = fileName; 80 _Command.Parameters.Add(paramFileName); 81 82 _Connection.Open(); 83 SQLiteDataReader dr = _Command.ExecuteReader(); 84 dr.Read(); 85 byte[] buffer = (byte[])dr["filecontent"]; 86 dr.Close(); 87 _Connection.Close(); 88 89 //Save the file to the specified path 90 File.WriteAllBytes(savePath, buffer); 91 92 return true; 93 }
If something is wrong, please comment and leave a message.