write/update local data base from datagridview

Question

i have a problam to insert data from datagridview to local data base, in my program - im uploading from csv/excel file data to datagridview and showing it to the user .

i have 3 localDB in my program that connected to each other by foreign keys and primary key , i want to insert some columns from the datagridview to my localDB and if that data already exist in my localDB ,dont insert it .

so my questions are:

1.how can i insert properly data from datagridview to my localDB .

2.if its already exist,i dont want to insert it, how to?

this is my code for uploading the file to show in the datagridview :

 private void upload_to_datagridview(object sender, EventArgs e)
    {
        try
        {
            string Pathconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";";
            OleDbConnection conn = new OleDbConnection(Pathconn);
            OleDbDataAdapter myDataAdaptor = new OleDbDataAdapter("Select * from [" + SheetName + "$]", conn);
            System.Data.DataTable dt = new System.Data.DataTable();
            myDataAdaptor.Fill(dt);
            dataGridView1.DataSource = dt;
        }

        catch(Exception)
        {
            MessageBox.Show("no workbook was uploaded please upload again!");
            wb.Close(false);
            excel.Quit();
        }

        wb.Close(false);
        excel.Quit();
    }

i have seen some codes for updating data from datagridview to localDB , but it was the whole datagridview , all of the columns , i want to take specific columns from the datagridview and to update specific columns from the dataDB .

please if someone explain me how to do it properly beacause i cant find anything.

thanks!

There is how to insert using your approach https://msdn.microsoft.com/pl-pl/library/system.data.oledb.oledbdataadapter.insertcommand(v=vs.110).aspx — Paweł Swajdo, Jan 25 '17 at 09:49
Ad.2 I assume that you do not have ID's in your dataTable so if you don't want to insert existing rows into Db you need to check it before insert, of course you have to tell which columns can be repeated and which not. another approach is to set unique key on some table columns and call insert in Try{}catch{} :) — Paweł Swajdo, Jan 25 '17 at 09:53

MAHDI · Accepted Answer · 2017-01-26T05:21:19.483

I will provide an alternative solution for your case , it might help and change your mind to think in diffrent way . The solution is Load and Save an XML instead of csv/Excel . You can load the data only one time into Datagrid then save it XML . Then you have your XML Data now and you can load and manipluate into Datagrid container then at the end save it into your local machine.

Let me know if you like the approach so i provide some code

Please follow the steps :
1) You need load csv/excel one time only and make sure to check update check box for datagridview , then you save it in XML 
2) Save it in XML format , use Write XML Code
3) Load it again by using Read XML Code
4) Update any data in any field then save it by using (Write XML Code)

//Read XML

DataSet ds1 = new DataSet();
ds1.ReadXml("d:/mahdi.xml");
dataGridView1.DataSource = ds1;
dataGridView1.DataMember = "tlbName";

//Write XML

 DataSet ds2 = new DataSet();
    dataGridView1.DataSource = ds2;
    XmlTextWriter newXml = new XmlTextWriter("d:/mahdi.xml", Encoding.UTF8);
    ds2.WriteXml(newXml);

//Display XML in TextBox

 DataSet ds3 = new DataSet();
    StringWriter swXML = new System.IO.StringWriter();
    ds3.WriteXmlSchema(swXML);
    textBox1.Text = swXML.ToString();

Let me know if it did not work

yes thanks alot, it will help me if you can – shlezz Jan 25 '17 at 13:33 — shlezz, Jan 25 '17 at 13:33

write/update local data base from datagridview

1 Answers1