Introduction
In this article you will get the detail about How to Import and Export Excel files into varbinary(max)in Sql server Using Asp.net C#
What are advantages of storing as varbinary file?
- We need not to depend on the file system,
- It will avoid the legal issues and data risk
Aspx code
Add just 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,
Code for Import Excel to Db,
-
- protected void BtnImportExcelToDB_Click(object sender, EventArgs e)
- {
-
-
- string filename = @ "D:\TPMS\Uploaded_Boq\Raveena_Boq1.xlsx";
-
-
- this.StoreExcelFileToDatabase(filename);
-
- }
-
-
-
- public void StoreExcelFileToDatabase(string excelFileName)
- {
-
- if (!File.Exists(excelFileName))
- {
- return;
- }
-
-
- byte[] excelContents = File.ReadAllBytes(excelFileName);
-
-
- string insertStmt = "INSERT INTO dbo.Tender_Excel_Source(FileName, FileContent) VALUES(@FileName, @BinaryContent)";
-
-
- 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;
-
-
- connection.Open();
- cmdInsert.ExecuteNonQuery();
- connection.Close();
- }
- }
Code for Export Excel from DB,
- 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
Note
Excel File had converted to the Byte file and will be saved like this.
Note
Now vice versa Byte file converted into original Excel file.
Hope the above information was useful, kindly let me know your feedback or suggestion.