CODEWITHSUNDEEP
c#.netasp.netsqlsql-server
sams5817
sams5817

Reputation: 1037

what is the best practice on update data into SQL Server in ASP.Net

I'm doing update data into SQL Server in ASP.Net.

and I only have a cs file, no aspx/ascx file, so I'll not using the SqlDataSource control here.

Below is my code:

string connStr = ConfigurationManager.ConnectionStrings["XXConnString"].ConnectionString;

SqlConnection conn = new SqlConnection(connStr);

if (conn.State == ConnectionState.Closed)
{
    conn.Open();
}

string query = @"exec dbo.XX_Insert_Announcement @AnnID ='" + id +
                                            "', @AnnTitle ='" + title +
                                            "', @AnnSubmitDateTime ='" + startDate + 
                                            "', @AnnProcessDateTime ='" + endDate + "'";

SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
conn.Close();

if (conn.State == ConnectionState.Open)
{
    conn.Close();
}

I think my code is not good enough for error handling, if the execution of the query failed, no error is throw and the code continue running without updating to the database.

Perhaps use Using statement can solve this issue, the code as below:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Connection.Open();
    command.ExecuteNonQuery();
}

please comment and advise on what is the best practice.

thank you in advance.

Upvotes: 2

Views: 2309

Answers (1)

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

Your second approach is better, but It would be better if you used a Parameterized query for better prevention from a SQL Injection Attack.

using (SqlConnection connection = new SqlConnection(connectionString))
{
 using(SqlCommand command = connection.GetCommand(queryString, CommandType.Text))
 {
  command.Parameters.Add(new SqlParameter("AnnID", id));
  command.Parameters.Add(new SqlParameter("AnnTitle", title));
  ............
  ..............
  command.Connection.Open();
  command.ExecuteNonQuery();
 }
}

Upvotes: 3

Related Questions