11-28 写sqlhelp类并用sqlhelp类完成登陆、注册 和显示用户表信息。

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) + "&nbsp&nbsp&nbsp&nbsp");
                    }
                    Response.Write("<br>");

                }
            }
            reader.Close();
            reader.Dispose();

        }