Building and consuming parameterized queries from ADO.Net to DLINQ

In this article, I will represent different techniques to build and make use of parameterized queries. The first way is the classical one, and I mean by that the ADO.Net technique. Then I'll expose the second one using the new technology, namely DLINQ.

First, the customers  table that will be used as data source is represented as under:

Figure 1

Say that we want to build a parameterized query that enables to select information about different customers those correspond to the country entered by the user. To do this, let's first leverage the UI.  For this reason, we create a new windows project named ParameterizedQuery. Then we add a data grid view, a group box and a combo box into the Form1 and we dispose them as follow:

Figure 2

The ADO.Net classic technique:

The data source used to leverage this example is the Customers table located in the NorthWind SQL server data base sample that is downloadable from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

After downloading and deploying the msi file, the NorthWind data base could be reached in C:\SQL Server 2000 data bases\NORTHWIND.MDF

In order to build a more secure system, we have to query data using stored procedures via SQL parameters rather than querying tables via inputs from the user input control such as a given text box  and that for the only purpose to avoid SQL injections queries.

First, let's create the stored procedure that enables querying data according to the country entered by the user via the ComboBox1 control. We can either use Visual Studio or Microsoft SQL Server to leverage that. And, indeed, this is the stored procedure core

ALTER PROCEDURE dbo.StoredProcedure1

         
         (

             @Country nvarchar(15)

          )

         

AS

/* SET NOCOUNT ON */

SELECT     CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Phone, Fax

FROM         Customers

WHERE    Country = @Country

 

RETURN

 

This is the result corresponding to the @Country = 'USA' parameterized executed query from within SQL Server.

Running [dbo].[StoredProcedure1] ( @Country = USA ).

CompanyName                              ContactName                    ContactTitle                   Address                                                      City            Region          PostalCode Phone                    Fax                     
---------------------------------------- ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- --------------- ---------- ------------------------ ------------------------
Great Lakes Food Market                  Howard Snyder                  Marketing Manager              2732 Baker Blvd.                                             Eugene          OR              97403      [phone]           <NULL>                  
Hungry Coyote Import Store               Yoshi Latimer                  Sales Representative           City Center Plaza 516 Main St.                               Elgin           OR              97827      [phone]           [phone]          
Lazy K Kountry Store                     John Steel                     Marketing Manager              12 Orchestra Terrace                                         Walla Walla     WA              99362      [phone]           [phone]          
Let's Stop N Shop                        Jaime Yorres                   Owner                          87 Polk St. Suite 5                                          San Francisco   CA              94117      [phone]           <NULL>                  
Lonesome Pine Restaurant                 Fran Wilson                    Sales Manager                  89 Chiaroscuro Rd.                                           Portland        OR              97219      [phone]           [phone]          
Old World Delicatessen                   Rene Phillips                  Sales Representative           2743 Bering St.                                              Anchorage       AK              99508      [phone]           [phone]          
Rattlesnake Canyon Grocery               Paula Wilson                   Assistant Sales Representative 2817 Milton Dr.                                              Albuquerque     NM              87110      [phone]           [phone]          
Save-a-lot Markets                       Jose Pavarotti                 Sales Representative           187 Suffolk Ln.                                              Boise           ID              83720      [phone]           <NULL>                  
Split Rail Beer & Ale                    Art Braunschweiger             Sales Manager                  P.O. Box 555                                                 Lander          WY              82520      [phone]           [phone]          
The Big Cheese                           Liz Nixon                      Marketing Manager              89 Jefferson Way Suite 2                                     Portland        OR              97201      [phone]           <NULL>                  
The Cracker Box                          Liu Wong                       Marketing Assistant            55 Grizzly Peak Rd.                                          Butte           MT              59801      [phone]           [phone]          
Trail's Head Gourmet Provisioners        Helvetius Nagy                 Sales Associate                722 DaVinci Blvd.                                            Kirkland        WA              98034      [phone]           [phone]          
White Clover Markets                     Karl Jablonski                 Owner                          305 - 14th Ave. S. Suite 3B                                  Seattle         WA              98128      [phone]           [phone]          
No rows affected.
(13 row(s) returned)
@RETURN_VALUE = 0

Finished running [dbo].[StoredProcedure1].

Now, let's switch to the Form1 code behind and declare some objects:

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

    }

}

At last but not least, let's run the application and try to change the comboBox1 values.
 

Figure 3

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