userinfo.cs

这是我做的第一个网络购书中心asp.net网站!

using System;
using System.Data;
using System.Configuration;
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;
using System.Data.OleDb;

/// <summary>
/// UserInfo 的摘要说明
/// </summary>
public class UserInfo
{
    DBConn dbconn = new DBConn();
	public UserInfo()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    /// <summary>
    /// 验证用户信息
    /// </summary>
    /// <param name="userid">用户名</param>
    /// <param name="password">用户密码</param>
    /// <returns>返回bool值</returns>
    public bool IsUserExist(string userid,string password)
    {
        OleDbConnection oledb = dbconn.GetConnection();
        oledb.Open();
        string sql = "SELECT * FROM tb_Member WHERE (Name='" + userid + "' )AND (Password='" + password + "')";
        OleDbCommand com = new OleDbCommand(sql, oledb);
        OleDbDataReader or = com.ExecuteReader();
        or.Read();
        if (or.HasRows)
        {
            or.Close();
            oledb.Close();
            return true;
        }
        else
        {
            or.Close();
            oledb.Close(); 
            return false;
        }
    }
    public bool IsUserExist(string userid)
    {
        OleDbConnection oledb = dbconn.GetConnection();
        oledb.Open();
        string sql = "SELECT * FROM tb_Member WHERE Name='" + userid + "'";
        OleDbCommand com = new OleDbCommand(sql, oledb);
        OleDbDataReader or = com.ExecuteReader();
        or.Read();
        if (or.HasRows)
        {
            or.Close();
            oledb.Close();
            return true;
        }
        else
        {
            or.Close();
            oledb.Close();
            return false;
        }
    }
    /// <summary>
    /// 返回用户信息
    /// </summary>
    /// <param name="userid">用户名</param>
    /// <param name="password">用户密码</param>
    /// <returns>返回DataSet</returns>
    public DataSet returnUserInfo(string userid, string password)
    {
        OleDbConnection oledb = dbconn.GetConnection();
        string sql = "SELECT * FROM tb_Member WHERE (Name='" + userid + "' )AND (Password='" + password + "')";
        OleDbDataAdapter oa = new OleDbDataAdapter(sql, oledb);
        DataSet result=new DataSet();
        oa.Fill(result,"tb_Member");
        return result;
    }
    /// <summary>
    /// 检验是否已存在该会员名称
    /// </summary>
    /// <param name="sql">查询语句</param>
    /// <returns>返回bool值</returns>
    public bool registerUser(string sql)
    {
        OleDbConnection oledb=dbconn.GetConnection();
        oledb.Open();
        OleDbCommand olecom = new OleDbCommand(sql, oledb);
        int i=olecom.ExecuteNonQuery();
        oledb.Close();
        if (i > 0)
            return true;
        else return false;
    }
    /// <summary>
    /// 获取登录用户信息
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <returns>返回DataSet用户信息</returns>
    public DataSet getUserInfo(string userName,out OleDbDataAdapter oleda)
    {
        OleDbConnection oledb = dbconn.GetConnection();
        string sql = "SELECT * FROM tb_Member WHERE Name='" + userName + "'";
        oleda = new OleDbDataAdapter(sql, oledb);
        OleDbCommandBuilder oleb = new OleDbCommandBuilder(oleda);
        DataSet result = new DataSet();
        oleda.Fill(result, "tb_Member");
        return result;
    }
    /// <summary>
    /// 弹出消息框
    /// </summary>
    /// <param name="page">页面page</param>
    /// <param name="str">显示文字</param>
    public void MessageBox(Page page,string str)
    {
        string str_word = "alert('" + str + "');";
        page.ClientScript.RegisterClientScriptBlock(this.GetType(), "", str_word, true);
    }
    public void ConfirmMessageBox(Page page, string str)
    {
        string str_word = "alert('" + str + "');location.href='index.aspx';";        
        page.ClientScript.RegisterClientScriptBlock(this.GetType(), "", str_word, true);
    }
    public void DGBind(int type, string table, DataList dl,bool IsTop)
    {
        string sql;
        if (IsTop)
        {
            sql = "SELECT top 4 * FROM tb_GoodsInfo";
        }
        else
        {
            sql = "SELECT * FROM tb_GoodsInfo";
        }
        if(type==1)
        {
            sql += " WHERE Isrefinement=1";
        }
        else if(type==2)
        {
            sql += " WHERE IsHot=1";
        }
        else if (type == 3)
        {
            sql += " WHERE IsDiscount=1";
        }
        sql += " ORDER BY GoodsID DESC";
        OleDbConnection oledb = dbconn.GetConnection();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, oledb);
        DataSet ds = new DataSet();
        da.Fill(ds, table);
        dl.DataSource = ds.Tables[table].DefaultView;
        dl.DataBind();
    }
    /// <summary>
    /// kindPage中的数据绑定
    /// </summary>
    public void DGBind(string name, DataList dl,int start,int end)
    {
        
        OleDbConnection oledb = dbconn.GetConnection();
        int classID = this.classId(name);

        string sql_Goods = "SELECT * FROM tb_GoodsInfo where ClassID="+classID;
        OleDbDataAdapter da = new OleDbDataAdapter(sql_Goods, oledb);
        DataSet ds = new DataSet();
        da.Fill(ds,start,end, name);
        dl.DataSource = ds.Tables[name].DefaultView;
        dl.DataBind();
    }
    public void DGBind(string name, DataList dl, int start, int end, string type)
    {

        OleDbConnection oledb = dbconn.GetConnection();
        //int classID = this.classId(name);

        string sql_Goods = "SELECT * FROM tb_GoodsInfo where "+type+"=1";
        OleDbDataAdapter da = new OleDbDataAdapter(sql_Goods, oledb);
        DataSet ds = new DataSet();
        da.Fill(ds, start, end, name);
        dl.DataSource = ds.Tables[name].DefaultView;
        dl.DataBind();
    }
    public int classId(string name)
    {
        string sql="SELECT ClassID from tb_Class where ClassName='"+name+"'";//类型编号
        OleDbConnection oledb = dbconn.GetConnection();
        OleDbCommand com = new OleDbCommand(sql, oledb);
        oledb.Open();
        OleDbDataReader re = com.ExecuteReader();
        int classId = 0;
        while(re.Read())
        {
        classId = Convert.ToInt32(re["ClassID"]);
        }
        re.Close();
        oledb.Close();
        return classId;
    }
    
    /// <summary>
    /// 数据绑定
    /// </summary>
    /// <param name="P_gv_gv">GridView控件</param>
    /// <param name="P_str_table">绑定的表名</param>
    /// <param name="P_str_Id">用户ID</param>
    public void SCBind(GridView P_gv_gv,string P_str_table,string P_str_Id)
    {
        string sql = "SELECT tb_ShopCart.MemberID, tb_ShopCart.SumPrice, tb_ShopCart.Num,tb_ShopCart.GoodsID, " +
            "tb_ShopCart.CartID, tb_GoodsInfo.MemberPrice,tb_GoodsInfo.MarketPrice, tb_GoodsInfo.GoodsName"
            + " FROM (tb_GoodsInfo INNER JOIN tb_ShopCart ON tb_GoodsInfo.GoodsID = tb_ShopCart.GoodsID)WHERE (tb_ShopCart.MemberID ="
            + P_str_Id + ")";
        OleDbConnection conn = dbconn.GetConnection();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, P_str_table);
        P_gv_gv.DataSource = ds.Tables[P_str_table].DefaultView;
        P_gv_gv.DataBind();
    }

    public DataSet reTotalDs(string P_str_ID,string P_str_table)
    {
        string sql = "SELECT Sum(SumPrice),Sum(Num) FROM tb_ShopCart WHERE MemberID=" + P_str_ID;
        OleDbConnection conn = dbconn.GetConnection();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, P_str_table);
        return ds;
    }
    /// <summary>
    /// 删除购物车中的一项
    /// </summary>
    /// <param name="P_str_cartID">序号</param>
    /// <param name="P_str_member">用户名</param>
    public void delCartID(string P_str_cartID,string P_str_member)
    {
        string sql = "DELETE FROM tb_ShopCart WHERE CartID=" + P_str_cartID + "AND MemberID=" + P_str_member;
        OleDbConnection conn = dbconn.GetConnection();
        conn.Open();
        OleDbCommand com = new OleDbCommand(sql, conn);
        com.ExecuteNonQuery();
        conn.Close();
    }
    public void delAllShopCart(string P_str_member)
    {
        string sql = "DELETE FROM tb_ShopCart WHERE MemberID=" + P_str_member;
        OleDbConnection conn = dbconn.GetConnection();
        conn.Open();
        OleDbCommand com = new OleDbCommand(sql, conn);
        com.ExecuteNonQuery();
        conn.Close();
    }
    public void addShopCart(string P_str_GoodsID,double P_double_Price,string P_str_MemberID)
    {
        string sql = "SELECT * FROM tb_ShopCart WHERE GoodsID=" + P_str_GoodsID + " AND MemberID="+P_str_MemberID;        
        OleDbConnection conn = dbconn.GetConnection();
        conn.Open();
        OleDbCommand com = new OleDbCommand(sql, conn);
        OleDbDataReader or = com.ExecuteReader();
        if (or.HasRows)
        {
            sql = "UPDATE tb_ShopCart SET Num =(Num+1)" + ", SumPrice =(SumPrice+" + P_double_Price
                + ") WHERE MemberID =" + P_str_MemberID + " AND " + "GoodsID=" + P_str_GoodsID;
            com = new OleDbCommand(sql, conn);
            com.ExecuteNonQuery();
        }
        else
        {
            sql = "INSERT INTO tb_ShopCart (GoodsID, Num, SumPrice, MemberID) VALUES (" + P_str_GoodsID + ",1," + P_double_Price
                + "," + P_str_MemberID + ")";
            com = new OleDbCommand(sql, conn);
            com.ExecuteNonQuery();
        }
        com.Dispose();
        conn.Close();

    }
    /// <summary>
    /// 测试语句
    /// </summary>
    /// <param name="page"></param>
    public void test(Page page)
    {
        page.Session["UID"] = null;
        page.Session["UID"] = 1;
        page.Session["UserName"] = "test";
    }
    /// <summary>
    /// 计算查询的返回行数
    /// </summary>
    public  int countLine(string sql)
    {
        int result = 0;
        OleDbConnection conn = dbconn.GetConnection();
        OleDbCommand com = new OleDbCommand(sql, conn);
        conn.Open();
        //永远返回是０～～update,insert才有
        // int result = com.ExecuteNonQuery();
        OleDbDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            result++;
        }
        conn.Close();
        return result;
    }

    public void sumitOrderInfo(double P_double_GF, double P_double_SFee, int P_int_SType, int P_int_PType, string P_int_UserID, string P_str_RName,
        string P_str_RPhone, string P_str_RPCode, string P_str_RAddress, string P_str_Email,bool P_bool_isPay)
    {
        int pay;
        if (P_bool_isPay)
            pay = 1;
        else
            pay = 0;
        string sql = "INSERT INTO tb_OrderInfo(OrderDate, GoodsFee, TotalPrice, ShipFee,ShipType, PayType, MemberID,"
            + "ReceiverName, ReceiverPhone, IsConfirm, IsPayment, IsConsignment, IsPigeonhole,ReceiverPostCode, ReceiverAddress, ReceiverEmails)"
            + "VALUES ('" + DateTime.Now.ToString() + "'," + P_double_GF + "," + (P_double_GF + P_double_SFee) + "," + P_double_SFee + ","
            + P_int_SType + "," + P_int_PType + "," + P_int_UserID + ",'" + P_str_RName + "','" + P_str_RPhone + "'," + "0," + pay + ",0,0,'" + P_str_RPCode
            + "','" + P_str_RAddress + "','" + P_str_Email + "')";
        OleDbConnection conn = dbconn.GetConnection();
        conn.Open();
        OleDbCommand com = new OleDbCommand(sql, conn);
        com.ExecuteNonQuery();
        conn.Close();
    }

    public bool isUserCart(string P_int_MemberID, double P_double_TotalPrice,double P_double_ShipFee)
    {
        string sql = "SELECT AdvancePayment FROM tb_Member WHERE MemberID=" + P_int_MemberID;
        OleDbConnection conn = dbconn.GetConnection();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "AdvancePayment");
        double AdvancePayment = Convert.ToDouble(ds.Tables["AdvancePayment"].Rows[0][0].ToString());
        if ((P_double_TotalPrice+P_double_ShipFee) > AdvancePayment)
        {
            return false;
        }
        else
        {
            double tmp = AdvancePayment - P_double_TotalPrice-P_double_ShipFee;
            sql = "UPDATE tb_Member SET AdvancePayment =" + tmp + " WHERE MemberID=" + P_int_MemberID;
            if (dbconn.upDate(sql))
                return true;
            else
                return false;
        }
    }

    public double reAdvancePayment(string P_str_MemberID)
    {
        string sql = "SELECT AdvancePayment FROM tb_Member WHERE MemberID=" + P_str_MemberID;
        OleDbConnection conn = dbconn.GetConnection();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "AdvancePayment");
        double AdvancePayment = Convert.ToDouble(ds.Tables["AdvancePayment"].Rows[0][0].ToString());
        return AdvancePayment;
    }

    public void upAdvancePayment(string P_str_MemberID,int P_int_Money)
    {
        string sql = "UPDATE tb_Member SET AdvancePayment =(AdvancePayment+" + P_int_Money + ") WHERE MemberID=" + P_str_MemberID;
        OleDbConnection conn = dbconn.GetConnection();
        conn.Open();
        OleDbCommand com = new OleDbCommand(sql, conn);
        com.ExecuteNonQuery();
        conn.Close();
    }
    /// <summary>
    /// 返回运费表格
    /// </summary>
    /// <param name="P_int_ShipID">运费ID</param>
    /// <returns>返回Dataset</returns>
    public DataSet reShipType(int P_int_ShipID)
    {
        string sql = "SELECT * FROM tb_ShipType WHERE ShipID=" + P_int_ShipID;
        OleDbConnection conn = dbconn.GetConnection();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "tb_ShipType");
        return ds;
    }
    public void delShopCart(int P_int_MemberID)
    {
        string sql = "DELETE FROM tb_ShopCart WHERE MemberID=" + P_int_MemberID;
        OleDbConnection conn = dbconn.GetConnection();
        conn.Open();
        OleDbCommand com = new OleDbCommand(sql, conn);
        com.ExecuteNonQuery();
        conn.Close();
    }
}