C#连接mysql数据库，封装对象进行简单的增删改查

第一次写博客，记录记录自己的学习过程，还有分享一些心得给大家

一、连接数据库
在vs中找到工具栏添加数据库，选择数据库源的时候大家可能会遇到没有MySQLDatabase这个选项，只需要你去网上下载mysql-connector-net就好了

然后安装重新打开项目就可以添加mysql数据库了，然后就按照提示填写最后测试连接就好了。
##二、下载mysql.data.dll插件

在网上下载mysql.data.dll，然后在项目的引用中添加这个插件，即可

三、现在可以编写sqlhelp类了
sqlNull方法可以解决之后封装对象中有null值的对象，详细看后面的的例子，sqlConn是数据库连接信息。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using MySql.Data.MySqlClient;
using System.Windows;

namespace yuangong
{
    class SqlHelp
    {

        /// <summary>
        /// DataSource : MY-LAPTOP
        /// Initial Catalog: Test
        /// User ID:
        /// Password:
        /// </summary>

        public SqlHelp() { }

        public static MySqlConnection conn;
        ///sqlNull方法可以解决之后的封装对象中参数有null值
        public object SqlNull(int obj)
        {
            if (obj == 0)
            {
                return DBNull.Value;
            }
            else
            {
                return obj;
            }
        }
        public object SqlNull(string obj)
        {
            if (obj == null)
            {
                return DBNull.Value;
            }
            else
            {
                return obj;
            }
        }
        public object SqlNull(DateTime obj)
        {
            if (obj == default(DateTime))
            {
                return DBNull.Value;
            }
            else
            {
                return obj;
            }
        }

        //打开数据库连接
        public static void OpenConn()
        {
            string SqlCon = "server=localhost;user id=root;password = 123456;database=employee";
            conn = new MySqlConnection(SqlCon);
            conn.Open();
        }
        //关闭数据库连接
        public static void CloseConn()
        {
            if (conn.State.ToString().ToLower() == "open")
            {
                conn.Close();
                conn.Dispose();
            }
        }
        //读取数据
        public static MySqlDataReader GetDataReaderValue(string sql, params MySqlParameter[] dic)
        {
            OpenConn();
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddRange(dic);
            MySqlDataReader dr = cmd.ExecuteReader();
            CloseConn();
            return dr;
        }
        //返回DataSet
        public static DataSet GetDataSetValue(string sql, string tableName, params MySqlParameter[] dic)
        {
            OpenConn();
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddRange(dic);
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, tableName);
            CloseConn();
            return ds;
        }
        //返回DataView
        public static DataView GetDataViewValue(string sql, string tableName, params MySqlParameter[] dic)
        {
            OpenConn();
            DataSet ds = new DataSet();
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddRange(dic);
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(ds, "temp");
            CloseConn();
            return ds.Tables[0].DefaultView;
        }
        //返回DataTable
        public static DataTable GetDataTableValue(string sql, params MySqlParameter[] dic)
        {
            OpenConn();
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddRange(dic);
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            CloseConn();
            return dt;
        }
        //执行一个Sql操作：添加，删除，更新操作
        public bool ExecuteNonQuery(string sql, params MySqlParameter[] dic)
        {
            try
            {
                OpenConn();
                MySqlCommand cmd;
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddRange(dic);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            finally
            {
                CloseConn();
            }
        }
        //执行一个Sql操作：添加，删除，更新操作，返回受影响行数
        public int ExecuteNonQueryCount(string sql, params MySqlParameter[] dic)
        {
            OpenConn();
            MySqlCommand cmd;
            cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddRange(dic);
            int value = cmd.ExecuteNonQuery();
            return value;
        }
        //执行一条返回第一条记录第一列的SqlCommand命令
        public object ExecuteScalar(string sql, params MySqlParameter[] dic)
        {
            OpenConn();
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddRange(dic);
            object value = cmd.ExecuteScalar();
            return value;
        }
        //返回记录数
        public int SqlServerRecordCount(string sql, params MySqlParameter[] dic)
        {
            OpenConn();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.Parameters.AddRange(dic);
            MySqlDataReader dr;
            dr = cmd.ExecuteReader();
            int RecordCount = 0;
            while (dr.Read())
            {
                RecordCount++;
            }
            CloseConn();
            return RecordCount;
        }
        //判断是否为数字
        public static bool GetSafeValue(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return false;
            }
            foreach (char ch in value)
            {
                if (!char.IsDigit(ch))
                {
                    return false;
                }
            }
            return true;
        }
    }
}

四、sqlHelp类应用举例
对数据库中的表attendance进行增删改，先建一个数据库表的实体类Attendance并将数据成员都初始化为空值或0，利用之前的sqlhelp类中sqlNull方法就可以解决sql语句中存在null值的情况了。

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace yuangong
{
    class AttendanceData
    {
        public bool AddAttendance(Attendence att)
        {
            string sql = " insert into attendance (employee_id,employee_name,status,late_time,AReason) values(@employeeId,@employeeName,@status,@lateTime,@aReason);";
            SqlHelp sqlHelp = new SqlHelp();
            MySqlParameter parameter1 = new MySqlParameter("employeeId", sqlHelp.SqlNull(att.employeeId));
            MySqlParameter parameter2 = new MySqlParameter("employeeName", sqlHelp.SqlNull(att.employeeName));
            MySqlParameter parameter3 = new MySqlParameter("status", sqlHelp.SqlNull(att.status));
            MySqlParameter parameter4 = new MySqlParameter("lateTime", sqlHelp.SqlNull(att.lateTime));
            MySqlParameter parameter5 = new MySqlParameter("aReason", sqlHelp.SqlNull(att.aReason));
            if (sqlHelp.ExecuteNonQuery(sql,parameter1,parameter2,parameter3,parameter4,parameter5)) return true;
            return false;
        }
        public bool DeleteById(int id)
        {
            string sql = " update attendance set deleted=1 where id = @id;";
            SqlHelp sqlHelp = new SqlHelp();
            MySqlParameter parameter = new MySqlParameter("id", id);
            if (sqlHelp.ExecuteNonQuery(sql, parameter)) return true;
            return false;
        }
        public bool UpdateById(int id,Attendence att)
        {
            string sql = "update attendance set employee_id = @employeeId,employee_name=@employeeName,status=@status,late_time=@lateTime,AReason=@aReason where id = @id;";
            SqlHelp sqlHelp = new SqlHelp();
            MySqlParameter parameter1 = new MySqlParameter("employeeId", sqlHelp.SqlNull(att.employeeId));
            MySqlParameter parameter2 = new MySqlParameter("employeeName", sqlHelp.SqlNull(att.employeeName));
            MySqlParameter parameter3 = new MySqlParameter("status", sqlHelp.SqlNull(att.status));
            MySqlParameter parameter4 = new MySqlParameter("lateTime", sqlHelp.SqlNull(att.lateTime));
            MySqlParameter parameter5 = new MySqlParameter("aReason", sqlHelp.SqlNull(att.aReason));
            if (sqlHelp.ExecuteNonQuery(sql, parameter1, parameter2, parameter3, parameter4, parameter5)) return true;
            return false;
        }
    }
    class Attendence
    {
        public int id { get; set; }
        public int employeeId { get; set; }
        public string employeeName { get; set; }
        public string status { get; set; }
        public DateTime lateTime { get; set; }
        public string aReason { get; set; }
        public DateTime update_time { get; set; }
        public int deleted { get; set; }
        public Attendence() 
        {
            id = 0;
            employeeId = 0;
            employeeName = null;
            status = null;
            lateTime = default(DateTime);
            aReason = null;
            update_time = DateTime.Now;
        }
    }
}

五、利用button按钮的点击事件进行测试
可以发现att对象中的一些成员属性是没有定义的但也可以向数据库添加记录，点击button后去数据库查看是否添加成功。

private void Button_Click(object sender, RoutedEventArgs e)
        {
            AttendanceData attendance = new AttendanceData();
            Attendence att = new Attendence();
            att.employeeId = 2;
            att.employeeName = "chenyang";
            att.status = "213";
            attendance.AddAttendance(att);
        }