简ASP.NET数据库三层之操作类

// *******************************************
// **  userDB类为对数据库中表tb_user的操作  **
// *******************************************
using  System;
using  System.Data;
using  System.Data.SqlClient;
using  System.Configuration;
using  System.Collections;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;

/**/ /// <summary>
/// db 的摘要说明
/// </summary>
public   class  userDB
... {
    private SqlConnection _Conn;            //SQL数据库连接
    private String _strDB = "tb_user";      //SQL数据库表
    //连接数据库
    public userDB() 
    ...{
        _Conn=new SqlConnection(ConfigurationManager.AppSettings["strConn"]);
    }
    //打开数据库
    public void Open()
    ...{
        if(_Conn.State==ConnectionState.Closed)
        ...{
            _Conn.Open();
        }
    }
    //关闭数据库
    public void Close()
    ...{
        if(_Conn.State==ConnectionState.Open)
        ...{
            _Conn.Close();
            _Conn.Dispose();
        }
    }
    //完全查询用户所有资料，用于管理员查看
    public useCollection GetuseDBs()
    ...{
        useCollection useC=new useCollection();
        string strSql = "select u_Id,u_State,u_Regdate,u_Name,u_Pwd," +
                               "u_Clue,u_Result,u_Type,u_Tname,u_Sex," +
                               "u_Tel,u_Email,u_Mobile,u_QQ,u_MSN," +
                               "u_Address,u_Post,u_Corp,u_Network,u_Xueli," +
                               "u_School,u_Money,u_Date,u_No,u_Remark from " + _strDB;
        SqlCommand cmd=new SqlCommand(strSql,_Conn);
        if(cmd.ExecuteScalar()!=null)
        ...{
            SqlDataReader dr=cmd.ExecuteReader();
            while(dr.Read())
            ...{
                tb_user tbu=PopulateTb_users(dr);
                useC.Add(tbu);
            }
            dr.Close();
            return useC;
        }
        else
        ...{
            return useC;
        }
    }
    //根据传入SQL查询特定用户
    public useCollection GetuseDBsql(string strSql)
    ...{
        useCollection useC = new useCollection();
        SqlCommand cmd = new SqlCommand(strSql, _Conn);
        if (cmd.ExecuteScalar() != null)
        ...{
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            ...{
                tb_user tbu = PopulateTb_users(dr);
                useC.Add(tbu);
            }
            dr.Close();
            return useC;
        }
        else
        ...{
            return useC;
        }
    }
    //指定用户名查询用户所有资料，用于用户自身信息查看
    public tb_user GetuseDB(string useName)
    ...{
        string strSql = "select u_Id,u_State,u_Regdate,u_Name,u_Pwd,"+
                               "u_Clue,u_Result,u_Type,u_Tname,u_Sex," +
                               "u_Tel,u_Email,u_Mobile,u_QQ,u_MSN," +
                               "u_Address,u_Post,u_Corp,u_Network,u_Xueli,"+
                               "u_School,u_Money,u_Date,u_No,u_Remark" +
                               " from " + _strDB + " where u_Name='" + useName + "'";
        SqlCommand cmd = new SqlCommand(strSql, _Conn);
        tb_user tbu=new tb_user();
        if(cmd.ExecuteScalar()!=null)
        ...{
            SqlDataReader dr = cmd.ExecuteReader();
            tbu=PopulateTb_user(dr);
            dr.Close();
            return tbu;
        }
        else
        ...{
            return tbu;
        }
    }
    //添加新用户，用于用户注册
    public string AddUser(tb_user tbuAdd)
    ...{
        String password = FormsAuthentication.HashPasswordForStoringInConfigFile(tbuAdd.u_Pwd, "MD5");
        String result = FormsAuthentication.HashPasswordForStoringInConfigFile(tbuAdd.u_Result, "MD5");
        String strDate = DateTime.Now.ToString();
        String strSql = "insert into " + _strDB + " (u_Regdate,u_Name,u_Pwd,u_Clue,u_Result," +
                                            "u_Type,u_Tname,u_Sex,u_Tel,u_Email) " +
                                            " values(@u_Regdate,@u_Name,@u_Pwd,@u_Clue,@u_Result," +
                                            "@u_Type,@u_Tname,@u_Sex,@u_Tel,@u_Email)";
        SqlCommand cmd=new SqlCommand(strSql,_Conn);
            cmd.Parameters.Add("@u_Regdate", System.Data.SqlDbType.DateTime).Value = strDate;
            cmd.Parameters.Add("@u_Name", System.Data.SqlDbType.NVarChar).Value = tbuAdd.u_Name;
            cmd.Parameters.Add("@u_Pwd", System.Data.SqlDbType.NVarChar).Value = password;
            cmd.Parameters.Add("@u_Clue", System.Data.SqlDbType.NVarChar).Value = tbuAdd.u_Clue;
            cmd.Parameters.Add("@u_Result", System.Data.SqlDbType.NVarChar).Value = result;
            
            cmd.Parameters.Add("@u_Type", System.Data.SqlDbType.NVarChar).Value = tbuAdd.u_Type;
            cmd.Parameters.Add("@u_Tname", System.Data.SqlDbType.NVarChar).Value = tbuAdd.u_Tname;
            cmd.Parameters.Add("@u_Sex", System.Data.SqlDbType.NVarChar).Value = tbuAdd.u_Sex;
            cmd.Parameters.Add("@u_Tel", System.Data.SqlDbType.NVarChar).Value = tbuAdd.u_Tel;
            cmd.Parameters.Add("@u_Email", System.Data.SqlDbType.NVarChar).Value = tbuAdd.u_Email;
        cmd.ExecuteNonQuery();
        
        tbuAdd.u_Name=(string)cmd.Parameters["@u_Name"].Value;
        return tbuAdd.u_Name;
    }    
    //指定用户名更新用户资料，用于用户注册
    public void UpdateDBuse(tb_user tbuse)
    ...{
        string strSql = "update " + _strDB + " set u_Mobile=@u_Mobile ,u_QQ=@u_QQ ,u_MSN=@u_MSN ,u_Address=@u_Address ," +
                                            "u_Post=@u_Post ,u_Corp=@u_Corp ,u_Network=@u_Network ,u_Xueli=@u_Xueli ," +
                                            "u_School=@u_School ,u_Money=@u_Money ,u_Date=@u_Date ,u_No=@u_No ," +
                                            "u_Remark=@u_Remark where u_Name='" + tbuse.u_Name + "'";
        SqlCommand cmd=new SqlCommand(strSql,_Conn);
            cmd.Parameters.Add("@u_Mobile", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Mobile;
            cmd.Parameters.Add("@u_QQ", System.Data.SqlDbType.NVarChar).Value = tbuse.u_QQ;
            cmd.Parameters.Add("@u_MSN", System.Data.SqlDbType.NVarChar).Value = tbuse.u_MSN;
            cmd.Parameters.Add("@u_Address", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Address;
            
            cmd.Parameters.Add("@u_Post", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Post;
            cmd.Parameters.Add("@u_Corp", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Corp;
            cmd.Parameters.Add("@u_Network", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Network;
            cmd.Parameters.Add("@u_Xueli", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Xueli;
            
            cmd.Parameters.Add("@u_School", System.Data.SqlDbType.NVarChar).Value = tbuse.u_School;
            cmd.Parameters.Add("@u_Money", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Money;
            cmd.Parameters.Add("@u_Date", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Date;
            cmd.Parameters.Add("@u_No", System.Data.SqlDbType.NVarChar).Value = tbuse.u_No;
            
            cmd.Parameters.Add("@u_Remark", System.Data.SqlDbType.NVarChar).Value = tbuse.u_Remark;
        if (cmd.ExecuteScalar() != null)
        ...{    
            cmd.ExecuteNonQuery();
        }
    }
    //指定用户名更改用户密码，用于用户本身和维护
    public void UpdateDBupwd(tb_user tbupwd)
    ...{
        String password = FormsAuthentication.HashPasswordForStoringInConfigFile(tbupwd.u_Pwd, "MD5");
        string strSql = "update " + _strDB + " set u_Pwd=@u_Pwd where u_Name='" + tbupwd.u_Name + "'";
        SqlCommand cmd=new SqlCommand(strSql,_Conn);
            cmd.Parameters.Add("@u_Pwd", System.Data.SqlDbType.NVarChar).Value = password;
        if (cmd.ExecuteScalar() != null)
        ...{
            cmd.ExecuteNonQuery();
        }
    }
    //提示问题和答案，用于用户本身和维护
    public void UpdaateDBuClue(tb_user tbuClue)
    ...{
        String result = FormsAuthentication.HashPasswordForStoringInConfigFile(tbuClue.u_Result, "MD5");
        String strSql = "update " + _strDB + " set u_Clue=@u_Clue,u_Result=@u_Result where u_Name='" + tbuClue.u_Name + "'";
        SqlCommand cmd=new SqlCommand(strSql,_Conn);
            cmd.Parameters.Add("@u_Clue", System.Data.SqlDbType.NVarChar).Value = tbuClue.u_Clue;
            cmd.Parameters.Add("@u_Result", System.Data.SqlDbType.NVarChar).Value = result;
        if (cmd.ExecuteScalar() != null)
        ...{
            cmd.ExecuteNonQuery();
        }
    }
    //指定用户名更新用户资料，用于用户本身维护和维护
    public void UpdateDBuser(tb_user tbuser)
    ...{
        string strSql = "update " + _strDB + " set u_Tname=@u_Tname ,u_Type=@u_Type ,u_Sex=@u_Sex ,u_Tel=@u_Tel ,u_Email=@u_Email ," +
                                            "u_Mobile=@u_Mobile ,u_QQ=@u_QQ ,u_MSN=@u_MSN ,u_Address=@u_Address ," +
                                            "u_Post=@u_Post ,u_Corp=@u_Corp ,u_Network=@u_Network ,u_Xueli=@u_Xueli ," +
                                            "u_School=@u_School ,u_Money=@u_Money ,u_Date=@u_Date ,u_No=@u_No ," +
                                            "u_Remark=@u_Remark where u_Name='" + tbuser.u_Name + "'";
        SqlCommand cmd = new SqlCommand(strSql, _Conn);
            cmd.Parameters.Add("@u_Tname", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Tname;
            cmd.Parameters.Add("@u_Type", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Type;

            cmd.Parameters.Add("@u_Sex", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Sex;
            cmd.Parameters.Add("@u_Tel", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Tel;
            cmd.Parameters.Add("@u_Email", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Email;
            cmd.Parameters.Add("@u_Mobile", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Mobile;

            cmd.Parameters.Add("@u_QQ", System.Data.SqlDbType.NVarChar).Value = tbuser.u_QQ;
            cmd.Parameters.Add("@u_MSN", System.Data.SqlDbType.NVarChar).Value = tbuser.u_MSN;
            cmd.Parameters.Add("@u_Address", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Address;
            cmd.Parameters.Add("@u_Post", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Post;

            cmd.Parameters.Add("@u_Corp", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Corp;
            cmd.Parameters.Add("@u_Network", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Network;
            cmd.Parameters.Add("@u_Xueli", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Xueli;
            cmd.Parameters.Add("@u_School", System.Data.SqlDbType.NVarChar).Value = tbuser.u_School;

            cmd.Parameters.Add("@u_Money", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Money;
            cmd.Parameters.Add("@u_Date", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Date;
            cmd.Parameters.Add("@u_No", System.Data.SqlDbType.NVarChar).Value = tbuser.u_No;
            cmd.Parameters.Add("@u_Remark", System.Data.SqlDbType.NVarChar).Value = tbuser.u_Remark;
        if (cmd.ExecuteScalar() != null)
        ...{    
            cmd.ExecuteNonQuery();
        }
    }      
    /**/////指定用户名更新用户所有资料，用于管理员维护
    //public void UpdateDBadmin(tb_user tbadmin)
    //{
    //    String password = FormsAuthentication.HashPasswordForStoringInConfigFile(tbadmin.u_Pwd, "MD5");
    //    String result = FormsAuthentication.HashPasswordForStoringInConfigFile(tbadmin.u_Result, "MD5");
    //    string strSql = "update" + _strDB + "set u_Pwd=@u_Pwd ,u_Clue=@u_Clue ,u_Result=@u_Result ,u_Type=@u_Type ,"+
    //                                        "u_Tname=@u_Tname ,u_Sex=@u_Sex ,u_Tel=@u_Tel ,u_Email=@u_Email ," +
    //                                        "u_Mobile=@u_Mobile ,u_QQ=@u_QQ ,u_MSN=@u_MSN ,u_Address=@u_Address ," +
    //                                        "u_Post=@u_Post ,u_Corp=@u_Corp ,u_Network=@u_Network ,u_Xueli=@u_Xueli ," +
    //                                        "u_School=@u_School ,u_Money=@u_Money ,u_Date=@u_Date ,u_No=@u_No ," +
    //                                        "u_Remark=@u_Remark where u_Name='" + tbadmin.u_Name + "'";
    //    SqlCommand cmd = new SqlCommand(strSql, _Conn);         
    //        cmd.Parameters.Add("@u_Pwd", System.Data.SqlDbType.NVarChar).Value = password;
            
    //        cmd.Parameters.Add("@u_Clue", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Clue;
    //        cmd.Parameters.Add("@u_Result", System.Data.SqlDbType.NVarChar).Value = result;
    //        cmd.Parameters.Add("@u_Type", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Type;
    //        cmd.Parameters.Add("@u_Tname", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Tname;
            
    //        cmd.Parameters.Add("@u_Sex", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Sex;
    //        cmd.Parameters.Add("@u_Tel", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Tel;
    //        cmd.Parameters.Add("@u_Email", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Email;
    //        cmd.Parameters.Add("@u_Mobile", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Mobile;
            
    //        cmd.Parameters.Add("@u_QQ", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_QQ;
    //        cmd.Parameters.Add("@u_MSN", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_MSN;
    //        cmd.Parameters.Add("@u_Address", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Address;
    //        cmd.Parameters.Add("@u_Post", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Post;
            
    //        cmd.Parameters.Add("@u_Corp", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Corp;
    //        cmd.Parameters.Add("@u_Network", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Network;
    //        cmd.Parameters.Add("@u_Xueli", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Xueli;
    //        cmd.Parameters.Add("@u_School", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_School;
            
    //        cmd.Parameters.Add("@u_Money", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Money;
    //        cmd.Parameters.Add("@u_Date", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Date;
    //        cmd.Parameters.Add("@u_No", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_No;
    //        cmd.Parameters.Add("@u_Remark", System.Data.SqlDbType.NVarChar).Value = tbadmin.u_Remark;
    //     if (cmd.ExecuteScalar() != null)
    //    {    
    //        cmd.ExecuteNonQuery();
    //    }
    //}
    //用户状态更改，用于管理员维护
    public void UpdateState(tb_user tbSt)
    ...{
        string strSql = "update " + _strDB + " set u_State=@u_State where u_Name='" + tbSt.u_Name + "'";
        SqlCommand cmd=new SqlCommand(strSql,_Conn);
            cmd.Parameters.Add("@u_State", System.Data.SqlDbType.Bit).Value = tbSt.u_State;
        if (cmd.ExecuteScalar() != null)
        ...{
            cmd.ExecuteNonQuery();   
        }     
    }
    /**/////指定查询用户发表的所有信息，暂不启用
    //public useCollection GetInfo(string userName)
    //{
    //    string strSql="select * from"+_strDB+"where u_Name='" + userName + "'";
    //    SqlCommand cmd=new SqlCommand(strSql,_Conn);
    //    useCollection useCn=new useCollection();
    //    if(cmd.ExecuteScalar()!=null)
    //    {
    //        SqlDataReader dr=cmd.ExecuteReader();
    //        while(dr.Read())
    //        {
    //            tb_user tba=PopulateTb_user(dr);
    //            useCn.Add(dr);
    //        }
    //        dr.Close();
    //        return useCn;
    //    }
    //    else
    //    {
    //        return useCn;
    //    }
    //}
    //指定用户名删除用户资料，用于管理员删除
    public void DeleteTbu(string useName)
    ...{
        string strSql="delete from " + _strDB + " where u_Name='" + useName + "'";
        SqlCommand cmd=new SqlCommand(strSql,_Conn);
        if (cmd.ExecuteScalar() != null)
        ...{
            cmd.ExecuteNonQuery();
        }
    }
    //给tb_user.cs赋与用户资料的值，内部函数
    private tb_user PopulateTb_user(SqlDataReader dr)
    ...{
        tb_user tbr=new tb_user();
            dr.Read();
            tbr.u_Id=Convert.ToInt32(dr["u_Id"]);
            tbr.u_State=Convert.ToBoolean(dr["u_State"]);
            tbr.u_Regdate=Convert.ToDateTime(dr["u_Regdate"]);
            tbr.u_Name=Convert.ToString(dr["u_Name"]);
            tbr.u_Pwd=Convert.ToString(dr["u_Pwd"]);
            
            tbr.u_Clue=Convert.ToString(dr["u_Clue"]);
            tbr.u_Result=Convert.ToString(dr["u_Result"]);
            tbr.u_Type=Convert.ToString(dr["u_Type"]);
            tbr.u_Tname=Convert.ToString(dr["u_Tname"]);
            tbr.u_Sex=Convert.ToString(dr["u_Sex"]);
            
            tbr.u_Tel=Convert.ToString(dr["u_Tel"]);
            tbr.u_Email=Convert.ToString(dr["u_Email"]);
            tbr.u_Mobile = Convert.ToString(dr["u_Mobile"]);
            tbr.u_QQ = Convert.ToString(dr["u_QQ"]);
            tbr.u_MSN=Convert.ToString(dr["u_MSN"]);
            
            tbr.u_Address=Convert.ToString(dr["u_Address"]);
            tbr.u_Post = Convert.ToString(dr["u_Post"]);
            tbr.u_Corp=Convert.ToString(dr["u_Corp"]);
            tbr.u_Network=Convert.ToString(dr["u_Network"]);
            tbr.u_Xueli=Convert.ToString(dr["u_Xueli"]);
            
            tbr.u_School=Convert.ToString(dr["u_School"]);
            tbr.u_Money=Convert.ToString(dr["u_Money"]);
            tbr.u_Date=Convert.ToString(dr["u_Date"]);
            tbr.u_No = Convert.ToString(dr["u_No"]);
            tbr.u_Remark=Convert.ToString(dr["u_Remark"]);
        return tbr;
    }
    private tb_user PopulateTb_users(SqlDataReader dr)
    ...{
        tb_user tbr = new tb_user();
        
        tbr.u_Id = Convert.ToInt32(dr["u_Id"]);
        tbr.u_State = Convert.ToBoolean(dr["u_State"]);
        tbr.u_Regdate = Convert.ToDateTime(dr["u_Regdate"]);
        tbr.u_Name = Convert.ToString(dr["u_Name"]);
        tbr.u_Pwd = Convert.ToString(dr["u_Pwd"]);

        tbr.u_Clue = Convert.ToString(dr["u_Clue"]);
        tbr.u_Result = Convert.ToString(dr["u_Result"]);
        tbr.u_Type = Convert.ToString(dr["u_Type"]);
        tbr.u_Tname = Convert.ToString(dr["u_Tname"]);
        tbr.u_Sex = Convert.ToString(dr["u_Sex"]);

        tbr.u_Tel = Convert.ToString(dr["u_Tel"]);
        tbr.u_Email = Convert.ToString(dr["u_Email"]);
        tbr.u_Mobile = Convert.ToString(dr["u_Mobile"]);
        tbr.u_QQ = Convert.ToString(dr["u_QQ"]);
        tbr.u_MSN = Convert.ToString(dr["u_MSN"]);

        tbr.u_Address = Convert.ToString(dr["u_Address"]);
        tbr.u_Post = Convert.ToString(dr["u_Post"]);
        tbr.u_Corp = Convert.ToString(dr["u_Corp"]);
        tbr.u_Network = Convert.ToString(dr["u_Network"]);
        tbr.u_Xueli = Convert.ToString(dr["u_Xueli"]);

        tbr.u_School = Convert.ToString(dr["u_School"]);
        tbr.u_Money = Convert.ToString(dr["u_Money"]);
        tbr.u_Date = Convert.ToString(dr["u_Date"]);
        tbr.u_No = Convert.ToString(dr["u_No"]);
        tbr.u_Remark = Convert.ToString(dr["u_Remark"]);
        return tbr;
    }
}