时间戳

首页 > 解决方案 > 将参数传递给数据访问层时出错

c# - 将参数传递给数据访问层时出错

问题描述

我将一个字符串传递给一个 DAL，它是带有参数、名字和姓氏的 Select Query。我不断得到的答案是 System.Data.SqlClient.SqlException：'必须声明标量变量“@FirstName”。' 请求是让 'DAL 返回一个数据集。

我有一个类似的功能，可以使用相同的参数更新一条记录，效果很好

private void button1_Click(object sender, EventArgs e)
{
    {
        strCmd = "SELECT * FROM Person.Person WHERE FirstName=@FirstName AND LastName=@LastName";

        // Add the parameters.
        data.aCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 50)).Value = "Morgan";
        data.aCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 50)).Value = "Zoe";

        ds = data.ReturnDataSet(strCmd);
        dataGridView1.DataSource = ds.Tables[0];
        dataGridView1.AutoGenerateColumns = true;
        BindingSource bSource = new BindingSource();
        bSource.DataSource = ds.Tables[0];
        //set the DataGridView DataSource
        dataGridView1.DataSource = bSource;
    }
}

达尔

public DataSet ReturnDataSet(string SQLSelectCommand)
{
    SqlDataAdapter daGeneric = new SqlDataAdapter(SQLSelectCommand, this.sQLConnection);
    DataSet ds = new DataSet();
    daGeneric.Fill(ds);
    return ds;
}

public bool UpdateData(string OdbcCommand)
{
    bool ret = false;
    try
    {
        aCommand.CommandText = OdbcCommand;
        int i = aCommand.ExecuteNonQuery();
        if (i > 0)
            ret = true;
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message);
        ret = false;
    }
    finally
    {
        //conn.Close();
    }
    return ret;
}

我仍然遇到问题，因为我无法创建存储过程。

这是完整的 DAL 代码：

using System.Data.Sql;
using System.Data;
using System.Configuration;
using System.Windows;
using System.Data.SqlClient;
using System;
using System.Windows.Forms;


namespace OJT.DAL
{
    public class OJT_Data
    {
        public SqlConnection sQLConnection = new SqlConnection();
        public SqlCommand aCommand = new SqlCommand();
        private string DatabaseName = "";
        public string strConnStr;
        private string strDatabaseName = "";

        public void openDB()
        {
            sQLConnection.ConnectionString = strConnStr;
            sQLConnection.Close();
            sQLConnection.Open();
            aCommand = sQLConnection.CreateCommand();
        }
        public SqlDataReader ReturnData(string SQLDBCommand)
        {
            SqlDataReader r = null;
            try
            {
                aCommand.CommandText = SQLDBCommand;
                r =   aCommand.ExecuteReader(System.Data.CommandBehavior.Default);
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return r;
        }
        public bool CheckTableExists(string strTableName)
        {
            bool tableExists = false;
            DataTable dt = sQLConnection.GetSchema("tables");
            foreach (DataRow row in dt.Rows)
            {
                string strTbleName = row["TABLE_NAME"].ToString();
                if (row["TABLE_NAME"].ToString() == strTableName)
                {
                    tableExists = true;
                    break;
                }
            }
            return tableExists;
        }
        public DataSet ReturnDataSet(string SQLSelectCommand)
        {
            SqlDataAdapter daGeneric = new SqlDataAdapter(SQLSelectCommand, this.sQLConnection);
            DataSet ds = new DataSet();
            daGeneric.Fill(ds);
            return ds;
        }


        public bool UpdateData(string OdbcCommand)
        {
            bool ret = false;
            try
            {
                aCommand.CommandType = CommandType.Text;
                aCommand.CommandText = OdbcCommand;
                int i = aCommand.ExecuteNonQuery();
                if (i > 0)
                    ret = true;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
                ret = false;
            }
            finally
            {
                //               conn.Close();
            }
            return ret;
        }
    }
}

标签： c#sqldata-access-layer

解决方案

首先，当您想使用存储过程时使用命令参数，例如，不用于查询。

如果你想使用过程，你必须像这样创建它们：

CREATE PROCEDURE procedureName
-- Add the parameters for the stored procedure here
 @FirstName varchar(100),
 @LastName varchar(100)
AS
BEGIN
 SELECT * FROM Person.Person WHERE FirstName=@FirstName AND LastName=@LastName;
END

然后您可以发送值：

SqlCommand command = new SqlCommand("procedureName", connection);
command.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 50)).Value = "Morgan";
command.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 50)).Value = "Zoe";

如果您能向我们展示您尝试执行查询的完整代码，那就太好了。

推荐阅读