Getting Coloum name from excel sheet and creating database table Part 2 through c#
Hi all
In my previous part related to this issue i only show how to insert data from excel sheet to sql server
for reference that please see
http://dotnetspider.com/resources/22915-Code-Geting-column-name-Excel-sheet-create.aspx
In this part i have done the whole work done;Let see how it works
1.my code read the excel sheet to get its schema
2.then create SQL database table (name given by user)in database
3.then insert coloum name in table present in excel sheet
4.then inserting the coloum value of excel sheet in database table
using sqlbulkcopy.
Note:- u must first create database and your excel sheet should has it's coloum name in first row.
//VARIOUS OBJECTS AND CONNECTION STRING FOR EXCEL AND SQL
string Excel = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mca23.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
string SQL = "Data Source=.; Initial Catalog=hr;uid=sa;pwd=;Integrated Security=True";
SqlCommand cmd1;
SqlCommand cmdDatabase;
SqlConnection contoSql ;
OleDbConnection contoExcel;
OleDbCommand cmd;
//Extended Properties=""Excel 8.0;
THIS PART TELL OLEDB PROVIDER THAT IT IS GOING TO READ EXCEL SHEET
//HDR=YES;
THIS TELL DATA IS FROM THE FIRST ROW.
//CODE FOR GETTING SCHEMA
contoExcel = new OleDbConnection(Excel);
contoExcel.Open();
cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", contoExcel);
OleDbDataReader rdr = cmd.ExecuteReader();
DataTable schema = rdr.GetSchemaTable();
//[Sheet1$]
BY THIS SHEET1 IS TREATED LIKE DATABASE TABLE
//CREATING DATABASE TABLE
contoSql = new SqlConnection(SQL);
cmdDatabase = new SqlCommand("Create table "+txtdbname.Text+" ("+"id varchar(100));", contoSql);
contoSql.Open();
try
{
cmdDatabase.ExecuteNonQuery();
}
catch (Exception error)
{
MessageBox.Show("Table already exist,Please enter another name");
txtdbname.Focus();
txtdbname.Text = "";
}
contoSql.Close();
// I CREATED TABLE WITH ONE DEFAULT COLOUM BECAUSE IT NOT LETTING ME TO CREATING EMPTY TABLE
//CODE FOR INSERTING COLOUM PRESENT IN SHEET TO DATABASE TABLE
foreach (DataRow row in schema.Rows)
{
int i = 0;
foreach (DataColumn col in schema.Columns)
{ //
if (i == 0)
{
name = Convert.ToString(row[0]);
contoSql.Open();
cmd1 = new SqlCommand("ALTER TABLE "+dbname.TEXT+" ADD "+name+" varchar(1000);", contoSql);
cmd1.ExecuteNonQuery();
contoSql.Close();
}
i = i + 1;
}
}
//DROPING COLOUM ID BECAUSE IT IS NOT PRESENT IN SHEET OF EXCEL
cmd1.CommandText = "ALTER TABLE "+dbname+" DROP COLUMN id;";
cmd1.Connection = contoSql;
contoSql.Open();
cmd1.ExecuteNonQuery();
//INSERTING DATA FROM SHEET TO DATABASE.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(contoSql))
{
bulkCopy.DestinationTableName = txtdbname.Text;
bulkCopy.WriteToServer(rdr);
contoSql.Close();
}
MessageBox.Show("Data inserted");
FOR ANY SUGGESTION PLEASE RESPONSE ME
REGARD Abhinav Misra
Hi,
This is great piece of code I have one question though, you are adding the column that is missing in the table and bulk copying the data I got that. what if the order of the columns does not match with the order of data. say 2nd column in the file is missing and you alter table which adds the column at the end of the columns. but according to the file this last column's data is in the 2nd place.