在线等回复
谁能给出一个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) output
AS
select NAME from CARD where CNO=@cno
go
其中cno是编号、name是名字 --------------------编程问答-------------------- http://www.cnblogs.com/kepton/archive/2011/07/19/2111040.html --------------------编程问答--------------------
--------------------编程问答--------------------
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;
}
}
}
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);
}
存储过程
use post
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[Pr_UpdateItemVoteCount]
(
@id int
)
AS
UPDATE
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);
补充：.NET技术 , ASP.NET
