Bulk update from a datatable sql server compact

[danimischiu]

Hy all,
I'm new at using sql server compact,so I don't know yet if what I want to do is possible:)?
I have the following setup:
1.Datatable created from a table from my database(this is the table that I want to update later);
2.Make modification on datatable and save it;
3.Bulk update table using the modified datatable;
Is it possible??Please help me..:)
This is what I've tried so far:

SqlCeConnection conn = new SqlCeConnection(connection);
            SqlCeDataAdapter da = new SqlCeDataAdapter();
            da.SelectCommand = new SqlCeCommand("select * from MyTable", conn);
            SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds, "MyTable");
            DataTable dt = ds.Tables["MyTable"];
//modify datatabele;

dt.Rows[i].SetField("cui", cui);
dt.Rows[i].SetField("denumire", denumire);
dt.Rows[i].SetField("nr_reg_com", regCom);
dt.Rows[i].SetField("adresa", adresa);
dt.Rows[i].SetField("fax", fax);

da.update(ds,"MyTable");

[pitic]

Try this after filling the DataTable (in your for loop or whatever you use to increment i)

var rowsFound = dt.Select("<unique identifier from table>=" + value);
rowsFound[0]["cui"] = cui;
rowsFound[0]["denumire"] = denumire;
...
da.update(dt);

[ChrisHunter]

You can create a table type in SQL server 2008 onwards which can be used to pass a data table into a stored procedure which should take up less resources and time than executing the stored proc for each row you need to update. I tried to use them in 2005 but creating types was only introduced in 2008. Have a look at the links below about creating types in SQL server:

http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

http://msdn.microsoft.com/en-us/library/ms175007.aspx

[pitic]

He is using SQL Compact Edition which does not support stored procedures (as I know)

[ChrisHunter]

Apologies, once again I didn't read the post properly.

[danimischiu]

Yes SQL Compact Edition which does not support stored procedures:(...and thanks pitic but your solution didn't work for me..:(

[pitic]

Run a step-by-step debug and give us more details about what's not working. Or paste full code.

[danimischiu]

First of all:

//1.procedure to create datatable  
  private DataTable CreateTable(int x)
        {
            DataTable tbl = new DataTable();
            tbl.Columns.Add("cui", typeof(string));
            tbl.Columns.Add("denumire", typeof(string));
            tbl.Columns.Add("nr_reg_com", typeof(string));
            tbl.Columns.Add("adresa", typeof(string));
            tbl.Columns.Add("fax", typeof(string));
            tbl.Columns.Add("telefon", typeof(string));
            tbl.Columns.Add("judet", typeof(string));
            tbl.Columns.Add("cod_postal", typeof(string));
            tbl.Columns.Add("data_start_tvai", typeof(string));
            tbl.Columns.Add("data_end_tvai", typeof(string));
            tbl.Columns.Add("tva_incasare", typeof(string));
            tbl.Columns.Add("platitor_tva", typeof(string));
            tbl.Columns.Add("cif_valid", typeof(string));
            tbl.Columns.Add("verificat", typeof(string));
            tbl.Columns.Add("cod_asis", typeof(string));
            for (int i = 0; i < x; i++)
                tbl.Rows.Add();
            return tbl;
        }

//2.procedure to modify data

   private void Verificare_Online_single(string cui, string cod_asis,int i,DataTable dt)
        {
            SqlCeConnection connection = new SqlCeConnection(Properties.Settings.Default.dbPartnersConnectionString);
            string update = "";
            string tva = "NU";
            string adresa = "";
            string fax = "";
            string denumire = "";
            string tel = "";
            string regCom = "";
            string judet = "";
            string platitorTVA = "NU";
            string codPostal = "";
            string dataS = "";
            string dataE = "";

            try
            {

                if (Main_App.Clase.VerificareCIF.ValidareCIF(cui)) //first where this condition is true
                {
                    string rsp = "";
                    try
                    {
                        using (WebClient client = new WebClient())
                        { rsp = client.DownloadString("server bla bla" + cui); }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message + "   Eroare la aducerea de pe server");
                    }
                    try
                    {
                        if (rsp.Length > 0 && rsp.Trim() != "")
                            rsp = rsp.Remove(rsp.Length - 1, 1);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    if (rsp != null || rsp.Trim() != "")
                    {

                        string mesaj = "";
                        string[] words = rsp.ToString().Split('|'); //putting the server response in an array;

                        //getting data from the array

                        denumire = words[1];
                        adresa = normalizare(words[3]);
                        judet = normalizare(words[5]);
                        regCom = normalizare(words[7]);
                        update = regCom;
                        tel = words[13];
                        fax = words[15];
                        codPostal = words[11];
                        if (words[31].Trim() != "NU")
                            platitorTVA = "DA";

                        // modyfing datatable
                            dt.Rows[i].SetField("cui", cui);
                            dt.Rows[i].SetField("denumire", denumire.Trim());
                            dt.Rows[i].SetField("nr_reg_com", "--");
                            dt.Rows[i].SetField("adresa", adresa);
                            dt.Rows[i].SetField("fax", fax);
                            dt.Rows[i].SetField("telefon", tel);
                            dt.Rows[i].SetField("judet", judet);
                            dt.Rows[i].SetField("cod_postal", codPostal);
                            dt.Rows[i].SetField("data_start_tvai", dataS);
                            dt.Rows[i].SetField("data_end_tvai", dataE);
                            dt.Rows[i].SetField("tva_incasare", tva);
                            dt.Rows[i].SetField("platitor_tva", platitorTVA);
                            dt.Rows[i].SetField("cif_valid", "DA");
                            dt.Rows[i].SetField("verificat", "DA");
                            dt.Rows[i].SetField("cod_asis", cod_asis);

                        //trying  what pitic suggested

                            var rowsFound = dt.Select("<cod_asis>=" + cod_asis); //the field 
                            rowsFound[0]["cui"] = cui;
                            rowsFound[0]["denumire"] = denumire;
                            rowsFound[0]["nr_reg_com"] = regCom.Trim();
                            rowsFound[0]["adresa"] = adresa;
                            rowsFound[0]["fax"] = fax;
                            rowsFound[0]["telefon"] = tel;
                            rowsFound[0]["judet"] = judet;
                            rowsFound[0]["cod_postal"] = codPostal;
                            rowsFound[0]["data_start_tvai"] = dataS;
                            rowsFound[0]["data_end_tvai"] = dataE;
                            rowsFound[0]["tva_incasare"] = tva;
                            rowsFound[0]["platitor_tva"] = platitorTVA;
                            rowsFound[0]["cif_valid"] = "DA";
                            rowsFound[0]["verificat"] = "DA";


                    }
                    else
                    {
                    //changing my datatable with data from the gridcontrol datatable

                        /*DataTable dtx = (DataTable)gridControl.DataSource;
                        dt.Rows[i].SetField("cui", dtx.Rows[i]["cui"].ToString().Trim());
                        dt.Rows[i].SetField("denumire", dtx.Rows[i]["denumire"].ToString().Trim());
                        dt.Rows[i].SetField("nr_reg_com", dtx.Rows[i]["nr_reg_com"].ToString().Trim());
                        dt.Rows[i].SetField("adresa", dtx.Rows[i]["adresa"].ToString().Trim());
                        dt.Rows[i].SetField("fax", dtx.Rows[i]["fax"].ToString().Trim());
                        dt.Rows[i].SetField("telefon", dtx.Rows[i]["telefon"].ToString().Trim());
                        dt.Rows[i].SetField("judet", dtx.Rows[i]["judet"].ToString().Trim());
                        dt.Rows[i].SetField("cod_postal", dtx.Rows[i]["cod_postal"].ToString().Trim());
                        dt.Rows[i].SetField("data_start_tvai", dtx.Rows[i]["data_start_tvai"].ToString().Trim());
                        dt.Rows[i].SetField("data_end_tvai", dtx.Rows[i]["data_end_tvai"].ToString().Trim());
                        dt.Rows[i].SetField("tva_incasare", dtx.Rows[i]["tva_incasare"].ToString().Trim());
                        dt.Rows[i].SetField("platitor_tva", dtx.Rows[i]["platitior_tva"].ToString().Trim());
                        dt.Rows[i].SetField("cif_valid", "NU");
                        dt.Rows[i].SetField("verificat", "DA");
                        dt.Rows[i].SetField("cod_asis", dtx.Rows[i]["cod_asis"].ToString().Trim());
                        */

                       // dt.ImportRow(dtx.Rows[i]);

                    }
                }


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

//3.

   private void Parcurgere_Verificare_Online()
        {
            string cui = "";
            string cod_asis = "";

            SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.dbPartnersConnectionString);
            SqlCeDataAdapter da = new SqlCeDataAdapter();
            da.SelectCommand = new SqlCeCommand("select * from Terti", conn);
            SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds, "Terti");

            DataTable dt = (DataTable)gridControl.DataSource;//ds.Tables["Terti"];
            DataTable dt2 = CreateTable(dt.Rows.Count);
            foreach (System.Data.DataColumn col in dt.Columns) col.ReadOnly = false;
            foreach (System.Data.DataColumn col in dt2.Columns) col.ReadOnly = false;

           // DevExpress.Data.CurrencyDataController.DisableThreadingProblemsDetection = true;

            //trying for the first 100 entries 

            for (int i = 0; i < 100; i++)  //dt.Rows.Count/5
            {
                cui = dt.Rows[i]["cui"].ToString().Trim();
                cod_asis = dt.Rows[i]["cod_asis"].ToString().Trim();
                cui = Main_App.Clase.VerificareCIF.FormatezCIF(cui);

                Verificare_Online_single(cui, cod_asis, i, dt2);

          /*     ThreadStart job = new ThreadStart(() => Verificare_Online_single(cui, cod_asis, i, dt2));
                Thread thread = new Thread(job);
                thread.Start(); 
                here I was trying to use multiple threads..a bump that I hope to fix later 
                 */


            }
            // changing  grid datasource
            gridControl.DataSource = dt2;

            da.Update(ds,"Terti");


            MessageBox.Show("Data Validated");
        }

The unique identifier from table is cod_asis;
Exceptions:

1.when I use
var rowsFound = dt.Select("<cod_asis>=" + cod_asis);
ex:Syntax Error:Missing operand before '<' operator.

2.when I use
var rowsFound = dt.Select("cod_asis=" + cod_asis);
ex:Index was outside the bound of the array

Hope I made some light with what I want to do:) and I'm praying for some helpful answers..

[pitic]

(edited las message as I saw the second line after)

it means it found no rows that match the criteria. Do a check if rowsFound.Length > 0 before setting the values for rowsFound[0].

[danimischiu]

It's strange because when I get:"Index was outside the bound of the array",the rowsFound.Length=0 but
dt.Rows[i]["cod_asis"].ToString() is the same as the value if search for in the datatable:(.

[pitic]

Verificare_Online_single(cui, cod_asis, i, dt2);

You are passing dt2 as parameter to the method, shouldn't it be dt? dt2 is the new empty table and you said you are doing an update.

DataTable dt2 = CreateTable(dt.Rows.Count);

My code will not work since it searches the dt for a row where the value "cod_asis" equals something. And since your parameter is dt2 (empty table) it can't find anything.

[danimischiu]

But as u can see in my code first I set the field for the dt in question, in this case dt2 which is empty..and after that I search for it...so the problem I belive is somewhere.You say I should try to modify directly on the datatable that I take from the gridcontrol?

[pitic]

Here's what I would do (did not write this in an environment so there may be some typos):

//from line 179 - 199
for (int i = 0; i < dt.Rows.Count; i++)
{
    var currentRow = dt.Rows[i];
    cui = currentRow["cui"].ToString().Trim();
    cod_asis = currentRow["cod_asis"].ToString().Trim();
    cui = Main_App.Clase.VerificareCIF.FormatezCIF(cui);
    Verificare_Online_single(cui, cod_asis, ref dt.Rows[i]);        
}

// refactored method
private void Verificare_Online_single(string cui, string cod_asis, ref DataRow dr)
{
    SqlCeConnection connection = new SqlCeConnection(Properties.Settings.Default.dbPartnersConnectionString);
    string update = "";
    string tva = "NU";
    string adresa = "";
    string fax = "";
    string denumire = "";
    string tel = "";
    string regCom = "";
    string judet = "";
    string platitorTVA = "NU";
    string codPostal = "";
    string dataS = "";
    string dataE = "";
    try
    {
        if (Main_App.Clase.VerificareCIF.ValidareCIF(cui)) //first where this condition is true
        {
            string rsp = "";
            try
            {
                using (WebClient client = new WebClient())
                { rsp = client.DownloadString("server bla bla" + cui); }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "   Eroare la aducerea de pe server");
            }
            try
            {
                if (rsp.Length > 0 && rsp.Trim() != "")
                    rsp = rsp.Remove(rsp.Length - 1, 1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            if (rsp != null || rsp.Trim() != "")
            {
                string mesaj = "";
                string[] words = rsp.ToString().Split('|'); //putting the server response in an array;
                //getting data from the array
                denumire = words[1];
                adresa = normalizare(words[3]);
                judet = normalizare(words[5]);
                regCom = normalizare(words[7]);
                update = regCom;
                tel = words[13];
                fax = words[15];
                codPostal = words[11];
                if (words[31].Trim() != "NU")
                    platitorTVA = "DA";

                // modyfing datatable
                dr["cui"] = cui;
                dr["denumire"] = denumire.Trim();
                dr["nr_reg_com"] = "--";
                dr["adresa"] = adresa;
                dr["fax"] = fax;
                dr["telefon"] = tel;
                dr["judet"] = judet;
                dr["cod_postal"] = codPostal;
                dr["data_start_tvai"] = dataS;
                dr["data_end_tvai"] = dataE;
                dr["tva_incasare"] = tva;
                dr["platitor_tva"] = platitorTVA;
                dr["cif_valid"] = "DA";
                dr["verificat"] = "DA";
                dr["cod_asis"] = cod_asis;
            }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

[danimischiu]

Thanks for your help pitic.I've managed to change the datatable:).Now I need to make a bulk update from the datatalbe but i get this:
"There was an error parsing the query. [ Token line number = 1,Token line offset = 21,Token in error = DEFAULT ]" ;

SqlCeConnection conn = new SqlCeConnection(myConection);
                    SqlCeDataAdapter da = new SqlCeDataAdapter();
                    da.SelectCommand = new SqlCeCommand("select * from Table", conn);
                    SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
         //Datatable is dt and is created;
                    using (cb)
                    {
                    da.Fill(dt);
                    conn.Open();
                    string delete = "delete from Table";
                    SqlCeCommand cmdDelete = new SqlCeCommand(delete, conn);
                    cmdDelete.ExecuteNonQuery();
                    da.Update(dt);
                    }

What should I do beacause I've searched a loooooooot for an answer:(..and didn't find anything

[pitic]

Perhaps because you are using "Table" as table name which is a reserved word. I'm not sure since I cannot test it right now. But just a thought...