How to use SQL Command Builder and SQL Data Apdater

Question

I read SQL Command Builder class from http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx and I found that I can show update done on dataset/database using select and update command.

SQL Command Builder concept is clear if I am using single dataset but what if I want to use two different dataset?

Scenario: I am reading values from database into one dataset ds1; which is assign to sql adapter and sql command builder. Now, I am reading only selected values from ds1 and storing into second dataset ds2; which is not assign to sql data adapter and sql command builder.

I am concern if I am updating any data on ds2 whether it will update database or not. Also, how should I do it using SQL Command builder and SQL Adapter.

//primary dataset

ds = new ProductDataSet();
        command = new SqlCommand(
            "SELECT no, name, price, cost, dept FROM PRODUCTS", connection);
        adapter = new SqlDataAdapter();
        adapter.SelectCommand = command;            
        adapter.Fill(ds, "table");

Primary dataset is fill on form load event. User will enter item no of his choice which will be search from primary ds and saved/display onto 2nd ds (2nd ds is not connected with with any adapter or command builder right now). For eg; 2nd ds have 3 items.

Now say user update any information on 2nd ds it should automatically update database and display on grid.

//2nd ds2 update code

for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
                            {
 string item = ds2.Tables[0].Rows[i][0].ToString();                   

command = new SqlCommand("UPDATE PRODUCTS SET " + _colName + " = '" + _newValue + "'" + "WHERE ITEM_NO = '" + item + "'", Class1.conn);                                    
datagrid.DataSource = ds2.Tables[0];

}

According to your suggestion if I am adding/declaring adapter/builder in above code it doesn't work. I am getting Table Mapping error.

Solution 1

Use another SQLAdapter and SQLCommandBuilder. The example on that page shows how to update your database. You just need to supply the fields to be updated in the form of a query, such as:

SELECT Name, Address, Phone, Email FROM Contact

and the command builder will generate the proper SQL UPDATE statement.

Solution 2

SqlCommandBuilder automatically generates INSERT, UPDATE and DELETE sql statements based on the SELECT statement for a single table.

For the Transact-SQL statements to be generated using SqlCommandBuilder, there are 2 steps

Step 1. Set the "SelectCommand" property of the SqlDataAdapter object

      SqlDataAdapter dataAdapter = new SqlDataAdapter();

      dataAdapter.SelectCommand = new SqlCommand("SELECT_Query", con);

      DataSet dataSet = new DataSet();
      dataAdapter.Fill(dataSet, "Students");

Step 2. Create an instance of SqlCommandBuilder class and associate the SqlDataAdapter object created above using DataAdapter property of the SqlCommandBuilder object

        SqlCommandBuilder builder = new SqlCommandBuilder();
        builder.DataAdapter = dataAdapter;

Step 3. Updating records of ds1

         dataAdapter.Update(ds1, "Students");

Comments

[Rushabh Shah]

Is it possible to read/select rows from dataset1 and insert into dataset2 and then use SQL Adapter and SQL Command Builder on dataset2 to update data in database.

[nawfal]

@RushabhShah there are plenty of threads on how to deal with these things. Please browse around here. Even related questions might help

[prprcupofcoffee]

@RushabhShah, yes - there is no problem doing that.

[Rushabh Shah]

@David : Can you show me some sample code for that. I am trying since yesterday but I am not able to achieve my task.

[prprcupofcoffee]

Can you update your question with code you have tried and describe the results?

[Rushabh Shah]

I have added code into my question. If I am declaring adapter, builder into my update code it doesn't work because my 2nd dataset had selected values from my 1st ds1. So, I can't re-fill my 2nd dataset. In that case, how should I update 2nd Dataset, update database and display onto DataGridView.

[prprcupofcoffee]

You didn't show the code you tried for updating the database, but there is an example on the link you mentioned ( msdn.microsoft.com/en-us/library/…). What line does the table mapping error happen on?