I'm trying to save Excel files into the database, I do not want to use filestream as it is required to have a server for that.
我正在尝试将Excel文件保存到数据库中,我不想使用文件流,因为它需要有一个服务器。
So how do I insert/update/select into the table that has a column of type varbinary(max)
?
那么如何在具有varbinary(max)类型的列的表中插入/更新/选择?
2 个解决方案
#1
15
If you want to do it in straight ADO.NET, and your Excel files aren't too big so that they can fit into memory at once, you could use these two methods:
如果你想直接在ADO.NET中做,并且你的Excel文件不是太大,以便它们可以一次装入内存,你可以使用这两种方法:
// store Excel sheet (or any file for that matter) into a SQL Server table
public void StoreExcelToDatabase(string excelFileName)
{
// if file doesn't exist --> terminate (you might want to show a message box or something)
if (!File.Exists(excelFileName))
{
return;
}
// get all the bytes of the file into memory
byte[] excelContents = File.ReadAllBytes(excelFileName);
// define SQL statement to use
string insertStmt = "INSERT INTO dbo.YourTable(FileName, BinaryContent) VALUES(@FileName, @BinaryContent)";
// set up connection and command to do INSERT
using (SqlConnection connection = new SqlConnection("your-connection-string-here"))
using (SqlCommand cmdInsert = new SqlCommand(insertStmt, connection))
{
cmdInsert.Parameters.Add("@FileName", SqlDbType.VarChar, 500).Value = excelFileName;
cmdInsert.Parameters.Add("@BinaryContent", SqlDbType.VarBinary, int.MaxValue).Value = excelContents;
// open connection, execute SQL statement, close connection again
connection.Open();
cmdInsert.ExecuteNonQuery();
connection.Close();
}
}
To retrieve the Excel sheet back and store it in a file, use this method:
要重新检索Excel工作表并将其存储在文件中,请使用以下方法:
public void RetrieveExcelFromDatabase(int ID, string excelFileName)
{
byte[] excelContents;
string selectStmt = "SELECT BinaryContent FROM dbo.YourTableHere WHERE ID = @ID";
using (SqlConnection connection = new SqlConnection("your-connection-string-here"))
using (SqlCommand cmdSelect = new SqlCommand(selectStmt, connection))
{
cmdSelect.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
connection.Open();
excelContents = (byte[])cmdSelect.ExecuteScalar();
connection.Close();
}
File.WriteAllBytes(excelFileName, excelContents);
}
Of course, you can adapt this to your needs - you could do lots of other things, too - depending on what you really want to do (not very clear from your question).
当然,你可以根据你的需要调整它 - 你可以做很多其他的事情 - 取决于你真正想做的事情(你的问题不是很清楚)。
#2
0
It depends on the data access technology you are using. For example, if you use Entity Framework, you can just save a byte array into the database using objects.
这取决于您使用的数据访问技术。例如,如果使用Entity Framework,则只需使用对象将字节数组保存到数据库中。
#1
15
If you want to do it in straight ADO.NET, and your Excel files aren't too big so that they can fit into memory at once, you could use these two methods:
如果你想直接在ADO.NET中做,并且你的Excel文件不是太大,以便它们可以一次装入内存,你可以使用这两种方法:
// store Excel sheet (or any file for that matter) into a SQL Server table
public void StoreExcelToDatabase(string excelFileName)
{
// if file doesn't exist --> terminate (you might want to show a message box or something)
if (!File.Exists(excelFileName))
{
return;
}
// get all the bytes of the file into memory
byte[] excelContents = File.ReadAllBytes(excelFileName);
// define SQL statement to use
string insertStmt = "INSERT INTO dbo.YourTable(FileName, BinaryContent) VALUES(@FileName, @BinaryContent)";
// set up connection and command to do INSERT
using (SqlConnection connection = new SqlConnection("your-connection-string-here"))
using (SqlCommand cmdInsert = new SqlCommand(insertStmt, connection))
{
cmdInsert.Parameters.Add("@FileName", SqlDbType.VarChar, 500).Value = excelFileName;
cmdInsert.Parameters.Add("@BinaryContent", SqlDbType.VarBinary, int.MaxValue).Value = excelContents;
// open connection, execute SQL statement, close connection again
connection.Open();
cmdInsert.ExecuteNonQuery();
connection.Close();
}
}
To retrieve the Excel sheet back and store it in a file, use this method:
要重新检索Excel工作表并将其存储在文件中,请使用以下方法:
public void RetrieveExcelFromDatabase(int ID, string excelFileName)
{
byte[] excelContents;
string selectStmt = "SELECT BinaryContent FROM dbo.YourTableHere WHERE ID = @ID";
using (SqlConnection connection = new SqlConnection("your-connection-string-here"))
using (SqlCommand cmdSelect = new SqlCommand(selectStmt, connection))
{
cmdSelect.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
connection.Open();
excelContents = (byte[])cmdSelect.ExecuteScalar();
connection.Close();
}
File.WriteAllBytes(excelFileName, excelContents);
}
Of course, you can adapt this to your needs - you could do lots of other things, too - depending on what you really want to do (not very clear from your question).
当然,你可以根据你的需要调整它 - 你可以做很多其他的事情 - 取决于你真正想做的事情(你的问题不是很清楚)。
#2
0
It depends on the data access technology you are using. For example, if you use Entity Framework, you can just save a byte array into the database using objects.
这取决于您使用的数据访问技术。例如,如果使用Entity Framework,则只需使用对象将字节数组保存到数据库中。