Connection Open

Question

Hi

I have below code & i want connection to be opened only once with Try Catch statement. If Category ID already exists it should not be added.

string constr = ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    string query = "INSERT INTO Category VALUES (@CategoryId,@CategoryName)";
    using (SqlCommand cmd = new SqlCommand(query))
    {
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@CategoryId", data.id);
        cmd.Parameters.AddWithValue("@CategoryName", data.snippet.title);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

Thanks

Jaimin Shethiya · Answer

Hello Try below code string connectionString = ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString; using (SqlConnection sqlConnection = new SqlConnection(connectionString)) { string query = "SELECT COUNT(1) FROM Category WHERE CategoryId = @CategoryId"; using (SqlCommand sqlCommand = new SqlCommand(query)) { sqlCommand.sqlConnectionnection = sqlConnection; sqlCommand.Parameters.AddWithValue("@CategoryId", data.id); try { // SQL Connection open sqlConnection.Open(); //Check the category data in our database. int count = (int)sqlCommand.ExecuteScalar(); if (count == 0) // Category Id does not exist in the database { query = "INSERT INTO Category VALUES (@CategoryId, @CategoryName)"; sqlCommand.CommandText = query; sqlCommand.Parameters.AddWithValue("@CategoryName", data.snippet.title); // Insert the data in the database side sqlCommand.ExecuteNonQuery(); } else { // Category Id already exists sqlConnectionsole.WriteLine("Category ID already exists."); } } catch (SqlException ex) { // Handle SQL exception sqlConnectionsole.WriteLine("An error occurred: {0}, {1}", ex.Message, ex.StackTrace); } finally { // SQL Connection close sqlConnection.Close(); } } } Thanks

Tahir Ansari · Answer

string constr = ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { string query = "SELECT COUNT(*) FROM Category WHERE CategoryId = @CategoryId"; using (SqlCommand cmd = new SqlCommand(query)) { cmd.Connection = con; cmd.Parameters.AddWithValue("@CategoryId", data.id); try { con.Open(); int count = (int)cmd.ExecuteScalar(); if (count == 0) // Category ID does not exist, so insert it { query = "INSERT INTO Category VALUES (@CategoryId, @CategoryName)"; cmd.CommandText = query; cmd.Parameters.AddWithValue("@CategoryName", data.snippet.title); cmd.ExecuteNonQuery(); } else { // Category ID already exists, handle this situation as needed Console.WriteLine("Category ID already exists."); } } catch (SqlException ex) { // Handle SQL exception Console.WriteLine("An error occurred: " + ex.Message); } finally { con.Close(); } } }