Data Exchange between SQL server & Excel

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