存储过程一般怎么使用 啊

[sirzxj]

如题 ？

[苍月]

首先建立存储过程
然后在需要的地方调用？ ...
LZ你要说 具体点 这个还真不好回答清楚噢 ...
要有具体的例子分析 ？ ...
我也是菜鸟 ..

[neng168]

给你看个例子

using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] s)
{
SqlConnection cn = new
SqlConnection("server=.\\SQLEXPRESS;database=master;uid=sa;pwd=");
try
{
cn.Open();
SqlCommand cmd = cn.CreateCommand();
//设置命令类型为存储过程
cmd.CommandType = CommandType.StoredProcedure;
//设置为存储过程名字
cmd.CommandText = "ch_Person";
//执行存储过程
cmd.ExecuteNonQuery();
}
catch (SqlException sqle)
{
Console.WriteLine("操作数据库出错:" + sqle.Message);
}
finally
{
cn.Close();
}
}
}

[sirzxj]

[Quote=引用 1 楼 forkerrigan 的回复:]

首先建立存储过程
然后在需要的地方调用？ ...
LZ你要说 具体点 这个还真不好回答清楚噢 ...
要有具体的例子分析 ？ ...
我也是菜鸟 ..
[/Quote]

一个 简单的 示例？

[subxli]

存储过程一般怎么使用

[rart2008]

create procedure 存储过程名称
@id int--参数
as
select * from city where id = @id

exec 存储过程名称 参数

[子夜__]

 /// <summary>
        /// 执行有参的查询 返回DataTable
        /// </summary>
        /// <param name="cmdtext">存储过程名称或SQL语句</param>
        /// <param name="ct">命令类型</param>
        /// <param name="para">参数数组</param>
        /// <returns>返回DataTable</returns>
        public static DataTable ReturnDataTable(string cmdtext, CommandType ct, SqlParameter[] para)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdtext, GetConn());
            cmd.CommandType =CommandType.StoredProcedure;
            cmd.Parameters.AddRange(para);//添加参数
            SqlDataReader dr = null;
            using (dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(dr);
            }
            return dt;
        }

定义一个查询的存储过程

DEMO

  public int TeacherApp_add(M_Model.Parameters parameter)
        {
            IDataParameter[] iData = new SqlParameter[5];
            iData[0] = new SqlParameter("@TableName", "S_Appraisalnote");//添加参数
            iData[1] = new SqlParameter("@sqlcolumns", parameter.Sqlcolumns);
            iData[2] = new SqlParameter("@sqlstrpara", parameter.NewSqlstrpara);
            iData[3] = new SqlParameter("@S_id", parameter.S_id);
            iData[4] = new SqlParameter("@C_id", parameter.ClassID);
            DataComponent dac = new DataComponent();
            int value = dac.ExecuteNonQuery("Proc_InsertS_Appraisalnote", iData);
            dac.Close();
            return value;
        }

参考

[心灵彩虹]

namespace Utility.DataAccessHelper.SqlServer
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections;
    /// <summary>
    /// 实用工具类，用于简化存储过程的调用。
    /// </summary>
    public class StoredProcedure
    {
        // 数据库连接字符串。
        private string connectionString;
        // 存储过程名称。
        private string storedProcedureName;
        // 存储过程输出参数值列表。
        private Hashtable outputValues;
        // 存储过程返回值
        private int returnValue;
        /// <summary>
        /// 初始化  StoredProceduer 对象。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串。</param>
        /// <example>
        /// string conStr = "@Data Source=server;Initial Catalog=database;Integrated Security=true";
        /// StoredProcedure sp = new StoredProcedure(conStr);
        /// </example>
        public StoredProcedure(string connectionString) {
            this.connectionString = connectionString;
            this.storedProcedureName = string.Empty;
            this.outputValues = new Hashtable();
        }
        /// <summary>
        /// 初始化 StoredProceduer 对象。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串。</param>
        /// <param name="storeProcedureName">存储过程名称。</param>
        /// <example>
        /// string conStr = "@Data Source=server;Initial Catalog=database;Integrated Security=true";
        /// string procName = "usp_xxxxxx";
        /// StoredProcedure sp = new StoredProcedure(conStr, procName);
        /// </example>
        public StoredProcedure(string connectionString, string storeProcedureName) {
            this.connectionString = connectionString;
            this.storedProcedureName = storeProcedureName;
            this.outputValues = new Hashtable();
        }
        /// <summary>
        /// 获取或设置存储过程名称。
        /// </summary>
        public string StoreProcedureName {
            get { return this.storedProcedureName; }
            set { this.storedProcedureName = value; }
        }
        /// <summary>
        /// 获取输出参数值列表。
        /// </summary>
        /// <example>
        /// object ret = sp.OutputValues["@p1"]; // @p1 是存储过程中的输出参数名。
        /// </example>
        public Hashtable OutputValues {
            get { return this.outputValues; }            
        }
        /// <summary>
        /// 获取存储过程返回值。
        /// int ret = sp.ReturnValue;
        /// </summary>
        public int ReturnValue {
            get { return this.returnValue; }
        }
        /// <summary>
        /// 执行 Insert/Delete/Update 存储过程。
        /// </summary>
        /// <param name="paraValues">传递给存储过程的参数值列表。</param>
        /// <returns>受影响的行数。</returns>
        /// <example> 
        /// int id = 1;
        /// string name = "abc";
        /// string phone = "123";
        /// 
        /// string conStr = "@Data Source=server;Initial Catalog=database;Integrated Security=true";
        /// string procName = "usp_Insert";
        /// StoredProcedure sp = new StoredProcedure(conStr, procName);
        /// try{
        ///     // 参数个数及顺序需要与存储过程中的一致。
        ///     sp.ExecuteNonQuery(id, name, phone);
        ///     switch(sp.ReturnValue){
        ///         case 1: break;
        ///         // ......
        ///     }
        /// 
        /// }catch(SqlException ex){
        /// }catch(Exception ex){
        /// }
        /// </example>
        /// <remarks>使用函数返回值时注意主子表级联更新的影响，
        /// 建议使用 ReturnValue 而不是函数返回值来判断存储过程是否成功执行。
        /// </remarks>
        public int ExecuteNonQuery(params object[] paraValues) {
            using (SqlConnection connection = new SqlConnection(this.connectionString)) {
                SqlCommand sqlCommand = this.CreateSqlCommand(connection);
                try {
                    this.DeriveParameters(sqlCommand);
                    this.AssignParameterValues(sqlCommand, paraValues);
                    connection.Open();
                    int affectedRowsCount = sqlCommand.ExecuteNonQuery();
                    this.returnValue = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value;
                    this.setOutputValues(sqlCommand);
                    connection.Close();
                    return affectedRowsCount;
                } catch {
                    throw;
                }
            }
        }
        /// <summary>
        /// 执行存储过程，返回 System.Data.DataTable。
        /// </summary>
        /// <param name="paraValues">传递给存储过程的参数值列表。</param>
        /// <returns>包含查询结果的 System.Data.DataTable。</returns>
        /// <example>
        /// string conStr = "@Data Source=server;Initial Catalog=database;Integrated Security=true";
        /// string procName = "usp_Select";
        /// StoredProcedure sp = new StoredProcedure(conStr, procName);
        /// 
        /// int id = 1;
        /// DataTable dt;
        /// try{
        ///     // 参数个数及顺序需要与存储过程中的一致。
        ///     dt = sp.ExecuteDataTable(id);
        /// }catch(SqlException ex){
        /// }catch(Exception ex){
        /// }
        /// </example>
       

[心灵彩虹]

接上面

 public DataTable ExecuteDataTable(params object[] paraValues) {
            using (SqlConnection connection = new SqlConnection(this.connectionString)) {
                SqlCommand sqlCommand = this.CreateSqlCommand(connection);
                try {
                    this.DeriveParameters(sqlCommand);
                    this.AssignParameterValues(sqlCommand, paraValues);
                    SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    connection.Close();
                    return dataTable;
                } catch {
                    throw;
                }
            }
        }
        /// <summary>
        /// 执行存储过程，填充指定的 System.Data.DataTable。
        /// </summary>
        /// <param name="dataTable">用于填充查询结果的 System.Data.DataTable。</param>
        /// <param name="paraValues">传递给存储过程的参数值列表。</param>
        /// <example> 
        /// string conStr = "@Data Source=server;Initial Catalog=database;Integrated Security=true";
        /// string procName = "usp_Select";
        /// StoredProcedure sp = new StoredProcedure(conStr, procName);
        /// 
        /// int id = 1;
        /// DataTable dt = new DataTable();
        /// try{
        ///     // 参数个数及顺序需要与存储过程中的一致。
        ///     sp.ExecuteFillDataTable(dt, id);
        /// }catch(SqlException ex){
        /// }catch(Exception ex){
        /// }
        /// </example>
        public void ExecuteFillDataTable(DataTable dataTable, params object[] paraValues) {
            using (SqlConnection connection = new SqlConnection(this.connectionString)) {
                SqlCommand sqlCommand = this.CreateSqlCommand(connection);
                try {
                    this.DeriveParameters(sqlCommand);
                    this.AssignParameterValues(sqlCommand, paraValues);
                    SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
                    adapter.Fill(dataTable);
                    connection.Close();
                } catch {
                    throw;
                }
            }
        }
        /// <summary>
        /// 执行存储过程返回 System.Data.SqlClient.SqlDataReader，
        /// 在 System.Data.SqlClient.SqlDataReader 对象关闭时，数据库连接自动关闭。
        /// </summary>
        /// <param name="paraValues">传递给存储过程的参数值列表。</param>
        /// <returns>包含查询结果的 System.Data.SqlClient.SqlDataReader 对象。</returns>
        /// <example>
        /// string conStr = "@Data Source=server;Initial Catalog=database;Integrated Security=true";
        /// string procName = "usp_Select";
        /// StoredProcedure sp = new StoredProcedure(conStr, procName); 
        /// 
        /// int id = 1;
        /// SqlDataReader sdr;
        /// try{
        ///     // 参数个数及顺序需要与存储过程中的一致。
        ///     sdr = p.ExecuteDataReader(id);
        /// }catch(SqlException ex){
        /// }catch(Exception ex){
        /// }
        /// </example>
        public SqlDataReader ExecuteDataReader(params object[] paraValues) {
            SqlConnection connection = new SqlConnection(this.connectionString);
            SqlCommand sqlCommand = this.CreateSqlCommand(connection);
            try {
                this.DeriveParameters(sqlCommand);
                this.AssignParameterValues(sqlCommand, paraValues);
                connection.Open();
                return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            } catch {
                throw;
            }
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="paraValues">传递给存储过程的参数值列表。</param>
        /// <returns>结果集中第一行的第一列或空引用（如果结果集为空）。</returns>
        /// <example> 
        /// string conStr = "@Data Source=server;Initial Catalog=database;Integrated Security=true";
        /// string procName = "usp_Select";
        /// StoredProcedure sp = new StoredProcedure(conStr, procName);
        /// 
        /// int id = 1;
        /// string name = "";
        /// try{
        ///     // 参数个数及顺序需要与存储过程中的一致。
        ///     name = sp.ExecuteScalar(id);
        /// }catch(SqlException ex){
        /// }catch(Exception ex){
        /// }
        /// </example>
        public object ExecuteScalar(params object[] paraValues) {
            using (SqlConnection connection = new SqlConnection(this.connectionString)) {
                SqlCommand sqlCommand = this.CreateSqlCommand(connection);
                try {
                    this.DeriveParameters(sqlCommand);
                    this.AssignParameterValues(sqlCommand, paraValues);
                    connection.Open();
                    object retVale = sqlCommand.ExecuteScalar();
                    connection.Close();
                    return retVale;
                } catch {
                    throw;
                }
            }
        }

        /// <summary>
        /// 从在 System.Data.SqlClient.SqlCommand 中指定的存储过程中检索参数信息并填充指定的 
        /// System.Data.SqlClient.SqlCommand 对象的 System.Data.SqlClient.SqlCommand.Parameters 集合。
        /// </summary>
        /// <param name="sqlCommand">将从其中导出参数信息的存储过程的 System.Data.SqlClient.SqlCommand 对象。</param>
        internal void DeriveParameters(SqlCommand sqlCommand) {
            try {
                sqlCommand.Connection.Open();
                SqlCommandBuilder.DeriveParameters(sqlCommand);
                sqlCommand.Connection.Close();
            } catch {
                throw;
            }
        }
        // 用指定的参数值列表为存储过程参数赋值。
        private void AssignParameterValues(SqlCommand sqlCommand, params object[] paraValues) {
            if (paraValues != null) {
                if ((sqlCommand.Parameters.Count - 1) != paraValues.Length) {
                    throw new Exception("传递的参数与存储过程参数不匹配。\n Stored Procedure Name: " + this.storedProcedureName);
                }
                for (int i = 0; i < paraValues.Length; i++) {
                    sqlCommand.Parameters[i + 1].Value = (paraValues[i] == null) ? DBNull.Value : paraValues[i];
                }
            }
        }

        // 创建用于执行存储过程的 SqlCommand。
        private SqlCommand CreateSqlCommand(SqlConnection connection) {
            SqlCommand command = new SqlCommand(this.storedProcedureName, connection);
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }

        // 获取存储过程的输出参数值列表。
        private void setOutputValues(SqlCommand sqlCommand) {
            this.outputValues.Clear();
            foreach (SqlParameter para in sqlCommand.Parameters) {
                if (para.Direction == ParameterDirection.Output || para.Direction == ParameterDirection.InputOutput) {
                    this.outputValues.Add(para.ParameterName, para.Value);
                }
            }
        }
    }
}

[新时代新目标新征程]

调用存储过程的执行方法

[exiori]

操作数据库啊

[flyerwing]

[Quote=引用 2 楼 neng168 的回复:]
给你看个例子

using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] s)
{
SqlConnection cn = new
SqlConnection("server=.\\SQLEXPRESS;databa……
[/Quote]
就是预先便宜并存储的TSQL语句吗,改怎么用就怎么用了.

[纯唇Yu弄]

[Quote=引用 6 楼 wxr0323 的回复:]

C# code
/// <summary>
/// 执行有参的查询 返回DataTable
/// </summary>
/// <param name="cmdtext">存储过程名称或SQL语句</param>
/// <param name="ct">命令类型</param>
/// <param name……
[/Quote]

+1

[huangwenquan123]

--拿之前回答的一个例子--
alter procedure proc_login
        @UserName varchar(255),
        @Password varchar(255),
        @state int output,
        @count int output,
        @ID int output
as
begin
declare @record int
set @record = (select count(1) from Users where UserName = @UserName)
if (@record>0) 
begin
   select @count=[count],@ID=ID from Users where UserName = @UserName and Password = @Password
   if @@rowcount > 0
   begin
    update Users set [Count]=[Count]+1 where UserName=@UserName
    set @state = 1
   end
   else 
   begin
    set @state = 2
    set @count=0
    set @id=0
   end 
end 
else
begin
    set @state = 3
    set @count=0
    set @id=0
end
end

    protected void Button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
            conn.Open();
            SqlCommand cmd = new SqlCommand("[proc_login]", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@UserName", TextBox1.Text));
            cmd.Parameters.Add(new SqlParameter("@Password", TextBox2.Text));
            cmd.Parameters.Add(new SqlParameter("@state", 0)).Direction = ParameterDirection.Output;
            cmd.Parameters.Add(new SqlParameter("@count", 0)).Direction = ParameterDirection.Output;
            cmd.Parameters.Add(new SqlParameter("@ID", 0)).Direction = ParameterDirection.Output;
            int result = cmd.ExecuteNonQuery();
            int count = Convert.ToInt32(cmd.Parameters["@count"].Value);
            int id = Convert.ToInt32(cmd.Parameters["@id"].Value);
            int state = Convert.ToInt32(cmd.Parameters["@state"].Value);
            if (state == 3)
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('不存在该用户')</script>");
            else if (state == 2)
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('密码错误')</script>");
            else
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('登陆次数为：" + (count + 1) + " 用户ID：" + id + "')</script>");
        }
    }