TechyMedia

A .NET Technology blog | .NET,C# Progamming,C# tutorials,WPF WCF|Software Engineer

NAVIGATION - SEARCH

Import Excel To SQL Server using DataTable as data Type

Export Data From Excel To SQL Server

Recently I was working on an application where in I need to scan a specific folder in file system for Excel files and export the Excel data to SQL server database table.This was to implement as a windows service with a timer which scans the folder at specific time interval and read excellsheets present and Export the same.

Eventhough there is bulk export option to export to database from Excel,CSV, .txt etc I was not able to use the same since there were some extra colums to be inserted in the same database table which need to be fetched from some other table during the insertion process.Also there need individual field validations before insertion.So a row by row approach was essential.

Most of the time, in such situations we end up in doing by executing an insertion stored procedure in a loop.Unfortunately if the number of records to be inserted is huge this approach will take much long time and become unreliable in situations.

My requirement was to send the excel records as a recordset to the stored procedure and process this recordset and insert record by record from stored procedure.

I came across 3 different ways for doing it.There are even more ways probably.

1) Sending the record set in xml format to stored procedure. Click here to see an article on this.

2) Sending the recordset to stored procedure as a DataTable with a user defined Table DataType.This was newly introduced from SQL Server 2008

3) Apart from that if you are familiar with DTS,SSIS packages etc. this application can be achieved in a different manner.

Here I am going to explain step by step approach of inserting excel data as DataTable.

Set up the required database items:

Create the Database Table to insert excel data.

A basic Employee table with ID as an identity field.

Create Table  [dbo].[Employee]
  ( 
	[EmpID] int identity(1,1),
	[EmpName] varchar (50) Not Null,
	[Dob] DateTime Not  Null,
	[JoinDate] DateTime Null,
	[Sal] Decimal (10, 2) Null,
  )

Create a userdefined data type as Table

Create a User defined datat type exactly similar to the fields of Excel sheet.

Create Type [dbo].[typEmploee] as Table
( 
	[EmpName] varchar (50),
	[Dob] DateTime,
	[JoinDate] DateTime,
	[Sal] Decimal (10, 2),
)

Create the stored procedure which take DataTable as parameter and do the insertion. replace DbName with your Database name.

USE [DbName]
Go
Create Procedure [dbo].[spInsertEmployee]
 @dataTableEmployee typEmployee readonly
 As
 Begin
 SET NOCOUNT ON;
 Insert into [Employee]
 (
	[EmpName],
	[Dob],
	[JoinDate],
	[Sal],
 ) 
  select 
   	 EmpName,
     	Dob,
     	JoinDate,
     	Sal
   	 from  @dataTableEmployee ;      
End

Create a new windows service project

File > New Project > Windows > Windows Service

See How to create a Windows Service in C# for more detailed article on windows service.

 

Create a DataTable with required columns

Write a method to create DataTable

private DataTable createDataTable()
{
      DataTable dtblExport = new DataTable();
      DataColumn col1 = new DataColumn("EmpName");
      col1.DataType = Type.GetType("System.String");
      DataColumn col2 = new DataColumn("Dob");
      col2.DataType = Type.GetType("System.DateTime");
      DataColumn col3 = new DataColumn("JoinDate");
      col3.DataType = Type.GetType("System.DateTime");
      DataColumn col4 = new DataColumn("Sal");
      col3.DataType = Type.GetType("System.Int32");

      dtblExport.Columns.Add(col1);
      dtblExport.Columns.Add(col2);
      dtblExport.Columns.Add(col3);
      dtblExport.Columns.Add(col4);
      return dtblExport; 
}

or simpler way ,

dtblExport.Columns.Add("EmpName",
                    Type.GetType("System.String"));
dtblExport.Columns.Add("Dob",
                    Type.GetType("System.DateTime"));
dtblExport.Columns.Add("JoinDate",
                    Type.GetType("System.DateTime"));
dtblExport.Columns.Add("Sal",
                    Type.GetType("System.Decimal"));

Set up the service and timer

In Service.cs write

public ExportService()
{
this.ServiceName = "ImportService";
InitializeComponent();
}
DataTable dtEscr;
Timer watchTimer;
int interval = 10; 
Dictionary<string, string> configurations = null;
string[] sheetArray = null;
string folderIdentifier = null;
protected override void OnStart(string[] args)
{
    watchTimer= new Timer(100000);
    watchTimer.Elapsed += new ElapsedEventHandler
                                      (watchTimer_Elapsed); 
    watchTimer.Start(); 
}

Create Excel connection string,Open Excel OLEDB Connection , Read Excel sheet

Write methods to open the excel connection , Read the Excelsheet and populate the DataTable during timer elapsed event and call the stored procedure with DataTable as parameter

private void tickTack_Elapsed(object sender, 
                                ElapsedEventArgs e) 
{
    string workSheetName = "Sheet1$";
    OleDbConnection connection = null;
    string[] files = Directory.GetFiles(e.Name);
    for (int i = 0; i < files.Length; i++)
    {
 	    //Better to use string builder 
        //instead of string handling

        string excelFilePath = files[i].ToString();
        string begins = 
            "Provider= Microsoft.Jet.OLEDB.4.0;Data Source=";            
        string ends = 
          ";Extended Properties= \'Excel 8.0;HDR=Yes;IMEX=1;'";
        string ExcelConnectionString = 
                          begins + excelFilePath + ends;
        connection = new OleDbConnection
                                 (ExcelConnectionString);
        connection.Open();
        DataTable dtExportExcel = CreateDataTable();
        OleDbCommand com = new OleDbCommand("Select * 
                from [" + workSheetName + "]", connection);
        OleDbDataReader dr = com.ExecuteReader();

        if (dr != null && dr.HasRows)
        {
            while (dr.Read())
            {
                if (!(string.IsNullOrEmpty(dr["EmpName"]
                                            .ToString())))
                {
                    DataRow drow = dtExportExcel.NewRow();
                    //Update data table from Excel data
                    drow["EmpName"] = Convert.ToString
                                          (dr["EmpName"]);
                    drow["Dob"] = Convert.
                                    ToDateTime(dr["Dob"]);
                    drow["JoinDate"] = 
                        Convert.ToDateTime(dr["JoinDate"]);
                    drow["Sal"] = 
                            Convert.ToDecimal(dr["Sal"]);
                    dtExportExcel.Rows.Add(drow);                           

                }
            }

            //call method which execute stored 
            //procedure for Export
            ExportToDb(dtExportExcel);
        }
            dr.Close();
            dr.Dispose();
	    }
        if (connection.State != ConnectionState.Closed) 
                    connection.Close();
        if (connection != null) connection.Dispose();
   }
}

Import Excel To SQL Server Table

private void ExportToDb(DataTable dtExportExcel)
{
    SqlCommand exportCommand = null;
            
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = 
    "Data Source=Server\\SQLEXPRESS;
       Initial Catalog=ExcelExportDb;Integrated Security=True"; 
    exportCommand = new SqlCommand();
    exportCommand.CommandType = CommandType.StoredProcedure;
    exportCommand.CommandText = "spInsertClmPsEscrAdv";
    SqlParameter param = exportCommand.Parameters.
              AddWithValue("@dataTableEmployee", dtExportExcel);              
    exportCommand.Connection = connection;
    connection.Open();
    exportCommand.ExecuteNonQuery();

    if (exportCommand != null) exportCommand.Dispose();
    if (connection.State != ConnectionState.Closed) 
            connection.Close();
    if (connection != null) connection.Dispose();
           
}

Project Installer

Right click on Service.cs Design window.

Click on ServiceProcessInstaller1 and open properties Window (F4).Set Account property as LocalSystem.

Debug the application

Please read article How to debug Windows service .If you are not comfortable with the methods then you can make your application logic working in normal windows application and later copy the logics to windows service.

Build and deploy the service

Once tested Fine Build the application.Install the application using installutil.exe tool.

installutil Service.exe ( Run from VisualStudio command prompt with full exe path)

Click here to read detailed article on installing windows service.

After installing, go to Controlpanel > Administrative Tools > Services . Installed service will be visible in the list. Right click and Start the service.You are ready to go.

For uninstall the windows service use

installutil /u Service.exe.

Free Job Schedulers for .NET

 

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading