准备下自己用的数据库访问组件,方便使用,现今已经有了成熟的样例代码
[1]PetShop 中的 SQLHelper 和 OracleHelper
OracleHelper简要代码:
readonly 的 字符串 存储配置信息
// 配置文件中存储public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["OraConnString1"].ConnectionString;public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["OraConnString2"].ConnectionString;public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["OraConnString3"].ConnectionString;public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["OraProfileConnString"].ConnectionString;public static readonly string ConnectionStringMembership = ConfigurationManager.ConnectionStrings["OraMembershipConnString"].ConnectionString;
hashtable 存储 Parm 信息
//创建一个Hashtable 用来缓存 参数信息 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// 执行数据库操作,(非Select)/// 数据库连接字符串/// 命令类型:存储过程或SQL语句/// SQL语句或存储过程名/// 参数列表 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);////// Execute an OracleCommand (that returns no resultset) against an existing database transaction /// using the provided parameters./// ////// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));/// /// an existing database transaction/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or PL/SQL command/// an array of OracleParamters used to execute the command///an int representing the number of rows affected by the command public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);////// Execute an OracleCommand (that returns no resultset) against an existing database connection /// using the provided parameters./// ////// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));/// /// an existing database connection/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or PL/SQL command/// an array of OracleParamters used to execute the command///an int representing the number of rows affected by the command public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);
(b)ExecuteReader(1个)
/// 执行查询返回DataReader/// Connection string/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or PL/SQL command/// an array of OracleParamters used to execute the commandpublic static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);
(c)ExecuteScalar(3个)
/// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string /// using the provided parameters./// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));/// a valid connection string for a SqlConnection/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or PL/SQL command/// an array of OracleParamters used to execute the command/// An object that should be converted to the expected type using Convert.To{Type} public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) ; ///执行 OracleCommand (返回 a 1x1 结果集) 通过指定的 SqlTransaction///A valid SqlTransaction///The CommandType (stored procedure, text, etc.)///The stored procedure name or PL/SQL command///An array of OracleParamters used to execute the command/// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters); ///使用 所提供的 参数信息,执行 Oracle 数据查询/// e.g.: /// Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));/// an existing database connection/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or PL/SQL command/// an array of OracleParamters used to execute the command/// An object that should be converted to the expected type using Convert.To{Type} public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters); (d)缓存Params(2个)
/// 将 参数信息 加入到缓存中 /// Key value to look up the parameters/// Actual parameters to cachedpublic static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters);/// 从缓存中获取 参数信息/// Key to look up the parameterspublic static OracleParameter[] GetCachedParameters(string cacheKey);
(e)私有方法 PrepareCommand
/// 私有方法: PrepareCommand ,执行SQL语句/// Existing command object/// Database connection object/// Optional transaction object/// Command type, e.g. stored procedure/// Command test/// Parameters for the commandprivate static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters);
(f)功能方法,其实与数据访问无关(2个)
/// Oracle ,bool类型转换成 string 类型public static string OraBit(bool value);///Oracle ,string类型转换成 bool 类型public static bool OraBool(string value);
SQLHelper简要
同Oracle一样作用//同Oracle基本相同//Database connection stringspublic static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString;public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString2"].ConnectionString;public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString3"].ConnectionString;public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString; // Hashtable to store cached parametersprivate static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
(a)ExecuteNonQuery(3个) 同OracleHelper
////// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string /// using the provided parameters./// ////// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));/// /// a valid connection string for a SqlConnection/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or T-SQL command/// an array of SqlParamters used to execute the command///an int representing the number of rows affected by the command public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);////// Execute a SqlCommand (that returns no resultset) against an existing database connection /// using the provided parameters./// ////// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));/// /// an existing database connection/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or T-SQL command/// an array of SqlParamters used to execute the command///an int representing the number of rows affected by the command public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);////// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction /// using the provided parameters./// ////// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));/// /// an existing sql transaction/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or T-SQL command/// an array of SqlParamters used to execute the command///an int representing the number of rows affected by the command public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);
(b)ExecuteReader(1个)
////// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters./// ////// e.g.: /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));/// /// a valid connection string for a SqlConnection/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or T-SQL command/// an array of SqlParamters used to execute the command///A SqlDataReader containing the results public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);
(c) ExecuteScalar(2个)
////// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string /// using the provided parameters./// ////// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));/// /// a valid connection string for a SqlConnection/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or T-SQL command/// an array of SqlParamters used to execute the command///An object that should be converted to the expected type using Convert.To{Type} public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);////// Execute a SqlCommand that returns the first column of the first record against an existing database connection /// using the provided parameters./// ////// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));/// /// an existing database connection/// the CommandType (stored procedure, text, etc.)/// the stored procedure name or T-SQL command/// an array of SqlParamters used to execute the command///An object that should be converted to the expected type using Convert.To{Type} public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);
(d)缓存params(2个)
////// add parameter array to the cache/// /// Key to the parameter cache/// an array of SqlParamters to be cachedpublic static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters);////// Retrieve cached parameters/// /// key used to lookup parameters///Cached SqlParamters array public static SqlParameter[] GetCachedParameters(string cacheKey);
(e)私有方法 PrepareCommand
////// Prepare a command for execution/// /// SqlCommand object/// SqlConnection object/// SqlTransaction object/// Cmd type e.g. stored procedure or text/// Command text, e.g. Select * from Products/// SqlParameters to use in the commandprivate static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms);
对比
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) ;public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);//OracleHelper独有的public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters);public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters);public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters);public static OracleParameter[] GetCachedParameters(string cacheKey);public static SqlParameter[] GetCachedParameters(string cacheKey);private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters);private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms);