Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I insert/retrieve Excel files to varbinary(max) column in SQL Server 2008?

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.

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

like image 614
Darius Suwardi Avatar asked Jun 24 '12 08:06

Darius Suwardi


People also ask

How do I import data from Excel to a table in SQL Server 2008?

Chose the Sql Server instance and chose the database to which the excel to be imported. Select Copy data from one or more tables or views and click Next . Now select the sheets to be imported to Sql Server . Now the wizard imports the data from Excel to Sql Server and click Close .

How do you update data from Excel to SQL?

Go to the SQL Spreads tab in Excel and select Design mode. A list of databases will appear on the right. Chose the database you are using and select an SQL table to update from Excel. From the Columns tab you can fine-tune how your table is presented in Excel.


1 Answers

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

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

like image 185
marc_s Avatar answered Oct 25 '22 20:10

marc_s