Modify someone's sqlhelper for ODBC

Whatever you want, the source author is unknown.

The modification to SQL SERVER was originally used for ODBC.

and modify the connection string, you can allow the modification one time.

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 = Configuratio nmanager.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= "CAC Hekey ">key to the parameter cache</param>//<param name=" cmdparameters ">an array of Odbcparamters To is 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&        Gt public static odbcparameter[] Getcacheparameters (string cacheKey) {odbcparameter[] cachedparms = (odbcp            Arameter[]) 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) c Achedparms[i]).            Clone ();        } return clonedparms; }//<summary>//Prepare a command for execution///</summary>/<param N        Ame= "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&L t;/param>//<param name= "cmdparms" >odbcparameters to use in the command</param> private STA tic 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 th        e database specified in the connection string using the provided parameters. </summary>//<param name= "connectionString" >a valid Conneciotn string for a Odbcconnection</pa ram>//<param name= "cmdtext" >the stored procedure name or T-ODBC command</param>//<re Turns>an int RepreseNting the number of rows affected by the command</returns> public static int ExecuteNonQuery (String connectio Nstring, 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 I        n the connection string using the provided parameters. </summary>//<param name= "connectionString" >a valid Conneciotn string for a Odbcconnection</pa ram>//<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 (Connec            Tionstring = = null) {connectionString = Connectionstringdefault;        } return ExecuteNonQuery (connectionString, Cmdtext, cmdtype, NULL); }//<summary>//Execute a OdbcCommand (that returns no resultset) against the database specified I        n the connection string using the provided parameters. </summary>//<remarks>//e.g.://int Result=executenonquery (Connstring,commandt Ype.        StoredProcedure, "Publishorders", New OdbcParameter ("@prodid", 24)); </remarks>//<param name= "connectionString" >a valid Conneciotn string for a Odbcconnection</pa ram>//<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 = Conn            Ectionstringdefault;            } odbccommand cmd = new OdbcCommand (); using (OdbcConnection conn = new OdbcConnection (connectionString)) {PrepareCommand (CMD, conn, n                Ull, Cmdtype, Cmdtext, commandparameters); int val = cmd.                ExecuteNonQuery (); Cmd.                Parameters.clear ();            return Val; }}///<summary>//Execute a OdbcCommand (that is returns no resultset) using an existing ODBC Transaction using the provided parameters. </summary>//<remarks>//e.g.://int result = ExecuteNonQuery (trans, Commandt Ype.        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 Exe        Cutenonquery (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 T He connection string using the provided parameters//</summary>//<param name= "connectionString ">a valid connection string for a odbcconnection</param>//<param name=" Cmdtype ">the CommandType (s Tored 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>//<ret Urns>a OdbcDataReader containing the results</returns> public static OdbcDataReader ExecuteReader (String c ONnectionstring, String cmdtext, CommandType cmdtype, params odbcparameter[] cmdparameters) {if (Connect            Ionstring = = 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 C Ode, because no DataReader would exist, hence the//commandbehaviour.closeconnection won't work T                ry {preparecommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparameters); OdbcDataReader dr = cmd.                ExecuteReader (commandbehavior.closeconnection); Cmd.                Parameters.clear ();            Return Dr; } catch (Exception) {conn.                Close ();            Throw }}///<summary&Gt Execute a odbccommand that return a resultset against the database specified in the connection string using the Provid Ed 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>//<re Turns>a DataTable containing the results</returns> public static DataTable executedatatable (string connect Ionstring, String cmdtext, CommandType cmdtype, params odbcparameter[] cmdparameters) {if (connectionst            Ring = = NULL) {connectionString = Connectionstringdefault;     } datatable dt = new DataTable ();       using (OdbcConnection conn = new OdbcConnection (connectionString)) {using (OdbcCommand C md = New OdbcCommand ()) {PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, Cmdparameter                    s);                    OdbcDataAdapter adapter = new OdbcDataAdapter (cmd); Adapter.                Fill (DT);        }} return DT; }//<summary>//Execute a OdbcCommand that returns the first column of the first record against th        e database specified in the connection string//using the provided parameters. </summary>//<remarks>//e.g.://Object obj = ExecuteScalar (connstring, Comma        Ndtype.storedprocedure, "Publishorders", New OdbcParameter ("@prodid", 24)); </remarks>//<param name= "connectionString" >a valid connection string for a Odbcconnection</pa ram>//<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 is converted to the expected type using convert.to{type}</returns> publ IC Static Object ExecuteScalar (String connectionString, String cmdtext, CommandType cmdtype, params odbcparameter[] Comma Ndparameters) {if (connectionString = = null) {connectionString = ConnectionS            Tringdefault;            } object val = null; using (odbcconnection connection = new OdbcConnection (connectionString)) {using (OdbcCommand cm D = New OdbcCommand ()) {PrepareCommand (cmd, connection, NULL, Cmdtype, Cmdtext, command           Parameters);         val = cmd.                    ExecuteScalar (); Cmd.                Parameters.clear ();            } return Val; }        }    }}

How to use:

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);

　　

Modify someone's sqlhelper for ODBC