菜鸟学DotNet的处女作,一个简单的MS-SQL数据操作类

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

namespace AnMeng.Web
{
    /**//// <summary>
    /// 程序说明:SQL数据库操作基类。
    /// </summary>
    public class DBHelper
    {
        Fields#region Fields
        /**//// <summary>
        /// SQL连接字符串
        /// </summary>
        public static string SQLConnectionString = string.Empty;
        #endregion

        Constructor#region Constructor
        public DBHelper()
        {            
        }
        #endregion

        Methods#region Methods
        /**//// <summary>
        /// 执行SELECT语句.
        /// </summary>
        /// <param name="select">SQL里的SELECT语句</param>
        /// <returns>返回所执行SELECT语句的数据表.</returns>
        public static DataTable DoSelect(string select)
        {
            DataTable dtRtn = new DataTable();
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            try
            {
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter(select,cn);
                da.Fill(dtRtn);
                return dtRtn;
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();
            }
        }
        
        /**//// <summary>
        /// 根据SELECT语句,返回指定行的DataRow对象.
        /// </summary>
        /// <param name="select">SQL里的SELECT语句</param>
        /// <param name="row">指定的行号</param>
        /// <returns>DataRow对象</returns>
        public static DataRow DoSelect(string select,int row)
        {
            DataTable dtRtn = new DataTable();
            DataRow drRtn = null;
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            try
            {
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter(select,cn);
                da.Fill(dtRtn);
                if (dtRtn != null)
                {
                    DataRow[] rows = dtRtn.Select();
                    if (rows != null)
                    {
                        drRtn = rows[row];
                    }
                }

                return drRtn;
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();
            }
        }
        /**//// <summary>
        /// 根据传入的表名与字段名,返回包含该字段所有记录的ArrayList对象.
        /// </summary>
        /// <param name="tableName">传入的表名</param>
        /// <param name="fieldName">传入的字段名</param>
        /// <param name="where">传入的条件语句</param>
        /// <returns>返回ArrayList对象</returns>
        public static ArrayList GetFieldValue(string tableName,string fieldName,string where)
        {
            ArrayList alRtn = new ArrayList();
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);

            string select = "SELECT " + fieldName + " FROM " + tableName;
            
            if (where.Length > 0)
            {
                select += " WHERE " + where;
            }

            try
            {
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter(select,cn);
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i=0;i<dt.Rows.Count;i++)
                    {
                        alRtn.Add(dt.Rows[i][fieldName]);
                    }
                }
                return alRtn;
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();
            }
        }
        /**//// <summary>
        /// 执行UPDATE语句.
        /// </summary>
        /// <param name="update">传入的UPDATE语句.</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        public static int DoUpdate(string update)
        {
            int rtn = 1;
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            SqlCommand cmd = new SqlCommand(update,cn);
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                rtn = 0;
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();                
            }
            return rtn;
        }
        /**//// <summary>
        /// 执行UPDATE的存储过程.
        /// </summary>
        /// <param name="storedProcedureName">存储过程名+Update</param>
        /// <param name="parameter">传入的参数值</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        public static int DoUpdate(string storedProcedureName,SqlParameter[] parameter)
        {
            return DBHelper.DoStoredProcedure(storedProcedureName + "Update",parameter);
        }
        /**//// <summary>
        /// 执行Insert语句.
        /// </summary>
        /// <param name="insert">传入的Insert语句</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        public static int DoInsert(string insert)
        {
            int rtn = 1;
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            SqlCommand cmd = new SqlCommand(insert,cn);
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                rtn = 0;
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();
            }
            return rtn;
        }
        /**//// <summary>
        /// 执行Insert的存储过程.
        /// </summary>
        /// <param name="storedProcedureName">存储过程名+Insert</param>
        /// <param name="parameter">传入的参数值</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        public static int DoInsert(string storedProcedureName,SqlParameter[] parameter)
        {
            return DBHelper.DoStoredProcedure(storedProcedureName + "Insert",parameter);
        }
        /**//// <summary>
        /// 批量执行Insert语句.
        /// </summary>
        /// <param name="insert">传入的一组Insert语句数组</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        public static int DoInsert(string[] insert)
        {
            int rtn = 1;
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            SqlCommand cmd;
            try
            {
                cn.Open();
                for(int i=0;i<insert.Length;i++)
                {
                    if (insert[i] != string.Empty)
                    {
                        cmd = new SqlCommand(insert[i],cn);
                        cmd.ExecuteNonQuery();
                    }
                }
                rtn = 0;
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();
            }
            return rtn;
        }
        /**//// <summary>
        /// 执行Delete语句.
        /// </summary>
        /// <param name="delete">传入的Delete语句</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        public static int DoDelete(string delete)
        {
            int rtn = 1;
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            SqlCommand cmd = new SqlCommand(delete,cn);
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                rtn = 0;
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();
            }
            return rtn;
        }
        /**//// <summary>
        /// 执行Delete的存储过程.
        /// </summary>
        /// <param name="storedProcedureName">存储过程名+Delete</param>
        /// <param name="parameter">传入的参数值</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        public static int DoDelete(string storedProcedureName,SqlParameter[] parameter)
        {
            return DBHelper.DoStoredProcedure(storedProcedureName + "Delete",parameter);
        }
        /**//// <summary>
        /// 返回符合条件的SELECT语句行数.
        /// </summary>
        /// <param name="tableName">传入的表名.</param>
        /// <param name="where">传入的条件语句(不带where关键字)</param>
        /// <returns>符合条件的行数值.(int)</returns>
        public static int GetCount(string tableName,string where)
        {
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            int rtn = 0;

            string sql = "SELECT count(*) FROM " + tableName;
            if (where.Length > 0)
            {
                sql += " WHERE " + where;
            }

            SqlCommand cmd = new SqlCommand(sql,cn);
            try
            {
                cn.Open();
                rtn = (int)cmd.ExecuteScalar();
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();
            }
            return rtn;
        }

        /**//// <summary>
        /// 执行存储过程.
        /// </summary>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <param name="parameter">传入的参数值</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        private static int DoStoredProcedure(string storedProcedureName,SqlParameter[] parameter)
        {
            int rtn = 1;
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName,cn);
            try
            {
                cn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                for(int i=0;i<parameter.Length;i++)
                    cmd.Parameters.Add(parameter[i]);
                
                cmd.ExecuteNonQuery();
                rtn = 0;
            }
            catch(Exception ex)
            {
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();                
            }
            return rtn;
        }
        /**//// <summary>
        /// 执行事务处理
        /// </summary>
        /// <param name="sql">SQL语句数组</param>
        /// <returns>0--为正常执行,其它值-发生错误</returns>
        public static int DoTransaction(string[] sql)
        {
            int rtn = 1;
            SqlConnection cn = new SqlConnection(Global.SQLConnectionString);
            SqlTransaction sqlTrans = null;
            SqlCommand cmd = cn.CreateCommand();
            try
            {
                cn.Open();
                sqlTrans = cn.BeginTransaction();
                cmd.Transaction = sqlTrans;
                //根据SQL数组的位数,来循环执行SQL语句
                for(int i=0;i<sql.Length;i++)
                {
                    cmd.CommandText =sql[i].ToString();
                    cmd.ExecuteNonQuery();
                }
                sqlTrans.Commit();
                rtn = 0;
            }
            catch(Exception ex)
            {
                sqlTrans.Rollback();
                throw new Exception(string.Empty,ex);
            }
            finally
            {
                cn.Close();                
            }
            return rtn;
        }
        #endregion

        Properties#region Properties

        #endregion
    }
}