数据库访问类DBHelp

来源：互联网发布：python html 提取文本编辑：程序博客网时间：2024/05/17 19:57

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

namespace DataAccessLayer
{
    public static class DBHelper
    {
        private static readonly string ConnectionStr = ConfigurationManager.ConnectionStrings["MyLocalSqlServer"].ConnectionString;

        //创建Command对象
        private static SqlCommand CreateCommand(CommandType CommType, string CommText, SqlParameter[] ParArray)
        {
            SqlCommand temp_comm = new SqlCommand();
            temp_comm.CommandType = CommType;
            temp_comm.CommandText = CommText;

            if (ParArray != null)
            {
                foreach (SqlParameter temp_par in ParArray)
                {
                    temp_comm.Parameters.Add(temp_par);
                }
            }
            return temp_comm;
        }

        //返回DataReader类型的数据
        public static SqlDataReader DataReaderRun(CommandType CommType, string CommText, SqlParameter[] ParArray, int CommandTime)
        {
            SqlConnection temp_con = new SqlConnection(ConnectionStr);
            SqlDataReader temp_dr = null;

            SqlCommand temp_command = CreateCommand(CommType, CommText, ParArray);
            if (CommandTime > -1)
            {
                temp_command.CommandTimeout = CommandTime;
            }
            try
            {
                temp_command.Connection = temp_con;
                temp_con.Open();

                temp_dr = temp_command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //写日志
                SystemLogEntity temp_LogEntity = new SystemLogEntity();
                temp_LogEntity.LogTypeID = (int)SystemLogType.数据库执行错误;
                temp_LogEntity.LogContent = "sql语句执行错误，错误信息：" + ex + "；sql语句为：" + CommText;
                if (ParArray != null && ParArray.Length > 0)
                {
                    temp_LogEntity.LogContent = temp_LogEntity.LogContent + "。参数为：";
                    for (int i = 0; i < ParArray.Length; i++)
                    {
                        temp_LogEntity.LogContent = temp_LogEntity.LogContent + ParArray[i].ParameterName + "：" + ParArray[i].Value + "；";
                    }
                }
                temp_LogEntity.LogObjName = "sql语句执行错误";
                temp_LogEntity.LogObjType = "sql语句执行错误";
                temp_LogEntity.LogObjID = "DBHelp.cs";
                temp_LogEntity.LogOpType = "";
                temp_LogEntity.LogResult = 1;
                temp_LogEntity.LogUser = "";
                temp_LogEntity.LogOpTime = DateTime.Now;
                temp_LogEntity.LogUserIP = "";

SystemLog temp_log = new SystemLog();
temp_log.SystemLogInsert(temp_LogEntity);

temp_con.Close();

throw new Exception("Log：" + temp_LogEntity.LogContent);
}

return temp_dr;
}

        public static SqlDataReader DataReaderRun(CommandType CommType, string CommText, SqlParameter[] ParArray)
        {
            return DataReaderRun(CommType, CommText, ParArray, -1);
        }

        //返回DataTable类型的数据
        public static DataTable DataTableRun(CommandType CommType, string CommText, SqlParameter[] ParArray, int CommandTime)
        {
            SqlConnection temp_con = new SqlConnection(ConnectionStr);
            SqlCommand temp_command = CreateCommand(CommType, CommText, ParArray);
            temp_command.Connection = temp_con;
            if (CommandTime > -1)
            {
                temp_command.CommandTimeout = CommandTime;
            }
            SqlDataAdapter temp_ada;
            DataSet temp_set = new DataSet();
            temp_ada = new SqlDataAdapter();
            temp_ada.SelectCommand = temp_command;

            try
            {
                temp_ada.Fill(temp_set, "ResultTable");
            }
            catch (Exception ex)
            {
                //写日志
                SystemLogEntity temp_LogEntity = new SystemLogEntity();
                temp_LogEntity.LogTypeID = (int)SystemLogType.数据库执行错误;
                temp_LogEntity.LogContent = "sql语句执行错误，错误信息：" + ex + "；sql语句为：" + CommText;
                if (ParArray != null && ParArray.Length > 0)
                {
                    temp_LogEntity.LogContent = temp_LogEntity.LogContent + "。参数为：";
                    for (int i = 0; i < ParArray.Length; i++)
                    {
                        temp_LogEntity.LogContent = temp_LogEntity.LogContent + ParArray[i].ParameterName + "：" + ParArray[i].Value + "；";
                    }
                }
                temp_LogEntity.LogObjName = "sql语句执行错误";
                temp_LogEntity.LogObjType = "sql语句执行错误";
                temp_LogEntity.LogObjID = "DBHelp.cs";
                temp_LogEntity.LogOpType = "";
                temp_LogEntity.LogResult = 1;
                temp_LogEntity.LogUser = "";
                temp_LogEntity.LogOpTime = DateTime.Now;
                temp_LogEntity.LogUserIP = "";

SystemLog temp_log = new SystemLog();
temp_log.SystemLogInsert(temp_LogEntity);

temp_con.Close();

throw new Exception("Log：" + temp_LogEntity.LogContent);
}

temp_command.Parameters.Clear();

return temp_set.Tables["ResultTable"];
}

        public static DataTable DataTableRun(CommandType CommType, string CommText, SqlParameter[] ParArray)
        {
            return DataTableRun(CommType, CommText, ParArray, -1);
        }

        //运行修改指令
        public static int CommandRun(CommandType CommType, string CommText, SqlParameter[] ParArray, int CommandTime)
        {
            SqlConnection temp_con = new SqlConnection(ConnectionStr);
            SqlCommand temp_com = CreateCommand(CommType, CommText, ParArray);
            temp_com.Connection = temp_con;
            if (CommandTime > -1)
            {
                temp_com.CommandTimeout = CommandTime;
            }
            int Result_i = 0;

            try
            {
                temp_con.Open();
                Result_i = temp_com.ExecuteNonQuery();
                temp_con.Close();
            }
            catch (Exception ex)
            {
                //写日志
                SystemLogEntity temp_LogEntity = new SystemLogEntity();
                temp_LogEntity.LogTypeID = (int)SystemLogType.数据库执行错误;
                temp_LogEntity.LogContent = "sql语句执行错误，错误信息：" + ex + "；sql语句为：" + CommText;
                if (ParArray != null && ParArray.Length > 0)
                {
                    temp_LogEntity.LogContent = temp_LogEntity.LogContent + "。参数为：";
                    for (int i = 0; i < ParArray.Length; i++)
                    {
                        temp_LogEntity.LogContent = temp_LogEntity.LogContent + ParArray[i].ParameterName + "：" + ParArray[i].Value + "；";
                    }
                }
                temp_LogEntity.LogObjName = "sql语句执行错误";
                temp_LogEntity.LogObjType = "sql语句执行错误";
                temp_LogEntity.LogObjID = "DBHelp.cs";
                temp_LogEntity.LogOpType = "";
                temp_LogEntity.LogResult = 1;
                temp_LogEntity.LogUser = "";
                temp_LogEntity.LogOpTime = DateTime.Now;
                temp_LogEntity.LogUserIP = "";

SystemLog temp_log = new SystemLog();
temp_log.SystemLogInsert(temp_LogEntity);

temp_con.Close();

throw new Exception("Log：" + temp_LogEntity.LogContent);
}

temp_com.Parameters.Clear();

return Result_i;
}

public static int CommandRun(CommandType CommType, string CommText, SqlParameter[] ParArray)
{

return CommandRun(CommType, CommText, ParArray, -1);
}

        //返回第一行第一列
        public static object ScalarRun(CommandType CommType, string CommText, SqlParameter[] ParArray, int CommandTime)
        {
            object ResultObj = null;
            SqlConnection temp_con = new SqlConnection(ConnectionStr);
            SqlCommand temp_com = CreateCommand(CommType, CommText, ParArray);
            temp_com.Connection = temp_con;
            if (CommandTime > -1)
            {
                temp_com.CommandTimeout = CommandTime;
            }
            try
            {
                temp_con.Open();
                ResultObj = temp_com.ExecuteScalar();
                temp_con.Close();
            }
            catch (Exception ex)
            {
                //写日志
                SystemLogEntity temp_LogEntity = new SystemLogEntity();
                temp_LogEntity.LogTypeID = (int)SystemLogType.数据库执行错误;
                temp_LogEntity.LogContent = "sql语句执行错误，错误信息：" + ex + "；sql语句为：" + CommText;
                if (ParArray != null && ParArray.Length > 0)
                {
                    temp_LogEntity.LogContent = temp_LogEntity.LogContent + "。参数为：";
                    for (int i = 0; i < ParArray.Length; i++)
                    {
                        temp_LogEntity.LogContent = temp_LogEntity.LogContent + ParArray[i].ParameterName + "：" + ParArray[i].Value + "；";
                    }
                }
                temp_LogEntity.LogObjName = "sql语句执行错误";
                temp_LogEntity.LogObjType = "sql语句执行错误";
                temp_LogEntity.LogObjID = "DBHelp.cs";
                temp_LogEntity.LogOpType = "";
                temp_LogEntity.LogResult = 1;
                temp_LogEntity.LogUser = "";
                temp_LogEntity.LogOpTime = DateTime.Now;
                temp_LogEntity.LogUserIP = "";

SystemLog temp_log = new SystemLog();
temp_log.SystemLogInsert(temp_LogEntity);

temp_con.Close();

throw new Exception("Log：" + temp_LogEntity.LogContent);
}

temp_com.Parameters.Clear();

return ResultObj;
}

        public static object ScalarRun(CommandType CommType, string CommText, SqlParameter[] ParArray)
        {
            return ScalarRun(CommType, CommText, ParArray, -1);
        }

        //专门为日志入库编写，防止出现死循环。
        public static int CommandRunSysLog(CommandType CommType, string CommText, SqlParameter[] ParArray)
        {
            int Result_i = 0;
            SqlConnection temp_con = new SqlConnection(ConnectionStr);
            SqlCommand temp_com = CreateCommand(CommType, CommText, ParArray);
            temp_com.Connection = temp_con;

            temp_con.Open();
            Result_i = temp_com.ExecuteNonQuery();
            temp_con.Close();

            return Result_i;
        }
    }
}