Import Data from Excel to a SQL Server 2000 use c#

Question

hai .... My name is ngurah please help about Import Data from Excel to a SQL Server 2000 use c#?? thanks

Satyapriya Nayak · Answer

Hi Ngurah, Try this... 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(Name,RollNumber)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(); } } } Thanks If this post helps you mark it as answer

ngurah arta · Answer

private void button1_Click(object sender, EventArgs e) { try { System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + button2.Text + "';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$] ", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource =DtSet.Tables[0]; MyConnection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button2_Click(object sender, EventArgs e) {/* OpenFileDialog dlg = new OpenFileDialog(); // Show all files dlg.Filter = string.Empty; dlg.ShowDialog(); */ /* all file dak terfokus OpenFileDialog dlg = new OpenFileDialog(); // Filter by Word Documents OR Excel Worksheets OR PowerPoint Presentations // OR Office Files // OR All Files *dlg.Filter = "Word Documents|*.doc|Excel Worksheets|*.xls|PowerPoint Presentations|*.ppt" + "|Office Files|*.doc;*.xls;*.ppt" + "|All Files|*.*"; dlg.ShowDialog(); */ OpenFileDialog dlg = new OpenFileDialog(); // Filter by Excel Worksheets dlg.Filter = "Excel Worksheets|*.xls"; dlg.ShowDialog(); if (dlg.ShowDialog() == DialogResult.OK) { button2.Text = Convert.ToString(dlg.FileName); //pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage; //pictureBox1.Image = Image.FromFile(openFile.FileName); } }

ngurah arta · Answer

Thanks Satyapriya Nayak but how to use windows form? I have project and show the data like this ,I hope, to Import Data from Excel to a SQL Server 2000 use c#??

Import Data from Excel to a SQL Server 2000 use c#

Answers (3)