存储过程的使用

下边是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 

 更多关于存储过程的请查看下边链接

http://zhidao.baidu.com/question/55801433

转载于:https://www.cnblogs.com/GreenGrass/archive/2012/08/16/2642199.html