update table in database from datatable

Question

I load data from database table like this...

using (view_adapter = new SqlDataAdapter("select * from TVServiceProvider", connection_string))
        {
            using (dt = new DataTable())
            {
                view_adapter.Fill(dt);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName.Substring(0, 2).Equals("id"))
                        dt.Columns[i].ReadOnly = false;
                }
                bs.DataSource = dt;
            }
        }

Where SqlDataAdapter view_adapter and DataTable dt. To apply changes to database I've created method

 void View_Adapter_Click(object sender, EventArgs e)
    {
        try
        {
            view_adapter.Update(dt);
            dt.AcceptChanges();
        }
        catch (Exception exc)
        {
            this.radLabelElement1.Text = exc.Message;
        }
    }

But when I click the button I've got an exception. It requires update command. Where and what command I should use?

Answer 1

What actually worked for me from Steve and Hamlet's suggestions is the following. I had one hiccup because I tried to do an accept changes on my rows and table before doing the view adapter update. The accept changes in only needed to save the changes to the datatable before reusing the datatable for displaying in a gridview or other operations.

SqlDataAdapter viewAdapter = new SqlDataAdapter("Select * From Users", DBConn);
SqlCommandBuilder builder = new SqlCommandBuilder(viewAdapter);
viewAdapter.UpdateCommand = builder.GetUpdateCommand();


DataTable Users = new DataTable();
viewAdapter.Fill(Users);
foreach (DataRow user in Users.Rows)
{
    foreach (DataColumn c in Users.Columns)
    {
        Console.WriteLine(c.ColumnName);
        if (c.DataType != typeof(DateTime))
        {
            // Clean up empty space around field entries
            user[c.ColumnName] = user[c.ColumnName].ToString().Trim();
        }

    }
   // user.AcceptChanges(); 
   // Do not do an accept changes for either the table or the row before your ViewAdapter Update. 
   // It will appear as though you do not have changes to push.
}

// Users.AcceptChanges();
viewAdapter.Update(Users);

Answer 2

Well, something is wrong or not clear in your code.
The view_adapter variable is initialized within a using block statement.
Thus, when exiting from the using block, the view_adatpter will be disposed by the framework and unusable in the click event. (like you have never called new to initialize it).
I suspect that you have another problem here. Using statement

A part from this, to automatically create the UpdateCommand, InsertCommand and DeleteCommand required to perform CRUD operations with a DataAdapter you could use a SqlCommandBuilder.
(This is possible only if you use one table in the select statement and that table has a primary key defined)

So to summarize everything:

string queryString = "select * from TVServiceProvider";
view_adapter = new SqlDataAdapter(queryString, connection_string);
SqlCommandBuilder builder = new SqlCommandBuilder(view_adapter)
builder.GetUpdateCommand(); // Force the building of commands
view_adapter.Fill(dt);

then your click event should works as is now.

Answer 3

You must create UpdateCommand and DeleteCommand for you view_adapter.

EDIT:

The code must look like this:

SqlDataAdapter view_adapter = new SqlDataAdapter();
view_adapter .SelectCommand = new SqlCommand(queryString, connection);
view_adapter .UpdateCommand = new SqlCommand(updateCommadString, connection);
view_adapter .DeleteCommand = new SqlCommand(deleteCommadString, connection);

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        view_adapter.Fill(dt);
        return dt;
    }

Answer 4

This code is not related to yours, but may help you, If you give it a look. I got it from MSDN

public static SqlDataAdapter CreateCustomerAdapter(
    SqlConnection connection)
{
    SqlDataAdapter adapter = new SqlDataAdapter();

    // Create the SelectCommand.
    SqlCommand command = new SqlCommand("SELECT * FROM Customers " +
        "WHERE Country = @Country AND City = @City", connection);

    // Add the parameters for the SelectCommand.
    command.Parameters.Add("@Country", SqlDbType.NVarChar, 15);
    command.Parameters.Add("@City", SqlDbType.NVarChar, 15);

    adapter.SelectCommand = command;

    // Create the InsertCommand.
    command = new SqlCommand(
        "INSERT INTO Customers (CustomerID, CompanyName) " +
        "VALUES (@CustomerID, @CompanyName)", connection);

    // Add the parameters for the InsertCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");

    adapter.InsertCommand = command;

    // Create the UpdateCommand.
    command = new SqlCommand(
        "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
        "WHERE CustomerID = @oldCustomerID", connection);

    // Add the parameters for the UpdateCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
    SqlParameter parameter = command.Parameters.Add(
        "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
    parameter.SourceVersion = DataRowVersion.Original;

    adapter.UpdateCommand = command;

    // Create the DeleteCommand.
    command = new SqlCommand(
        "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);

    // Add the parameters for the DeleteCommand.
    parameter = command.Parameters.Add(
        "@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    parameter.SourceVersion = DataRowVersion.Original;

    adapter.DeleteCommand = command;

    return adapter;
}