SQL Transaction with Parameters

Question

I am using a SQL Transaction statement to execute a stored procedure. Traditionally, I would use command parameters to insert different variables into the command.

When I tried to use the same method with a Transaction, the procedure would not insert into the database, although the transaction would work without an error.

Here is how I am trying to do it:

SqlConnection db = DataConn.SqlConnection();

    db.Open();
    SqlTransaction transaction = db.BeginTransaction();

    try
    {
        const string strSql = "procSiteAddMember @uID, @userName, @password, @nickname, @email, @siteAddress";

        var sqlComm = new SqlCommand(strSql, db, transaction) {CommandType = CommandType.Text};

        sqlComm.Parameters.Add(new SqlParameter("@uID", SqlDbType.VarChar, 255)).Value = uID;
        sqlComm.Parameters.Add(new SqlParameter("@userName", SqlDbType.VarChar, 20)).Value =
            txtRegisterUsername.Text.Trim();
        sqlComm.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar, 20)).Value =
            txtRegisterPassword.Text;
        sqlComm.Parameters.Add(new SqlParameter("@nickname", SqlDbType.VarChar, 20)).Value =
            txtRegisterNickname.Text.Trim();
        sqlComm.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 20)).Value = txtRegisterEmail.Text.Trim();
        sqlComm.Parameters.Add(new SqlParameter("@siteAddress", SqlDbType.VarChar, 20)).Value = lblNickname.Text.Trim();

        //sqlComm.ExecuteNonQuery();
        //DataConn.Disconnect();

        transaction.Commit();

        Response.Redirect("~/Member/" + txtRegisterNickname.Text);
    }
    catch (Exception ent)
    {
        Response.Write("Error: " + ent.Message);
    }

I saw This post - But it seems pretty long winded with a lot of variables.

You *must* have to call one of three `sqlComm.ExecuteNonQuery();` or `ExecuteScalar()` or `ExecuteReader()` method. — kv-prajapati, Oct 15 '12 at 07:56
Oh wow... Well, for anyone else wandering. That code DOES work, just uncomment the `sqlComm.ExecuteNonQuery();` @AVD: post your response as an answer :) — TheGeekZn, Oct 15 '12 at 07:58
Either `DataConn.SqlConnection()` is returning a *new* connection each time, in which case you're probably leaking connections since it's not in a `using`, or it's returning a *single* connection, which is not going to perform well for an asp.net application. — Damien_The_Unbeliever, Oct 15 '12 at 08:00
You should have to `commit` transactions in finally block and `rollback()` method of transaction inside the `catch` block or use `using` block. — kv-prajapati, Oct 15 '12 at 08:02
@AVD, that DataConn.Connect just returns an open SQLConnection - Put the commit in a finally block. — TheGeekZn, Oct 15 '12 at 08:31

score 1 · Accepted Answer · answered Oct 27 '16 at 09:52

You already solved this but since no one answered I'll do it for future reference.

You still need to execute the query so uncommment your line sqlComm.ExecuteNonQuery();

Also don't forget to add transaction.Rollback(); in your catch block, which you have to put inside another try-catch block in case the Rollback throws an exception.

Example:

try
{
    ...
    transaction.Commit();

}
catch (Exception ex)
{
    try
    {
        ...
        transaction.Rollback();
    }
    catch (Exception ex2)
    {
        ...
    }
}

For more information visit: https://msdn.microsoft.com/en-us/library/86773566(v=vs.110).aspx

SQL Transaction with Parameters

1 Answers1