CODEWITHSUNDEEP
c#sqlwebformsado.net
abramlimpin
abramlimpin

Reputation: 5077

Performance Implications of Using a Method in ADO.NET

Instead of the traditional way of executing a query like this:

protected void btnAdd_Click(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection(
        ConfigurationManager.ConnectionStrings["MyCon"].ConnectionString);
    connection.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = connection;
    cmd.CommandText = "INSERT INTO Test VALUES (@FN, @LN, @DateAdded)";
    cmd.Parameters.AddWithValue("@FN", txtFN.Text);
    cmd.Parameters.AddWithValue("@LN", txtLN.Text);
    cmd.Parameters.AddWithValue("@DateAdded", DateTime.Now);
    cmd.ExecuteNonQuery();
    connection.Close();
}

I created a separate class that allows me to re-use a method to execute a query by determining the SQL statement and its parameters (names and values):

DB.cs

static SqlConnection connection = new SqlConnection(
    ConfigurationManager.ConnectionStrings["MyCon"].ConnectionString);

public static void Execute(string query, SqlParameter[] parameters)
{
    using (connection)
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

which then allows me to lessen my coding process here:

protected void btnAdd_Click(object sender, EventArgs e)
{
    string SQL = "INSERT INTO Test VALUES (@FN, @LN, @DateAdded)";
    SqlParameter[] parameters = {
        new SqlParameter("@FN", txtFN.Text),
        new SqlParameter("@LN", txtLN.Text),
        new SqlParameter("@DateAdded", DateTime.Now)
    };

    DB.Execute(SQL, parameters);
}

Are there any performance implications in doing these? Also, are there ways to improve the existing code? Thanks!

Upvotes: 0

Views: 86

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726519

There should be no performance difference between the two approaches, because in both cases you are using parameterized query. Since you are programming directly to SqlCommand's methods (as opposed to IDbCommand methods) there is also no difference in portability to other RDBMS providers, because both implementations are tied to SQL Server.

You could make interface slightly easier to use by marking the second argument params, i.e.

public static void Execute(string query, params SqlParameter[] parameters)

This would let you make a call like this:

DB.Execute(
    "INSERT INTO Test VALUES (@FN, @LN, @DateAdded)"
,   new SqlParameter("@FN", txtFN.Text)
,   new SqlParameter("@LN", txtLN.Text)
,   new SqlParameter("@DateAdded", DateTime.Now)
);

Upvotes: 1

Related Questions