可重用数据库操作基本类

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;

/**//// SQL数据库操作对象命名空间
namespace DBO
...{
    /**//// 数据条目类，用来方便转换数据项目成各种格式
    public class DateItem
    ...{
        private Object fObject;

        public DateItem(Object dataObj)
        ...{
            if (dataObj == null)
                throw new System.ArgumentNullException("数据条目对象参数不能为空");
            fObject = dataObj;
        }

        //转换成字符串类型,DBnull转换为""
        public override string ToString()
        ...{
            string result = "";
            if (!(fObject is DBNull))
                result = fObject.ToString().TrimEnd(null);
            return result;
        }

        //转换成数字类型,DBnull默认转换成0
        public int ToInt()
        ...{
            int result = 0;
            int.TryParse(ToString(), out result);
            return result;
        }

        //转换成日期类型
        public DateTime ToDateTime()
        ...{
            DateTime result = DateTime.Now;
            result = System.Convert.ToDateTime(ToString());
            return result;
        }

        //转换成bool型
        public bool toBool()
        ...{
            //尝试转换字符串
            if (ToString().ToLower() == "true")
                return true;
            else if (ToString().ToLower() == "false")
                return false;
            else if (ToInt() != 0)
                return true;
            else
                return false;
        }
    }


    //数据库操作基础类
    public class DBA
    ...{
        //配置文件中连接字符串的名称
        private static string CONN_STRING = "pc";
        private string fQueryString;

        // 当前数据游标位置
        private int fCurIndex = 0;
        private string fConnString;

        private SqlConnection fConnection;
        private SqlCommand fCommand;
        private SqlDataAdapter fDataAdapter;
        private DataSet fDataSet;
        private SqlTransaction fsqlTrans;

        public DBA()
        ...{
            fConnString = GetConnString();
            InitSQL("", null);
        }

        public DBA(string queryStr, params SqlParameter[] cmdParms)
        ...{
            fConnString = GetConnString();
            InitSQL(queryStr, cmdParms);
        }

        //初始化数据访问对象
        private void InitSQL(string qString, SqlParameter[] cmdParms)
        ...{
            fQueryString = qString;
            fConnection = new SqlConnection(fConnString);
            fConnection.Open();

            fCommand = new SqlCommand();
            fCommand.Connection = fConnection;
            fCommand.CommandType = CommandType.Text;
            fCommand.CommandText = fQueryString;
            if (cmdParms != null)
                PrepareSqlParms(fCommand, cmdParms);

            fDataAdapter = new SqlDataAdapter();
            fDataAdapter.SelectCommand = fCommand;

            fDataSet = new DataSet();

            if (fQueryString.Length > 0)
                fDataAdapter.Fill(fDataSet);

        }

        //静态方法,SQL字符串清理:替换单引号
        private static string sqlEncode(string sqlString)
        ...{
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < sqlString.Length; i++)
                sb.Append((sqlString[i] == ''') ? "''" : sqlString[i].ToString());
            return sb.ToString();
        }

        //静态方法,给SqlCommand对象设定参数
        private static void PrepareSqlParms(SqlCommand sqlCmd, SqlParameter[] cmdParms)
        ...{
            sqlCmd.Parameters.Clear();
            if (cmdParms.Length > 0)
            ...{
                foreach (SqlParameter parm in cmdParms)
                    sqlCmd.Parameters.Add(parm);
            }
        }

        //静态方法,返回DB连接字符串
        public static string GetConnString()
        ...{
            return ConfigurationManager.ConnectionStrings[CONN_STRING].ConnectionString;
        }

        //静态方法,用来执行执行一个查询(insert、update、delete)，返回受影响的行数。
        public static int ExecuteNonQuery(string queryString, params SqlParameter[] cmdParms)
        ...{
            int result;
            using (SqlConnection sqlConn = new SqlConnection(GetConnString()))
            ...{
                sqlConn.Open();
                SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
                PrepareSqlParms(sqlCmd, cmdParms);
                result = sqlCmd.ExecuteNonQuery();
                sqlCmd.Parameters.Clear();
                sqlConn.Close();
            }
            return result;
        }

        //静态方法,执行一个查询(count()、sum()等统计函数)，返回结果集中第一行的第一列。
        public static Object ExecuteScalar(string queryString, params SqlParameter[] cmdParms)
        ...{
            Object result;
            using (SqlConnection sqlConn = new SqlConnection(GetConnString()))
            ...{
                sqlConn.Open();
                SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
                PrepareSqlParms(sqlCmd, cmdParms);
                result = sqlCmd.ExecuteScalar();
                sqlCmd.Parameters.Clear();
                sqlConn.Close();
            }
            return result;
        }

        //静态方法,执行查询，返回sqlDataReader对象，Reader.Close()之后自动关闭连接
        public static SqlDataReader QueryData(string queryString, params SqlParameter[] cmdParms)
        ...{
            SqlConnection sqlConn = new SqlConnection(GetConnString());
            sqlConn.Open();
            SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
            PrepareSqlParms(sqlCmd, cmdParms);
            SqlDataReader sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sqlReader;
        }

        // 缺省索引器
        public DateItem this[string index]
        ...{
            get
            ...{
                object result;
                try
                ...{
                    result = fDataSet.Tables[0].Rows[fCurIndex][index];
                }
                catch (ArgumentException e)
                ...{
                    result = "无此字段名(" + index + ")<br> " + e.Message;
                }
                catch (Exception e)
                ...{
                    result = "结果集中无记录<br> " + e.Message;
                }
                return new DateItem(result);
            }
        }

        // 向后移动数据游标
        public bool MoveNext()
        ...{
            if (!EOF)
            ...{
                fCurIndex++;
                return true;
            }
            return false;
        }
        
        // 是否到达数据尾部
        public bool EOF
        ...{
            get ...{ return fCurIndex >= Count; }
        }

        //当前索引
        public int CurrentIndex
        ...{
            get ...{ return fCurIndex; }
        }

        public int Count
        ...{
            get ...{ return Rows.Count; }
        }

        // 表的行集
        public DataRowCollection Rows
        ...{
            get ...{ return fDataSet.Tables[0].Rows; }
        }

        // 表的列集
        public DataColumnCollection Columns
        ...{
            get
            ...{
                return fDataSet.Tables[0].Columns;
            }
        }

        // 提供一个可供利用的数据源
        public DataView DataSource
        ...{
            get ...{ return fDataSet.Tables[0].DefaultView; }
        }

        // 执行已有查询，返回受影响的行数，结果集装载到内置对象中。
        public int ReOpen()
        ...{
            return fDataAdapter.Fill(fDataSet);
        }

        // 执行一个查询，返回受影响的行数，结果集装载到内置对象中。用于执行select。
        public int Open(string queryString, params SqlParameter[] cmdParms)
        ...{
            int result;
            fQueryString = queryString;
            fCommand.CommandText = fQueryString;
            PrepareSqlParms(fCommand, cmdParms);
            if (fsqlTrans != null)
                fCommand.Transaction = fsqlTrans;
            fDataSet.Clear();
            result = fDataAdapter.Fill(fDataSet);
            return result;
        }

        //执行一个查询返回影响行数
        public int Execute(string queryString, params SqlParameter[] cmdParms)
        ...{
            fQueryString = queryString;
            fCommand.CommandText = fQueryString;
            PrepareSqlParms(fCommand, cmdParms);
            if (fsqlTrans != null)
                fCommand.Transaction = fsqlTrans;
            return fCommand.ExecuteNonQuery();
        }

        //开始一个事务操作
        public void BeginTransaction()
        ...{
            if ((fsqlTrans != null) && (fConnection != null))
                fsqlTrans = fConnection.BeginTransaction();
        }

        //提交事务
        public void Commit()
        ...{
            if (fsqlTrans != null)
            ...{
                fsqlTrans.Commit();
                fsqlTrans = null;
            }
        }

        //回滚事务
        public void Rollback()
        ...{
            if (fsqlTrans != null)
            ...{
                fsqlTrans.Rollback();
                fsqlTrans = null;
            }
        }

        // 释放数据库连接。
        public void Close()
        ...{
            if (fDataSet != null)
                fDataSet.Dispose();
            if (fConnection != null)
            ...{
                fConnection.Close();
                fConnection.Dispose();
            }
            if (fCommand != null)
                fCommand.Dispose();
            if (fDataAdapter != null)
                fDataAdapter.Dispose();
        }
    }
}