TechNet
Products
IT Resources
Downloads
Training
Support
Products
Windows
Windows Server
System Center
Microsoft Edge
Office
Office 365
Exchange Server
SQL Server
SharePoint Products
Skype for Business
See all products »
Resources
Channel 9 Video
Evaluation Center
Learning Resources
Microsoft Tech Companion App
Microsoft Technical Communities
Microsoft Virtual Academy
Script Center
Server and Tools Blogs
TechNet Blogs
TechNet Flash Newsletter
TechNet Gallery
TechNet Library
TechNet Magazine
TechNet Wiki
Windows Sysinternals
Virtual Labs
Solutions
Networking
Cloud and Datacenter
Security
Virtualization
Updates
Service Packs
Security Bulletins
Windows Update
Trials
Windows Server 2016
System Center 2016
Windows 10 Enterprise
SQL Server 2016
See all trials »
Related Sites
Microsoft Download Center
Microsoft Evaluation Center
Drivers
Windows Sysinternals
TechNet Gallery
Training
Expert-led, virtual classes
Training Catalog
Class Locator
Microsoft Virtual Academy
Free Windows Server 2012 courses
Free Windows 8 courses
SQL Server training
Microsoft Official Courses On-Demand
Certifications
Certification overview
Special offers
MCSE Cloud Platform and Infrastructure
MCSE: Mobility
MCSE: Data Management and Analytics
MCSE Productivity
Other resources
Microsoft Events
Exam Replay
Born To Learn blog
Find technical communities in your area
Azure training
Official Practice Tests
Support options
For business
For developers
For IT professionals
For technical support
Support offerings
More support
Microsoft Premier Online
TechNet Forums
MSDN Forums
Security Bulletins & Advisories
Not an IT pro?
Microsoft Customer Support
Microsoft Community Forums
Sign in
Home
Library
Wiki
Learn
Gallery
Downloads
Support
Forums
Blogs
Resources For IT Professionals
United States (English)
Россия (Pусский)
中国(简体中文)
Brasil (Português)
Skip to locale bar
Post an article
Translate this page
Powered by
Microsoft® Translator
Wikis - Page Details
First published by
Karthik Elumalai
When:
22 Jul 2016 2:28 AM
Last revision by
Carsten Siemens
When:
10 Aug 2016 12:58 PM
Revisions:
8
Comments:
5
Options
Subscribe to Article (RSS)
Share this
Engage!
Wiki Ninjas Blog
(
Announcements
)
Wiki Ninjas on Twitter
TechNet Wiki Discussion Forum
Can You Improve This Article?
Positively!
Click Sign In to add the tip, solution, correction or comment that will help other users.
Report inappropriate content using
these instructions
.
Wiki
>
TechNet Articles
>
Import and Export Excel Files into Varbinary (Max) in SQL Server
Import and Export Excel Files into Varbinary (Max) in SQL Server
Article
History
Import and Export Excel Files into Varbinary (Max) in SQL Server
Table of Contents
Introduction
Aspx code
Add just two buttons,
Codebehind code,
Code for Import Excel to Db,
Code for Export Excel from DB,
Database Create Table Script
Note
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,
//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();
}
}
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.