Introduction

In this article, you will learn how to import and export Excel files into Varbinary (max) in SQL Server Using ASP.NET C#

What Are the Advantages of Storing as a Varbinary File?

• We don't need to depend on the file system.
• It will avoid data risks.

ASPX Code

Just add two buttons:

<asp:Button ID="BtnImportExcelToDB" runat="server" Text="ImportExcelToDb" OnClick="BtnImportExcelToDB_Click" />  
<asp:Button ID="BtnExportExcelFromDB" runat="server" Text="ExportExcelFromDb" OnClick="BtnExportExcelFromDB_Click" />  

Codebehind Code

Import Code

//Button Click event for the ImportExcelToDB   
protected void BtnImportExcelToDB_Click(object sender, EventArgs e)  
{  
    //Specify the filepath where the file exist  
    string filename = @ "D:\TPMS\Uploaded_Boq\Raveena_Boq1.xlsx";  

    //pass the filename as a parameter  
    this.StoreExcelFileToDatabase(filename);   
}  
    
// store Excel sheet (or any file for that matter) into a SQL Server table  
public void StoreExcelFileToDatabase(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.Tender_Excel_Source(FileName, FileContent) VALUES(@FileName, @BinaryContent)";  
  
    // set up connection and command to do INSERT  
    using(SqlConnection connection = new SqlConnection(OSMC.constring_Property))  
    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();  
    }  
}  

Export Code

protected void BtnExportExcelFromDB_Click(object sender, EventArgs e)  
{  
    string filepathtostore = @ "D:\TPMS\Uploaded_Boq\Raveena_boq_From_Db.xlsx";  
    RetrieveExcelFileFromDatabase(4, filepathtostore);  
}  
  
public void RetrieveExcelFileFromDatabase(int ID, string excelFileName)   
{  
    byte[] excelContents;  
    string selectStmt = "SELECT FileContent FROM dbo.Tender_Excel_Source WHERE file_sequence_no = @ID";  
  
    using(SqlConnection connection = new SqlConnection(OSMC.constring_Property))  
    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);  
}  

Database Create Table Script

USE [tpms_release1]  
GO  
  
/****** Object: Table [dbo].[Tender_Excel_Source] Script Date: 09-06-16 Morning 10:19:05 ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
SET ANSI_PADDING ON  
GO  
  
CREATE TABLE [dbo].[Tender_Excel_Source](  
    [fk_tender_id] [int] NULL,  
    [file_sequence_no] [int] IDENTITY(1,1) NOT NULL,  
    [FileName] [nvarchar](1024) NULL,  
    [FileContent] [varbinary](max) NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  
GO  
  
SET ANSI_PADDING OFF  
GO  

Output

When you're done, it should look like this:

Click on the import button, and the Excel file will have been converted to a Byte file and will be saved like this:

Click on the export button, and the opposite will happen:

I hope the above information was useful. Kindly leave your feedback or suggestions.