Data Exchange between SQL server & Excel
Sometime we need to pass data to & fro from excel sheet and SQL server. To accomplish this task we can write two functions that one that import data from excel sheet to your table in database here we can use bulk copy class. We could Column mapping to map column from excel sheet to column from database.
Many time we have Identity on First column we should not include that column in our excel sheet which we are going to import into database table. We need to have fair understanding of constraint on column like unique key constraint, check constraint that should not get violated by data in excel sheet.
We will try out our trick on country table which can be created as follows
CREATE TABLE [dbo].[Country](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryCapital] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryCurrency] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryCurrencySymbol] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedDatetime] [datetime] NULL,
[LastAccessDatetime] [datetime] NULL,
[Deleted] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Country_Deleted] DEFAULT ('N'),
[CountryLongDesc] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
)
Here CountryId has identity & primary key,Delete has check constraint for value between ‘Y’ & ‘N’.
Function To Import Excel-sheet to sql server may look like
Code:
public static void ImportExcelToDb(String FilePath,string TableName)
{
string strMappedPath = System.Web.HttpContext.Current.Server.MapPath(FilePath);
string ExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMappedPath + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string destinationConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;
OleDbConnection ExcelConnection = new OleDbConnection(ExcelConnectionString);
ExcelConnection.Open();
OleDbCommand Excelcommand = new OleDbCommand("Select * FROM [Country$]", ExcelConnection);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(Excelcommand);
ExcelAdapter.SelectCommand = Excelcommand;
DataSet dsExcel = new DataSet();
ExcelAdapter.Fill(dsExcel);
ExcelConnection.Close();
SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnectionString);
bulkCopy.DestinationTableName = TableName;
bulkCopy.ColumnMappings.Add("CountryName", "CountryName");
bulkCopy.ColumnMappings.Add("CountryCapital", "CountryCapital");
bulkCopy.ColumnMappings.Add("CountryCurrency", "CountryCurrency");
bulkCopy.ColumnMappings.Add("CountryCurrencySymbol", "CountryCurrencySymbol");
bulkCopy.ColumnMappings.Add("CreatedDatetime", "CreatedDatetime");
bulkCopy.ColumnMappings.Add("LastAccessDatetime", "LastAccessDatetime");
bulkCopy.ColumnMappings.Add("Deleted", "Deleted");
bulkCopy.ColumnMappings.Add("CountryLongDesc", "CountryLongDesc");
bulkCopy.WriteToServer(dsExcel.Tables[0]);
}
In this function I am passing ‘FilePath’ as first param which is relative path of our .xls(say book.xls) file.This file has one sheet Named Country which will act as source for data
How book.xls look like:
CountryName CountryCapital CountryCurrency CountryCurrencySymbol CreatedDatetime LastAccessDatetime Deleted CountryLongDesc
Indiana Delhi Rupee Rs 2010-08-03 2010-08-03 N India
Pokistan Islamabad Rupee Rs 2010-08-03 2010-08-03 N Pakistan
Nopel Kathmandoo Rupee Rs 2010-08-03 2010-08-04 N Nepal
UZA Washington D.C. Dollar $ 2010-08-03 2010-08-03 N USA
UL London Pound P 2010-08-04 2010-08-04 N England
Now Time to Export Data from Sql Server to Excel sheet.
Here are useful functions that I am using for this purpose
Code:
public static void ExportFromDbToExcel(string TableName)
{
DataSet ds;
string StrConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;
IDBManager dbManager = new DBManager(DataProvider.MSSQL, StrConnectionString);
dbManager.Open();
string strCmd = "select CountryName,CountryCapital,CountryCurrency,CountryCurrencySymbol,CONVERT(datetime,CreatedDatetime,120)CreatedDatetime,CONVERT(datetime,LastAccessDatetime,120) LastAccessDatetime from " + TableName;
ds = dbManager.ExecuteDataSet(CommandType.Text, strCmd);
dbManager.Close();
MSSQLHelper.ExportDataSetToExcel(ds, TableName);
}
public static void ExportDataSetToExcel(DataSet ds, string filename)
{
HttpResponse response = HttpContext.Current.Response;
filename = filename + ".xls";
response.Clear();
response.Charset = "";
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
using (StringWriter stringwriter = new StringWriter())
{
using (HtmlTextWriter htmlwriter = new HtmlTextWriter(stringwriter))
{
GridView gv = new GridView();
gv.DataSource = ds.Tables[0];
gv.DataBind();
gv.RenderControl(htmlwriter);
response.Write(stringwriter.ToString());
response.End();
}
}
}
The second function ‘ExportDataSetToExcel’ actually convert dataset to excel sheet,first function query the table from database read the required data in dataset & pass this dataset to second function which do needfull
Sometime we need to pass data to & fro from excel sheet and SQL server. To accomplish this task we can write two functions that one that import data from excel sheet to your table in database here we can use bulk copy class. We could Column mapping to map column from excel sheet to column from database.
Many time we have Identity on First column we should not include that column in our excel sheet which we are going to import into database table. We need to have fair understanding of constraint on column like unique key constraint, check constraint that should not get violated by data in excel sheet.
We will try out our trick on country table which can be created as follows
CREATE TABLE [dbo].[Country](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryCapital] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryCurrency] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryCurrencySymbol] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedDatetime] [datetime] NULL,
[LastAccessDatetime] [datetime] NULL,
[Deleted] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Country_Deleted] DEFAULT ('N'),
[CountryLongDesc] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
)
Here CountryId has identity & primary key,Delete has check constraint for value between ‘Y’ & ‘N’.
Function To Import Excel-sheet to sql server may look like
Code:
public static void ImportExcelToDb(String FilePath,string TableName)
{
string strMappedPath = System.Web.HttpContext.Current.Server.MapPath(FilePath);
string ExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMappedPath + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string destinationConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;
OleDbConnection ExcelConnection = new OleDbConnection(ExcelConnectionString);
ExcelConnection.Open();
OleDbCommand Excelcommand = new OleDbCommand("Select * FROM [Country$]", ExcelConnection);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(Excelcommand);
ExcelAdapter.SelectCommand = Excelcommand;
DataSet dsExcel = new DataSet();
ExcelAdapter.Fill(dsExcel);
ExcelConnection.Close();
SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnectionString);
bulkCopy.DestinationTableName = TableName;
bulkCopy.ColumnMappings.Add("CountryName", "CountryName");
bulkCopy.ColumnMappings.Add("CountryCapital", "CountryCapital");
bulkCopy.ColumnMappings.Add("CountryCurrency", "CountryCurrency");
bulkCopy.ColumnMappings.Add("CountryCurrencySymbol", "CountryCurrencySymbol");
bulkCopy.ColumnMappings.Add("CreatedDatetime", "CreatedDatetime");
bulkCopy.ColumnMappings.Add("LastAccessDatetime", "LastAccessDatetime");
bulkCopy.ColumnMappings.Add("Deleted", "Deleted");
bulkCopy.ColumnMappings.Add("CountryLongDesc", "CountryLongDesc");
bulkCopy.WriteToServer(dsExcel.Tables[0]);
}
In this function I am passing ‘FilePath’ as first param which is relative path of our .xls(say book.xls) file.This file has one sheet Named Country which will act as source for data
How book.xls look like:
CountryName CountryCapital CountryCurrency CountryCurrencySymbol CreatedDatetime LastAccessDatetime Deleted CountryLongDesc
Indiana Delhi Rupee Rs 2010-08-03 2010-08-03 N India
Pokistan Islamabad Rupee Rs 2010-08-03 2010-08-03 N Pakistan
Nopel Kathmandoo Rupee Rs 2010-08-03 2010-08-04 N Nepal
UZA Washington D.C. Dollar $ 2010-08-03 2010-08-03 N USA
UL London Pound P 2010-08-04 2010-08-04 N England
Now Time to Export Data from Sql Server to Excel sheet.
Here are useful functions that I am using for this purpose
Code:
public static void ExportFromDbToExcel(string TableName)
{
DataSet ds;
string StrConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;
IDBManager dbManager = new DBManager(DataProvider.MSSQL, StrConnectionString);
dbManager.Open();
string strCmd = "select CountryName,CountryCapital,CountryCurrency,CountryCurrencySymbol,CONVERT(datetime,CreatedDatetime,120)CreatedDatetime,CONVERT(datetime,LastAccessDatetime,120) LastAccessDatetime from " + TableName;
ds = dbManager.ExecuteDataSet(CommandType.Text, strCmd);
dbManager.Close();
MSSQLHelper.ExportDataSetToExcel(ds, TableName);
}
public static void ExportDataSetToExcel(DataSet ds, string filename)
{
HttpResponse response = HttpContext.Current.Response;
filename = filename + ".xls";
response.Clear();
response.Charset = "";
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
using (StringWriter stringwriter = new StringWriter())
{
using (HtmlTextWriter htmlwriter = new HtmlTextWriter(stringwriter))
{
GridView gv = new GridView();
gv.DataSource = ds.Tables[0];
gv.DataBind();
gv.RenderControl(htmlwriter);
response.Write(stringwriter.ToString());
response.End();
}
}
}
The second function ‘ExportDataSetToExcel’ actually convert dataset to excel sheet,first function query the table from database read the required data in dataset & pass this dataset to second function which do needfull
