Update Sql Table from DataTable

[dinesh.isuranga]

I Have a Just Simple Question. But It Made me Confuse. I have a DataTable Which has bind to textBoxes and Other Items. therfore when I made a change through txtBox or Other Items it reflect to dataTable. what I have to do is Saving updated Datatable to Sql Table. I tried to use Adapter. but we have to use a select Command there we can't use it.. please help me..

[Narue]

Provided the table has a primary key, you can easily generate the SQL to update with a SqlCommandBuilder and actually do the updates with a SqlDataAdapter:

using (var cmd = new SqlCommand(selectQuery, connection)) {
    using (var da = new SqlDataAdapter(cmd)) {
        using (var cb = new SqlCommandBuilder(da))
            da.Update(table);
    }
}

[dinesh.isuranga]

dear friend thanks for replying me..
but friend as i mention you before my datatable is alrady updated through binding components.. so if i use this code my datatable will override with this select ststement.. is nt it.. threfore need a way without having a select query.. please help me.. thanxs buddy..

[Narue]

so if i use this code my datatable will override with this select ststement

That's not correct. The select query is required by SqlCommandBuilder for generating insert, update, and delete statements. The select statement isn't run to refill the table unless you call the Fill method of your data adapter after updating. So da.Update(table) only runs insert, update, and delete statements.

threfore need a way without having a select query

You can build the SqlCommand objects for insert, update, and delete manually, then add them to the data adapter before calling Update. That has the same effect as using a SqlCommandBuilder, except it's more work on your part.

[dinesh.isuranga]

This did nt work friend.

SqlCommand cmd = new SqlCommand("select * from dbo.bind_tab", conn.createconnection());
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            da.Update(dt);

i used this way but not suit.

String UpdateQuery = @"update dbo.bind_tab
                                            set 
                                            empname = '" + dt.Rows[0]["empname"].ToString() + "', " +
                                " empage = " + Int32.Parse(dt.Rows[0]["empage"].ToString()) + ", " +
                                " empsinhala = '" + dt.Rows[0]["empsinhala"].ToString() + "', " +
                                " emptamil = '" + dt.Rows[0]["emptamil"].ToString() + "', " +
                                " empother = '" + dt.Rows[0]["empother"].ToString() + "' " +
                                " where eid = '" + cmbEmpID.Text.ToString() + "'";
            db.UpdateRecord(UpdateQuery);

db.UpdateRecord(UpdateQuery) is a methotd to update record...

public void UpdateRecord(String UpdateQuery)
{
try
{
adapter.UpdateCommand = new SqlCommand(UpdateQuery, conn.createconnection());
adapter.UpdateCommand.ExecuteNonQuery();
conn.deleteconnection();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
conn.deleteconnection();
}
}

need help friend...

[Narue]

"Did not work" is not useful information. Did you get an error? Did it run but fail to update your tables?

[kvprajapati]

Read Narue's second post. First of all, populate the datatable instance via Fill method, do some insert/delete/update actions on datatable and invoke Update method if you want to commit these changes.

SqlCommand cmd = new SqlCommand("select * from dbo.bind_tab", conn.createconnection());
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataTable dt=new DataTable();

da.Fill(dt);

dt.Rows.Add("value1","value2","value3"); //I presume three varchar columns

da.Update(dt);

[dinesh.isuranga]

it runs but did not update the Sql Table. tha is the answer for 1st question friend..

for the 2nd one..
I have done this.. then it added to the SqlTable..

[dinesh.isuranga]

I used a Method to return my DataTable According to Select Query Like this..

public DataTable ReturnDatatable(String SelectQuery)
        {
            try
            {
                command = new SqlCommand(SelectQuery,conn.createconnection());
                adapter = new SqlDataAdapter(command);
                ds = new DataSet();
                adapter.Fill(ds, "temp");
                dt = ds.Tables["temp"];
                conn.deleteconnection();
                return dt;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                conn.deleteconnection();
                return null;
            }
        }

then after that return table is binding to the text boxes..
like this..

DataTable dt;
Query = "select * from dbo.bind_tab b where eid = '" + cmbEmpID.Text.ToString() + "'";
            dt = db.ReturnDatatable(Query);

now i wrote a method to bind textboxes like this..

public void TextBoxBinding(DataTable dt, TextBox txb, String Filed)
        {
            txb.DataBindings.Add("Text", dt, Filed);
        }

now i call it like this...

db.TextBoxBinding(dt, txbEmpName, "empname");

now you can undestand my quetion in details..