sqlhelper.cs

教务管理系统 基于.net和sqlserver2000的教务管理系统



using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
namespace Park.Common
{
    /// <summary>
    /// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 
    /// common uses of SqlClient.
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// SQL Server connection string
        /// </summary>
        protected static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;

        /// <summary>
        /// SQL Command
        /// </summary>
        protected static string strCmd;
        private SqlHelper() { }

        /// <summary>
        /// Execute SQL Commands
        /// </summary>
        /// <param name = "strSQL"> string </param>
        /// <return> int </return>
        public static int ExecuteSQLCmd(string strSQL)
        {
            SqlConnection currentConn = new SqlConnection(strConn);
            SqlCommand currentCmd = new SqlCommand(strSQL, currentConn);
            try
            {
                currentConn.Open();
                currentCmd.ExecuteNonQuery();
                return 0;
            }

            // In case of some exception

            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }

            // We have to close the connection at last correctly.

            finally
            {
                currentCmd.Dispose();
                currentConn.Close();
            }
        }
        /// <summary>
        /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            //finally //不能在此关闭，否则，返回的对象将无法使用
            //{
            //	cmd.Dispose();
            //	connection.Close();
            //}	


        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, 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 = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            //finally //不能在此关闭，否则，返回的对象将无法使用
            //{
            //	cmd.Dispose();
            //	connection.Close();
            //}	

        }
        /// <summary>
        /// Excute SQL Commands to reader. Just for query use.
        /// </summary>
        /// <param name="strSQL">string</param>
        /// <returns>int</returns>
        public static int ExecuteSQLCmdEx(string strSQL)
        {
            SqlConnection currentConn = new SqlConnection(strConn);
            SqlCommand currentCmd = new SqlCommand(strSQL, currentConn);
            try
            {
                currentConn.Open();

                // Just for query, so we use SqlDataReader here.

                SqlDataReader currentReader = currentCmd.ExecuteReader();
                if (currentReader.Read())
                {
                    return 0;
                }
                else
                {
                    throw new Exception("Value Unavailable!");
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                currentCmd.Dispose();
                currentConn.Close();

            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static bool ExecuteSQLCmdTrue(string strSQL)
        {
            SqlConnection currentConn = new SqlConnection(strConn);
            SqlCommand currentCmd = new SqlCommand(strSQL, currentConn);
            bool temp = false;
            try
            {
                currentConn.Open();

                // Just for query, so we use SqlDataReader here.

                SqlDataReader currentReader = currentCmd.ExecuteReader();
                while (currentReader.Read())
                {
                    temp = true;
                    temp &= true;
                }

            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                currentCmd.Dispose();
                currentConn.Close();

            }
            return temp;
        }

        /// <summary>
        /// Get DataSet
        /// </summary>
        /// <param name="strSQL">string</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteSQLForDS(string strSQL)
        {
            SqlConnection currentConn = new SqlConnection(strConn);
            try
            {
                currentConn.Open();
                SqlDataAdapter currentDA = new SqlDataAdapter(strSQL, currentConn);
                DataSet ds = new DataSet("ds");
                currentDA.Fill(ds);
                return ds;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                currentConn.Close();
            }
        }

        /// <summary>
        /// Just get a single value
        /// </summary>
        /// <param name="strSQL">string</param>
        /// <returns>int</returns>
        public static int ExecuteSQLForValue(string strSQL)
        {
            SqlConnection currentConn = new SqlConnection(strConn);
            SqlCommand currentCmd = new SqlCommand(strSQL, currentConn);

            try
            {
                currentConn.Open();
                // Only return the first line of the results
                object ret = currentCmd.ExecuteScalar();
                if (Object.Equals(ret, null))
                {
                    throw new Exception("变量值未知");
                }
                else
                {
                    return (int)ret;
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                currentCmd.Dispose();
                currentConn.Close();
            }
        }

        /// <summary>
        /// Return for an object
        /// </summary>
        /// <param name="strSQL">string</param>
        /// <returns>object</returns>
        public static Object ExecuteSQLForValueEx(string strSQL)
        {
            SqlConnection currentConn = new SqlConnection(strConn);
            SqlCommand currentCmd = new SqlCommand(strSQL, currentConn);
            try
            {
                currentConn.Open();
                object ret = currentCmd.ExecuteScalar();
                if (Object.Equals(ret, null))
                {
                    throw new Exception("Value unavailable!");
                }