Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Import and Export Excel Files into Varbinary (Max) in SQL Server

DZone's Guide to

Import and Export Excel Files into Varbinary (Max) in SQL Server

See how to combine a bit of C#, MS SQL, and ASPX to create quick and easy import and export buttons for your database.

· Database Zone ·
Free Resource

Discover Tarantool's unique features which include powerful stored procedures, SQL support, smart cache, and the speed of 1 million ACID transactions on a single CPU core!

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:

Output


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

Excel File


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

Excel File



Excel File


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

Discover Tarantool's unique features such as powerful stored procedures, SQL support, smart cache, and the speed of 1 million ACID transactions on a single CPU.

Topics:
c# ,excel ,sql server ,import and export

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}