存储过程的使用
下边是C#实现存储过程传递参数的代码
public DataTable GetCardRecordeByEnglishName(string displayNamey, DateTime startTime, DateTime endTimes)
{
string strConn = ConfigurationManager.AppSettings["CardConnection"];
SqlParameter[] prams = {
Database.MakeInParam("@f_EnglishName",System.Data.SqlDbType.NVarChar,50,displayNamey),
Database.MakeInParam("@f_StartTime",System.Data.SqlDbType.DateTime,8,startTime),
Database.MakeInParam("@f_EndTime",System.Data.SqlDbType.DateTime,8,endTimes)
};
return new SqlPlus(strConn).ExecuteDataTable(CommandType.StoredProcedure, "proc_GetCardRecordByTime", prams);
}
/// <summary>
/// 执行SQL命令，返回DataTable
/// </summary>
public DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
DataTable oTab = new DataTable(cmdText);
using (SqlConnection connection = new SqlConnection(this.SqlConnString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(oTab);
}
return oTab;
}
/// <summary>
/// 执行SQL命令
/// </summary>
private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandTimeout = 120;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
数据库新建存储过程
CREATE PROCEDURE [dbo].[Test]
@dt datetime,
@allLinename varchar(10)
AS
select * from allline where date=@dt and name=@allLineName
注：存储过程参数如果不写类型则默认为输入参数,参数是varchar时要标注其长度
create proc SelectWebSet
@c varchar(10) output
as
exec('select '+@c+' from webset where id=1')
go
更多关于存储过程的请查看下边链接
