c#中可以使用的sqlhelper类

已于 2022-12-28 09:17:12 修改
阅读量1k 收藏 1
点赞数
文章标签： c# 数据库 java
于 2022-12-28 09:11:13 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ALI_SAf/article/details/128463673
版权 
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlTypes;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Test.DButility
{
    public class DBHelperSQL
    {
        #region 程序级应用变量
        
        public static string connectionString = "charset='utf8';pooling=true;SSLMode=none;Data Source='" + ConfigurationManager.AppSettings["Conn_server"] + "';"
                                                              + "Database='" + ConfigurationManager.AppSettings["Conn_database"] + "';"
                                                              + "Port=" + ConfigurationManager.AppSettings["Conn_port"] + ";"
                                                              + "User Id='" + ConfigurationManager.AppSettings["Conn_uid"] + "';"
                                                              + "Password='" + ConfigurationManager.AppSettings["Conn_pwd"] + "';"
                                                                + "Allow User Variables = True;";
        public static int Result = 0;
        #endregion

        public DataTable fsQuery(string strSQL)
        {
            return GetDataSet(CommandType.Text, strSQL, null).Tables[0];
        }

        public DataTable QueryLongTime(string strSQL)
        {
            return GetDataSetLongTime(CommandType.Text, strSQL, null).Tables[0];
        }

        public DataSet GetDataSetLongTime(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

        {

            //创建一个MySqlCommand对象          
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandTimeout = 30;
            //创建一个MySqlConnection对象          

            MySqlConnection conn = new MySqlConnection(connectionString);

            try
            {

                //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数                

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                //调用 MySqlCommand  的 ExecuteReader 方法              

                MySqlDataAdapter adapter = new MySqlDataAdapter();

                adapter.SelectCommand = cmd;

                DataSet ds = new DataSet();

                adapter.Fill(ds);

                //清除参数              

                cmd.Parameters.Clear();
                conn.Dispose();
                conn.Close();

                return ds;

            }

            catch (Exception e)
            {
                conn.Dispose();
                conn.Close();
                throw;
            }
        }

        public int fsExeSQL(string strSQL)
        {
            return ExecuteNonQuery(CommandType.Text, strSQL, null);
        }

        public int IsOpen()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connectionString))
                {

                    try
                    {
                        conn.Open();
                    }
                    catch (Exception)
                    {

                        conn.Dispose();
                        conn.Close();
                        throw;
                    }
                    if (conn.State == ConnectionState.Open)
                    {
                        return 1;
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch (Exception e)
            {

                return -1;
            }
        }

        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {

            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandTimeout = 15;
            using (MySqlConnection conn = new MySqlConnection(connectionString))

            {

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                int val = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

                return val;

            }

        }
        private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)

                conn.Open();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;

            if (trans != null)

                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {

                foreach (MySqlParameter parm in cmdParms)

                    cmd.Parameters.Add(parm);

            }
        }

        public static int ExistsCount(object obj, string table)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("SELECT COUNT(*) FROM " + table + " WHERE");
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.GetValue(obj, null) != null)
                    {
                        sql.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "' AND " + "");
                    }
                }
                string start = sql.ToString() + " 1 = 1";
                using (MySqlCommand cmd = new MySqlCommand(start, conn))
                {
                    cmd.Connection = conn;
                    cmd.CommandText = start;
                    cmd.CommandType = CommandType.Text;
                    MySqlDataAdapter adapter = new MySqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    cmd.Parameters.Clear();
                    conn.Close();
                    var id = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    return id;
                }
            }
        }


        public static int IsExistsId(object obj, string table)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("SELECT id FROM " + table + " WHERE");
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.GetValue(obj, null) != null)
                    {
                        sql.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "' AND " + "");
                    }
                }
                string start = sql.ToString() + " 1 = 1";
                using (MySqlCommand cmd = new MySqlCommand(start, conn))
                {
                    cmd.Connection = conn;
                    cmd.CommandText = start;
                    cmd.CommandType = CommandType.Text;
                    MySqlDataAdapter adapter = new MySqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    cmd.Parameters.Clear();
                    conn.Close();
                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        return -1;
                    }
                    var id = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    return id;
                }
            }
        }
        public DataSet GetDataSet(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

        {

            //创建一个MySqlCommand对象          

            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandTimeout = 30;
            //创建一个MySqlConnection对象          

            MySqlConnection conn = new MySqlConnection(connectionString);

            try
            {

                //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数                

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                //调用 MySqlCommand  的 ExecuteReader 方法              

                MySqlDataAdapter adapter = new MySqlDataAdapter();

                adapter.SelectCommand = cmd;

                DataSet ds = new DataSet();

                adapter.Fill(ds);

                //清除参数              

                cmd.Parameters.Clear();

                conn.Close();
                conn.Dispose();
                return ds;

            }

            catch (Exception e)
            {

                conn.Close();
                conn.Dispose();
                throw e;
            }
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public static object GetSingle(string SQLString, int Times)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        public static int Insert(object obj)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                //string table = GetTableName(obj);
                string table = GetTableName(obj);
                sql.Append("INSERT INTO " + table + "(");
                sqlend.Append(" VALUES (");

                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.Name.ToLower() != "id")
                    {
                        string columnValue = item.GetValue(obj, null) + "";
                        if (string.IsNullOrEmpty(columnValue))
                        {
                            continue;
                        }
                        if (item.PropertyType == typeof(DateTime))
                        {
                            DateTime dt;
                            DateTime.TryParse(columnValue, out dt);
                            if (dt <= SqlDateTime.MinValue.Value)
                                continue;
                        }
                        sql.Append(" " + item.Name + ",");
                        sqlend.Append(" '" + columnValue + "',");
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + ")";
                string end = sqlend.ToString();
                end = end.Substring(0, end.Length - 1) + ")";
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                }
                //conn.Close();
            }
            return n;
        }

        public static int Save(object obj)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                //string table = GetTableName(obj);
                string table = GetTableName(obj);
                sql.Append("INSERT INTO " + table + " (");
                sqlend.Append(" VALUES (");

                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.Name.ToLower() != "id")
                    {
                        string columnValue = item.GetValue(obj, null) + "";
                        if (string.IsNullOrEmpty(columnValue))
                        {
                            continue;
                        }
                        if (item.PropertyType == typeof(DateTime))
                        {
                            DateTime dt;
                            DateTime.TryParse(columnValue, out dt);
                            if (dt <= SqlDateTime.MinValue.Value)
                                continue;
                        }
                        sql.Append(" " + item.Name + ",");
                        sqlend.Append(" '" + columnValue + "',");
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + ")";
                string end = sqlend.ToString();
                end = end.Substring(0, end.Length - 1) + ")";
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    cmd.CommandTimeout = 15;
                    n = cmd.ExecuteNonQuery();
                    if (n > 0)
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT LAST_INSERT_ID();";
                        cmd.CommandType = CommandType.Text;
                        MySqlDataAdapter adapter = new MySqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        cmd.Parameters.Clear();
                        conn.Close();
                        var id = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                        return id;
                    }
                }
                //conn.Close();
            }
            return n;
        }

        public static int Save(object obj, string table)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("INSERT INTO " + table + " (");
                sqlend.Append(" VALUES (");

                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.Name.ToLower() != "id")
                    {
                        string columnValue = item.GetValue(obj, null) + "";
                        if (string.IsNullOrEmpty(columnValue))
                        {
                            continue;
                        }
                        if (item.PropertyType == typeof(DateTime))
                        {
                            DateTime dt;
                            DateTime.TryParse(columnValue, out dt);
                            if (dt <= SqlDateTime.MinValue.Value)
                                continue;
                        }
                        sql.Append(" " + item.Name + ",");
                        sqlend.Append(" '" + columnValue + "',");
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + ")";
                string end = sqlend.ToString();
                end = end.Substring(0, end.Length - 1) + ")";
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    cmd.CommandTimeout = 15;
                    n = cmd.ExecuteNonQuery();
                    if (n > 0)
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT LAST_INSERT_ID();";
                        cmd.CommandType = CommandType.Text;
                        MySqlDataAdapter adapter = new MySqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        cmd.Parameters.Clear();
                        conn.Close();
                        var id = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                        return id;
                    }
                }
                //conn.Close();
            }
            return n;
        }

        public static int Insert(object obj, string table)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("INSERT INTO " + table + "(");
                sqlend.Append(" VALUES (");

                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.Name.ToLower() != "id")
                    {
                        string columnValue = item.GetValue(obj, null) + "";
                        if (string.IsNullOrEmpty(columnValue))
                        {
                            continue;
                        }
                        if (item.PropertyType == typeof(DateTime))
                        {
                            DateTime dt;
                            DateTime.TryParse(columnValue, out dt);
                            if (dt <= SqlDateTime.MinValue.Value)
                                continue;
                        }
                        sql.Append(" " + item.Name + ",");
                        sqlend.Append(" '" + columnValue + "',");
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + ")";
                string end = sqlend.ToString();
                end = end.Substring(0, end.Length - 1) + ")";
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return n;
        }

        public static int Delete(string table, object id)
        {
            string sql = @$"delete from {table} where id = {id}";
            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection conn = new MySqlConnection(connectionString))

            {
                if (conn.State != ConnectionState.Open)

                    conn.Open();

                cmd.Connection = conn;

                cmd.CommandText = sql;

                cmd.CommandType = CommandType.Text;

                int val = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                conn.Close();
                return val;

            }
        }

        public static int Delete(object obj)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                string table = GetTableName(obj);
                sql.Append("DELETE FROM" + table);
                sqlend.Append(" WHERE");
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name.ToLower() == "id")
                    {
                        sqlend.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'");
                        break;
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + " ";
                string end = sqlend.ToString();
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return n;
        }

        public static int Update(object obj, string table)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("UPDATE " + table + " set");
                sqlend.Append("WHERE");
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.GetValue(obj, null) != null)
                    {
                        sql.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'," + "");
                    }
                }
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name.ToLower() == "id")
                    {
                        sqlend.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'");
                        break;
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + " ";
                string end = sqlend.ToString();
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return n;
        }

        public static int Update(object obj)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                string table = GetTableName(obj);
                sql.Append("UPDATE " + table + " set");
                sqlend.Append("WHERE");
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.GetValue(obj, null) != null)
                    {
                        sql.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'," + "");
                    }
                }
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name.ToLower() == "id")
                    {
                        sqlend.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'");
                        break;
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + " ";
                string end = sqlend.ToString();
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return n;
        }

        public static int UpdateNull(object obj)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                string table = GetTableName(obj);
                sql.Append("UPDATE " + table + " set");
                sqlend.Append("WHERE");
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    sql.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'," + "");
                }
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name.ToLower() == "id")
                    {
                        sqlend.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'");
                        break;
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + " ";
                string end = sqlend.ToString();
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return n;
        }

        public static int Update(object obj, string table, string where)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("UPDATE " + table + " set");
                sqlend.Append("WHERE ");
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.GetValue(obj, null) != null)
                    {
                        sql.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'," + "");
                    }
                }
                sqlend.Append(where);
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + " ";
                string end = sqlend.ToString();
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return n;
        }

        public static int UpdateNull(object obj, string table, string where)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("UPDATE " + table + " set");
                sqlend.Append("WHERE ");
                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    sql.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'," + "");
                }
                sqlend.Append(where);
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + " ";
                string end = sqlend.ToString();
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return n;
        }

        //public static void Begin()
        //{
        //    using (MySqlConnection conn = new MySqlConnection(connectionString))
        //    {
        //        conn.Open();
        //        using (MySqlCommand cmd = new MySqlCommand("Begin;", conn))
        //        {
        //            cmd.ExecuteNonQuery();
        //            conn.Close();
        //        }
        //    }
        //}

        //public static void RollBack()
        //{
        //    using (MySqlConnection conn = new MySqlConnection(connectionString))
        //    {
        //        conn.Open();
        //        using (MySqlCommand cmd = new MySqlCommand("rollback;", conn))
        //        {
        //            cmd.ExecuteNonQuery();
        //            conn.Close();
        //        }
        //    }
        //}

        //public static void Commit()
        //{
        //    using (MySqlConnection conn = new MySqlConnection(connectionString))
        //    {
        //        conn.Open();
        //        using (MySqlCommand cmd = new MySqlCommand("Commit;", conn))
        //        {
        //            cmd.ExecuteNonQuery();
        //            conn.Close();
        //        }
        //    }
        //}

        private static string GetTableName(object obj)
        {
            var pro = obj.GetType().ToString().Split('.');
            string table = pro[pro.Length - 1].Replace("m_", "");
            return table;
        }

        public static int GetNextId(object obj)
        {
            var table = GetTableName(obj);
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("INSERT INTO " + table + " (");
                sqlend.Append(" VALUES (");

                foreach (PropertyInfo item in pro)
                {
                    if (item.Name == "TableName")
                    {
                        continue;
                    }
                    else if (item.Name.ToLower() != "id")
                    {
                        string columnValue = item.GetValue(obj, null) + "";
                        if (string.IsNullOrEmpty(columnValue))
                        {
                            continue;
                        }
                        if (item.PropertyType == typeof(DateTime))
                        {
                            DateTime dt;
                            DateTime.TryParse(columnValue, out dt);
                            if (dt <= SqlDateTime.MinValue.Value)
                                continue;
                        }
                        sql.Append(" " + item.Name + ",");
                        sqlend.Append(" '" + columnValue + "',");
                    }
                }
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + ")";
                string end = sqlend.ToString();
                end = end.Substring(0, end.Length - 1) + ")";
                sqltext = start + end;
                using (MySqlCommand cmd = new MySqlCommand(sqltext, conn))
                {
                    n = cmd.ExecuteNonQuery();
                    if (n > 0)
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT LAST_INSERT_ID();";
                        cmd.CommandType = CommandType.Text;
                        MySqlDataAdapter adapter = new MySqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        cmd.Parameters.Clear();
                        var id = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                        var delete_sql = $"delete from {table} where id = {id}";
                        cmd.CommandText = delete_sql;
                        var del = cmd.ExecuteNonQuery();
                        conn.Close();
                        return id;
                    }
                }
            }
            return Convert.ToInt32(0);
        }

        public static async Task<DataTable> QuerySqlAsync(string sql)
        {
            using (MySqlConnection myconn = new MySqlConnection(connectionString))
            {
                try
                {
                    await myconn.OpenAsync();
                    using (MySqlCommand mycom = myconn.CreateCommand())
                    {
                        mycom.CommandText = sql;
                        MySqlDataAdapter adap = new MySqlDataAdapter(mycom);
                        DataSet ds = new DataSet();
                        await adap.FillAsync(ds);
                        await myconn.CloseAsync();
                        return ds.Tables[0];
                    }
                }
                catch (Exception e)
                {
                    await myconn.CloseAsync();
                    myconn.Dispose();
                    return null;
                }
            }
        }

        public async static Task<object> ExecuteSqlAsync(string sqlText)
        {
            //在回调函数关闭数据库
            MySqlConnection connection = null;
            try
            {
                connection = new MySqlConnection();
                connection.ConnectionString = connectionString;
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlText;
                await connection.OpenAsync();
                var result = cmd.BeginExecuteNonQuery(CallbackAsyncExecuteNonQuery, cmd);
                return Result;
            }
            catch (Exception ex)
            {
                if (connection != null)
                {
                    await connection.CloseAsync();
                }
                connection.Dispose();
                return null;
            }
        }

        public async static void CallbackAsyncExecuteNonQuery(IAsyncResult callBack)
        {
            MySqlCommand cmm = null;
            try
            {
                cmm = (MySqlCommand)callBack.AsyncState;
                if (cmm == null)
                {
                    return;
                }
                Result = cmm.EndExecuteNonQuery(callBack);
            }
            catch (Exception ex)
            {
                if (cmm != null)
                {
                    await cmm.Connection.CloseAsync();
                    cmm.Dispose();
                }
                else
                {

                }
            }
            finally
            {
                if (cmm != null && cmm.Connection != null && cmm.Connection.State != ConnectionState.Closed)
                {
                    cmm.Dispose();
                    await cmm.Connection.CloseAsync();
                }
            }
        }

        public async static Task<int?> SaveAsync(object obj, string table)
        {
            //在回调函数关闭数据库
            MySqlConnection connection = null;
            try
            {
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                //string table = GetTableName(obj);
                sql.Append("INSERT INTO " + table + "(");
                sqlend.Append(" VALUES (");
                Parallel.ForEach(pro, item =>
                {
                    string columnValue = item.GetValue(obj, null) + "";
                    if (!string.IsNullOrEmpty(columnValue))
                    {
                        if (item.PropertyType == typeof(DateTime))
                        {
                            DateTime dt;
                            DateTime.TryParse(columnValue, out dt);
                            if (dt <= SqlDateTime.MinValue.Value)
                            {
                                return;
                            }
                        }
                        sql.Append(" " + item.Name + ",");
                        sqlend.Append(" '" + columnValue + "',");
                    }
                });
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + ")";
                string end = sqlend.ToString();
                end = end.Substring(0, end.Length - 1) + ")";
                sqltext = start + end;
                connection = new MySqlConnection();
                connection.ConnectionString = connectionString;
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqltext;
                await connection.OpenAsync();
                var result = cmd.BeginExecuteNonQuery(CallbackSaveAsync, cmd);


                if (result.IsCompleted)
                {
                    cmd.Connection = connection;
                    cmd.CommandText = "SELECT LAST_INSERT_ID();";
                    cmd.CommandType = CommandType.Text;
                    MySqlDataAdapter adapter = new MySqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    cmd.Parameters.Clear();
                    connection.Close();
                    Result = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }
                return Result;
            }
            catch (Exception ex)
            {
                if (connection != null)
                {
                    await connection.CloseAsync();
                }
                connection.Dispose();
                return null;
            }
        }

        public async static void CallbackSaveAsync(IAsyncResult callBack)
        {
            MySqlCommand cmd = null;
            try
            {
                cmd = (MySqlCommand)callBack.AsyncState;
                if (cmd == null)
                {
                    return;
                }
                if (cmd.EndExecuteNonQuery(callBack) > 0)
                {

                }
            }
            catch (Exception ex)
            {
                if (cmd != null)
                {
                    await cmd.Connection.CloseAsync();
                    cmd.Dispose();
                }
                else
                {

                }
            }
            finally
            {
                if (cmd != null && cmd.Connection != null && cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Dispose();
                    await cmd.Connection.CloseAsync();
                }
            }
        }

    }
}

2.

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Tset.DButility
{
    public delegate Task<int> AsyncMethodCaller(MySqlCommand cmd, MySqlConnection connection);
    public class DBhelperAsync
    {
        #region 程序级应用变量
        public static Exception Error = null;
        public static string connectionString = "charset='utf8';pooling=true;SSLMode=none;Data Source='" + ConfigurationManager.AppSettings["Conn_server"] + "';"
                                                              + "Database='" + ConfigurationManager.AppSettings["Conn_database"] + "';"
                                                              + "Port=" + ConfigurationManager.AppSettings["Conn_port"] + ";"
                                                              + "User Id='" + ConfigurationManager.AppSettings["Conn_uid"] + "';"
                                                              + "Password='" + ConfigurationManager.AppSettings["Conn_pwd"] + "';";
        #endregion
        private static async Task<string> GetTableName(object obj)
        {
            string[] pro = null;
            await Task.Run(() => pro = obj.GetType().ToString().Split('.')); string table = pro[pro.Length - 1].Replace("m_", ""); ;
            return table;
        }

        public static async Task<DataTable> QuerySqlAsync(string sql)
        {
            using (MySqlConnection myconn = new MySqlConnection(connectionString))
            {
                try
                {
                    await myconn.OpenAsync();
                    using (MySqlCommand mycom = myconn.CreateCommand())
                    {
                        mycom.CommandText = sql;
                        MySqlDataAdapter adap = new MySqlDataAdapter(mycom);
                        DataSet ds = new DataSet();
                        await adap.FillAsync(ds);
                        await myconn.CloseAsync();
                        return ds.Tables[0];
                    }
                }
                catch (Exception ex)
                {
                    Error = ex;
                    await myconn.CloseAsync();
                    await myconn.DisposeAsync();
                    return null;
                }
            }
        }

        public async static Task<int> ExecuteSqlAsync(string sqlText)
        {
            //在回调函数关闭数据库
            MySqlConnection connection = null;
            try
            {
                connection = new MySqlConnection();
                connection.ConnectionString = connectionString;
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlText;
                await connection.OpenAsync();
                var Execute_Count = cmd.ExecuteNonQuery();
                await connection.CloseAsync();
                return Execute_Count;
            }
            catch (Exception ex)
            {
                Error = ex;
                if (connection != null)
                {
                    await connection.CloseAsync();
                }
                await connection.DisposeAsync();
                return -1;
            }
        }

        public async static Task<int> SaveAsync(object obj, string table)
        {
            //在回调函数关闭数据库
            MySqlConnection connection = null;
            try
            {
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                sql.Append("INSERT INTO " + table + "(");
                sqlend.Append(" VALUES (");
                Parallel.ForEach(pro, item =>
                {
                    string columnValue = item.GetValue(obj, null) + "";
                    if (!string.IsNullOrEmpty(columnValue))
                    {
                        if (item.PropertyType == typeof(DateTime))
                        {
                            DateTime dt;
                            DateTime.TryParse(columnValue, out dt);
                            if (dt <= SqlDateTime.MinValue.Value)
                            {
                                return;
                            }
                        }
                        sql.Append(" " + item.Name + ",");
                        sqlend.Append(" '" + columnValue + "',");
                    }
                });
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + ")";
                string end = sqlend.ToString();
                end = end.Substring(0, end.Length - 1) + ")";
                sqltext = start + end;
                connection = new MySqlConnection();
                connection.ConnectionString = connectionString;
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqltext;
                await connection.OpenAsync();
                AsyncMethodCaller caller = new AsyncMethodCaller(GetLastInsertId);
                var workTask = Task.Run(() => caller.Invoke(cmd, connection)).Result;
                return workTask;
            }
            catch (Exception ex)
            {
                Error = ex;
                if (connection != null)
                {
                    await connection.CloseAsync();
                }
                await connection.DisposeAsync();
                return -1;
            }
        }

        public async static Task<int> SaveAsync(object obj)
        {
            //在回调函数关闭数据库
            MySqlConnection connection = null;
            try
            {
                StringBuilder sql = new StringBuilder();
                StringBuilder sqlend = new StringBuilder();
                PropertyInfo[] pro = obj.GetType().GetProperties();
                string sqltext = string.Empty;
                var table = await GetTableName(obj);
                sql.Append("INSERT INTO " + table + "(");
                sqlend.Append(" VALUES (");
                Parallel.ForEach(pro, item =>
                {
                    string columnValue = item.GetValue(obj, null) + "";
                    if (!string.IsNullOrEmpty(columnValue))
                    {
                        if (item.PropertyType == typeof(DateTime))
                        {
                            DateTime dt;
                            DateTime.TryParse(columnValue, out dt);
                            if (dt <= SqlDateTime.MinValue.Value)
                            {
                                return;
                            }
                        }
                        sql.Append(" " + item.Name + ",");
                        sqlend.Append(" '" + columnValue + "',");
                    }
                });
                string start = sql.ToString();
                start = start.Substring(0, start.Length - 1) + ")";
                string end = sqlend.ToString();
                end = end.Substring(0, end.Length - 1) + ")";
                sqltext = start + end;
                connection = new MySqlConnection();
                connection.ConnectionString = connectionString;
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqltext;
                await connection.OpenAsync();
                AsyncMethodCaller caller = new AsyncMethodCaller(GetLastInsertId);
                var workTask = Task.Run(() => caller.Invoke(cmd, connection)).Result;
                return workTask;
            }
            catch (Exception ex)
            {
                Error = ex;
                if (connection != null)
                {
                    await connection.CloseAsync();
                }
                await connection.DisposeAsync();
                return -1;
            }
        }

        private async static Task<int> GetLastInsertId(MySqlCommand cmd, MySqlConnection connection)
        {
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    cmd.Connection = connection;
                    cmd.CommandText = "SELECT LAST_INSERT_ID();";
                    cmd.CommandType = CommandType.Text;
                    MySqlDataAdapter adapter = new MySqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    await adapter.FillAsync(ds);
                    cmd.Parameters.Clear();
                    //connection.Close();
                    await connection.CloseAsync();
                    await connection.DisposeAsync();
                    int id = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    sw.Stop();
                    return id;
                }
                return 0;
            }
            catch (Exception ex)
            {
                Error = ex;
                await connection.CloseAsync();
                await connection.DisposeAsync();
                return -1;
            }
        }

    }
}

ALI_SAf
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
C# SqlHelper类 （微软官方）
03-07
微软官方C# SqlHelper类 ，内带有注释说明，供大家参考
详解使用C#编写SqlHelper类
08-29
本篇文章主要介绍了使用C#编写SqlHelper类，这是一个非常有用的工具类，可以帮助我们更方便地操作数据库。在本文中，我们将一步步地创建一个SqlHelper类，封装了一些常用的数据库操作方法。 首先，我们需要配置连接...
sqlhelper 的使用 （C#）超级详细的入门教程
RJGCWJH的博客
11-17 9260
sqlhelper 的使用 （C#）超级详细的入门教程
SqlHelper方法
weixin_43522117的博客
03-13 4345
1.SqlHelper介绍 是一个sql的辅助类，可以是代码更简洁，更有可读性！ 2. 类方法方法介绍 retBool(Integerresult):判断数据库操作是否成功 getObject(List<E>list)：从list中取第一条数据返回对应List中泛型的单个结果 fillWrapper(Page<?>page, Wrapper<?>wrapper)：填充Wrapper sqlSession(Class<?>clazz)：获取...
微软官方SqlHelper类 数据库辅助操作类
qq165285727的专栏
03-28 282
Web.config配置。
C#：SqlHelper
cxu123321的博客
07-06 580
C#：SqlHelper 虽然日常工作中都是调用别人写好的底层，但是要真正学到技术，还是要懂些底层原理，最好是能自己写底层 一、底层 注：引用命名空间 using System.Data; using System.Data.SqlClient; 1 public class SqlHelper 2 { 3 /// <summary&g...
学习笔记：SqlHelper（SQL SERVER）
Q_0048的博客
03-23 798
一、思维导图 二、知识点描述 　　SqlHelper用于简化重复的去写那些数据库连接（SqlConnection）,SqlCommand,SqlDataReader等等的代码。SqlHelper 封装过后通常是只需要给方法传入一些参数如数据库连接字符串，SQL参数等，就可以访问数据库。SqlHelper 类用于通过一组静态方法来封装数据访问功能。该类不能被继承或实例化，因此将其声明为包含专...
C#编写SqlHelper类
08-29
在C#中使用ADO.NET连接数据库的时候，每次连接都要编写连接，打开，执行SQL语句的代码，很麻烦，编写一个SqlHelper类，把每次连接都要写的代码封装成方法，把要执行的SQL语句通过参数传进去，可以大大简化编码
C# SqlHelper类
01-23
C#的SqlHelper类并且有个简单的小DEMO，提供几个方法的简答使用和注释！
MSSQL数据访问基础类
生有可恋的专栏
08-29 518
using System;using System.Data;using System.Data.SqlClient;using System.Collections;using System.Collections.Specialized;using System.Configuration;using System.Web;using System.Web.
常用SqlHelper类（C#）
Hugh的博客
11-04 2万+
SqlHelper类 public static class SqlHelper { //连接字符串 private static readonly string connStr = ConfigurationManager.ConnectionStrings["mssql"].ConnectionString; //1.执行增、删、...
C# SqlHelper类的使用
xzya321的博客
04-02 5716
SqlHelper类 1.首先SqlHelper类是一个基于.NET Framework的数据库操作组件，包含了数据库的操作方法。可以简化在C#中使用ADO.NET连接数据库时每次都要编写连接、打开、执行SQL语句的代码，它将每次连接都要写的代码封装成方法，把要执行的SQL语句通过参数传进去，如数据库连接字符串，SQL参数等，就可以访问数据库了。 2.SqlHelper类的编写不详细说，有完整的现...
C# SqlHelper
weixin_43942765的博客
09-28 906
每当小编在做一个新项目的时候都不可避免的会接触到与数据库的交互，小编准备一份，分享给大家，小编不觉得自己的这份SqlHelper多好，因为与部门里的老师傅们相比，我简直是逊毙了，不过平常自己练习项目的时候这个还是可以减少大家很多时间的 using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; name
Mybatis工具SqlHelper - 获取Mybatis方法的Sql
热门推荐
偶尔记一下 - mybatis.io
10-13 3万+
如果你想在代码中获取执行的sql
SqlHelper中的ExcuteNonQuery方法
Asyouwishing1的博客
04-17 274
SqlHelper中的ExcuteNonQuery方法。
C# SQLHelper类
技术分享博客
02-23 916
SQLHelper类简单sqlhelper类代码简单sqlhelper调用方法升级版sqlHelper类代码升级版sqlHelper类调用方法向数据库插入图片和执行存储过程的sqlHelper向数据库插入图片和执行存储过程的sqlHelper的调用方法 简单sqlhelper类代码 using System.Configuration; using System.Data; using System.Data.SqlClient; namespace UserInfoMgr { class Sql
C#数据库教程4-ADO.NET自己封装SqlHelper类
panjinliang066333的博客
04-21 3102
目录 一、版本1.0 1、方法封装 （1）ExecuteNonQuery方法 （2）ExecuteScalar方法 （3）ExecuteDataTable方法 2、调用测试 界面 （1）插入数据 （2）查询数据 （3）DataTable查询 二、版本2.0 1、方法封装 （1）ExecuteNonQuery方法 （2）ExecuteScalar方法 （3）ExecuteDataTable方法 2、调用测试 界面 （1）插入数据 （2）查询数据 （3）DataTabl.
SqlHelper 详解
非闪--BLOG
03-05 1万+
SqlHelper 类实现详细信息SqlHelper 类用于通过一组静态方法来封装数据访问功能。该类不能被继承或实例化，因此将其声明为包含专用构造函数的不可继承类。 在 SqlHelper 类中实现的每种方法都提供了一组一致的重载。这提供了一种很好的使用 SqlHelper 类来执行命令的模式，同时为开发人员选择访问数据的方式提供了必要的
C#中SqlHelper工具类的实现
一支黑色の铅笔博客
04-21 406
/存储过程 or Sql语句。/// 第一行第一列。
c#sqlhelper类
最新发布
01-30
C是计算机编程语言中的一种编程语言。它是由丹尼斯·里奇于1972年在贝尔实验室开发的，用于编写UNIX操作系统。C语言具有简洁、高效、灵活的特点，被广泛应用于各种领域的软件开发中。 C语言的语法相对简单，易于理解和学习，不需要过多的额外语法规则。C语言的语法结构与其他高级编程语言相似，如变量、数据类型、循环、条件语句等，使得开发者能够迅速上手。 C语言的执行效率高，能够生成高效的机器代码。C语言是一种静态类型语言，在编译时就确定了变量的数据类型，使得程序在运行时更加高效和稳定。 C语言具有丰富的函数库，方便开发者调用和使用，可以实现各种功能。同时，C语言还支持指针，可以进行底层的内存操作和管理，提高程序性能和灵活性。 由于C语言的广泛应用和强大的功能，许多计算机科学领域都采用C语言进行编程，例如操作系统、数据库、嵌入式系统、游戏开发等。 总之，C语言是一种广泛应用于软件开发领域的编程语言，具有简洁、高效、灵活等特点，是学习和使用编程语言的基础之一。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值