C# .NET - insert or update sql tables using excel data

Asked By anu sharma on 05-Feb-06 10:33 AM
I need to insert or update sql tables using excel data. If it was just inserting I would have done it with DTS. But since updation is involved I have to write code.  The The tables are pretty big some of them consist about 150 fields. Sometimes I may need to update only 10 of them. The input data will be in an excel sheet. 

Can someone give me an idea about how to do this or provide some sample code in vb.net or c#??

first of all get the - Asked By Venkat K on 05-Feb-06 11:25 AM

data to a datatable from the excel sheet

   public Boolean  OpenConnection()
        {

            try
            {
                this.ExcelConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.ExcelFile  + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
                this.ExcelConnection.Open();
                this.Current_msg = "SUCCESS - Connection opened to Excel";
            }
            catch(Exception ex)
            {
                //this.Current_msg = "ERROR : " + ex.Message;
                MessageBox.Show("Error " + ex.ToString() + "is occurred");
                return false;
            }
            return true;
        }

        //Close Excel Connection
        public Boolean CloseConnection()
        {
            try
            {
                this.ExcelConnection.Close();
                this.ExcelConnection = null;
                this.Current_msg = "Success - Connection closed";
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error " + ex.ToString() + "is occurred");
            }
            return true;

        }

        public DataTable GetData()
        {
            try
            {
                DataTable targetData;
                OleDbCommand cmd = new OleDbCommand("select Party_Code, Scrip_Code, Net from [" + SheetName + "$]");
                cmd.Connection = this.ExcelConnection;
                OleDbDataAdapter daexcel = new OleDbDataAdapter();
                daexcel.SelectCommand = cmd;
                targetData = new DataTable();
                daexcel.Fill(targetData);
                this.Current_msg = "Success - Extracted Data";
                return targetData;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Fails to extract Data" + ex.ToString());
                return null;
            }

        }
        #endregion

and then to update , insert , and delete from the datatable see this link
http://www.akadia.com/services/dotnet_update_form.html

thanks rajesh - Asked By anu sharma on 05-Feb-06 11:32 AM

thanks rajesh, this helps