using System;
using System.Data;
using System.Data.SqlClient;namespace lsb.DBUtility
{
    /// <summary>
    /// SQL Server 数据库操作类
    /// </summary>
    public class SqlHelper
    {
        private static string connectionString = "Data Source=.;Initial Catalog=FBMS;Integrated Security=True";/// <summary>
        /// 执行查询, 返回单个值
        /// </summary>
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        return cmd.ExecuteScalar();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }/// <summary>
        /// 执行带参数查询, 返回单个值
        /// </summary>
        public static object ExecuteScalar(string sql, SqlParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, conn, null, sql, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return obj;
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }/// <summary>
        /// 执行查询, 返回多个值
        /// </summary>
        public static SqlDataReader ExecuteReader(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        return cmd.ExecuteReader();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }/// <summary>
        /// 执行带参数查询, 返回多个值
        /// </summary>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, conn, null, sql, cmdParms);
                        SqlDataReader sdr = cmd.ExecuteReader();
                        cmd.Parameters.Clear();
                        return sdr;
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }/// <summary>
        /// 执行非查询, 返回影响行数
        /// </summary>
        public static int ExceuteNonQuery(string sql, params SqlParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, conn, null, sql, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }/// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if ((conn.State == ConnectionState.Broken) || (conn.State == ConnectionState.Closed))
            {
                conn.Open();
            }

            cmd.Connection