SqlConnection oConnection;

//The parameter used to enter data via UI input control

SqlParameter CountryParam = new SqlParameter("@Country", SqlDbType.NChar);

//The sql command  

SqlCommand cmdDataGrid = new SqlCommand("StoredProcedure1");

//The Source is the data set used as the data grid view data source

DataSet Source = new DataSet();

SqlDataAdapter oAdapter = new SqlDataAdapter();

And then we implement the load event handler with this code:

private void Form1_Load(object sender, EventArgs e)

{

//Precise that the type of the data source is a stored procedure

cmdDataGrid.CommandType = CommandType.StoredProcedure;

//This list is used to populate the combo box later

List<string> oList = new List<string>();

//The connection must be initialized

oConnection = new SqlConnection();

//The connection string could be changed to fit given needs

oConnection.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" +

  @"C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';Integrated Security=True;" +

  @"Connect Timeout=30;User Instance=True";

//The try here enables to catch sql exceptions when opening the connection

try

{

    oConnection.Open();

    //This command is used to select all countries in the Customers data table

    SqlCommand cmdCombo = new SqlCommand("SELECT DISTINCT Country FROM Customers", oConnection);

    //The reader here is used to populate the oList

    SqlDataReader oReader = cmdCombo.ExecuteReader();

    try

    {

       while (oReader.Read())

       {

         oList.Add(oReader[0].ToString());

       }

    }

    finally { oReader.Close(); }

    //Set the data source of the data set Source as oList

    comboBox1.DataSource = oList;  

}

catch (SqlException caught)

{

    MessageBox.Show(caught.Message);

}

finally

{

    oConnection.Close();

}

}

After that, we implement the comboBox1 SelectedIndexChanged event handler with the following code:

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)

{

    try

    {

        //Set the connection

        cmdDataGrid.Connection = oConnection;

        //Set the sql parameter value as the selected comboBox1 text

        CountryParam.Value = comboBox1.Text;

        //Add the sql parameter to the command parameters collection

        cmdDataGrid.Parameters.Add(CountryParam);

        //Set the command of the adapter later used to fill the Source

        oAdapter.SelectCommand = cmdDataGrid;

        //Fill the Source data source used later as data source for the

        //dataGridView1

        oAdapter.Fill(Source);

        dataGridView1.DataSource = Source;

        dataGridView1.DataMember = Source.Tables[0].TableName;

    }

    finally

    {

       //As we don't need to save them in this example

       cmdDataGrid.Parameters.Clear();

    }

}

The data grid view content changes according to the comboBox1 selected value.

The DLINQ new technique:

First, if System.Data.Linq doesn't appear in the namespace list then add we it, if it is not already found, then it is necessary to add a reference to System.Data.Linq. To do this, we can add a reference by right clicking the project and selecting Add reference, then by selecting System.Data.Linq in the .Net tab as bellow.

Figure 4

Then, we add a reference to the System.Data.Linq.Mappinq that enables us to define Entity classes  witches are a sort of proxies' objects that represent the effective tables located in the data base as the LINQ is object based and it couldn't interact directly with a relational model environment. The class that will represent the NorthWind table Customers is defined as under:

[Table(Name = "Customers")]

        class Customer

        {

            [Column(Name = "CustomerID", IsPrimaryKey = true)]

            public string Identifier;

            [Column(Name = "CompanyName")]

            public string CompanyName;

            [Column(Name = "ContactName")]

            public string ContactName;

            [Column(Name = "Address")]

            public string Address;

            [Column(Name = "City")]

            public string City;

            [Column(Name = "Region")]

            public string Region;

            [Column(Name = "Country")]

            public string Region;

        }

One among differences between ADO.Net and DLINQ is, in fact, that the DataContext object will replace the famous sqlConnection. And we have two ways to make use of DataContext. Simply, we can define a DataContext as follows:

DataContext oDataContext = new DataContext(@"Data Source=STANDARD;Initial Catalog=" +
      @"C:\SQL SERVER 2000 SAMPLE DATABASES\NORTHWND.MDF';Integrated Security=True");

But it is worth to derive a given object form DataContext and append to it some customized members that could be helpful in our context. Indeed and according to this example, we need to deal only with the Customers table, so the new derived object should contain a method that returns the given table, namely the Customer table.

public class NorthWind : DataContext
{

           //Constructor

           public NorthWind():base(@"Data Source=STANDARD;Initial Catalog=" +

                             @"C:\SQL SERVER 2000 SAMPLE  DATABASES\NORTHWND.MDF';" +

                                   "Integrated Security=True")

           {

           }

            //Returns the Customer table from the Northwind data base

           public Table<Customer> GetCustomerTable()

           {

               return this.GetTable<Customer>();

           }

}

The rest of variables are defined within the scope of the Form1 as under:

/* The derived object from Data context

         * is used to define an instance used later

           to get the customer table*/

        NorthWind oNorthWind;

        /*The customer table variable */

        Table<Customer> Customers;

        /* This dataset will be used

            as datagrid view data source later*/

        DataSet Source;

        /*The oAdapter will be used to populate

         the data set Source*/

        SqlDataAdapter oAdapter;

        /*This command will be the result of the

          Onversion of the dlinq query into sqlcommand

          type*/

        SqlCommand oCommand;

Then we implement the Form1 load event handler as follow:

private void Form1_Load(object sender, EventArgs e)

       
{

            oAdapter = new SqlDataAdapter();

            oNorthWind = new NorthWind();

            //Custmer is set thanks to the NorthWind object method

            Customers = oNorthWind.GetCustomerTable();

            /* This query serves as data source for the

            combo box1, Distinct is used to avoid the redendance */

            var query = (from cust in Customers

                         select cust.Country).Distinct();

            //Populate the combo box

            foreach (string c in query)

            comboBox1.DataSource = query;

        }

To enable user display data according to the comboBox1 selected value, we implement the comboxBox1 selectedValueChanged event handler as bellow:

IQueryable<Customer> Query;

 private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)

 {

  //An extention method is used here to query the Customer table

  Query = oNorthWind.GetCustomerTable().Where(cus => cus.Country == comboBox1.Text);

  /* The get GetCommand method enables to get a

  boxed command object that we have to unboxe it using "as SqlCommand" */

  oCommand = oNorthWind.GetCommand(Query) as SqlCommand;

  //The rest is easy to undersand ........

  oAdapter.SelectCommand = oCommand;

            Source = new DataSet();

            oAdapter.Fill(Source);

            dataGridView1.DataSource = Source;

            dataGridView1.DataMember = Source.Tables[0].TableName;       

 }

We run the application now:

Figure 5

As we expect, we obtain as same result as the first technique, that's it.

Good Dotneting!!!