我要如何插入/检索的Excel文件的varbinary（max）列在SQL Server 2008？ [英] How do I insert/retrieve Excel files to varbinary(max) column in SQL Server 2008?

问题描述

我想要的Excel文件保存到数据库中，我不想使用FILESTREAM，因为它是需要有一个服务器的。

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.

那么，如何插入/更新/选择到具有类型的列的表 VARBINARY（最大）？

So how do I insert/update/select into the table that has a column of type varbinary(max)?

推荐答案

如果你想这样做直ADO.NET，和你的Excel文件不是太大，使他们能够适应到内存中一次，你可以使用这两种方法：

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:

// 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();
    }
}

要检索的Excel表背并将其存储在一个文件中，使用此方法：

To retrieve the Excel sheet back and store it in a file, use this method:

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).

这篇关于我要如何插入/检索的Excel文件的varbinary（max）列在SQL Server 2008？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！