lserp_cs_6.0/其他程序/AFKAbutment - 副本/SqlHelper.cs

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

namespace AFKAbutment
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public static class SqlHelper
    {
        /// <summary>
        /// 数据连接
        /// </summary>
        public static SqlConnection _connection;
        /// <summary>
        /// 请求超时时间
        /// </summary>
        public static int CommandTimeout;

        #region ExecuteAdapter

        /// <summary>
        /// <para>说明：获取适配器</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="commandParameters">The command parameters.</param>
        /// <returns>SqlDataAdapter.</returns>
        public static SqlDataAdapter ExecuteAdapter(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlDataAdapter adapter;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, _connection, null, cmdType, cmdText, commandParameters);
                adapter = new SqlDataAdapter(cmd);
            }
            catch
            {
                throw;
            }
            return adapter;
        }


        /// <summary>
        /// <para>说明：获取适配器</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <returns>SqlDataAdapter.</returns>
        public static SqlDataAdapter ExecuteAdapter(CommandType cmdType, string cmdText)
        {
            SqlDataAdapter adapter;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, _connection, null, cmdType, cmdText, null);
                adapter = new SqlDataAdapter(cmd);
            }
            catch
            {
                throw;
            }
            return adapter;
        }
        #endregion

        #region ExecuteDataTable
        /// <summary>
        /// <para>说明：执行sql返回DataTable</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdText">The command text.</param>
        /// <returns>DataTable.</returns>
        public static DataTable ExecuteDataTable(string cmdText)
        {
            return ExecuteDataSet(cmdText).Tables[0];
        }
        /// <summary>
        /// <para>说明：执行sql返回DataTable</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdText">The command text.</param>
        /// <returns>DataTable.</returns>
        public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] commandParameters)
        {
            return ExecuteDataSet(CommandType.Text, cmdText, "temp", commandParameters).Tables[0];
        }

        /// <summary>
        /// <para>说明：执行sql返回指定字段值</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-08-15 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="cmdText">The command text.</param>
        /// <returns>System.Object.</returns>
        public static object ExecuteObject(string fieldName, string cmdText)
        {
            object result = null;
            try
            {
                result = ExecuteDataSet(cmdText).Tables[0].Rows[0][fieldName];
            }
            catch (Exception)
            {

            }
            return result;
        }
        /// <summary>
        /// <para>说明：执行sql返回指定字段值</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-11-27 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="commandParameters">The command parameters.</param>
        /// <returns>System.Object.</returns>
        public static object ExecuteObject(string fieldName, string cmdText, params SqlParameter[] commandParameters)
        {
            object result = null;
            try
            {
                result = ExecuteDataSet(CommandType.Text, cmdText, "temp", commandParameters).Tables[0].Rows[0][fieldName];
            }
            catch (Exception)
            {

            }
            return result;
        }
        /// <summary>
        /// <para>说明：执行sql返回指定字段值</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-08-15 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="cmdText">The command text.</param>
        /// <returns>System.Object.</returns>
        public static string ExecuteString(string fieldName, string cmdText)
        {
            object obj = ExecuteObject(fieldName, cmdText);
            return obj == null ? "" : obj.ToString();
        }
        /// <summary>
        /// <para>说明：执行sql返回指定字段值</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-11-27 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="commandParameters">The command parameters.</param>
        /// <returns>System.String.</returns>
        public static string ExecuteString(string fieldName, string cmdText, params SqlParameter[] commandParameters)
        {
            object obj = ExecuteObject(fieldName, cmdText, commandParameters);
            return obj == null ? "" : obj.ToString();
        }
        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// <para>说明：查询结果集</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="tabName">Name of the tab.</param>
        /// <param name="commandParameters">The command parameters.</param>
        /// <returns>DataSet.</returns>
        public static DataSet ExecuteDataSet(int cmdType, string cmdText, string tabName, params SqlParameter[] commandParameters)
        {
            CommandType _cmdType = CommandType.Text;
            switch (cmdType)
            {
                case 4:
                    _cmdType = CommandType.StoredProcedure;
                    break;
                case 512:
                    _cmdType = CommandType.TableDirect;
                    break;
            }
            return ExecuteDataSet(_cmdType, cmdText, tabName, commandParameters);
        }
        /// <summary>
        /// <para>说明：查询结果集</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">语句类型存储过程或者sql语句</param>
        /// <param name="cmdText">执行内容</param>
        /// <param name="tabName">表名</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>DataSet.</returns>
        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, string tabName, params SqlParameter[] commandParameters)
        {
            DataSet set2;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, _connection, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, tabName);
                //adapter.FillSchema(dataSet, SchemaType.Mapped, tabName);
                set2 = dataSet;
            }
            catch (Exception e)
            {
                if (e.Message.StartsWith("在从服务器接收结果时发生传输级错误") ||
                    e.Message.StartsWith("在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误") ||
                    e.Message.StartsWith("在向服务器发送请求时发生传输级错误"))
                {
                    throw new Exception("无法连接服务器,请检查网络连接.");
                }
                else
                {
                    throw;
                }
            }
            finally
            {
                cmd.Dispose();
            }

            return set2;
        }
        /// <summary>
        /// <para>说明：查询结果集</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">语句类型存储过程或者sql语句</param>
        /// <param name="cmdText">执行内容</param>
        /// <param name="tabName">表名</param>
        /// <returns>DataSet.</returns>
        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, string tabName)
        {
            return ExecuteDataSet(cmdType, cmdText, tabName, null);
        }
        /// <summary>
        /// <para>说明：查询结果集</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdText">执行内容</param>
        /// <returns>DataSet.</returns>
        public static DataSet ExecuteDataSet(string cmdText)
        {
            return ExecuteDataSet(CommandType.Text, cmdText, "temp");
        }
        /// <summary>
        /// <para>说明：查询结果集</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="procedureName">Name of the procedure.</param>
        /// <param name="tabName">表名</param>
        /// <param name="parameterNames">The parameter names.</param>
        /// <param name="parameterValues">The parameter values.</param>
        /// <returns>DataSet.</returns>
        public static DataSet ExecuteDataSet(string procedureName, string tabName, string[] parameterNames, object[] parameterValues)
        {
            DataSet set2;
            try
            {
                SqlCommand cmd = new SqlCommand(procedureName, _connection);
                cmd.CommandType = CommandType.StoredProcedure;

                if (parameterNames != null && parameterValues != null)
                {
                    for (int i = 0; i < parameterNames.Length && i < parameterValues.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
                    }
                }
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, tabName);
                set2 = dataSet;
            }
            catch
            {
                throw;
            }
            return set2;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// <para>说明：执行sql语句或者存储过程</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="commandParameters">The command parameters.</param>
        /// <returns>System.Int32.</returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            int num = 0;
            try
            {
                PrepareCommand(cmd, _connection, null, cmdType, cmdText, commandParameters);
                num = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            catch (Exception e)
            {
                if (e.Message.StartsWith("在从服务器接收结果时发生传输级错误") ||
                    e.Message.StartsWith("在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误") ||
                    e.Message.StartsWith("在向服务器发送请求时发生传输级错误"))
                {
                    throw new Exception("无法连接服务器,请检查网络连接.");
                }
                else
                {
                    throw;
                }
            }
            finally
            {
                cmd.Dispose();
            }
            return num;
        }
        /// <summary>
        /// <para>说明：执行sql语句或者存储过程</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <returns>System.Int32.</returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// <para>说明：执行sql语句或者存储过程</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdText">The command text.</param>
        /// <returns>System.Int32.</returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText);
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// <para>说明：执行sql语句或者存储过程并返回结果集（只读、只进）</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="commandParameters">The command parameters.</param>
        /// <returns>SqlDataReader.</returns>
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlDataReader reader2;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, _connection, null, cmdType, cmdText, commandParameters);
                SqlDataReader reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                reader2 = reader;
            }
            catch
            {
                throw;
            }
            return reader2;
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// <para>说明：执行sql语句或者存储过程并返回结果集第一行第一列</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="Connectionection">The connectionection.</param>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="commandParameters">The command parameters.</param>
        /// <returns>System.Object.</returns>
        public static object ExecuteScalar(SqlConnection Connectionection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, Connectionection, null, cmdType, cmdText, commandParameters);
            object obj2 = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return obj2;
        }
        /// <summary>
        /// <para>说明：执行sql语句或者存储过程并返回结果集第一行第一列</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="commandParameters">The command parameters.</param>
        /// <returns>System.Object.</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            {
                PrepareCommand(cmd, _connection, null, cmdType, cmdText, commandParameters);
                object obj2 = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj2;
            }
        }
        /// <summary>
        /// <para>说明：执行sql语句或者存储过程并返回结果集第一行第一列</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <returns>System.Object.</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText)
        {
            SqlCommand cmd = new SqlCommand();
            {
                PrepareCommand(cmd, _connection, null, cmdType, cmdText, null);
                object obj2 = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj2;
            }
        }
        /// <summary>
        /// <para>说明：执行sql语句或者存储过程并返回结果集第一行第一列</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-08-21 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmdText">The command text.</param>
        /// <returns>System.Object.</returns>
        public static object ExecuteScalar(string cmdText)
        {
            SqlCommand cmd = new SqlCommand();
            {
                PrepareCommand(cmd, _connection, null, CommandType.Text, cmdText, null);
                object obj2 = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj2;
            }
        }
        #endregion

        #region PrepareCommand
        /// <summary>
        /// <para>说明： sql参数处理</para>
        /// <para>创建人：龚宇超</para>
        /// <para>创建日期：2017-07-24 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="cmd">The command.</param>
        /// <param name="Connection">The connection.</param>
        /// <param name="trans">The trans.</param>
        /// <param name="cmdType">Type of the command.</param>
        /// <param name="cmdText">The command text.</param>
        /// <param name="cmdParms">The command parms.</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection Connection, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            cmd.Connection = Connection;
            cmd.CommandTimeout = CommandTimeout;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        #endregion
    }
}