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