文章/答案/技术大牛

发布

社区首页 >问答首页 >我可以执行多条SQL语句吗？

问我可以执行多条SQL语句吗？
EN

Stack Overflow用户

提问于 2017-11-29 08:39:03

回答 5查看 4.9K关注 0票数 1

我正在运行一个用于电影的sql数据库，并且我正在研究这个函数，以便每当用户检出一部电影时，该电影都会被更新，表明它已被检出，然后检出的电影将被添加到检出电影的表中。我有一个运行没有任何错误的代码，但是我的签出电影没有添加到我的签出表中。显示下面所选电影的网格视图的代码：

<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代码：

 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";

        }
    }

UPDATE SQL语句表示电影已拍摄，但不会将电影添加到checked out表中。

sql

sql-server

database

回答 5

Stack Overflow用户

发布于 2017-11-29 10:22:19

我看到了一些缺失的概念。

为什么update使用command.ExecuteReader();而insert使用command.ExecuteNonQuery();。

我认为你应该使用

ExecuteNonQuery

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

此sql语法需要用方括号表示值:

插入检出(MovieID，SubscriberID)值(@MovieID，@SubscriberID)

我希望这能解决你的问题。

票数 2

Stack Overflow用户

发布于 2017-11-29 08:50:37

我认为你最好在事务块中执行编码。您还可以使用";“进行更新，如下所示。

 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;

票数 1

Stack Overflow用户

发布于 2017-11-29 14:43:45

我可以执行多条SQL语句吗？

你当然可以！您可以在单个或多个SqlCommand中执行它。:)

UPDATE SQL语句表示电影已拍摄，但没有将电影添加到checked out表中

您使用的是本地数据库吗？如果是，请查看Data not saving permanently to SQL table。

无论如何，我想我的数据访问我的代码，可以帮助你在你的发展。

免责声明:我知道使用SqlCommand作为参数更好，但是我变得懒惰了，所以这里是string版本。

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;
        }
    }
}

示例用法：

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();
    }
}

如何在一个命令中执行多条SQL语句？

您只需使用BEGIN和END;封装查询即可。

例如：

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

请注意，您需要在语句后使用;。如果我是你，我会用StringBuilder来写我的长查询。此外，只有在不读取任何数据的情况下，将多个查询作为一个发送才有用。

IDAL接口是用来做什么的？

在我的一些项目中，我不得不使用不同的数据库。使用相同的接口，我能够以最少的代码更改为Oracle、MSSql和MySql创建DAL类。它也是面向对象的方法。:)

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/47543253

复制

相似问题

问我可以执行多条SQL语句吗？
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我可以执行多条SQL语句吗？EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我可以执行多条SQL语句吗？
EN