修改的某人的SqlHelper FOR ODBC
2024-09-26 20:59:09
随便找来的,源作者不明.
本来是SQL SERVER 的 修改为 ODBC使用.
并且修改了连接字符串,可以允许修改一次.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Collections;
using System.Data; using System.Data.Odbc; namespace DBUtility
{
public abstract class OdbcHelper
{
//Database connection strings
private static string connectionStringDefault; public static string ConnectionStringDefault
{
get {
if(string.IsNullOrEmpty(connectionStringDefault))
{
return connectionStringDefault = ConfigurationManager.ConnectionStrings["OdbcConnStringDefault"].ConnectionString;
}
else
{
return connectionStringDefault;
}
}
set {
if (string.IsNullOrEmpty(connectionStringDefault))
connectionStringDefault = value;
}
} //Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="cacheKey">Key to the parameter cache</param>
/// <param name="cmdParameters">an array of OdbcParamters to be cached</param>
public static void SetCacheParameters(string cacheKey, params OdbcParameter[] parameters)
{ parmCache[cacheKey] = parameters;
} /// <summary>
/// Retrieve cached parameters
/// </summary>
/// <param name="cacheKey">key used to lookup parameters</param>
/// <returns>Cached OdbcParamters array</returns>
public static OdbcParameter[] GetCacheParameters(string cacheKey)
{
OdbcParameter[] cachedParms = (OdbcParameter[])parmCache[cacheKey];
if (cachedParms == null)
{
return null;
}
OdbcParameter[] clonedParms = new OdbcParameter[cachedParms.Length];
for (int i = 0; i < cachedParms.Length; i++)
{
clonedParms[i] = (OdbcParameter)((ICloneable)cachedParms[i]).Clone();
}
return clonedParms;
} /// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">OdbcCommand object</param>
/// <param name="conn">OdbcConneciotn object</param>
/// <param name="trans">OdbcTransaction 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">OdbcParameters to use in the command</param>
private static void PrepareCommand(OdbcCommand cmd, OdbcConnection conn, OdbcTransaction trans, CommandType cmdType, string cmdText, OdbcParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (OdbcParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
} /// <summary>
/// Execute a OdbcCommand (that returns no resultset) against the database specified in the connection string using the provided parameters.
/// </summary>
/// <param name="connectionString">a valid conneciotn string for a OdbcConnection</param>
/// <param name="cmdText">the stored procedure name or T-Odbc command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, string cmdText)
{
if (connectionString == null)
{
connectionString = ConnectionStringDefault;
}
return ExecuteNonQuery(connectionString, cmdText, CommandType.Text, null);
} /// <summary>
/// Execute a OdbcCommand (that returns no resultset) against the database specified in the connection string using the provided parameters.
/// </summary>
/// <param name="connectionString">a valid conneciotn string for a OdbcConnection</param>
/// <param name="cmdType">the CommandType (stored procedure,text,etc.)</param>
/// <param name="cmdText">the stored procedure name or T-Odbc command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, string cmdText, CommandType cmdType)
{
if (connectionString == null)
{
connectionString = ConnectionStringDefault;
}
return ExecuteNonQuery(connectionString, cmdText, cmdType, null);
} /// <summary>
/// Execute a OdbcCommand (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 OdbcParameter("@prodid",24));
/// </remarks>
/// <param name="connectionString">a valid conneciotn string for a OdbcConnection</param>
/// <param name="cmdType">the CommandType (stored procedure,text,etc.)</param>
/// <param name="cmdText">the stored procedure name or T-Odbc command</param>
/// <param name="commandParameters">an array of OdbcParameters 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, string cmdText, CommandType cmdType, params OdbcParameter[] commandParameters)
{
if (connectionString == null)
{
connectionString = ConnectionStringDefault;
}
OdbcCommand cmd = new OdbcCommand();
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
} /// <summary>
/// Execute a OdbcCommand (that returns no resultset) using an existing Odbc Transaction using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OdbcParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">an existing Odbc transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-Odbc command</param>
/// <param name="commandParameters">an array of OdbcParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(OdbcTransaction trans, string cmdText, CommandType cmdType, params OdbcParameter[] commandParameters)
{
int val = 0;
using (OdbcCommand cmd = new OdbcCommand())
{
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
return val;
} /// <summary>
/// Execute a OdbcCommand that returns a resultset against the database specified in the connection string using the provided parameters
/// </summary>
/// <param name="connectionString">a valid connection string for a OdbcConnection</param>
/// <param name="cmdType">the CommandType(stored procedure,text,etc.)</param>
/// <param name="cmdText">the stroed procedure name or T-Odbc command</param>
/// <param name="cmdParameters">an array of OdbcParameters used to execute the command</param>
/// <returns>A OdbcDataReader containing the results</returns>
public static OdbcDataReader ExecuteReader(string connectionString, string cmdText, CommandType cmdType, params OdbcParameter[] cmdParameters)
{
if (connectionString == null)
{
connectionString = ConnectionStringDefault;
}
OdbcCommand cmd = new OdbcCommand();
OdbcConnection conn = new OdbcConnection(connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);
OdbcDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch (Exception)
{
conn.Close();
throw;
}
} /// <summary>
/// Execute a OdbcCommand that return a resultset against the database specified in the connection string using the provided parameters
/// </summary>
/// <param name="connectionString">a valid connection string for a OdbcConnection</param>
/// <param name="cmdType">the CommandType(stored procedure,text,etc.)</param>
/// <param name="cmdText">the stored procedure name or T-Odbc command</param>
/// <param name="cmdParameters">an array of OdbcParameters userd to execute the command</param>
/// <returns>A DataTable containing the results</returns>
public static DataTable ExecuteDataTable(string connectionString, string cmdText, CommandType cmdType, params OdbcParameter[] cmdParameters)
{
if (connectionString == null)
{
connectionString = ConnectionStringDefault;
}
DataTable dt = new DataTable();
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
using (OdbcCommand cmd = new OdbcCommand())
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);
OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
adapter.Fill(dt);
}
}
return dt;
} /// <summary>
/// Execute a OdbcCommand 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 OdbcParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a OdbcConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-Odbc command</param>
/// <param name="commandParameters">an array of OdbcParameters 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, string cmdText, CommandType cmdType, params OdbcParameter[] commandParameters)
{
if (connectionString == null)
{
connectionString = ConnectionStringDefault;
}
object val = null;
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
using (OdbcCommand cmd = new OdbcCommand())
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
return val;
}
} }
}
使用方式:
string conString = "Driver={SQL Native Client};Server=abc;Database=Items;Uid=sa;Pwd=111111;";
DBUtility.OdbcHelper.ConnectionStringDefault = conString;
DataTable dt = DBUtility.OdbcHelper.ExecuteDataTable(conString, "SELECT * FROM item", CommandType.Text, null);
最新文章
- ObjC宏定义-预编译小功能
- C#教程(1) -- .Net与C#简介
- python之ftplib库
- Groovy中那些神奇注解之Memoized
- veridata实验例（3）验证veridata发现insert操作不会导致同步
- DOM小解
- pip --upgrade批量更新过期的python库
- 常见BOM实用对象
- Unreal Engine 4 Radiant UI 入门教程（零）在场景中摆放网页
- iOS项目的命名规范
- django项目的部署
- 第18课 类型萃取（2）_获取返回值类型的traits
- flask框架的教程--虚拟环境的安装[一]
- mybatis源码-解析配置文件（四）之配置文件Mapper解析
- [No0000174]Spring常用注解（收藏大全）
- mysql_test
- SSIS ->> Excel Destination无法接受大于255个字符长度的字符字段
- 在Android studio中到入Eclipse
- 爬取乌云上所有人民币和乌云符号的漏洞（python脚本）
- 翻译：MLAPP（2.1节 概率概述）