Member Avatar for 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");
  • 3 Contributors
  • 14 Replies
  • 1K Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by pitic

Recommended Answers

All 14 Replies

Member Avatar for 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);
Member Avatar for 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

Member Avatar for pitic

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

Member Avatar for ChrisHunter

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

Member Avatar for danimischiu

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

Member Avatar for pitic

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

Member Avatar for 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..

Member Avatar for 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].

Edited by pitic
Member Avatar for 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:(.

Member Avatar for 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.

Member Avatar for 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?

Member Avatar for 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);
    }
}
Edited by pitic because: modified the reference paraneter
Member Avatar for 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

Member Avatar for 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...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.