Update SQL database with using statement and SqlParameters

Question

I'm just starting out to learn all about sql databases. For now based on previous suggestions that was given to me online, I've incorporated a using statement to avoid problems in sql connection and then I applied parameterized queries to avoid sql injection. I was also been given an advice to use sqlparameter arrays to supplement the parameterized queries that i implemented. I just want to know if there are other techniques that you would recommend to improve my code, which i will indicate below. Feel free to suggest better solutions.

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["brandsConnection"].ToString())) {
 string query = "UPDATE [guitarBrands] SET type = @type, name = @name, image = @image WHERE id = @id";
 SqlParameter[] p = new SqlParameter[4];
 p[0] = new SqlParameter("@type", newType.Text);
 p[1] = new SqlParameter("@name", newName.Text);
 p[2] = new SqlParameter("@image", newImage.Text);
 p[3] = new SqlParameter("@id", id);
 connection.Open();
 using (SqlCommand command = new SqlCommand(query, connection))
 {
 GetExample(command, p);
 command.ExecuteNonQuery();
 connection.Close();
 command.Parameters.Clear();
 }
}
public void GetExample(SqlCommand command, params SqlParameter[] p)
{
 if (p != null && p.Any()){
 command.Parameters.AddRange(p);
 }
}

• BCdotWEB: Instead of writing ADO.NET code, learn to use an ORM like Entity Framework. As Jeremy Miller said: "if you’re writing ADO.Net code by hand, you’re stealing from your employer or client. ".

Answer 1

Stacking the using statements

I am particularly difficult to please with code that has nesting, and I highly prefer to stack the usings when it is possible.

using (SqlConnection connection = ...)
{
 using (SqlCommand command = ...)
 {
 ...
 }
}

versus

using (SqlConnection connection = ...)
using (SqlCommand command = ...)
{
 ...
}

Exception Handling

You should include exception handling into your code, as it will likely throw an exception sooner or later (such as getting disconnected, network issues, etc.)

using (SqlConnection connection = ...)
using (SqlCommand command = ...)
{
 try
 {
 // open the connection, execute, etc
 }
 catch
 {
 // log and handle exception(s)
 }
}

Using Lists not Arrays

It would likely do you more harm than good to be using an array, especially in the context of maintainable code. I would recommend you switch to using Lists, like this:

List<SqlParameter> p = new List<SqlParameter>();
p.Add(new SqlParameter("@type", newType.Text));
p.Add(new SqlParameter("@name", newName.Text));
p.Add(new SqlParameter("@image", newImage.Text));
p.Add(new SqlParameter("@id", id));

This will let you introduce more items into your parameters without having to do deal with the headaches of explicitly indexing.

Note: If you want to keep the same method signature for your GetExample() method, then don't forget to pass the parameters as an array --- you could use ToArray() to accomplish that.

---

Here it all is together...

string query = "UPDATE [guitarBrands] SET type = @type, name = @name, image = @image WHERE id = @id";
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["brandsConnection"].ToString()))
using (SqlCommand command = new SqlCommand(query, connection)
{
 try
 {
 // open the connection, execute, etc
 List<SqlParameter> p = new List<SqlParameter>();
 p.Add(new SqlParameter("@type", newType.Text));
 p.Add(new SqlParameter("@name", newName.Text));
 p.Add(new SqlParameter("@image", newImage.Text));
 p.Add(new SqlParameter("@id", id));
 connection.Open();
 GetExample(command, p.ToArray());
 command.ExecuteNonQuery();
 command.Parameters.Clear();
 }
 catch
 {
 // log and handle exception(s)
 }
}

• paparazzo: Fail. Use query before it is defined