在线等回复

Question

谁能给出一个C#调用存储过程的完整的实例 这是存储过程
USE BookInfo 
go 
if exists (select * from sysobjects where name='proc_book')
drop procedure proc_book
go
create procedure proc_book
@cno varchar(20),
@name varchar(20) outputASselect NAME from CARD where CNO=@cnogo
其中cno是编号、name是名字

Answer 1

http://www.cnblogs.com/kepton/archive/2011/07/19/2111040.html

Answer 2

 public class DBHelperSQL
    {
        private string connectionString = null;
        public DBHelperSQL(string connString)
        {
            this.connectionString = connString;
        }
         /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名，默认为 default </param>
        /// <returns>DataSet</returns>
        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            DateTime dtStart = DateTime.Now;
            if (tableName == null)
            {
                tableName = "default";
            }
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                string sql = storedProcName;
                foreach (SqlParameter p in parameters)
                {
                    sql += "@" + p.Value;
                }
                writeLog(dtStart, sql);
                return dataSet;
            }
        }
}

Answer 3

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[InsertUsers]
(
    @UserName varchar(255),@Password varchar(255),@UserID int output
)
as
insert into Users(UserName,Password)values(@UserName,@Password)
set @UserID=@@Identity
调用
 using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
            conn.Open();
            SqlCommand cmd = new SqlCommand("[InsertUsers]", 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("@UserID", 0)).Direction = ParameterDirection.Output;
            int result = cmd.ExecuteNonQuery();
            Response.Write(result + " UserID：" + cmd.Parameters["@UserID"].Value);
        }

Answer 4

存储过程
use post 
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[Pr_UpdateItemVoteCount]
(
@id int
)
ASUPDATE 
postchoose
SET
hits = hits + 1
WHERE
id = @id
//调用
        SqlConnection myConnection = new SqlConnection(@"Data Source=(local);
                          Initial Catalog=post;Integrated Security=True"); //创建连接
///创建Command
SqlCommand myCommand = new SqlCommand("Pr_UpdateItemVoteCount",myConnection);
///设置为执行存储过程
myCommand.CommandType = CommandType.StoredProcedure; ///添加存储过程的参数
SqlParameter pItemID = new SqlParameter("@id",SqlDbType.Int,4);
pItemID.Value = nItemID;
myCommand.Parameters.Add(pItemID);