I have three textboxes (txtSourceType, txtSourceText, txtSourceDesc) to receive values for a table in Access. The value for txtSourceType will be converted to "int" type, and the other two will be text type. I have an OleHelper class in place and the code for adding a record to the table works well as follows.
Expand|Select|Wrap|Line Numbers
- public int SourceAdd() //This method is defined here and referenced elsewhere in this project.
- {
- string strCmdText = "INSERT INTO [tbl_Source] (fld_SourceType, fld_SourceText, fld_SourceDesc) VALUES ("
- + this.iSourceType + ", '" + this.sSourceText + "', '" + this.sSourceDesc + "')";
- try
- {
- return OleHelper.ExecuteNonQuery(OleHelper.CONN_STRING, CommandType.Text, strCmdText);
- }
- catch (InvalidCastException e)
- {
- return -1;
- }
- }
The following is the OleHelper.
Expand|Select|Wrap|Line Numbers
- class OleHelper
- {
- public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["LenguaGenius2013_Engl_Voca.Properties.Settings.LenguaGenius2013ConnectionString"].ToString();
- private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
- #region PrepareCommand
- public static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] 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 (OleDbParameter parm in cmdParms)
- {
- cmd.Parameters.Add(parm);
- }
- }
- }
- public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
- {
- parmCache[cacheKey] = cmdParms;
- }
- public static OleDbParameter[] GetCachedParameters(string cacheKey)
- {
- OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
- if (cachedParms == null)
- return null;
- OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
- for (int i = 0, j = cachedParms.Length; i < j; i++)
- clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
- return clonedParms;
- }
- #endregion
- #region ExecuteNonQuery
- public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- OleDbCommand cmd = new OleDbCommand();
- PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
- int val = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- if (cmd.Connection.State == ConnectionState.Open)
- {
- cmd.Connection.Close();
- }
- return val;
- }
- public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- OleDbCommand cmd = new OleDbCommand();
- PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
- int val = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- if (conn.State == ConnectionState.Open)
- {
- conn.Close();
- }
- return val;
- }
- public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParams)
- {
- OleDbCommand cmd = new OleDbCommand();
- using (OleDbConnection conn = new OleDbConnection(connString))
- {
- PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParams);
- int val = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- if (cmd.Connection.State == ConnectionState.Open)
- {
- cmd.Connection.Close();
- }
- return val;
- }
- }
- public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText)
- {
- return ExecuteNonQuery(trans, cmdType, cmdText, null);
- }
- public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText)
- {
- return ExecuteNonQuery(conn, cmdType, cmdText, null);
- }
- public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText)
- {
- return ExecuteNonQuery(connString, cmdType, cmdText, null);
- }
- #endregion
- #region ExecuteReader
- public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- OleDbCommand cmd = new OleDbCommand();
- OleDbConnection conn = new OleDbConnection(connString);
- try
- {
- PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
- OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- cmd.Parameters.Clear();
- if (conn.State == ConnectionState.Open)
- {
- conn.Close();
- }
- return rdr;
- }
- catch
- {
- conn.Close();
- throw;
- }
- }
- public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText)
- {
- return ExecuteReader(connString, cmdType, cmdText, (OleDbParameter[])null);
- }
- #endregion
- #region ExecuteDataset
- public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- OleDbCommand cmd = new OleDbCommand();
- PrepareCommand(cmd, conn, (OleDbTransaction)null, cmdType, cmdText, cmdParms);
- OleDbDataAdapter da = new OleDbDataAdapter(cmd);
- DataSet ds = new DataSet();
- da.Fill(ds);
- cmd.Parameters.Clear();
- if (conn.State == ConnectionState.Open)
- {
- conn.Close();
- }
- return ds;
- }
- public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- using (OleDbConnection conn = new OleDbConnection(connString))
- {
- conn.Open();
- return ExecuteDataset(conn, cmdType, cmdText, cmdParms);
- }
- }
- public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText)
- {
- return ExecuteDataset(connString, cmdType, cmdText, (OleDbParameter[])null);
- }
- public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText)
- {
- return ExecuteDataset(conn, cmdType, cmdText, (OleDbParameter[])null);
- }
- #endregion
- #region ExecuteDataTable
- public static DataTable ExecuteDataTable(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- OleDbCommand cmd = new OleDbCommand();
- PrepareCommand(cmd, conn, (OleDbTransaction)null, cmdType, cmdText, cmdParms);
- OleDbDataAdapter da = new OleDbDataAdapter(cmd);
- DataSet ds = new DataSet();
- da.Fill(ds);
- cmd.Parameters.Clear();
- if (conn.State == ConnectionState.Open)
- {
- conn.Close();
- }
- return ds.Tables[0];
- }
- public static DataTable ExecuteDataTable(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- using (OleDbConnection conn = new OleDbConnection(connString))
- {
- conn.Open();
- return ExecuteDataTable(conn, cmdType, cmdText, cmdParms);
- }
- }
- public static DataTable ExecuteDataTable(string connString, CommandType cmdType, string cmdText)
- {
- return ExecuteDataTable(connString, cmdType, cmdText, (OleDbParameter[])null);
- }
- public static DataTable ExecuteDataTable(OleDbConnection conn, CommandType cmdType, string cmdText)
- {
- return ExecuteDataTable(conn, cmdType, cmdText, (OleDbParameter[])null);
- }
- #endregion
- #region ExecuteScalar
- public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- OleDbCommand cmd = new OleDbCommand();
- PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
- object val = cmd.ExecuteScalar();
- cmd.Parameters.Clear();
- if (conn.State == ConnectionState.Open)
- {
- conn.Close();
- }
- return val;
- }
- public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
- {
- OleDbCommand cmd = new OleDbCommand();
- using (OleDbConnection conn = new OleDbConnection(connString))
- {
- PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
- object val = cmd.ExecuteScalar();
- cmd.Parameters.Clear();
- if (conn.State == ConnectionState.Open)
- {
- conn.Close();
- }
- return val;
- }
- }
- public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText)
- {
- return ExecuteScalar(connString, cmdType, cmdText, null);
- }
- public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText)
- {
- return ExecuteScalar(conn, cmdType, cmdText, null);
- }
- #endregion
- }