using System.Data;

using System.Data.SqlClient;

namespace DataService

{

   public class SQLHelper

    {

        public static readonly string connString = "Server=127.0.0.1;Database=WinFormDemo;uid=sa;pwd=123";

        #region ExecuteNonQuery

        /// <summary>

        /// 不带参数的ExcuteNonQuery

        /// </summary>

        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText)

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandTimeout = ;

            using (SqlConnection conn = new SqlConnection(connString))

            {

                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, (SqlParameter[])null);

                int val = cmd.ExecuteNonQuery();

                return val;

            }

        }

        /// <summary>

        /// 带参数的ExcuteNonQuery

        /// </summary>

        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandTimeout = ;

            using (SqlConnection conn = new SqlConnection(connString))

            {

                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms);

                int val = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

                return val;

            }

        }

        #endregion ExecuteNonQuery

        #region ExecuteScalar

        /// <summary>

        /// 不带参数的ExecuteScalar

        /// </summary>

        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText)

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandTimeout = ;

            using (SqlConnection conn = new SqlConnection(connString))

            {

                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, (SqlParameter[])null);

                object val = cmd.ExecuteScalar();

                return val;

            }

        }

        /// <summary>

        /// 带参数的ExecuteScalar

        /// </summary>

        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandTimeout = ;

            using (SqlConnection conn = new SqlConnection(connString))

            {

                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms);

                object val = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

                return val;

            }

        }

        #endregion ExecuteScalar

        #region ExecuteReader

        /// <summary>

        /// 不带参数的ExecuteReader

        /// </summary>

        public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText)

        {

            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(connString);

            cmd.CommandTimeout = ;

            try

            {

                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, (SqlParameter[])null);

                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return rdr;

            }

            catch

            {

                conn.Close();

                throw;

            }

        }

        /// <summary>

        /// 带参数的ExecuteReader

        /// </summary>

        public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)

        {

            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(connString);

            cmd.CommandTimeout = ;

            try

            {

                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms);

                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return rdr;

            }

            catch

            {

                conn.Close();

                throw;

            }

        }

        #endregion ExecuteReader

        #region ExecuteDataSet

        /// <summary>

        /// 不带参数的ExecuteDataSet,可以分页

        /// </summary>

        public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText)

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandTimeout = ;

            using (SqlConnection conn = new SqlConnection(connString))

            {

                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, (SqlParameter[])null);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                DataSet ds = new DataSet();

                da.Fill(ds, "dtable");

                cmd.Parameters.Clear();

                return ds;

            }

        }

        /// <summary>

        /// 带参数的ExecuteDataSet,可以分页

        /// </summary>

        public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandTimeout = ;

            using (SqlConnection conn = new SqlConnection(connString))

            {

                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                DataSet ds = new DataSet();

                da.Fill(ds, "dtable");

                cmd.Parameters.Clear();

                return ds;

            }

        }

        #endregion ExecuteDataSet

        #region PrepareCommand

        /// <summary>

        /// 初始化工作

        /// </summary>

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)

        {

            if (conn.State != ConnectionState.Open)

                conn.Open();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;

            if (trans != null)

                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)

            {

                foreach (SqlParameter parm in cmdParms)

                    cmd.Parameters.Add(parm);

            }

        }

        #endregion PrepareCommand

    }

}