How to: Execute a Parameterized SQL Query

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

This topic provides examples of how to execute SQL query with parameters.

To run the samples in this topic, you need to use\import the System.Data, System.Data.Common and Telerik.OpenAccess.Data.Common namespaces.

Suppose, you have a Categories table like the one shown in the figure below:

And you have created a domain model that looks like:

Executing Parameterized Commands

The following code sample demonstrates how to construct a query string with one parameter. It creates an OACommand instance, adds one OAParameter to the OAParameterCollection collection of that OACommand. Finally, the command is executed and the result is examined. In summary, here are the steps you need to perform:

1. Create a new instance of the OpenAccessContext.
2. Get an existing instance of the OAConnection class, by using the OpenAccessContext.Connection property.
3. Create a string with the SQL select statement.
4. Create a new instance of the OACommand class, by using the OAConnection.CreateCommand method.
5. Set the OACommand.CommandText property.
6. Execute the command by calling the OACommand.ExecuteReader method.
7. The final step is to consume the result.

private static void ExecuteParamSqlSelectStatement()
{
    // 1. Create a new instance of the OpenAccessContext.
    using ( EntitiesModel dbContext = new EntitiesModel() )
    {
       // 2. Retrieve the OAConnection instance.
       using ( IDbConnection oaConnection = dbContext.Connection )
       {
           // 3. Create a string containing the sql query
           const string SqlQueryString = "select * from Categories where CategoryId = @Id";
           // 4. Create a new instance of the OACommand class.
           using ( IDbCommand oaCommand = oaConnection.CreateCommand() )
           {
               // 5. Initialize OAParameters
               IDbDataParameter idParam = oaCommand.CreateParameter();
               idParam.ParameterName = @"Id";
               idParam.DbType = System.Data.DbType.Int32;
               idParam.Value = 1;
               // 6. Set the CommandText property.
               oaCommand.CommandText = SqlQueryString;
               // 7. Add parameters to the Parameters collection of the command.
               oaCommand.Parameters.Add( idParam );
               // 8. Execute the command.
               using ( IDataReader reader = oaCommand.ExecuteReader() )
               {
                   // 9. Examine the result.
                   while ( reader.Read() )
                   {
                       Console.Write( string.Format( "Id: {0}", reader.GetInt32( 0 ) ) );
                       Console.Write( string.Format( "Name: {0}", reader.GetString( 1 ) ) );
                       Console.WriteLine();
                   }
               }
           }
       }
    } 
}

Private Sub ExecuteParamSqlSelectStatement()
    ' 1. Create a new instance of the OpenAccessContext.
    Using dbContext As New EntitiesModel()
        ' 2. Retrieve the OAConnection instance.
        Using oaConnection As IDbConnection = dbContext.Connection
            ' 3. Create a string containing the sql query
            Const SqlQueryString As String = "select * from Categories where CategoryId = @Id"
            ' 4. Create a new instance of the OACommand class.
            Using oaCommand As IDbCommand = oaConnection.CreateCommand()
                ' 5. Initialize OAParameters
                Dim idParam As IDbDataParameter = oaCommand.CreateParameter()
                idParam.ParameterName = "Id"
                idParam.DbType = System.Data.DbType.Int32
                idParam.Value = 1
                ' 6. Set the CommandText property.
                oaCommand.CommandText = SqlQueryString
                ' 7. Add parameters to the Parameters collection of the command.
                oaCommand.Parameters.Add(idParam)
                ' 8. Execute the command.
                Using reader As IDataReader = oaCommand.ExecuteReader()
                    ' 9. Examine the result.
                    Do While reader.Read()
                        Console.Write(String.Format("Id: {0}", reader.GetInt32(0)))
                        Console.Write(String.Format("Name: {0}", reader.GetString(1)))
                        Console.WriteLine()
                    Loop
                End Using
            End Using
        End Using
    End Using
End Sub

Optionally, if the execution of the command is expected to take long time, you can set an appropriate value for the CommandTimeout property of oaCommand. By default, it is the one specified in Runtime Configuration. If the command exceeds the timeout it will be terminated and a backend specific exception will be thrown.

How it Works?

In the previous code-snippet, you started off by creating a string with the SQL select statement. This statement contains one parameter: @Id. This is a placeholder that will be replaced by values when the statement is executed. Next, you create a single parameter that bind the placeholder name to specific values. For the select statement, you bind the value 1 to the @Id placeholder. To execute the SQL statement, you pass the string with the SQL statement to the CommandText property of the OACommand instance, and add the single OAParameter to the OAParameterCollection exposed by the command. Finally, the command is executed and the result is iterated.

It is a good practice to enclose the DbDataReader and OACommand instances in using statements.

Best Practices

You may ask yourself the following question: Why do you need to parameterize your queries? The best practice is to use parameters whenever possible. Here are some reasons:

• Parameterized SQL statements prevent SQL injection attacks. If you construct the statement as a string by appending together strings that you get from a user interface (e.g. WinForms TextBox control), you may end up constructing a SQL statement that does some damage to your database or reveals sensitive information. When you use parameterized SQL statements, the parameters are handled in a way that prevent this.
• Parameterized SQL statements allow you to reuse the non-varying part of the statement. This reuse can improve the code maintenance.
• Parameterized SQL statements make your code more configurable. For example, the statements could come from a configuration or a XML file. Thus making some changes to the application won't require a re-compilation of the code.