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 ?


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))

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

        while (reader.Read())

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();

    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.


This article has been dead for over six months. Start a new discussion instead.