DBHelper类怎样调用存储过程及传参

[zhangfengyi]

网上下载的DBHelper类，一直在使用参数化查询，现在想改成存储过程，没看明白怎样向调用存储过程，以及传参，有用过的帮忙看下。

/// <summary>
    /// 执行主要操作的类
    /// </summary>
    public class DataBase
    {
        private DbDataAdapter mDataAdapter; //指向传入的DbDataAdapter的引用
        private DbCommand mCommand;  //指向传入的DbDataAdapter.SelectCommand的引用

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="DDA">获得一个实例化了的DbDataAdapter的派生类</param>
        public DataBase(DbDataAdapter DDA)
        {
            mDataAdapter = DDA;
            mCommand = DDA.SelectCommand;
        }

        /// <summary>
        /// 判断一个stirng是否为储存过程
        /// </summary>
        /// <param name="SQLText">目标string</param>
        /// <returns>返回是否为储存过程的调用</returns>
        private bool IsProcedure(string SQLText)
        {
            if (SQLText.Contains(" "))
            {
                string[] tmp;
                tmp = SQLText.Split(' ');
                if (tmp[0].ToUpper() == "EXECUTE" || tmp[0].ToUpper() == "EXEC")
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 执行数据库命令返回受影响的行数
        /// </summary>
        /// <param name="SQLText">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string SQLText)
        {
            if (IsProcedure(SQLText)) { mCommand.CommandType = CommandType.StoredProcedure; } else { mCommand.CommandType = CommandType.Text; }
            mCommand.CommandText = SQLText;
            try
            {
                mCommand.Connection.Open();
                return mCommand.ExecuteNonQuery();
            }
            finally
            {
                mCommand.Connection.Close();
                ClearParameters();
            }
        }

        /// <summary>
        /// 执行数据库命令返回DataReader
        /// </summary>
        /// <param name="SQLText">SQL命令</param>
        /// <returns>返回DataReader</returns>
        public DbDataReader ExecuteReader(string SQLText)
        {
            if (IsProcedure(SQLText)) { mCommand.CommandType = CommandType.StoredProcedure; } else { mCommand.CommandType = CommandType.Text; }
            mCommand.CommandText = SQLText;
            mCommand.Connection.Open();
            try
            {
                return mCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            finally
            { ClearParameters(); }
        }

        /// <summary>
        /// 执行统计的方法
        /// </summary>
        /// <param name="SQLText">SQL命令</param>
        /// <returns>返回object对象代表统计数据或者结果的第一列第一行</returns>
        public object ExecuteScalar(string SQLText)
        {
            if (IsProcedure(SQLText)) { mCommand.CommandType = CommandType.StoredProcedure; } else { mCommand.CommandType = CommandType.Text; }
            mCommand.CommandText = SQLText;
            try
            {
                mCommand.Connection.Open();
                return mCommand.ExecuteScalar();
            }
            finally
            {
                mCommand.Connection.Close();
                ClearParameters();
            }
        }

        /// <summary>
        /// 执行查询返回填充的DataSet对象
        /// </summary>
        /// <param name="SQLText">SQl命令</param>
        /// <param name="VisualTableName">虚拟表名</param>
        /// <param name="StartIndex">制定返回多少行以后的数据</param>
        /// <param name="Count">制定总共返回多少行</param>
        /// <returns>返回按要求填充了的DataSet</returns>
        //public DataSet ExecuteDataSet(string SQLText, string VisualTableName, int StartIndex, int Count)
        public DataSet ExecuteDataSet(string SQLText)
        {
            DataSet ds = new DataSet();
            if (IsProcedure(SQLText)) { mCommand.CommandType = CommandType.StoredProcedure; } else { mCommand.CommandType = CommandType.Text; }
            mCommand.CommandText = SQLText;
            try
            {
                mCommand.Connection.Open();
                //mDataAdapter.Fill(ds, StartIndex, Count, VisualTableName);
                mDataAdapter.Fill(ds);
                return ds;
            }
            finally
            {
                mCommand.Connection.Close();
                ClearParameters();
            }
        }
        //下面为重载调用,不包含实际代码
        //public DataSet ExecuteDataSet(string SQLText, int StartIndex, int Count) { return ExecuteDataSet(SQLText, "Table1", StartIndex, Count); }
        //public DataSet ExecuteDataSet(string SQLText, string VisualTableName) { return ExecuteDataSet(SQLText, VisualTableName, 0, 0); }
        //public DataSet ExecuteDataSet(string SQLText) { return ExecuteDataSet(SQLText, "Table1", 0, 0); }

        /// <summary>
        /// 添加一个参数
        /// </summary>
        /// <param name="ParameterName">参数的名称</param>
        /// <param name="Value">参数的值</param>
        /// <param name="Type">参数值的类型</param>
        /// <param name="Size">参数值的大小</param>
        /// <param name="Direction">参数的返回类型</param>
        /// <returns>返回添加后的参数对象DbParameter</returns>
        public DbParameter AddParameter(string ParameterName, object Value, DbType Type, int Size, ParameterDirection Direction)
        {
            DbParameter dbp = mCommand.CreateParameter();
            dbp.ParameterName = ParameterName;
            dbp.Value = Value;
            dbp.DbType = Type;
            if (Size != 0) { dbp.Size = Size; }
            dbp.Direction = Direction;
            mCommand.Parameters.Add(dbp);
            return dbp;
        }
        //下面为重载调用,不包含实际代码        
        public DbParameter AddParameter(string ParameterName, object Value, DbType Type, int Size) { return AddParameter(ParameterName, Value, Type, Size, ParameterDirection.Input); }
        /// <summary>
        /// 清除DbParameterCollection中所有DbParameter的引用
        /// </summary>
        public void ClearParameters()
        {
            mCommand.Parameters.Clear();
        }

        public DbParameterCollection Parameters
        {
            get { return mCommand.Parameters; }
        }

        public DbCommand Command
        {
            get { return mCommand; }
        }

        public DbDataAdapter DataAdapter
        {
            get { return mDataAdapter; }
        }
    }

通过参数化调用

        public bool IsExist_tmp(Esa_Office.Model.news_Template model)
        {
            myData.AddParameter("@tempName", model.tempName);
            string sqlstr = "select * from Template where tempName=@tempName";
            DataTable dt = myData.ExecuteDataSet(sqlstr).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else return false;
        }

[exception92]

if (SQLText.Contains(" ")) ->存储过程名称和后边的参数是以一个空格为分割。应该类似这样：

EXECUTE Proc_GetName param1,param2

[不胖到130不改名字]

你平时sqlstr怎么传参就怎么传 var sqlstr = "exec proc1 @tempName"

[FainSheeg]

有什么不同吗？你直接用SQL语句的时候，主要的无非是三个东西要设置：
一：sqltext="select * from table where id=@id"
二：cmd.CommandType=CommandType.Text
三：cmd.Parameters.Add

改用存储过程的话
一：sqltext="procedureName"
二：cmd.CommandType=CommandType.Procedure
三：跟上面一样

[果然C]

[zhangfengyi]

问题来了，我在程序中这样调用，strsql = "exec uLogin @username,@password,@merchantno,@usertype";
找不到存储过程 'exec uLogin @username,@password,@merchantno,@usertype'，
但是在sql server中是可以执行的
exec uLogin 'admin','1443A360776CF6419871FA0F5A8178E9','11','2'
有说是权限的问题，加上了dbo.uLogin，还是报找不到存储过程。

[EdsionWang]

  public bool IsExist_tmp(Esa_Office.Model.news_Template model)
        {
            myData.AddParameter("@userName", username);
            myData.AddParameter("@password", passwd);
            myData.AddParameter("@merchantno", merchantno);
           myData.AddParameter("@usertype",  usertype);
            string sqlstr = "exec uLogin";
            DataTable dt = myData.ExecuteDataSet(sqlstr).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else return false;
        }

[zhangfengyi]

引用 5 楼 EdsionWang 的回复:

  public bool IsExist_tmp(Esa_Office.Model.news_Template model)
        {
            myData.AddParameter("@userName", username);
            myData.AddParameter("@password", passwd);
            myData.AddParameter("@merchantno", merchantno);
           myData.AddParameter("@usertype",  usertype);
            string sqlstr = "exec uLogin";
            DataTable dt = myData.ExecuteDataSet(sqlstr).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else return false;
        }

找不到存储过程 'exec uLogin'。
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。 
异常详细信息: System.Data.SqlClient.SqlException: 找不到存储过程 'exec uLogin'。
源错误: 
行 199:                mCommand.Connection.Open();
行 200:                //mDataAdapter.Fill(ds, StartIndex, Count, VisualTableName);
行 201:                mDataAdapter.Fill(ds);
行 202:                return ds;
行 203:            }

源文件: E:\hyl_Manager\Esa_JianQuan\trunk\DBUtility\DBHelper.cs    行: 201 
堆栈跟踪: 
[SqlException (0x80131904): 找不到存储过程 'exec uLogin'。]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1960362
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4890571
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2412
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +59
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +293
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
   Esa_Office.DBUtility.DataBase.ExecuteDataSet(String SQLText) in E:\hyl_Manager\Esa_JianQuan\trunk\DBUtility\DBHelper.cs:201
   Esa_Office.DAL.LoginInfo.uLogin(User model) in E:\hyl_Manager\News\DAL\LoginInfo.cs:34
   Esa_Office.BLL.LoginInfo.uLogin(User user) in E:\hyl_Manager\News\BLL\LoginInfo.cs:18
   Login.IbtnEnter_Click(Object sender, EventArgs e) in e:\hyl_Manager\News\WEB\Login.aspx.cs:209
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

[zhangfengyi]

[EdsionWang]

引用 5 楼 EdsionWang 的回复:

  public bool IsExist_tmp(Esa_Office.Model.news_Template model)
        {
            myData.AddParameter("@userName", username);
            myData.AddParameter("@password", passwd);
            myData.AddParameter("@merchantno", merchantno);
           myData.AddParameter("@usertype",  usertype);
            string sqlstr = "uLogin";
            DataTable dt = myData.ExecuteDataSet(sqlstr).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else return false;
        }

去掉exec，只写存储过程名就可以了。

[zhangfengyi]

引用 8 楼 EdsionWang 的回复:

[quote=引用 5 楼 EdsionWang 的回复:]

  public bool IsExist_tmp(Esa_Office.Model.news_Template model)
        {
            myData.AddParameter("@userName", username);
            myData.AddParameter("@password", passwd);
            myData.AddParameter("@merchantno", merchantno);
           myData.AddParameter("@usertype",  usertype);
            string sqlstr = "uLogin";
            DataTable dt = myData.ExecuteDataSet(sqlstr).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else return false;
        }

去掉exec，只写存储过程名就可以了。[/quote]
这个类是网上下载的，有问题：

if (IsProcedure(SQLText)) { mCommand.CommandType = CommandType.StoredProcedure; } else { mCommand.CommandType = CommandType.Text; }
mCommand.CommandText = SQLText;

当判断是存储过程的时候，mCommand.CommandText不应该传直接SQLText，应该分割后再传，修改后

if (IsProcedure(SQLText)) 
            { 
                mCommand.CommandType = CommandType.StoredProcedure;
                mCommand.CommandText = SQLText.Split(' ')[1].ToString();
            } 
            else 
            { 
                mCommand.CommandType = CommandType.Text;
                mCommand.CommandText = SQLText;
            }

现在报：过程或函数 'uLogin' 需要参数 '@usertype'，但未提供该参数。应该是参数没传进来，还的解决传参的问题

[正怒月神]

我觉得，你直接下载一个包含存储过程的 dbhelper就好了。 至于直接sql运行，我记得exec procName 参数 好像还要设置commandtype=proc。 太久不用了。

[zhangfengyi]

可以了，参数就按照上边的就可以了，谢谢各位！