Can I execute multiple SQL statements?

Question

I am running an sql database for movies and I am working on this function so that whenever a user checks out a movie, the movie would be updated saying it has been checked out, then the checked out movie would be added to a table for checked out movies. I have a code that runs without any errors but my checked out movie does not get added to my checked out table. Code for the gridview that displays the selected movie below:

<asp:ListBox ID="CheckOutList" runat="server" OnSelectedIndexChanged="Get_data" AutoPostBack="true">

        </asp:ListBox>

<asp:Panel ID="panel5" runat="server" Visible="false">
        <asp:GridView id="one_data" AutoGenerateColumns="false" runat="server" DataKeyNames="MovieID">
            <Columns>
                <asp:BoundField DataField="MovieID"
                    HeaderText="Movie ID"/>
                <asp:BoundField DataField="MovieTitle"
                    HeaderText="Movie"/>
                <asp:BoundField DataField="DateChecked"
                    HeaderText="Date Checked"/>
                <asp:BoundField DataField="CheckedOut"
                    HeaderText="Checked Out"/>
            </Columns>
        </asp:GridView>
        <asp:Button runat="server" Text="Choose another Movie" OnClick="GoBack" />
        <asp:Button runat="server" Text="Check Out" OnClick="CheckOut" />
    </asp:Panel>

CS code for checking out the movie:

 public void CheckOut(object sender, EventArgs e)
    {
        get_connection();
        try
        {

            connection.Open();
            command = new SqlCommand("UPDATE Content SET DateChecked=@DateChecked, CheckedOut=@CheckedOut WHERE MovieID=@MovieID", connection);
            command.Parameters.AddWithValue("@DateChecked", DateTime.Now);
            command.Parameters.AddWithValue("@CheckedOut", 'Y');
            //command.Parameters.AddWithValue("@MovieID",);
            command.Parameters.AddWithValue("@MovieID", CheckOutList.SelectedValue);

            reader = command.ExecuteReader();
            one_data.DataSource = reader;
            one_data.DataBind();

            reader.Close();


            command = new SqlCommand("INSERT INTO checkout (MovieID, SubscriberID) VALUES @MovieID, @SubscriberID", connection);
            command.Parameters.AddWithValue("@MovieID", CheckOutList.SelectedValue);
            command.Parameters.AddWithValue("@SubscriberID", loginName.Text);
            command.ExecuteNonQuery();
        }
        catch (Exception err)
        {
            // Handle an error by displaying the information.
            lblInfo.Text = "Error reading the database. ";
            lblInfo.Text += err.Message;
        }
        finally
        {
            connection.Close();
            lblInfo.Text = "Movie Checked Out";

        }
    }

The UPDATE SQL statement does work saying the movie has been taken but the movie does not get added to the checked out table.

Answer 1

I see some missing concept.

1. why update use command.ExecuteReader(); while insert use command.ExecuteNonQuery(); .
   I think you should use

ExecuteNonQuery

2. INSERT INTO checkout (MovieID, SubscriberID) VALUES @MovieID, @SubscriberID .
   This sql syntax need bracket for values :

INSERT INTO checkout (MovieID, SubscriberID) VALUES (@MovieID, @SubscriberID)

I hope this solve yours.

Answer 2

I think you better execute coding in the transaction block. also you can update as following examlpe with ";"

 connection.ConnectionString = connectionString;
 command.CommandText = @"
     UPDATE MultiStatementTest SET somevalue = somevalue + 1;
     UPDATE MultiStatementTest SET" + (generateError ? "WONTWORK" : "") + 
                   " somevalue = somevalue + 2;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

Answer 3

Can I execute multiple SQL statements?

Of course you can! You can execute it in a single or multiple SqlCommand. :)

The UPDATE SQL statement does work saying the movie has been taken but the movie does not get added to the checked out table

Are you using a local database? If yes, check out Data not saving permanently to SQL table.

Anyway, I would like to my code for data access which could help you in your development.

Disclaimer: I know that using SqlCommand as parameter is better but I got lazy so here's the string version.

public interface IDAL
{
    void BeginTransaction();
    void EndTransaction();
    void SaveChanges();
    DataTable RetrieveData(string query, [CallerMemberName] string callerMemberName = "");
    string RetrieveString(string query, [CallerMemberName] string callerMemberName = "");
    bool ExecuteNonQuery(string query, [CallerMemberName] string callerMemberName = "");
    bool ExecuteNonQuery(string query, object[] parameters, [CallerMemberName] string callerMemberName = "");
}

public class MSSQLDAL : IDAL, IDisposable 
{
    private bool disposed = false;
    private string _connectionString { get; set; }
    private SqlTransaction _transaction { get; set; }
    private SqlConnection _connection { get; set; }
    private IsolationLevel _isolationLevel { get; set; }
    private bool _isCommitted { get; set; }

    public string ConnectionString
    {
        get { return _connectionString; }
    }

    public MSSQLDAL(string connectionString)
    {
        this.connectionString = _connectionString;
        this._connection = new SqlConnection();
        this._connection.ConnectionString = this._connectionString;
        this._isolationLevel = IsolationLevel.ReadCommitted;
        this._isCommitted = false;
    }

    public void BeginTransaction()
    {
        this.Open();
    }

    public void EndTransaction()
    {
        this.Close();
    }

    public void SaveChanges()
    {
        if(_transaction != null)
        {
            _transaction.Commit();
            this._isCommitted = true;
        }
        this.EndTransaction();
    }

    public DataTable RetrieveData(string query, [CallerMemberName] string callerMemberName = "")
    {
        DataTable dataTable = new DataTable();

        try
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = _connection;
                command.Transaction = _transaction;
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }
            }

            //this.AuditSQL(query, string.Empty);
        }
        catch (Exception ex)
        {
            this.AuditSQL(query, ex.Message, callerMemberName);
        }

        return dataTable;
    }

    public string RetrieveString(string query, [CallerMemberName] string callerMemberName = "")
    {
        string text = string.Empty;

        try
        {
            using (SqlCommand oracleCommand = new SqlCommand())
            {
                oracleCommand.Connection = _connection;
                oracleCommand.Transaction = _transaction;
                oracleCommand.CommandText = query;
                oracleCommand.CommandType = CommandType.Text;

                using (SqlDataReader dataReader = oracleCommand.ExecuteReader())
                {
                    dataReader.Read();

                    text = dataReader.GetValue(0).ToString();
                }
            }

            //this.AuditSQL(query, string.Empty);

        }
        catch (Exception ex)
        {
            this.AuditSQL(query, ex.Message, callerMemberName);
        }

        return text;
    }

    public bool ExecuteNonQuery(string query, [CallerMemberName] string callerMemberName = "")
    {
        bool success = false;

        try
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = _connection;
                command.Transaction = _transaction;
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }

            //this.AuditSQL(query, string.Empty);

            success = true;
        }
        catch (Exception ex)
        {
            this.AuditSQL(query, ex.Message, callerMemberName);

            success = false;
        }

        return success;
    }

    public bool ExecuteNonQuery(string query, object[] parameters, [CallerMemberName] string callerMemberName = "")
    {
        bool success = false;

        try
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = _connection;
                command.Transaction = _transaction;
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                command.Parameters.AddRange(parameters);

                command.ExecuteNonQuery();
            }

            //this.AuditSQL(query, string.Empty);

            success = true;
        }
        catch (Exception ex)
        {
            this.AuditSQL(query, ex.Message, callerMemberName);

            success = false;
        }

        return success;
    }

    private void Open()
    {
        if(_connection.State == ConnectionState.Closed)
        {
            _connection.Open();
            _transaction = _connection.BeginTransaction(_isolationLevel);
        }            
    }

    private void Close()
    {
        if (!this._isCommitted)
        {
            if (this._transaction != null)
            {
                this._transaction.Rollback();
            }
        }
        if(this._connection.State == ConnectionState.Open)
        {
            this._connection.Close();
        }
    }

    private void AuditSQL(string query, string message, string callerMemberName = "")
    {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.AppendLine("**************************************************************************************************");
        stringBuilder.AppendLine(string.Format("DATETIME: {0}", DateTime.Now.ToString("MM/dd/yyyy HHmmss")));
        stringBuilder.AppendLine(string.Format("SQL: {0}", query));
        stringBuilder.AppendLine(string.Format("MESSAGE: {0}", message));
        if (!string.IsNullOrWhiteSpace(callerMemberName))
        {
            stringBuilder.AppendLine(string.Format("METHOD: {0}", callerMemberName));
        }
        stringBuilder.AppendLine("**************************************************************************************************");

        Logger.WriteLineSQL(stringBuilder.ToString()); // Log the query result. Add an #if DEBUG so that live version will no longer log.
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (!disposed)
        {
            if (disposing)
            {
                if (!this._isCommitted)
                {
                    if (this._transaction != null)
                    {
                        this._transaction.Rollback();
                    }
                }
                this._transaction.Dispose();
                this._connection.Dispose();
            }
            // Free your own state (unmanaged objects).
            // Set large fields to null.
            // Free other state (managed objects).
            this._transaction = null;
            this._connection = null;
            disposed = true;
        }
    }
}

Sample usage:

public void CheckOut(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; // Assuming it is in your web.config

    try
    {
        using(MSSQLDAL dal = new MSSQLDAL(connectionString))
        {
            dal.BeginTransaction();

            string updateQuery = "UPDATE Content SET DateChecked=@DateChecked, CheckedOut=@CheckedOut WHERE MovieID=@MovieID";

            SqlParameter uDateChecked = new SqlParameter("DateChecked", SqlDbType.DateTime);
            uDateChecked = DateTime.Now;
            SqlParameter uCheckedOut = new SqlParameter("CheckedOut", SqlDbType.VarChar);
            uCheckedOut = 'Y';
            SqlParameter uMovieID = new SqlParameter("MovieID", SqlDbType.Int);
            uMovieID = CheckOutList.SelectedValue;

            ICollection<SqlParameter> updateParameters = new List<SqlParameter>();

            updateParameters.Add(uDateChecked);
            updateParameters.Add(uCheckedOut);
            updateParameters.Add(uMovieID);

            bool updateSuccessful = dal.ExecuteNonQuery(updateQuery, updateParameters.ToArray()); 

            string insertQuery = "INSERT INTO checkout (MovieID, SubscriberID) VALUES (@MovieID, @SubscriberID)";

            SqlParameter iSubscriberID = new SqlParameter("SubscriberID", SqlDbType.VarChar);
            iSubscriberID = loginName.Text;
            SqlParameter iMovieID = new SqlParameter("MovieID", SqlDbType.Int);
            iMovieID = CheckOutList.SelectedValue;

            ICollection<SqlParameter> insertParameters = new List<SqlParameter>();

            insertParameters.Add(iSubscriberID);
            insertParameters.Add(iMovieID);

            bool insertSuccessful = dal.ExecuteNonQuery(insertQuery, insertParameters.ToArray()); 

            if(updateSuccessful && insertSuccessful)
            {
                dal.SaveChanges();

                lblInfo.Text = "Movie Checked Out";
            }
            else
            {
                lblInfo.Text = "Something is wrong with your query!";
            }
        }   
    }
    catch(Exception ex)
    {
        StringBuilder sb = new StringBuilder();
        sb.AppendLine("Error reading the database.");
        sb.AppendLine(ex.Message);
        if(ex.InnerException != null)
            sb.AppendLine(ex.InnerException.Message);

        lblInfo.Text = sb.ToString();
    }
}

How can I execute multiple SQL statements in a single command?

You simply need to encapsulate your queries with a BEGIN and END;.

Ex:

BEGIN
    SELECT 'A';
    SELECT * FROM TableA;
END;

Take note that you need to have ; after your statements. I'd use a StringBuilder to write my long queries if I were you. Also, sending multiple queries as one is only useful if you are not reading any data.

What's the IDAL interface for?

In some of my projects I had to work with different databases. Using the same interface, I am able to create a DAL class for Oracle, MSSql and MySql with very minimal code change. It is also the OOP way. :)

Answer 4

Please change the code like this and try.change the reader.close() after executenonquery().

try
        {

            connection.Open();
            command = new SqlCommand("UPDATE Content SET DateChecked=@DateChecked, CheckedOut=@CheckedOut WHERE MovieID=@MovieID", connection);
            command.Parameters.AddWithValue("@DateChecked", DateTime.Now);
            command.Parameters.AddWithValue("@CheckedOut", 'Y');
            //command.Parameters.AddWithValue("@MovieID",);
            command.Parameters.AddWithValue("@MovieID", CheckOutList.SelectedValue);

            reader = command.ExecuteReader();
            one_data.DataSource = reader;
            one_data.DataBind();




            command = new SqlCommand("INSERT INTO checkout (MovieID, SubscriberID) VALUES @MovieID, @SubscriberID", connection);
            command.Parameters.AddWithValue("@MovieID", CheckOutList.SelectedValue);
            command.Parameters.AddWithValue("@SubscriberID", loginName.Text);
            command.ExecuteNonQuery();

           reader.Close();
        }

Answer 5

I finally found an answer after reading all the posts and found that this actually got my INSERT to work

try
        {

            connection.Open();
            command = new SqlCommand("UPDATE Content SET DateChecked=@DateChecked, CheckedOut=@CheckedOut WHERE MovieID=@MovieID", connection);
            command.Parameters.AddWithValue("@DateChecked", DateTime.Now);
            command.Parameters.AddWithValue("@CheckedOut", 'Y');
            //command.Parameters.AddWithValue("@MovieID",);
            command.Parameters.AddWithValue("@MovieID", CheckOutList.SelectedValue);

            //reader = command.ExecuteReader();
            command.ExecuteNonQuery();
            one_data.DataSource = reader;
            one_data.DataBind();
            connection.Close();



            connection.Open();
            command = new SqlCommand("INSERT INTO checkout (MovieID, SubscriberID) VALUES (@MovieID, @SubscriberID)", connection);
            command.Parameters.AddWithValue("@MovieID", CheckOutList.SelectedValue);
            command.Parameters.AddWithValue("@SubscriberID", loginName.Text);
            command.ExecuteNonQuery();
            //reader.Close();
        }