c# sqlserver数据库工具类

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

/// <summary>
/// 数据库工具
/// </summary>
public class DatabaseUtil
{
    /// <summary>
    /// 数据库连接语句
    /// </summary>
    private static string strConn = "连接语句";


    /// <summary>
    /// 执行sql查询语句
    /// </summary>
    /// <param name="strSql"></param>
    /// <param name="strError"></param>
    /// <returns></returns>
    /// <summary>
    /// 执行sql查询语句
    /// </summary>
    /// <param name="strSql"></param>
    /// <param name="strError"></param>
    /// <returns></returns>
    public static DataSet ExecSqlSelect(string strSql)
    {
        SqlConnection con = null;
        try
        {

            con = new SqlConnection(strConn);
            DataSet resDs = new DataSet();

            con.Open();
            SqlDataAdapter sda = new SqlDataAdapter(strSql, con);
            sda.Fill(resDs);

            return resDs;
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (con != null)
            {
                con.Close();
            }
        }
    }



    /// <summary>
    /// 获取dataSet
    /// </summary>
    /// <param name="procName">存储过程名字</param>
    /// <param name="parirsDic">返回</param>
    /// <returns>返回dataset</returns>
    public static DataSet ExecProc(string procName, Dictionary<string, object> parirsDic)
    {
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection(strConn);
            Console.WriteLine("数据库连接成功！");

            //参数集
            Dictionary<string, SqlParameter> argDic = new Dictionary<string, SqlParameter>();//执行存储过程的

            if (parirsDic != null && parirsDic.Count > 0)
            {
                Dictionary<string, object>.Enumerator it = parirsDic.GetEnumerator();
                while (it.MoveNext())
                {
                    SqlParameter tempPar = new SqlParameter(it.Current.Key, it.Current.Value);
                    argDic.Add(it.Current.Key, tempPar);
                }
            }

            SqlCommand cmd = new SqlCommand(procName, conn);

            Dictionary<string, SqlParameter>.Enumerator it2 = argDic.GetEnumerator();
            while (it2.MoveNext())
            {
                cmd.Parameters.Add(it2.Current.Value);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dap = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds;
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (conn != null)
            {
                //关闭数据库连接
                conn.Close();
            }
        }
    }



    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="procName">存储过程名字</param>
    /// <param name="parirsDic">输入参数</param>
    /// <param name="outPutDic">输出参数</param>
    /// <returns></returns>
    public static SqlParameterCollection ExecProc(string procName, Dictionary<string, object> parirsDic, Dictionary<string, object> outPutDic)
    {
        //SqlParameterCollection sqlColl;
        //sqlColl["@OrderNoReturn"].Value
        //返回值这么取
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection(strConn);
            Console.WriteLine("数据库连接成功！");

            //参数集
            Dictionary<string, SqlParameter> argDic = new Dictionary<string, SqlParameter>();//执行存储过程的

            if (parirsDic != null && parirsDic.Count > 0)
            {
                Dictionary<string, object>.Enumerator it = parirsDic.GetEnumerator();
                while (it.MoveNext())
                {
                    SqlParameter tempPar = new SqlParameter(it.Current.Key, it.Current.Value);
                    argDic.Add(it.Current.Key, tempPar);
                }
            }

            //设置错误代码返回值
            int errorId = 1000;
            SqlParameter returnPara = new SqlParameter("@return", errorId);
            returnPara.Direction = ParameterDirection.ReturnValue;
            argDic.Add("@return", returnPara);

            if (outPutDic != null && outPutDic.Count > 0)
            {
                Dictionary<string, object>.Enumerator it = outPutDic.GetEnumerator();
                while (it.MoveNext())
                {
                    SqlParameter tempPar = new SqlParameter(it.Current.Key, it.Current.Value);
                    tempPar.Direction = ParameterDirection.Output;
                    tempPar.Size = 2000;
                    argDic.Add(it.Current.Key, tempPar);
                }
            }

            SqlCommand cmd = new SqlCommand(procName, conn);

            Dictionary<string, SqlParameter>.Enumerator it2 = argDic.GetEnumerator();
            while (it2.MoveNext())
            {
                cmd.Parameters.Add(it2.Current.Value);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            dap.Fill(dt);

            //errorId = Convert.ToInt32(argDic["@return"].Value);
            return cmd.Parameters;
        }
        catch (Exception ex)
        {
            Console.WriteLine("数据库连接失败！" + ex.Message);
            throw;//后端执行异常
        }
        finally
        {
            if (conn != null)
            {
                //关闭数据库连接
                conn.Close();
            }
        }
    }



    /// <summary>
    /// 执行sql语句(增、删、改)
    /// </summary>
    /// <param name="sqlList"></param>
    /// <returns></returns>
    public static int ExecSqlString(List<string> sqlList)
    {
        SqlConnection conn = null;
        SqlTransaction tran = null;
        SqlCommand cmd = null;
        try
        {
            conn = new SqlConnection(strConn);
            conn.Open();
            tran = conn.BeginTransaction();
            cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = tran;
            StringBuilder stringBuilder = new StringBuilder();
            for (int i = 0; i < sqlList.Count; i++)
            {
                stringBuilder.AppendLine(sqlList[i]);
            }
            cmd.CommandText = stringBuilder.ToString();

            int row = cmd.ExecuteNonQuery();
            tran.Commit();
            return row;
        }
        catch (Exception ex)
        {
            if (tran != null)
            {
                tran.Rollback();
            }
            throw;
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }

            if (cmd != null)
            {
                cmd.Dispose();
            }

            if (tran != null)
            {
                tran.Dispose();
            }
        }
    }


    private static string GetSqlString(TableOperateInfo tableOperateInfo)
    {
        string res = string.Empty;
        switch (tableOperateInfo.operateType)
        {
            case OperateType.Insert:
                {
                    res = InsertOperate(tableOperateInfo);
                    return res;
                }
            case OperateType.Update:
                {
                    res = UpdateOperate(tableOperateInfo);
                    return res;
                }
            case OperateType.Delete:
                {

                    return res;
                }
        }
        return res;
    }

    /// <summary>
    /// 插入操作
    /// </summary>
    /// <param name="tableOperateInfo"></param>
    /// <returns></returns>
    private static string InsertOperate(TableOperateInfo tableOperateInfo)
    {
        string res = string.Empty;
        res += "insert into " + tableOperateInfo.tableName;
        List<string> list_name = new List<string>();
        List<object> list_value = new List<object>();
        Dictionary<string, object>.Enumerator it = tableOperateInfo.args.GetEnumerator();
        while (it.MoveNext())
        {
            list_name.Add(it.Current.Key);
            list_value.Add(it.Current.Value);
        }

        //名字
        string str_name = " (";
        for (int i = 0; i < list_name.Count; i++)
        {
            str_name += list_name[i] + ", ";
        }
        str_name = str_name.Substring(0, str_name.Length - 2);
        str_name += ") ";
        str_name += "values";

        string str_value = " (";
        for (int i = 0; i < list_value.Count; i++)
        {
            if (list_value[i].GetType() == typeof(sbyte)
                || list_value[i].GetType() == typeof(byte)
                || list_value[i].GetType() == typeof(short)
                || list_value[i].GetType() == typeof(ushort)
                || list_value[i].GetType() == typeof(int)
                || list_value[i].GetType() == typeof(uint)
                || list_value[i].GetType() == typeof(long)
                || list_value[i].GetType() == typeof(ulong)
                || list_value[i].GetType() == typeof(float)
                || list_value[i].GetType() == typeof(double))
            {
                str_value += list_value[i] + ", ";
            }
            if (list_value[i].GetType() == typeof(string))
            {
                str_value += "\'" + list_value[i] + "\'" + ", ";
            }
        }
        str_value = str_value.Substring(0, str_value.Length - 2);
        str_value += ")";
        res += str_name + str_value;
        return res;
    }



    private static string UpdateOperate(TableOperateInfo tableOperateInfo)
    {
        string res = string.Empty;
        res += "update " + tableOperateInfo.tableName + " set ";
        Dictionary<string, object>.Enumerator it = tableOperateInfo.args.GetEnumerator();
        while (it.MoveNext())
        {
            string key = it.Current.Key;
            object value = it.Current.Value;
            if (value.GetType() == typeof(sbyte)
                || value.GetType() == typeof(byte)
                || value.GetType() == typeof(short)
                || value.GetType() == typeof(ushort)
                || value.GetType() == typeof(int)
                || value.GetType() == typeof(uint)
                || value.GetType() == typeof(long)
                || value.GetType() == typeof(ulong)
                || value.GetType() == typeof(float)
                || value.GetType() == typeof(double))
            {
                res += key + " = " + value + ", ";
            }
            if (value.GetType() == typeof(string))
            {
                res += key + " = " + "\'" + value + "\'" + ", ";
            }

        }
        res = res.Substring(0, res.Length - 2);
        res += tableOperateInfo.whereText;
        return res;
    }



    public static int ExecSqlString_v2(List<TableOperateInfo> tableOperateInfos)
    {
        SqlConnection conn = null;
        SqlTransaction tran = null;
        SqlCommand cmd = null;
        try
        {
            conn = new SqlConnection(strConn);
            conn.Open();
            tran = conn.BeginTransaction();
            cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = tran;

            StringBuilder stringBuilder = new StringBuilder();
            for (int i = 0; i < tableOperateInfos.Count; i++)
            {
                stringBuilder.AppendLine(GetSqlString(tableOperateInfos[i]));
            }
            cmd.CommandText = stringBuilder.ToString();
            int row = cmd.ExecuteNonQuery();
            tran.Commit();
            return row;
        }
        catch (Exception ex)
        {
            if (tran != null)
            {
                tran.Rollback();
            }
            throw;
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }

            if (cmd != null)
            {
                cmd.Dispose();
            }

            if (tran != null)
            {
                tran.Dispose();
            }
        }
    }



    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="procName">存储过程名字</param>
    /// <param name="parirsDic">参数列表</param>
    /// <returns></returns>
    public static DataSet GetData(string procName, Dictionary<string, object> parirsDic)
    {
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection(strConn);
            conn.Open();
            //打开数据库连接
            //conn.Open();
            Console.WriteLine("数据库连接成功！");

            List<SqlParameter> parasList = new List<SqlParameter>();

            if (parirsDic != null && parirsDic.Count > 0)
            {
                Dictionary<string, object>.Enumerator it = parirsDic.GetEnumerator();
                while (it.MoveNext())
                {
                    parasList.Add(new SqlParameter(it.Current.Key, it.Current.Value));
                }
            }


            SqlCommand cmd = new SqlCommand(procName, conn);
            for (int i = 0; i < parasList.Count; i++)
            {
                cmd.Parameters.Add(parasList[i]);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dap = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            dap.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (conn != null)
            {
                //关闭数据库连接
                conn.Close();
            }
        }
    }

    /// <summary>
    /// 表操作类
    /// </summary>
    public class TableOperateInfo
    {
        /// <summary>
        /// 字段名和值
        /// </summary>
        public Dictionary<string, object> args = new Dictionary<string, object>();

        /// <summary>
        /// 表名
        /// </summary>
        public string tableName { get; set; }


        /// <summary>
        /// where条件语句
        /// </summary>
        public string whereText { get; set; }

        /// <summary>
        /// 操作类型
        /// </summary>
        public OperateType operateType = OperateType.Insert;

        public TableOperateInfo(string tableName, OperateType operateType, Dictionary<string, object> args, string whereText)
        {
            this.tableName = tableName;
            this.operateType = operateType;
            this.args = args;
            this.whereText = whereText;
        }
    }

    public enum OperateType
    {
        /// <summary>
        /// 插入
        /// </summary>
        Insert = 0,
        /// <summary>
        /// 更新
        /// </summary>
        Update = 1,

        /// <summary>
        /// 
        /// </summary>
        Delete = 2

    }
}