public static int ExcuteNonQuery(string sql,params SqlParameter[] parameters)
{
using (SqlConnection con=new SqlConnection (constr ))
{
con.Open();
using (SqlCommand cmd=con.CreateCommand ())
{
cmd.CommandText = sql;
foreach(SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteNonQuery();
}
}
}
public static object ExcuteScalar(string sql,params SqlParameter [] parameters)
{
using (SqlConnection con=new SqlConnection (constr ))
{
con.Open();
using (SqlCommand cmd=new SqlCommand ())
{
cmd.CommandText = sql;
foreach (SqlParameter parameter in parameters )
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteScalar();
}
}
}
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(constr);//不使用using,不关闭连接
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
foreach (SqlParameter parameter in parameters)//说明还传了参数
{
cmd.Parameters.Add(parameter);
}
SqlDataReader reader= cmd.ExecuteReader(System .Data .CommandBehavior.CloseConnection );当返回dataReader的时候,connection不能关闭,DataReader不能关闭。给command对象一个参数 SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return reader;
}
}
}
protected void Button1_Click(object sender, EventArgs e)//登陆
{
DataTable ds= sqlhelp.Executedatatable("select * from users where Fname=@username and Fpassword=@password",new SqlParameter("@username",txtusername .Text ),new SqlParameter ("@password",txtpwd .Text ));
if (ds.Rows.Count > 0)
{
if (ds.Rows[0]["Fpassword"].ToString() == txtpwd.Text)
{
Response.Write("登陆成功");
}
else
{
Response.Write("密码有误!");
}
}
else
{
Response.Write("用户名不存在");
}
}
protected void Button2_Click(object sender, EventArgs e)//注册
{
DataTable a=sqlhelp.Executedatatable("select * from users where Fname=@fname",new SqlParameter ("@fname",txtusername .Text ));
//int result = sqlhelp.ExcuteNonQuery("insert into users (Fname,Fpassword) values (@name,@pwd)", new SqlParameter("@name",txtusername .Text ), new SqlParameter("@pwd",txtpwd.Text ));
if (a.Rows.Count > 0)
{
Response.Write("换个吧、这个已经有人注册过");
}
else
{
int result = sqlhelp.ExcuteNonQuery("insert into users (Fname,Fpassword) values (@name,@pwd)", new SqlParameter("@name", txtusername.Text), new SqlParameter("@pwd", txtpwd.Text));
Response.Write("注册成功");
}
}
protected void BtnShow_Click(object sender, EventArgs e)//显示用户信息
{
string sql = "select * from users";
SqlDataReader reader = sqlhelp.ExecuteReader(sql);
if (reader.HasRows)
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Response.Write(reader.GetValue(i) + "    ");
}
Response.Write("<br>");
}
}
reader.Close();
reader.Dispose();
}