SqlHelper

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
namespace ProjectManage.DAL
{
 /// <summary>
    /// SQL Server数据库操作的公共类
    /// </summary>
    public class SqlHelper
    {
        //数据库连接的字符串
        public static string ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
  //private static string ConnStr = GetAppConfigValue("ConnStr");

        //读取连接字符串
        //public static string GetAppConfigValue(string key)
        //{
        //    string appPath = AppDomain.CurrentDomain.BaseDirectory.ToString() + "App.config";
        //    XmlDocument configData = new XmlDocument();
        //    configData.Load(appPath);
        //    return configData.SelectSingleNode("/configuration/appSettings/add[@key='" + key + "']").Attributes["value"].Value;
        //}

        public static DataTable RunQuery(CommandType cmdType, string cmdText, SqlParameter[] commandParameters, out string sError)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;               
                da.Fill(ds);
            }
   sError = "";
            if (ds.Tables.Count > 0) return ds.Tables[0];
            else return null;
        }

        /// <summary>
        /// 执行增,删,改的sql语句或者存储过程
        /// </summary>      
        /// <param name="cmdType">命令类型(存储过程,sql语句)</param>
        /// <param name="cmdText">命令文本(存储过程的名称,sql语句)</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>返回值</returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] commandParameters, out string sError)
        {
            int R = 0;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();           
                try
                {
                    PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                    cmd.ExecuteNonQuery();                   
                    sError="";
                    R=(int)cmd.Parameters["Return Value"].Value;
                }catch(Exception ex)
                {
                    sError = ex.Message;
                    R = -1;
                }
            }

            return R;
        }

 

        /// <summary>
        /// 执行查询(存储过程或sql语句)
        /// </summary>      
        /// <param name="cmdType">命令类型(存储过程,sql语句)</param>
        /// <param name="cmdText">命令文本(存储过程的名称,sql语句)</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>返回SqlDataReader的对象(只读只进的数据集)</returns>
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, SqlParameter[] commandParameters, out string sError)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnStr);
           
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                sError = "";
                return rdr;
            }
            catch(Exception ex)
            {
                sError=ex.Message;
                conn.Close();
                return null;
            }
        }

        /// <summary>
        /// 执行查询(存储过程或sql语句)
        /// </summary>      
        /// <param name="cmdType">命令类型(存储过程,sql语句)</param>
        /// <param name="cmdText">命令文本(存储过程的名称,sql语句)</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>返回SqlDataReader的对象(只读只进的数据集)</returns>
        public static SqlDataReader ExecuteReaderPage(CommandType cmdType, string cmdText, SqlParameter[] commandParameters, out string sError)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnStr);
           
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                sError = "";
                return rdr;
            }
            catch (Exception ex)
            {
                sError = ex.Message;
                conn.Close();
                return null;
            }
        }

        /// <summary>
        /// 执行查询(存储过程或sql语句)
        /// </summary>      
        /// <param name="cmdType">命令类型(存储过程,sql语句)</param>
        /// <param name="cmdText">命令文本(存储过程的名称,sql语句)</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>返回查询结果的第一行第一列</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection connection = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 构造SqlCommand对象
        /// </summary>
        /// <param name="cmd">需要修改的SqlCommand对象</param>
        /// <param name="conn">关联的连接</param>       
        /// <param name="cmdType">关联的命令类型</param>
        /// <param name="cmdText">关联的命令文本</param>
        /// <param name="cmdParms">关联的参数数组</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            cmd.CommandTimeout = 600;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm);
            }
            cmd.Parameters.Add(new SqlParameter("Return Value", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
        }

        //关于事务处理
        //开始事务
        public static void BeginTrans(SqlConnection conn, SqlTransaction trans)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            if (trans == null)
            {
                trans = conn.BeginTransaction();//开始事务
            }
        }

        //提交事务
        public static void CommitTrans(SqlConnection conn, SqlTransaction trans)
        {
            if (trans != null)
            {
                trans.Commit();//事务提交
                conn.Close();
            }
        }

        //回滚事务
        public static void RollBackTrans(SqlConnection conn, SqlTransaction trans)
        {
            if (trans != null)
            {
                trans.Rollback();//事务回滚
                conn.Close();
            }
        }

        /// <summary>
        /// 执行增,删,改的sql语句或者存储过程
        /// </summary>      
        /// <param name="cmdType">命令类型(存储过程,sql语句)</param>
        /// <param name="cmdText">命令文本(存储过程的名称,sql语句)</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>成功影响的行数</returns>
        public static int ExecuteNonQueryTrans(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] commandParameters, out string sError)
        {          
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = trans;
            int R = 0;
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                cmd.ExecuteNonQuery();
                sError = "";
                R = (int)cmd.Parameters["Return Value"].Value;
            }
            catch (Exception ex)
            {
                sError = ex.Message;
                R = -1;
            }
            return R;
        }

        #region 参数
        /// <summary>
        /// 创建SqlParameter
        /// </summary>
        /// <param name="sParamName">参数名称</param>
        /// <param name="DbType">参数类型，SqlDbType枚举</param>
        /// <param name="size">参数大小</param>
        /// <param name="d">参数类型</param>
        /// <param name="v">参数值</param>
        /// <returns>返回创建好的SqlParameter</returns>
        public static SqlParameter MakeParam(string sParamName, SqlDbType DbType,
            Int32 size, ParameterDirection d, object v)
        {
            SqlParameter pa;
            if (size > 0)
                pa = new SqlParameter(sParamName, DbType, size);
            else
                pa = new SqlParameter(sParamName, DbType);

            pa.Direction = d;

            if (!(d == ParameterDirection.Output && v == null))
                pa.Value = v;

            return pa;
        }

        /// <summary>
        /// 创建传入参数
        /// </summary>
        /// <param name="sParamName">参数名</param>
        /// <param name="d">参数类型，为SqlDbType枚举</param>
        /// <param name="size">大小</param>
        /// <param name="v">参数值</param>
        /// <returns>返回创建好的输入参数</returns>
        public static SqlParameter MakeInParam(string sParamName, SqlDbType d, int size, object v)
        {
            return MakeParam(sParamName, d, size, ParameterDirection.Input, v);
        }

        /// <summary>
        /// 创建输出参数
        /// </summary>
        /// <param name="sParamName">参数名</param>
        /// <param name="d">参数类型，为SqlDbType枚举成员</param>
        /// <param name="size">参数大小</param>
        /// <returns>返回创建好的输出参数</returns>
        public static SqlParameter MakeOutParam(string sParamName, SqlDbType d, int size)
        {
            return MakeParam(sParamName, d, size, ParameterDirection.Output, null);
        }
        #endregion

    }
}