分数不够了 ...
存储过程 alter proc data
(
@yhm varchar(50) ,
@yhml varchar(20) ,
@js nchar(10) ,
@bj varchar(20) output,
@bj1 int
)
as
set @bj=''
set @bj1=''
begin
if exists( select * from 论坛登陆表 where 用户名=@yhm)
begin
if exists(select * from 论坛登陆表 where 用户名=@yhm and 用户命令=@yhml and 角色= @js)
begin
set @bj='登陆成功'
set @bj1='1'
end
else
begin
set @bj='密码不正确'
set @bj1='0'
end
end
else
begin
set @bj='用户名不存在'
set @bj1='0'
end
end
go调用的方法 ..
我调动的方法是
public bool ExecProc(string strProcName, string[] strInputPars, ArrayList alInputValues, string[] strOutputPars, ref ArrayList alOutputValues)
{
cmdStudentManage = new SqlCommand();
cmdStudentManage.Connection = cnn;
cmdStudentManage.CommandType = CommandType.StoredProcedure;
cmdStudentManage.CommandText = strProcName;
SqlParameter parmReturnValue;
try
{
for (int i = 0; i < strInputPars.Length; i++)
cmdStudentManage.Parameters.Add("@" + strInputPars[i], alInputValues[i]); for (int i = 0; i < strOutputPars.Length; i++)
{
if (alOutputValues[i] is int)
cmdStudentManage.Parameters.Add("@" + strOutputPars[i], SqlDbType.Int);
else if (alOutputValues[i] is string)
cmdStudentManage.Parameters.Add("@" + strOutputPars[i], SqlDbType.VarChar, 200);
cmdStudentManage.Parameters["@" + strOutputPars[i]].Direction = System.Data.ParameterDirection.Output;
} parmReturnValue = cmdStudentManage.Parameters.Add("ReturnValue", SqlDbType.Int);
parmReturnValue.Direction = ParameterDirection.ReturnValue;
cmdStudentManage.ExecuteNonQuery(); for (int i = 0; i < strOutputPars.Length; i++)
{
alOutputValues[i] = cmdStudentManage.Parameters["@" + strOutputPars[i]].Value;
}
return true;
}
catch (Exception e1)
{
return false;
}
finally
{
ConnectionX.CloseConnection(cnn);
}
}
各位大侠给我看看 如果调用此方法应该怎么写.... 如果不清楚 请看我上次发的帖子
http://topic.csdn.net/u/20091224/10/3e092205-7aed-4c34-abd9-caf05ed46c5b.html?seed=49473131&r=62302915#r_62302915
存储过程 alter proc data
(
@yhm varchar(50) ,
@yhml varchar(20) ,
@js nchar(10) ,
@bj varchar(20) output,
@bj1 int
)
as
set @bj=''
set @bj1=''
begin
if exists( select * from 论坛登陆表 where 用户名=@yhm)
begin
if exists(select * from 论坛登陆表 where 用户名=@yhm and 用户命令=@yhml and 角色= @js)
begin
set @bj='登陆成功'
set @bj1='1'
end
else
begin
set @bj='密码不正确'
set @bj1='0'
end
end
else
begin
set @bj='用户名不存在'
set @bj1='0'
end
end
go调用的方法 ..
我调动的方法是
public bool ExecProc(string strProcName, string[] strInputPars, ArrayList alInputValues, string[] strOutputPars, ref ArrayList alOutputValues)
{
cmdStudentManage = new SqlCommand();
cmdStudentManage.Connection = cnn;
cmdStudentManage.CommandType = CommandType.StoredProcedure;
cmdStudentManage.CommandText = strProcName;
SqlParameter parmReturnValue;
try
{
for (int i = 0; i < strInputPars.Length; i++)
cmdStudentManage.Parameters.Add("@" + strInputPars[i], alInputValues[i]); for (int i = 0; i < strOutputPars.Length; i++)
{
if (alOutputValues[i] is int)
cmdStudentManage.Parameters.Add("@" + strOutputPars[i], SqlDbType.Int);
else if (alOutputValues[i] is string)
cmdStudentManage.Parameters.Add("@" + strOutputPars[i], SqlDbType.VarChar, 200);
cmdStudentManage.Parameters["@" + strOutputPars[i]].Direction = System.Data.ParameterDirection.Output;
} parmReturnValue = cmdStudentManage.Parameters.Add("ReturnValue", SqlDbType.Int);
parmReturnValue.Direction = ParameterDirection.ReturnValue;
cmdStudentManage.ExecuteNonQuery(); for (int i = 0; i < strOutputPars.Length; i++)
{
alOutputValues[i] = cmdStudentManage.Parameters["@" + strOutputPars[i]].Value;
}
return true;
}
catch (Exception e1)
{
return false;
}
finally
{
ConnectionX.CloseConnection(cnn);
}
}
各位大侠给我看看 如果调用此方法应该怎么写.... 如果不清楚 请看我上次发的帖子
http://topic.csdn.net/u/20091224/10/3e092205-7aed-4c34-abd9-caf05ed46c5b.html?seed=49473131&r=62302915#r_62302915
/// <summary>
/// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
} return command;
}
我觉得你写的这两个方法只可以写在页面 ...
我前面的哪个方法的返回值是 bool类型的
而你写的方法的返回值是 SqlCommand SqlDataReader
在三层里怎么写呀!
/// <summary>
/// 准备存储过程所需参数
/// </summary>
/// <returns></returns>
public SqlParameter[] GetLoginParameter()
{
SqlParameter[] parameter =
{
new SqlParameter("@yhm", SqlDbType.VarChar, 50), // 用户名
new SqlParameter("@yhml", SqlDbType.VarChar, 20),//用户密码
new SqlParameter("@js", SqlDbType.NVarChar, 10),// 角色
new SqlParameter("@bj", SqlDbType.VarChar, 20),
new SqlParameter("@bj1", SqlDbType.Int, 4)
};
return parameter;
} /// <summary>
/// 调用存储过程
/// </summary>
/// <param name="userName"></param>
/// <param name="passsword"></param>
/// <param name="role"></param>
/// <param name="bj1"></param>
/// <param name="result"></param>
/// <returns></returns>
public DataSet GetLoginDataSet(string userName, string passsword, string role, int bj1, ref string result)
{
SqlParameter[] parameter = GetLoginParameter(); parameter[0].Value = userName;
parameter[1].Value = parameter;
parameter[2].Value = role;
parameter[3].Direction = ParameterDirection.Output;//声明为输出类型
parameter[4].Value = bj1;//说实话,我没看出来你这个bj1在存储过程中有什么用处 DataSet ds = RunProcedure("data", parameter, "论坛登陆表");
result = parameter[3].ToString();
return ds;
}调用具体没测试,呵呵大概是这样private bool Login(string userName, string passsword, string role, int bj1, ref string result)
{
string result = "";
DataSet ds = new DataSet();
ds = GetLoginDataSet(userName, passsword, role, bj1, ref result);
if(ds != null && result == "登录成功")
{
return true;
}
else
{
return false;
}
}
}存储过程操作方法: /// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
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();
return dataSet;
}
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
} return command;
}