我们小组用到的基类

不知道以后还用不用更新~~~~
自己先记下来~~~

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;


namespace CuctSoft
{
    /// <summary>
    /// base.--数据库操作通用基类
    /// </summary>
    ///<author>
    /// E-space 2007-4-22
    /// </author> 

    public class SqlDb
    {

        #region "Fields of base calss(数据库连接字符)"

        /// <summary>
        /// connecting to Database
        /// ["personal"]--根据Web.config可更改
        /// </summary>
        public string strConn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

        /// <summary>
        /// SQL command
        /// </summary>
        public string strSQL;

        #endregion


        #region "Functions of base class(数据库操作)"


        /// <summary>
        /// executing SQL commands－执行一段SQL语句
        /// </summary>
        /// <param name="strSQL">string</param>
        /// <returns>return int</returns>

        public void dbExecuteSql(string strSQL)
        {
            SqlConnection myCn = new SqlConnection(strConn);
            SqlCommand myCmd = new SqlCommand(strSQL, myCn);
            try
            {
                myCn.Open();
                myCmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                myCn.Close();
            }
        }


        /// <summary>
        ///executing SQL commands－有执行成功判断
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句,为字符串类型string</param>
        /// <returns>返回执行情况,整形int</returns>
        public int dbExecuteSqlEx(string strSQL)
        {
            SqlConnection myCn = new SqlConnection(strConn);
            SqlCommand myCmd = new SqlCommand(strSQL, myCn);

            try
            {
                myCn.Open();
                SqlDataReader myReader = myCmd.ExecuteReader();
                if (myReader.Read())
                {
                    return 0;
                }
                else
                {
                    throw new Exception("Value Unavailable!");
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                myCn.Close();
            }
        }


        /// <summary>
        /// get dataset－通过输入的SQL语句得到一个数据集
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>(DataSet)</returns>
        public DataSet dbExecuteSql4Ds(string strSQL)
        {
            SqlConnection myCn = new SqlConnection(strConn);
            try
            {
                myCn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(strSQL, myCn);
                DataSet ds = new DataSet("ds");

                sda.Fill(ds);
                return ds;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                myCn.Close();
            }
        }


        /// <summary>
        /// get single value-返回SQL语句数据集中的第一行第一列整型
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>(int)</returns>
        public int dbExecuteSql4Value(string strSQL)
        {
            SqlConnection myCn = new SqlConnection(strConn);
            SqlCommand myCmd = new SqlCommand(strSQL, myCn);
            try
            {
                myCn.Open();
                object r = myCmd.ExecuteScalar();
                if (Object.Equals(r, null))
                {
                    throw new Exception("value unavailable！");
                }
                else
                {
                    try { int a = (int)r; }
                    catch { return 0; }
                    return (int)r;

                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                myCn.Close();
            }
        }
        /// <summary>
        /// get single value-返回SQL语句数据集中的第一行第一列整型
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>(int)</returns>
        public string dbExecuteSql4Values(string strSQL)
        {
            SqlConnection myCn = new SqlConnection(strConn);
            SqlCommand myCmd = new SqlCommand(strSQL, myCn);
            try
            {
                myCn.Open();
                object r = myCmd.ExecuteScalar();
                if (Object.Equals(r, null))
                {
                    return "";
                }
                else
                {
                    return (string)r;
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                myCn.Close();
            }
        }


        public object dbExecSql4Values(string strSQL)
        {
            SqlConnection myCn = new SqlConnection(strConn);
            SqlCommand myCmd = new SqlCommand(strSQL, myCn);
            try
            {
                myCn.Open();
                object r = myCmd.ExecuteScalar();
                if (Object.Equals(r, null))
                {
                    return "";
                }
                else
                {
                    return r;
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                myCn.Close();
            }
        }

        /// <summary>
        /// get object－返回SQL语句对应的数据集的对象
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>(object)</returns>
        public object dbExecuteSql4ValueEx(string strSQL)
        {
            SqlConnection myCn = new SqlConnection(strConn);
            SqlCommand myCmd = new SqlCommand(strSQL, myCn);
            try
            {
                myCn.Open();
                object r = myCmd.ExecuteScalar();
                if (Object.Equals(r, null))
                {
                    throw new Exception("object unavailable!");
                }
                else
                {
                    return r;
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                myCn.Close();
            }
        }


        /// <summary>
        /// execute multipul SQL commands －将指定数组中的N条SQL语句同步执行。
        /// </summary>
        /// <author>
        /// e-space 2007-4-22
        /// </author>
        /// <param name="strSQLs">string</param>
        /// <returns>int</returns>
        public int dbExecuteSqls(string[] strSQLs)
        {
            SqlConnection myCn = new SqlConnection(strConn);
            SqlCommand myCmd = new SqlCommand();
            int j = strSQLs.Length;

            try
            {
                myCn.Open();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            SqlTransaction myTrans = myCn.BeginTransaction();

            try
            {
                myCmd.Connection = myCn;
                myCmd.Transaction = myTrans;

                foreach (string str in strSQLs)
                {
                    myCmd.CommandText = str;
                    myCmd.ExecuteNonQuery();
                }
                myTrans.Commit();
                return 0;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                myTrans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                myCn.Close();
            }
        }


        /// <summary>
        /// 提供一个Select的数据集，类型为SqlDataReader，不支持自动分页。
        /// </summary>
        /// <author>
        /// e-space  2007-4-22
        /// </author>
        /// <param name="selectString">检索字符串</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader dbODSGetSDR(string selectString)
        {
            SqlConnection con = new SqlConnection(strConn);

            SqlCommand cmd = new SqlCommand(selectString, con);
            con.Open();
            SqlDataReader dtr =
              cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dtr;
        }


        /// <summary>
        /// 为objectDataSource提供一个Select的数据集，类型为DataTable，支持自动分页。
        /// </summary>
        /// <author>
        ///E-space 2007-4-22
        /// </author>
        /// <param name="sqlCommand">检索字符串</param>
        /// <param name="tableName">DataTable表名</param>
        /// <returns>DataTable</returns>
        //[DataObjectMethod(DataObjectMethodType.Select, true)]
        public DataTable dbODSGetDataTable(string sqlCommand, string tableName)
        {


            SqlConnection conn = new SqlConnection(strConn);
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand, conn);

            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                //da.Fill(ds, startRecord, maxRecords, "Employees");
                da.Fill(ds, tableName);
            }
            catch (SqlException e)
            {
                string a = e.Errors.ToString();
            }
            finally
            {
                conn.Close();
            }

            if (ds.Tables[tableName] != null)
                return ds.Tables[tableName];

            return null;
        }


        public DataTable dbODSGetDataTable(string sqlCommand)
        {


            SqlConnection conn = new SqlConnection(strConn);
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand, conn);

            DataTable dt = new DataTable();

            try
            {
                conn.Open();
                //da.Fill(ds, startRecord, maxRecords, "Employees");
                da.Fill(dt);
            }
            catch (SqlException e)
            {
                string a = e.Errors.ToString();
            }
            finally
            {
                conn.Close();
            }

            if (dt != null)
                return dt;

            return null;
        }



        /// <summary>
        /// 执行存储过程，返回影响的行数        
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }


        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
        #endregion


        #region 
        /// <summary>
        /// 调用createCmd创建SqlDataReadere方法
        /// </summary>
        /// <param name="storeName">存储过程名</param>
        /// <param name="paras">参数数组</param>

        public void ExecStore(string storeName, params SqlParameter[] paras)
        {
           createCmd(storeName, paras).ExecuteNonQuery();
        }
        #endregion


        #region 定义私有的SqlCommand方法
        /// <summary>
        /// 定义私有的SqlCommand方法
        /// </summary>
        /// <param name="storeName"></param>
        /// <param name="paras"></param>
        /// <returns>返回SqlCommand类型的strCmd</returns>
        private  SqlCommand createCmd(string storeName, params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(strConn);
            con.Open();
            try
            {
                SqlCommand strCmd = new SqlCommand(storeName, con);
                strCmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter para in paras)
                {
                    strCmd.Parameters.Add(para);
                }
                strCmd.Dispose();
                return strCmd;
            }
            catch (SqlException error)
            {
                throw error;
            }
        }
        #endregion




    }
}