Asp.net中有关数据库操作的定义类 DataControl

一、不管是Asp.net、C#还是其他开发语言，都必不可少的用到数据库方面的操作，执行数据库表的增、删、改、查，这就需要我们定义一个数据库操作类，方便程序中调用，简化操作。

1.下面定义一个DataControl类实现对数据库操作，  需要添加web.config配置文件，在<configuration>下的<appSettings>设置要操作数据库的数据源如：<add key="ConnectionString"value="DataSource=***；InitialCatalog=数据库名;UID=用户名;pwd=密码";></appSettings></configuration>

//数据库的相关操作

public class DataControl
{

    /// <summary>
    /// 填充DataSet
    /// </summary>
    /// <param name="strSql">sql语句</param>
    /// <returns></returns>
    public static DataSet ExecuteDataset(String strSql)
    {
        DataSet ds = new DataSet();
        ds = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ConnectionString"].ToString(), CommandType.Text, strSql);
        return ds;
    }
    public static DataSet ExecuteDataset(String strSql, string ConnectionString)
    {
        DataSet ds = new DataSet();
        ds = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings[ConnectionString].ToString(), CommandType.Text, strSql);
        return ds;
    }
    public static DataSet ExcuteDatasetBig(string strsql)
    {
        DataSet ds = new DataSet();
        string strConnection = ConfigurationManager.AppSettings["ConnectionString"].ToString();

        return ds;
    }

 

    /// <summary>
    /// 执行单条sql语句
    /// </summary>
    /// <param name="sql">sql语句</param>
    public static void ExecuteNonQuery(String sql)
    {
        SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["ConnectionString"].ToString(), CommandType.Text, sql);
    }
    public static void ExecuteNonQuery(string sql, string ConnectionString)
    {
        SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings[ConnectionString].ToString(), CommandType.Text, sql);
    }

 

    /// <summary>
    /// 填充DropDownList
    /// </summary>
    /// <param name="dd">要填充的DropDownList</param>
    /// <param name="sql">获取数据集的sql语句</param>
    /// <param name="value">value值</param>
    /// <param name="text">text值</param>
    public static void FillDropDownList(DropDownList dd, String sql, String value, String text)
    {
        DataSet ds = new DataSet();
        ds = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ConnectionString"].ToString(), CommandType.Text, sql);
        dd.DataTextField = text;
        dd.DataValueField = value;
        dd.DataSource = ds.Tables[0];
        dd.DataBind();
    }
    public static void FillDropDownList(DropDownList dd, String sql, String value, String text, string ConnectionString)
    {
        DataSet ds = new DataSet();
        ds = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings[ConnectionString].ToString(), CommandType.Text, sql);
        dd.DataTextField = text;
        dd.DataValueField = value;
        dd.DataSource = ds.Tables[0];
        dd.DataBind();
    }

 

    /// <summary>
    /// 填充RadioButtonList
    /// </summary>
    /// <param name="rb">要填充的RadioButtonList</param>
    /// <param name="sql">获取数据集的sql语句</param>
    /// <param name="value">value值</param>
    /// <param name="text">text值</param>
    public static void FillRadioButtonList(RadioButtonList rb, String sql, String value, String text)
    {
        DataSet ds = new DataSet();
        ds = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ConnectionString"].ToString(), CommandType.Text, sql);
        rb.DataTextField = text;
        rb.DataValueField = value;
        rb.DataSource = ds.Tables[0];
        rb.DataBind();
    }
    public static void FillRadioButtonList(RadioButtonList rb, String sql, String value, String text, string ConnectionString)
    {
        DataSet ds = new DataSet();
        ds = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings[ConnectionString].ToString(), CommandType.Text, sql);
        rb.DataTextField = text;
        rb.DataValueField = value;
        rb.DataSource = ds.Tables[0];
        rb.DataBind();
    }

 

   /// <summary>
    /// 填充GridView
    /// </summary>
    /// <param name="gv">要填充的Gridview</param>
    /// <param name="strSql">获取数据集的sql</param>
    public static void FillGridView(GridView gv, String strSql)
    {
        DataSet ds = new DataSet();
        ds = DataControl.ExecuteDataset(strSql);
        gv.DataSource = ds.Tables[0];
        gv.DataBind();
    }
    public static void FillGridView(GridView gv, String strSql, string ConnectionString)
    {
        DataSet ds = new DataSet();
        ds = DataControl.ExecuteDataset(strSql, ConnectionString);
        gv.DataSource = ds.Tables[0];
        gv.DataBind();
    }

     /// <summary>
    /// 填写日志   (把登录后的信息写入到数据库日志表中)
    /// </summary>
    /// <param name="Users">用户名</param>
    /// <param name="Content">内容</param>
    /// <param name="iDate">插入日期</param>
    /// <param name="Ip">用户ip</param>
    public static void writeLogs(string Users, string Content, string iDate, string Ip)
    {
        try
        {
            DataControl.ExecuteNonQuery("insert into LOGS(LOGS_USERS,LOGS_CONTENT,LOGS_TIME,LOGS_IP) values('" + Users + "','" + Content + "','" + iDate + "','" + Ip + "')");
        }
        catch (Exception)
        {

            throw;
        }
    }

 

}

以上方法即是 有关常用数据库的操作，可在后文中直接调用！

2.登录界面设计中经常用到的有关判断“用户是否登录”以及“用户名是否存在”的方法也可以在DataControl类定义，方便后文直接调用，方法实现如下：

//判断用户是否存在  参数为 用户Id

public static bool isExist(string userid)

    {
        try
        {
            DataSet ds = new DataSet();
            ds = DataControl.ExecuteDataset("select * from SYSUSER where SYSUSER_UN='" + userid + "'");
            if (ds.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception)
        {
            return true;
        }
    }

//检查用户是否登录，  参数为：用户名和密码

 public static bool check(string strUser, string strPwd)
    {
        DataSet ds = new DataSet();
        ds = DataControl.ExecuteDataset("select SYSUSER_PWD from SYSUSER where SYSUSER_UN='" + strUser + "' and SYSUSER_ZT='1'");   //sql查询语句是从数据库中查询定义用户登录的表
        try
        {

         //MD5为加密，若数据库中的信息未设置加密方式，则不用
            if (ds.Tables[0].Rows[0][0].ToString() == Common.MD5(strPwd))  

            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception)
        {
            return false;
        }
    }

3. 根据以上DataControl类的定义，登录界面的设计代码可以简化为如下：

          public static string User;
        public static string Pwd;
        /**********通过DNS获取本机IP地址***************/
        private static string GetLocalIPAddr(string HostIP , string strHost)
        {
            string strHostIP = "";
            //获取本地主机的地址信息  及与主机关联的IP地址列表
            IPHostEntry oIPHost = Dns.Resolve(Environment.MachineName);
            if (oIPHost.AddressList.Length > 0) 
            {
                strHostIP = oIPHost.AddressList[0].ToString();
            }
            return strHostIP;
        }
        public string strHostIP { get; set; }

        public string strHost { get; set; }

 

/*************登录操作**************/
        private void btnLog_Click(object sender, EventArgs e)
        {
            User = this.txtUser.Text;
            Pwd = this.txtPwd.Text;
            //检查用户是否登录
            if (DataControl.check (User  ,Pwd ))
            {
                if (txtUser.Text != "")
                {
                    string IP = "";
                    string sql = "select SYSUSER_UN ,SYSUSER_PWD from SYSUSER where SYSUSER_UN ='" +User + "'";
                    DataControl.ExecuteDataset(sql);
                    //调用以上获取本地主机地址的方法，取得IP地址  并把登录成功信息写入LOG日志表中
                    IP = GetLocalIPAddr(strHostIP, strHost);
                    DataControl.writeLogs(User.ToString(), "登录", DateTime.Now.ToString(), IP);

                    MessageBox.Show("登录成功！");
                    label3.Text = User + "登录成功!";
                    this.DialogResult = DialogResult.OK;  
                }
                else
                {
                    MessageBox.Show("用户名和密码不能为空！");
                }
            }
            else
            {
                MessageBox.Show("用户名或密码错误，请重新登录！");
                label3.Text = "请核实信息后，再登录！";
            }          
        }

       //取消按钮
        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
        }
经过以上有关数据的操作的定义类后，登录操作可以简化为以上方法实现！