C#读写MySQL工具类

工具类

public class MappingDao
{
    /// <summary>
    /// 向MySQL表中插入记录
    /// </summary>
    /// <typeparam name="T">表类</typeparam>
    /// <param name="conn">MySQL连接</param>
    /// <param name="tableObject">表对象</param>
    /// <returns>成功插入记录数</returns>
    public static int SetTableObject<T>(MySqlConnection conn,T tableObject)
    {
        string TableStr = tableObject.GetType().Name;
        string FieldStr = "";
        string ValueStr = "";
        foreach (System.Reflection.PropertyInfo info in tableObject.GetType().GetProperties())
        {
            if(info.GetValue(tableObject,null) != null)
            {
                string field = info.Name;
                object value = info.GetValue(tableObject, null);

                if (value.GetType() == typeof(string))
                {
                    if (!value.ToString().Equals(""))
                    {
                        FieldStr += (field + ",");
                        ValueStr += ("'" + value + "',");
                    }
                }
                else if (value.GetType() == typeof(decimal))
                {
                    FieldStr += (field + ",");
                    ValueStr += ("'" + value + "',");
                }
                else if (value.GetType() == typeof(DateTime))
                {
                    DateTime dateTime = new DateTime(0001, 01, 01);
                    if (DateTime.Compare(dateTime, (DateTime)value) < 0)
                    {
                        FieldStr += (field + ",");
                        ValueStr += ("'" + value + "',");
                    }
                }
            }
        }
        FieldStr = FieldStr.Substring(0, FieldStr.Length - 1);
        ValueStr = ValueStr.Substring(0, ValueStr.Length - 1);
        string sql = "insert into " + TableStr + " (" + FieldStr + ") values (" + ValueStr + ")";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        return cmd.ExecuteNonQuery();
    }
	
    /// <summary>
    /// 查询MySQL表记录
    /// </summary>
    /// <typeparam name="T">表类</typeparam>
    /// <param name="conn">MySQL连接</param>
    /// <param name="sql">查询语句</param>
    /// <returns>结果记录对象集合</returns>
    public static List<T> GetTableObjects<T>(MySqlConnection conn, string sql)
    {
        List<T> tLst = new List<T>();
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            var instance = Activator.CreateInstance<T>();
            foreach (System.Reflection.PropertyInfo info in instance.GetType().GetProperties())
            {
                if (string.IsNullOrEmpty(reader.GetValue(reader.GetOrdinal(info.Name)).ToString()))
                {
                    continue;
                }
                info.SetValue(instance, reader.GetValue(reader.GetOrdinal(info.Name)), null);
            }
            tLst.Add(instance);
        }
        reader.Close();
        return tLst;
    }

    public static int RemoveFrameRecordByNO(MySqlConnection conn,string frmNo)
    {
        string sql = "delete from azhtfb where CHAMAC='" + frmNo + "'";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        return cmd.ExecuteNonQuery();
    }

    public static MySqlConnection GetMySqlConnection(string connStr)
    {
        //String connetStr = "server=127.0.0.1;port=3306;user=root;password=root;database=ecogeodb;";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
            return conn;
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
    }
}

对象类

/// <summary>
/// 表对象类，类名必须与MySQL表名一致
/// </summary>
public class azhtfb
{
    //这里的属性名随意
    string chamac;
    string stxmbh;
    string chamaa;
    string sttflb;
    int stblc;
    string sttffw;
    decimal stzjd;
    decimal styjd;
    decimal stswd;
    decimal stxwd;
    decimal sttfmj;
    DateTime stdcqssj;
    DateTime stdczzsj;
    string stgzff;
    string stbz;

    //必须有无参构造器
    public azhtfb()
    {
    }

    public azhtfb(string chamac, string stxmbh, string chamaa, 
                  string sttflb, int stblc, string sttffw, decimal stzjd, 
                  decimal styjd, decimal stswd, decimal stxwd, decimal sttfmj, 
                  DateTime stdcqssj, DateTime stdczzsj, string stgzff, string stbz
                 )
    {
        this.chamac = chamac;
        this.stxmbh = stxmbh;
        this.chamaa = chamaa;
        this.sttflb = sttflb;
        this.stblc = stblc;
        this.sttffw = sttffw;
        this.stzjd = stzjd;
        this.styjd = styjd;
        this.stswd = stswd;
        this.stxwd = stxwd;
        this.sttfmj = sttfmj;
        this.stdcqssj = stdcqssj;
        this.stdczzsj = stdczzsj;
        this.stgzff = stgzff;
        this.stbz = stbz;
    }

    ///这里的属性名（CHAMAC,STXMBH...）必须对应数据库中表的字段名（注意字段类型）
    public string CHAMAC { get => chamac; set => chamac = value; }
    public string STXMBH { get => stxmbh; set => stxmbh = value; }
    public string CHAMAA { get => chamaa; set => chamaa = value; }
    public string STTFLB { get => sttflb; set => sttflb = value; }
    public int STBLC { get => stblc; set => stblc = value; }
    public string STTFFW { get => sttffw; set => sttffw = value; }
    public decimal STZJD { get => stzjd; set => stzjd = value; }
    public decimal STYJD { get => styjd; set => styjd = value; }
    public decimal STSWD { get => stswd; set => stswd = value; }
    public decimal STXWD { get => stxwd; set => stxwd = value; }
    public decimal STTFMJ { get => sttfmj; set => sttfmj = value; }
    public DateTime STDCQSSJ { get => stdcqssj; set => stdcqssj = value; }
    public DateTime STDCZZSJ { get => stdczzsj; set => stdczzsj = value; }
    public string STGZFF { get => stgzff; set => stgzff = value; }
    public string STBZ { get => stbz; set => stbz = value; }
}