Replacement for SqlConnection and SqlCommand

Question

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.

I am not sure what you are looking for , in place of open and close methods, in any case "using" statement is a good practice and you don't have to call close — apomene
– apomene, Commented Sep 16, 2014 at 19:23
Calling con.Dispose() is better because it both closes and disposes the object. — DidIReallyWriteThat
– DidIReallyWriteThat, Commented Sep 16, 2014 at 19:24
One replacement that can save you a lot of boring, messy boilerplate code is Entity Framework. It gives you a nice, object-based view of your data, and handles a lot of the nitty-gritty details of mapping to and from relational tables, and generates a lot of the SQL statements needed. — marc_s
– marc_s, Commented Sep 16, 2014 at 19:46
@WanMohdAdzha: I have no idea what you mean by your reference to WCF. WCF is about services - SqlCommand, SqlConnection and Entity Framework are about data access - two totally separate, distinct fields .... WCF has nothing to do with data access .... — marc_s
– marc_s, Commented Sep 17, 2014 at 7:33

Chuck Buford · Accepted Answer · 2014-09-16 19:35:17Z

1

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.

edited Sep 16, 2014 at 19:35

answered Sep 16, 2014 at 19:28

Chuck Buford

3791 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zer0 Over a year ago

params is a keyword.

Chuck Buford Over a year ago

@Calvin Smith I would say an absolute necessity for maintainable code

DidIReallyWriteThat Over a year ago

@ChuckBuford lol, I would say one of the ways to have a slight chance to even try to have maintainable code.

Chuck Buford Over a year ago

@CalvinSmith Absolutely

Wan Mohd Adzha Over a year ago

Thanks @Chuck Buford . this is beautifully done . passing an array of parameters and returning value in a Datatable . nice and simple . Thanks again for the idea input .

Community · Accepted Answer · 2017-05-23 12:29:30Z

0

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

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Sep 16, 2014 at 19:49

Habib

224k30 gold badges420 silver badges446 bronze badges

1 Comment

Wan Mohd Adzha Over a year ago

using 'using' .. i never know it would close once its done its work . very nice answer . Tq Soo much . Entity Framework will look for it . its the LinQ to SQL right ? .. will try to migrate from old fashioned technique to new one .

Zer0 · Accepted Answer · 2014-09-16 19:27:59Z

0

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";
        }
    }
}

answered Sep 16, 2014 at 19:27

Zer0

7,3841 gold badge22 silver badges34 bronze badges

1 Comment

Wan Mohd Adzha Over a year ago

yup .. this one is nice . no need to open write the long connection string . just call GetConnection() ... Tq soo much Zer0 for the idea .

Collectives™ on Stack Overflow

Replacement for SqlConnection and SqlCommand

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related