Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
BRH 9
#DOTNET 8
#DATAACCESS 7
Data Access 5
.NET 2
VS2010 2
dot net 2
DAAB 2
#LINQ 2
LINQ 2

Archive · View All
May 2010 5
April 2010 3
May 2011 1
December 2010 1

Deepak Palkar's Blog

Creating and Executing Parameterized Queries

May 11 2010 6:52PM by Deepak Palkar   

In this post we’ll discuss how we can execute parameterized queries in .Net. At times when you can’t use the most recommended way to retrieve/update data through Stored Procedures, you can rely on parameterized queries. It is much safer and recommended than building a sql string dynamically, which is a bit more error prone as well as hard to maintain. Parameterized queries are queries that have one or more embedded parameters in sql statement which are also type safe. You build them separately and attach them into the sql statement.

Following example shows how to use parameterized queries with ADO.Net.

        
    static void GetCustomersWithCity(string city)
    {
        DataSet ds;
        SqlConnection con = new SqlConnection("server=localDBServer;database=AdventureWorks;Trusted_Connection=yes");

        SqlCommand cmd = new SqlCommand("select c.FirstName, c.LastName  from Person.Contact c " + 
                                            " inner join Person.Address a " + 
                                            " on c.ContactID = a.AddressID " + 
                                            " where a.City = @CityParam;", con);

        SqlParameter cityParam = cmd.Parameters.Add("@CityParam", SqlDbType.VarChar);
        cityParam.Value = city;

        ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;
        adapter.Fill(ds);
    }
Parameterized queries using Data Access Application Block (DAAB)
Here’s how you can configure the Data Access Application Block in your Project.
	
    static void GetCustomersWithCity_DAAB(string city)
    {
        Database db = DatabaseFactory.CreateDatabase();
        string sql = "select c.FirstName, c.LastName  from Person.Contact c " +
                                            " inner join Person.Address a " +
                                            " on c.ContactID = a.AddressID " +
                                            " where a.City = @CityParam;";
        DbCommand cmd = db.GetSqlStringCommand(sql);
        db.AddInParameter(cmd, "@CityParam", DbType.String, city);
        DataSet ds = db.ExecuteDataSet(cmd);
    }

Parameterized queries using LINQ to SQL

The parameters are expressed in the query text by using the same curly notation used by Console.WriteLine() and String.Format(). In fact, String.Format() is actually called on the query string you provide, substituting the curly braced parameters with generated parameter names such as @p0, @p1 …, @p(n).

    
static void GetCustomersWithCity_LINQ(string city)
    {
        DataContext db = new DataContext();
	IEnumerable<Contact> contacts = 
				db.ExecuteQuery<Contact>
				("select c.FirstName, c.LastName  from Person.Contact c " +
                                            " inner join Person.Address a " +
                                            " on c.ContactID = a.AddressID " +
                                            " where a.City = {0}", city);
    }

SQL parameters in Parameterized queries are type safe, which is much better way to do it compared to building a dynamic sql string. Same way you can write your Insert/Update/Delete statements.

Happy Coding!!

  

Tags: Data Access, BRH, DAAB, #DATAACCESS, #DOTNET,


Deepak Palkar
179 · 1% · 269
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]