DataSet and DataAdapter Help

[m-soft]

Hello,

I am trying to learn c# and sql server and I am not sure how should I proceed in the following situation:

I have a database with 2 tables: Clients and OrdersFromClients
I am trying to make a form with a listbox in which I will display all the clients found in Clients table.
When I select one client in the listbox I need to display all the orders made by that client and also I want to be able to edit them.

Using SqlDataAdapter and DataSet I know how to retieve the clients from Clients table,
BUT I don't know how to select the orders from the OrdersFromClients table using data from the same DataSet.
(a simple sql command should be something like "SELECT * FROM OrdersFromClients WHERE ClientId = SelectedClientID").

In short I know what does the following code:

 DataSet dat_set = new DataSet();
 da = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);         
System.Data.DataSet dat_set = new System.Data.DataSet();
da.Fill(dat_set, "Clients");

BUT I don'w know how to add to dat_set OrdersFromClients data and manipulate them (update, delete or add new orders)

Is there anybody who want to help me ?
Thanks!

[djjeavons]

Hi

You could use a TableAdapter to retrieve and update data. It's not something that I have much experience with but the MSDN documentation should provide you with plenty of examples for each of the scenarios. This might be the better choice as it will handle a lot of the work for you.

Alternatively, you could use DataReader to load your clients into the ListBox and then a DataTable to bind all orders for the selected client to something like a DataGridView. You can then use standard SQL statements to update data back to the database. For example, to load clients to the list box using a DataReader would be something like:

string connectionString = @"Server=DJJEAVONS-PC\SQLEXPRESS;Database=Test;Trusted_Connection=True;";

string clientsStatement = "SELECT ClientID, ClientName FROM Clients";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand(clientsStatement, connection))
    {
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            clientListBox.Items.Add(reader["ClientID"].ToString());
        }
    }
}

And to take the selected ID and populate a DataTable to bind to a DataGridView would be similar too:

string connectionString = @"Server=DJJEAVONS-PC\SQLEXPRESS;Database=Test;Trusted_Connection=True;";

string ordersStatement = "SELECT OrderID, ClientID, OrderName FROM OrdersFromClients WHERE ClientID = @ClientID";

using (SqlDataAdapter adapter = new SqlDataAdapter(ordersStatement, connectionString))
{
    adapter.SelectCommand.Parameters.AddWithValue("@ClientID", clientListBox.SelectedItem.ToString());

    DataTable ordersTable = new DataTable();
    adapter.Fill(ordersTable);

    ordersGridView.DataSource = ordersTable;
}

When you want to update, add or delete data you would use the SqlCommand object and either the ExecuteNonQuery or ExecuteScalar methods.

HTH