CODEWITHSUNDEEP
c#.netasp.net-mvct-sqlado.net
Wan Mohd Adzha
Wan Mohd Adzha

Reputation: 23

Replacement for SqlConnection and SqlCommand

I have been writing a lot of open and close connection to a Microsoft SQL Server database. I'm not sure whether it is the latest technique available for .NET. Is there any latest .NET function that I'm missing?

Example code:

protected string InjectUpdateToProductDBString(string Command, TextBox Data, string TBColumn)
{
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AuthenticationDBConnectionString"].ConnectionString;

        SqlConnection con = new SqlConnection(connectionString);
        con.Open();

        SqlCommand cmd = new SqlCommand(command, con);
        cmd.Parameters.AddWithValue("@" + TBColumn, Data.Text.ToString());

        cmd.ExecuteNonQuery();

        con.Close();

        return "Data successfully updated";
    }

Is there any replacement for this fussy code technique? Just a discussion to improve my code technique.

Upvotes: 0

Views: 2485

Answers (3)

Habib
Habib

Reputation: 223282

Make sure that you enclose your SqlConnection in using statement. It will ensure the connection is closed even if there is an exception. Also, enclose your SqlCommand object in using statement, that will ensure disposal of unmanaged resources.

In your current code snippet if there is an exception at cmd.ExecuteNonQuery(); then your line con.Close would not execute, leaving the connection open.

So your method could be like:

protected string InjectUpdateToProductDBString(string Command, TextBox Data, string TBColumn)
{
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AuthenticationDBConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand(command, con))
        {
            cmd.Parameters.AddWithValue("@" + TBColumn, Data.Text.ToString());
            cmd.ExecuteNonQuery();
        }
    }
    return "Data successfully updated";
}

Later you can return a DataTable or List<T> for your returned rows from the query.

If you want to move away from ADO.Net, then you can look into Object-Relation Mapping (ORM), which would provide you objects based on your database and easier way to manage your code base. Entity framework is one of them. You may see https://stackoverflow.com/questions/132676/which-orm-for-net-would-you-recommend

Upvotes: 0

Chuck Buford
Chuck Buford

Reputation: 379

There are other ways to write it and other tools you could use (like Entity Framework).

However, I recommend that you create a static function (or several) for your data access calls.

  protected DataTable ExecuteSqlDataReader(string connection, string sqlQuery, SqlParameter[] cmdParams)
  {

     MySqlConnection con = new MySqlConnection(connection);
     MySqlCommand cmd = new MySqlCommand(sqlQuery, con);
     cmd.Parameters = cmdParams;
     MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
     DataTable dt = new DataTable();
     sda.Fill(dt);
     sda.Command.Close();
     return dt;

  }

Create methods for Getting a dataTable, One value, ExecuteNonQuery, and even break it further down by abstracting out the SqlCommand creation to it's own method.

In any project, this code should be written only a few times.

Upvotes: 1

Zer0
Zer0

Reputation: 7354

private SqlConnection GetConnection()
{
    var con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AuthenticationDBConnectionString"].ConnectionString);
    con.Open();
    return con;
}

protected string InjectUpdateToProductDBString(string Command, TextBox Data, string TBColumn)
{
    using (var con = GetConnection())
    {
        using (var cmd = con.CreateCommand())
        {
            cmd.Parameters.AddWithValue("@" + TBColumn, Data.Text);
            cmd.ExecuteNonQuery();
            return "Data Succesfully Updated";
        }
    }
}

Upvotes: 0

Related Questions