ahmed salah

ahmed salah

  • 1.6k
  • 530
  • 121.6k

How to Import data to sql from Excel data table by c#

Mar 7 2018 6:13 PM

Problem

How to write code import data table to SQL server 2012 .

Details

I have excel sheet name InvoiceData.xlsx have two columns

  1. UnitCode    CurrentMeterReading  
  2. 21544                2900  
  3. 22152                9000  
  4. 19822                9200  

I get success data from excel sheet to datatable as following :

  1. public System.Data. DataTable Showdataprint()  
  2.         {  
  3.   
  4.             OleDbConnection con = new OleDbConnection(connectionString);  
  5.             con.Open();     
  6.             string str = @"SELECT  [UnitCode],[CurrentMeterReading] FROM  [Sheet1$]";  
  7.             OleDbCommand com = new OleDbCommand();  
  8.             com = new OleDbCommand(str, con);  
  9.             OleDbDataAdapter oledbda = new OleDbDataAdapter();  
  10.             oledbda = new OleDbDataAdapter(com);  
  11.             DataSet ds = new DataSet();  
  12.             ds = new DataSet();  
  13.             oledbda.Fill(ds,"[Sheet1$]");  
  14.             con.Close();  
  15.             System.Data.DataTable dt = new System.Data.DataTable();  
  16.             dt = ds.Tables["[Sheet1$]"];  
  17.             return dt;  
  18.   
  19.         }  

I get successfully data from excel to Datatable Now I need to Import data from datatable to sql server 2012 TableName : WahInvoice PK : Serial

  1. CREATE TABLE [dbo].[WAHInvoice](  
  2.     [Serial] [intNOT NULL,  
  3.     [Year] [intNULL,  
  4.     [Month] [intNULL,  
  5.     [UnitCode] [intNULL,  
  6.     [CurrentReadingDate] [dateNULL,  
  7.     [CurrentMeterReading] [decimal](18, 2) NULL,  
  8.  CONSTRAINT [PK_WAHInvoice_1] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [Serial] ASC  
  11.   
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  13. ON [PRIMARY]  

I need to know how to Import data from excel to sql .

suppose i have data on sql in table WahInvoice

with max serial 2000 this meaning data will be as following :

  1. Serial  UnitCode          CurrentMeterReading  
  2.  2001   21544                2900  
  3.  2002   22152                9000  
  4.  2003   19822                9200  

and all values for columns remaining will be null .

Actually what i write for Import data to sql under import button Under Import button click i write as following :

  1. System.Data.DataTable dt = new System.Data.DataTable();  
  2.                     dt = Showdataprint();  
  3. //How to import datatable to sql server 2012  

Answers (4)