From Excel Convert into sqlserver database

Question

i am having data in excel From Excel i want to convert into sql server Database.how to do. Regards Rao.

Lacy Michelle · Answer

I think first you need to export excel to datatable please see below code: Workbook workbook = new Workbook(); workbook.LoadFromFile(DataTableSample.xls); Worksheet sheet = workbook.Worksheets[0]; DataTable dataTable = sheet.ExportDataTable(); then connect your datatble to sql sever. but you need a c# excel component to perform above code.of courseif you also need to import datatable from database to excel this tool also can help you.

Satyapriya Nayak · Answer

Untitled Page Insert Data         using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.IO; using System.Data.OleDb; using System.Data.SqlClient; using System.Text; namespace Excel_data_gridview_db1 { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; protected void insertdata_Click(object sender EventArgs e) { OleDbConnection conn = new OleDbConnection(@Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Server.MapPath(test.xls) + ;Extended Properties=Excel 8.0); OleDbCommand com = new OleDbCommand(select * from [Sheet1$] conn); conn.Open(); OleDbDataReader reader = com.ExecuteReader(); string Name = ; string RollNumber = ; while (reader.Read()) { Name = valid(reader 0); RollNumber = valid(reader 1); insertdataintosql(Name RollNumber); } conn.Close(); l1.Text = Records Inserted successfully; } protected string valid(OleDbDataReader reader int str1) { object val = reader[str1]; if (val != DBNull.Value) return val.ToString(); else return Convert.ToString(0); } public void insertdataintosql(string Name string RollNumber) { SqlConnection conn = new SqlConnection(connStr); SqlCommand com = new SqlCommand(); com.Connection = conn; com.CommandText = insert into student(NameRollNumber)values(@Name@RollNumber); com.Parameters.Add(@Name SqlDbType.VarChar).Value = Name; com.Parameters.Add(@RollNumber SqlDbType.VarChar).Value = RollNumber; com.CommandType = CommandType.Text; conn.Open(); com.ExecuteNonQuery(); conn.Close(); } } }