准备下自己用的数据库访问组件,方便使用,现今已经有了成熟的样例代码
[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());
方法声明列表
(a)ExecuteNonQuery(3个)
/// 执行数据库操作,(非Select) /// <param name="connString">数据库连接字符串</param> /// <param name="cmdType">命令类型:存储过程或SQL语句</param> /// <param name="cmdText">SQL语句或存储过程名</param> /// <param name="commandParameters">参数列表</param> public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters); /// <summary> /// Execute an OracleCommand (that returns no resultset) against an existing database transaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24)); /// </remarks> /// <param name="trans">an existing database transaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OracleParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters); /// <summary> /// Execute an OracleCommand (that returns no resultset) against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OracleParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);
(b)ExecuteReader(1个)
/// 执行查询返回DataReader /// <param name="connString">Connection string</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OracleParamters used to execute the command</param> public 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)); /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OracleParamters used to execute the command</param> /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) ; ///执行 OracleCommand (返回 a 1x1 结果集) 通过指定的 SqlTransaction ///<param name="transaction">A valid SqlTransaction</param> ///<param name="commandType">The CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">The stored procedure name or PL/SQL command</param> ///<param name="commandParameters">An array of OracleParamters used to execute the command</param> ///<returns>An object containing the value in the 1x1 resultset generated by the command</returns> 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)); /// <param name="conn">an existing database connection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OracleParamters used to execute the command</param> /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters);
(d)缓存Params(2个)
/// 将 参数信息 加入到缓存中 /// <param name="cacheKey">Key value to look up the parameters</param> /// <param name="commandParameters">Actual parameters to cached</param> public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters); /// 从缓存中获取 参数信息 /// <param name="cacheKey">Key to look up the parameters</param> public static OracleParameter[] GetCachedParameters(string cacheKey);
(e)私有方法 PrepareCommand
/// 私有方法: PrepareCommand ,执行SQL语句 /// <param name="cmd">Existing command object</param> /// <param name="conn">Database connection object</param> /// <param name="trans">Optional transaction object</param> /// <param name="cmdType">Command type, e.g. stored procedure</param> /// <param name="cmdText">Command test</param> /// <param name="commandParameters">Parameters for the command</param> private 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 strings public 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 parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
(a)ExecuteNonQuery(3个) 同OracleHelper
/// <summary> /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters); /// <summary> /// Execute a SqlCommand (that returns no resultset) against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters); /// <summary> /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="trans">an existing sql transaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);
(b)ExecuteReader(1个)
/// <summary> /// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);
(c) ExecuteScalar(2个)
/// <summary> /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters); /// <summary> /// Execute a SqlCommand that returns the first column of the first record against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);
(d)缓存params(2个)
/// <summary> /// add parameter array to the cache /// </summary> /// <param name="cacheKey">Key to the parameter cache</param> /// <param name="cmdParms">an array of SqlParamters to be cached</param> public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters); /// <summary> /// Retrieve cached parameters /// </summary> /// <param name="cacheKey">key used to lookup parameters</param> /// <returns>Cached SqlParamters array</returns> public static SqlParameter[] GetCachedParameters(string cacheKey);
(e)私有方法 PrepareCommand
/// <summary> /// Prepare a command for execution /// </summary> /// <param name="cmd">SqlCommand object</param> /// <param name="conn">SqlConnection object</param> /// <param name="trans">SqlTransaction object</param> /// <param name="cmdType">Cmd type e.g. stored procedure or text</param> /// <param name="cmdText">Command text, e.g. Select * from Products</param> /// <param name="cmdParms">SqlParameters to use in the command</param> private 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);
浙公网安备 33010602011771号