C# 操作SqlServer数据库

C# 操作SqlServer数据库

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleApp11
{
    class SqlServerHelper : IDisposable
    {
        private SqlConnection sqlConnection;
        public SqlServerHelper(string connectionStr)
        {
            try
            {
                if (sqlConnection != null)
                    return;
                sqlConnection = new SqlConnection(connectionStr);
                Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库连接失败!!!");
                Console.WriteLine(e);
            }
        }
        private void Open()
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
        }
        public SqlServerMessage Insert_Delete_Update(string sql)
        {
            try
            {
                Open();
                SqlCommand command = new SqlCommand(sql, sqlConnection);
                int num = command.ExecuteNonQuery();
                command.Dispose();
                return new SqlServerMessage() { Count = num,Error = string.Empty};
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库Insert_Delete_Update失败!!!");
                Console.WriteLine(e.Message);
            }
            return new SqlServerMessage() { Count = -1,Error = "数据库Insert_Delete_Update失败!!!" };
        }
        public async Task<SqlServerMessage> Insert_Delete_UpdateAsync(string sql)
        {
            try
            {
                Open();
                SqlCommand command = new SqlCommand(sql, sqlConnection);
                int num = await command.ExecuteNonQueryAsync();
                command.Dispose();
                return new SqlServerMessage() { Count = num, Error = string.Empty };
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库Insert_Delete_Update失败!!!");
                Console.WriteLine(e.Message);
            }
            return new SqlServerMessage() { Count = -1, Error = "数据库Insert_Delete_Update失败!!!" };
        }
        public SqlServerMessage Insert(string table, string[] keys, object[] values)
        {
            if (keys == null || keys == null || string.IsNullOrEmpty(table))
                return new SqlServerMessage() { Count = -1, Error = "传入的形参有空值" };
            if (keys.Length != values.Length)
                return new SqlServerMessage() { Count = -1, Error = "Key和Value的长度不同" };
            Open();
            //(uid,name) values(@UID,@NAME)
            var kSbr = new StringBuilder();
            var vSbr = new StringBuilder();
            for (int i = 0; i < keys.Length; i++)
            {
                kSbr.Append(keys[i]);
                vSbr.Append("@");
                vSbr.Append(keys[i].ToUpper());
                if (i + 1 < keys.Length)
                {
                    kSbr.Append(",");
                    vSbr.Append(",");
                }
            }
            string sql_insert = $"insert into {table}({kSbr}) values({vSbr})";
            try
            {
                SqlCommand cmd_insert = new SqlCommand(sql_insert, sqlConnection);
                for (int i = 0; i < keys.Length; i++)
                {
                    SqlParameter parameter = new SqlParameter("@" + keys[i].ToUpper(), values[i]);
                    cmd_insert.Parameters.Add(parameter);
                }
                var num = cmd_insert.ExecuteNonQuery();
                cmd_insert.Dispose();
                return new SqlServerMessage() { Count = num, Error = string.Empty };
            }
            catch (Exception e)
            {
                Console.WriteLine("插入数据库失败!!!");
                Console.WriteLine(e.Message);
            }
            return new SqlServerMessage() { Count = -1, Error = "插入数据库失败!!!" };
        }
        public SqlServerMessage Insert(string table, Dictionary<string, object> dict)
        {
            if (table == null || dict == null)
                return new SqlServerMessage() { Count = -1, Error = "传入的形参有空值" };
            Open();
            var kSbr = new StringBuilder();
            var vSbr = new StringBuilder();
            //(uid,name) values(@UID,@NAME)
            int index = 0;
            foreach (var pair in dict)
            {
                kSbr.Append(pair.Key);
                vSbr.Append("@");
                vSbr.Append(pair.Key.ToUpper());
                if (index + 1 < dict.Count)
                {
                    kSbr.Append(",");
                    vSbr.Append(",");
                }
                index++;
            }
            string sql_insert = $"insert into {table}({kSbr}) values({vSbr})";
            try
            {
                SqlCommand cmd_insert = new SqlCommand(sql_insert, sqlConnection);
                foreach (var pair in dict)
                {
                    SqlParameter parameter = new SqlParameter("@" + pair.Key.ToUpper(), pair.Value);
                    cmd_insert.Parameters.Add(parameter);
                }
                var num = cmd_insert.ExecuteNonQuery();
                cmd_insert.Dispose();
                return new SqlServerMessage() { Count = num, Error = string.Empty };
            }
            catch (Exception e)
            {
                Console.WriteLine("插入数据库失败!!!");
                Console.WriteLine(e.Message);
            }
            return new SqlServerMessage() { Count = -1, Error = "插入数据库失败!!!" };
        }
        public async Task<SqlServerMessage> InsertAsync(string table, string[] keys, object[] values)
        {
            if (keys == null || keys == null || string.IsNullOrEmpty(table))
                return new SqlServerMessage() { Count = -1, Error = "传入的形参有空值" };
            if (keys.Length != values.Length)
                return new SqlServerMessage() { Count = -1, Error = "Key和Value的长度不同" };
            Open();
            //(uid,name) values(@UID,@NAME)
            var kSbr = new StringBuilder();
            var vSbr = new StringBuilder();
            for (int i = 0; i < keys.Length; i++)
            {
                kSbr.Append(keys[i]);
                vSbr.Append("@");
                vSbr.Append(keys[i].ToUpper());
                if (i + 1 < keys.Length)
                {
                    kSbr.Append(",");
                    vSbr.Append(",");
                }
            }
            string sql_insert = $"insert into {table}({kSbr}) values({vSbr})";
            try
            {
                SqlCommand cmd_insert = new SqlCommand(sql_insert, sqlConnection);
                for (int i = 0; i < keys.Length; i++)
                {
                    SqlParameter parameter = new SqlParameter("@" + keys[i].ToUpper(), values[i]);
                    cmd_insert.Parameters.Add(parameter);
                }
                var num = await cmd_insert.ExecuteNonQueryAsync();
                cmd_insert.Dispose();
                return new SqlServerMessage() { Count = num, Error = string.Empty };
            }
            catch (Exception e)
            {
                Console.WriteLine("插入数据库失败!!!");
                Console.WriteLine(e.Message);
            }
            return new SqlServerMessage() { Count = -1, Error = "插入数据库失败!!!" };
        }
        public async Task<SqlServerMessage> InsertAsync(string table, Dictionary<string, object> dict)
        {
            if (table == null || dict == null)
                return new SqlServerMessage() { Count = -1, Error = "传入的形参有空值" };
            Open();
            var kSbr = new StringBuilder();
            var vSbr = new StringBuilder();
            //(uid,name) values(@UID,@NAME)
            int index = 0;
            foreach (var pair in dict)
            {
                kSbr.Append(pair.Key);
                vSbr.Append("@");
                vSbr.Append(pair.Key.ToUpper());
                if (index + 1 < dict.Count)
                {
                    kSbr.Append(",");
                    vSbr.Append(",");
                }
                index++;
            }
            string sql_insert = $"insert into {table}({kSbr}) values({vSbr})";
            try
            {
                SqlCommand cmd_insert = new SqlCommand(sql_insert, sqlConnection);
                foreach (var pair in dict)
                {
                    SqlParameter parameter = new SqlParameter("@" + pair.Key.ToUpper(), pair.Value);
                    cmd_insert.Parameters.Add(parameter);
                }
                var num = await cmd_insert.ExecuteNonQueryAsync();
                cmd_insert.Dispose();
                return new SqlServerMessage() { Count = num, Error = string.Empty };
            }
            catch (Exception e)
            {
                Console.WriteLine("插入数据库失败!!!");
                Console.WriteLine(e.Message);
            }
            return new SqlServerMessage() { Count = -1, Error = "插入数据库失败!!!" };
        }
        public SqlServerMessage Update(string table,Dictionary<string,object> setDict,Dictionary<string,object> whereDict)
        {
            var setSbr = new StringBuilder();
            var whereSbr = new StringBuilder();
            // set name=@NAME,passworld=@PASSWORLD where id=@ID;
            int index = 0;
            foreach (var pair in setDict)
            {
                setSbr.Append(pair.Key);
                setSbr.Append("=@");
                setSbr.Append(pair.Key.ToUpper());
                if (index + 1 < setDict.Count)
                    setSbr.Append(",");
                index++;
            }
            index = 0;
            foreach (var pair in whereDict)
            {
                whereSbr.Append(pair.Key);
                whereSbr.Append("=@");
                whereSbr.Append(pair.Key.ToUpper());
                if (index + 1 < whereDict.Count)
                    whereSbr.Append(",");
                index++;
            }
            string sql_update = $"update {table} set {setSbr} where {whereSbr};";
            try
            {
                Open();
                SqlCommand cmd_update = new SqlCommand(sql_update, sqlConnection);
                foreach (var pair in setDict)
                    cmd_update.Parameters.AddWithValue("@" + pair.Key.ToUpper(), pair.Value);
                foreach (var pair in whereDict)
                    cmd_update.Parameters.AddWithValue("@" + pair.Key.ToUpper(), pair.Value);
                int res_2 = cmd_update.ExecuteNonQuery();
                cmd_update.Dispose();
                return new SqlServerMessage() { Count = res_2, Error = string.Empty };
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库Update失败");
                Console.WriteLine(e.Message);
            }
            return new SqlServerMessage() { Count = -1, Error = "数据库Update失败" };
        }
        public async Task<SqlServerMessage> UpdateAsync(string table, Dictionary<string, object> setDict, Dictionary<string, object> whereDict)
        {
            var setSbr = new StringBuilder();
            var whereSbr = new StringBuilder();
            // set name=@NAME,passworld=@PASSWORLD where id=@ID;
            int index = 0;
            foreach (var pair in setDict)
            {
                setSbr.Append(pair.Key);
                setSbr.Append("=@");
                setSbr.Append(pair.Key.ToUpper());
                if (index + 1 < setDict.Count)
                    setSbr.Append(",");
                index++;
            }
            index = 0;
            foreach (var pair in whereDict)
            {
                whereSbr.Append(pair.Key);
                whereSbr.Append("=@");
                whereSbr.Append(pair.Key.ToUpper());
                if (index + 1 < whereDict.Count)
                    whereSbr.Append(",");
                index++;
            }
            string sql_update = $"update {table} set {setSbr} where {whereSbr};";
            try
            {
                Open();
                SqlCommand cmd_update = new SqlCommand(sql_update, sqlConnection);
                foreach (var pair in setDict)
                    cmd_update.Parameters.AddWithValue("@" + pair.Key.ToUpper(), pair.Value);
                foreach (var pair in whereDict)
                    cmd_update.Parameters.AddWithValue("@" + pair.Key.ToUpper(), pair.Value);
                int res_2 = await cmd_update.ExecuteNonQueryAsync();
                cmd_update.Dispose();
                return new SqlServerMessage() { Count = res_2, Error = string.Empty };
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库Update失败");
                Console.WriteLine(e.Message);
            }
            return new SqlServerMessage() { Count = -1, Error = "数据库Update失败" };
        }
        public SqlServerMessage Delete(string table, Dictionary<string, object> whereDict,bool isDelete = false)
        {
            if (isDelete)
            {
                var whereSbr = new StringBuilder();
                int index = 0;
                foreach (var pair in whereDict)
                {
                    whereSbr.Append(pair.Key);
                    whereSbr.Append("=@");
                    whereSbr.Append(pair.Key.ToUpper());
                    if (index + 1 < whereDict.Count)
                        whereSbr.Append(",");
                    index++;
                }
                string sql_delete = $"DELETE FROM {table} WHERE {whereSbr};";
                try
                {
                    Open();
                    SqlCommand cmd_delete = new SqlCommand(sql_delete, sqlConnection);
                    foreach (var pair in whereDict)
                        cmd_delete.Parameters.AddWithValue("@" + pair.Key.ToUpper(), pair.Value);
                    int res_3 = cmd_delete.ExecuteNonQuery();
                    cmd_delete.Dispose();
                    return new SqlServerMessage() { Count = res_3, Error = string.Empty };
                }
                catch (Exception e)
                {
                    Console.WriteLine("数据库Delete失败!");
                    Console.WriteLine(e.Message);
                }
                return new SqlServerMessage() { Count = -1, Error = "数据库Delete失败!" };
            }
            else
            {
                var deleteDict = new Dictionary<string, object>();
                deleteDict.Add("IsDelete", true);
                return Update(table, deleteDict, whereDict);
            }
        }
        public async Task<SqlServerMessage> DeleteAsync(string table, Dictionary<string, object> whereDict, bool isDelete = false)
        {
            if (isDelete)
            {
                var whereSbr = new StringBuilder();
                int index = 0;
                foreach (var pair in whereDict)
                {
                    whereSbr.Append(pair.Key);
                    whereSbr.Append("=@");
                    whereSbr.Append(pair.Key.ToUpper());
                    if (index + 1 < whereDict.Count)
                        whereSbr.Append(",");
                    index++;
                }
                string sql_delete = $"DELETE FROM {table} WHERE {whereSbr};";
                try
                {
                    Open();
                    SqlCommand cmd_delete = new SqlCommand(sql_delete, sqlConnection);
                    foreach (var pair in whereDict)
                        cmd_delete.Parameters.AddWithValue("@" + pair.Key.ToUpper(), pair.Value);
                    int res_3 = await cmd_delete.ExecuteNonQueryAsync();
                    cmd_delete.Dispose();
                    return new SqlServerMessage() { Count = res_3, Error = string.Empty };
                }
                catch (Exception e)
                {
                    Console.WriteLine("数据库Delete失败!");
                    Console.WriteLine(e.Message);
                }
                return new SqlServerMessage() { Count = -1, Error = "数据库Delete失败!" };
            }
            else
            {
                var deleteDict = new Dictionary<string, object>();
                deleteDict.Add("IsDelete", true);
                return await UpdateAsync(table, deleteDict, whereDict);
            }
        }
        public DataSet Find(string sql, string tableName)
        {
            try
            {
                Open();
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection);
                da.Fill(ds, tableName);
                da.Dispose();
                return ds;
            }
            catch (Exception e)
            {
                sqlConnection.Close();
                Console.WriteLine("数据库查找失败!!!");
                Console.WriteLine(e.Message);
            }
            return null;
        }
        public List<Dictionary<string,object>> Find(string sql)
        {
            try
            {
                Open();
                var commond = new SqlCommand(sql, sqlConnection);
                SqlDataReader sqlDataReader = commond.ExecuteReader();
                commond.Dispose();
                var count = sqlDataReader.FieldCount;
                List<Dictionary<string, object>> ret = new List<Dictionary<string, object>>();
                while (sqlDataReader.Read())
                {
                    var dict = new Dictionary<string, object>();
                    for (int i = 0; i < count; i++)
                    {
                        var name = sqlDataReader.GetName(i);
                        var value = sqlDataReader.GetValue(i);
                        dict.Add(name, value);
                    }
                    ret.Add(dict);
                }
                return ret;
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库查找失败!!!");
                Console.WriteLine(e.Message);
            }
            return null;
        }
        public async Task<List<Dictionary<string, object>>> FindAsync(string sql)
        {
            try
            {
                Open();
                var commond = new SqlCommand(sql, sqlConnection);
                SqlDataReader sqlDataReader = await commond.ExecuteReaderAsync();
                commond.Dispose();
                var count = sqlDataReader.FieldCount;
                List<Dictionary<string, object>> ret = new List<Dictionary<string, object>>();
                while (await sqlDataReader.ReadAsync())
                {
                    var dict = new Dictionary<string, object>();
                    for (int i = 0; i < count; i++)
                    {
                        var name = sqlDataReader.GetName(i);
                        var value = sqlDataReader.GetValue(i);
                        dict.Add(name, value);
                    }
                    ret.Add(dict);
                }
                return ret;
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库查找失败!!!");
                Console.WriteLine(e.Message);
            }
            return null;
        }
        public void Dispose()
        {
            if (sqlConnection != null)
            {
                if(sqlConnection.State != ConnectionState.Closed)
                    sqlConnection.Close();
                sqlConnection = null;
            }
        }
    }


    struct SqlServerMessage
    {
        public long Count { get; set; }
        public string Error { get; set; }
        public override string ToString()
        {
            return $"响应行数:{Count},错误:{Error}";
        }
    }
}