首页 > 代码库 > 采用存储过程写的登录和查询(只为测试)
采用存储过程写的登录和查询(只为测试)
未采用三层
Sql 语句 :
create procedure sp_Login3
@userName varchar(20),
@passWord varchar(20),
@msg int Output //表示输出参数
as
if(exists(select * from UserInfo where LoginId=@userName and [passWord]=@passWord))
begin
set @msg=1
end
else
begin
set @msg=0
end
cs后台代码,我这里没有分层:
string sql="server=;database=;uid=;pwd=";
//创建连接
using(Sqlconnection conn=new sqlconnection(sql)){
conn.open();
sqlcommand cpm=new sqlcommand("sp_Login3",conn); //"sp_Login3"存储过程名
com.commandType=CommandType.StoredProcedure;
SqlParameter spname = new SqlParameter("@userName", SqlDbType.VarChar, 20);
SqlParameter sppwd = new SqlParameter("@PassWord", SqlDbType.VarChar, 20);
SqlParameter spint = new SqlParameter("@msg", SqlDbType.Int);
spname.Value = http://www.mamicode.com/TextBox1.Text;
sppwd.Value = http://www.mamicode.com/TextBox2.Text;
spint.Direction = ParameterDirection.Output;
com.Parameters.Add(spname);
com.Parameters.Add(sppwd);
com.Parameters.Add(spint);
com.ExecuteNonQuery();
int count = Convert.ToInt32(spint.Value);
if (count > 0)
{
Context.Response.Write("登录成功");
}
else
{
Context.Response.Write("登录失败");
}
}
查询:
string sql = "server=;database=MyOffice;uid=;pwd=";
SqlConnection conn = new SqlConnection(sql);
conn.Open();
SqlCommand com = new SqlCommand("Sp_sa2", conn);
com.CommandType = CommandType.StoredProcedure;
DataSet st = new DataSet();
SqlDataAdapter dr = new SqlDataAdapter(com);
dr.Fill(st);
List<UserInfo> list = new List<UserInfo>();
for (int i = 0; i < st.Tables[0].Rows.Count; i++)
{
UserInfo user = new UserInfo();
user.Id = (int)st.Tables[0].Rows[i]["Id"];
user.LoginId = (string)st.Tables[0].Rows[i]["LoginId"];
user.UserName = (string)st.Tables[0].Rows[i]["UserName"];
user.PassWord = (string)st.Tables[0].Rows[i]["PassWord"];
user.Gender = (int)st.Tables[0].Rows[i]["Gender"];
user.DepartId=(int)st.Tables[0].Rows[i]["DepartId"];
user.RoleId = (int)st.Tables[0].Rows[i]["RoleId"];
user.UserStateId = (int)st.Tables[0].Rows[i]["UserStateId"];
user.SubmitTime = (DateTime)st.Tables[0].Rows[i]["SubmitTime"];
list.Add(user);
}
GridView1.DataSource = list;
GridView1.DataBind();
修改存储过程:
只需将Create 改为 Alter
重命名存储过程:
修改存储过程名需要用到系统的存储过程 sp_name
sp_name 原存储过程名,新的存储过程名
删除存储过程
drop procedure 存储过程名
删除带条件的存储过程
采用存储过程写的登录和查询(只为测试)