Line 1: using System;
Line 2: using System.Web;
Line 3: using System.Data;
Line 4: using System.Data.Common;
Line 5: using System.Data.Odbc;
Line 6: using System.Web.SessionState;
Line 7:
Line 8: namespace TrainingMonitor.DataService
Line 9: {
Line 10: /// <summary>
Line 11: /// Summary description for DataAccessBase.
Line 12: /// </summary>
Line 13: public class DataAccessBase
Line 14: {
Line 15: protected OdbcConnection _connection;
Line 16: protected OdbcDataAdapter _adapter;
Line 17: protected OdbcCommand _command;
Line 18: DataSet _ds;
Line 19: string _connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
Line 20: string _ConnState = "Closed";
Line 21: protected OdbcCommand _select;
Line 22: protected OdbcCommand _insert;
Line 23: protected OdbcCommand _update;
Line 24: protected OdbcCommand _delete;
Line 25:
Line 26: //protected void InitializeSelectCommand(string selectCommand)
Line 27: //{
Line 28: // if (this._connection.State == ConnectionState.Closed)
Line 29: // {
Line 30: // this._connection.Open();
Line 31: // }
Line 32: // _select = new OdbcCommand(selectCommand, this._connection);
Line 33: // this._connection.Close();
Line 34: //}
Line 35:
Line 36: //protected void InitializeUpdateCommand(string updateCommand)
Line 37: //{
Line 38: // if (this._connection.State == ConnectionState.Closed)
Line 39: // {
Line 40: // this._connection.Open();
Line 41: // }
Line 42: // _update = new OdbcCommand(updateCommand, this._connection);
Line 43: // this._connection.Close();
Line 44: //}
Line 45:
Line 46: //protected void IntiailizeInsertCommand(string insertCommand)
Line 47: //{
Line 48: // if (this._connection.State == ConnectionState.Closed)
Line 49: // {
Line 50: // this._connection.Open();
Line 51: // }
Line 52: // _insert = new OdbcCommand(insertCommand, this._connection);
Line 53: // this._connection.Close();
Line 54: //}
Line 55:
Line 56: //protected void InitializeDeleteCommand(string deleteCommand)
Line 57: //{
Line 58: // if (this._connection.State == ConnectionState.Closed)
Line 59: // {
Line 60: // this._connection.Open();
Line 61: // }
Line 62: // _delete = new OdbcCommand(deleteCommand, this._connection);
Line 63: // this._connection.Close();
Line 64: //}
Line 65:
Line 66: public DataAccessBase()
Line 67: {
Line 68: this._connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
Line 69: this._connection = new OdbcConnection(this._connectionString);
Line 70: //try
Line 71: //{
Line 72: // if (this._ConnState != "Open")//postback)//this._connection.State==open)
Line 73: // {
Line 74: // this._connection.Open();
Line 75: // this._ConnState = "Open";
Line 76: // }
Line 77: //}
Line 78: //catch (OdbcException oEx)
Line 79: //{
Line 80: // this._connection.Close();
Line 81: // throw oEx;
Line 82: //}
Line 83: //finally
Line 84: //{
Line 85: //}
Line 86: }
Line 87:
Line 88: protected OdbcDataReader ExecuteSelect()
Line 89: {
Line 90: OdbcDataReader reader;
Line 91:
Line 92: try
Line 93: {
Line 94: if (this._connection.State == ConnectionState.Closed)
Line 95: {
Line 96: this._connection.Open();
Line 97: }
Line 98: reader = this._select.ExecuteReader();
Line 99: this._connection.Close();
Line 100: }
Line 101: catch (OdbcException oEx)
Line 102: {
Line 103: throw oEx;
Line 104: }
Line 105: finally
Line 106: {
Line 107: this._connection.Close();
Line 108: }
Line 109: return reader;
Line 110: }
Line 111:
Line 112: protected DataSet FillDataSet(string SqlQry)
Line 113: {
Line 114: try
Line 115: {
Line 116: //if (_connection.State == ConnectionState.Open)
Line 117: //{
Line 118: // _connection.Close();
Line 119: //}
Line 120: //_connection.Open();
Line 121: _adapter = new OdbcDataAdapter(SqlQry, _connection);
Line 122: _ds = new DataSet();
Line 123: _adapter.Fill(_ds);
Line 124: //_connection.Close();
Line 125:
Line 126: }
Line 127: catch (OdbcException ex)
Line 128: {
Line 129: LogError(ex, SqlQry);
Line 130: //throw ex;
Line 131: }
Line 132: finally
Line 133: {
Line 134: //_connection.Close();
Line 135: }
Line 136: return _ds;
Line 137: }
Line 138:
Line 139: protected int ExecuteNonQry(string SqlQry)
Line 140: {
Line 141: int i = 0;
Line 142: try
Line 143: {
Line 144: if (_connection.State == ConnectionState.Open)
Line 145: {
Line 146: _connection.Close();
Line 147: }
Line 148:
Line 149: _connection.Open();
Line 150:
Line 151: _command = new OdbcCommand(SqlQry, _connection);
Line 152:
Line 153: i = _command.ExecuteNonQuery();
Line 154:
Line 155: }
Line 156: catch (OdbcException ex)
Line 157: {
Line 158: LogError(ex);
Line 159: }
Line 160: finally
Line 161: {
Line 162: _connection.Close();
Line 163: }
Line 164:
Line 165: return i;
Line 166: }
Line 167:
Line 168:
Line 169: public void LogError(Exception ex)
Line 170: {
Line 171: try
Line 172: {
Line 173: if (_connection.State == ConnectionState.Open)
Line 174: {
Line 175: _connection.Close();
Line 176: }
Line 177:
Line 178: string sqlQry = "call InsertError (?, ?)";
Line 179:
Line 180: string errMsg = string.Empty;
Line 181: if (ex.InnerException != null)
Line 182: errMsg = ex.InnerException.Message + " " + (ex.InnerException).StackTrace.ToString();
Line 183: else
Line 184: errMsg = ex.Message + " " + ex.StackTrace.ToString();
Line 185:
Line 186: DateTime error_time = DateTime.Now;
Line 187: string error_message = errMsg;
Line 188:
Line 189: _command = new OdbcCommand(sqlQry, _connection);
Line 190: _command.CommandType = CommandType.StoredProcedure;
Line 191: _command.Parameters.Add("@error_time", OdbcType.DateTime).Value = error_time;
Line 192: _command.Parameters.Add("@error_message", OdbcType.VarChar).Value = error_message.Length > 2000 ? error_message.Substring(0, 2000) : error_message;
Line 193:
Line 194: _connection.Open();
Line 195:
Line 196: _command.ExecuteNonQuery();
Line 197:
Line 198: }
Line 199: catch (OdbcException odbcex)
Line 200: {
Line 201: throw odbcex;
Line 202: }
Line 203: finally
Line 204: {
Line 205: _connection.Close();
Line 206: }
Line 207:
Line 208: }
Line 209:
Line 210: public void LogError(Exception ex, string sqlSelect)
Line 211: {
Line 212: try
Line 213: {
Line 214: if (_connection.State == ConnectionState.Open)
Line 215: {
Line 216: _connection.Close();
Line 217: }
Line 218:
Line 219: string sqlQry = "call InsertError (?, ?)";
Line 220:
Line 221: string errMsg = string.Empty;
Line 222: if (ex.InnerException != null)
Line 223: errMsg = ex.InnerException.Message + " " + (ex.InnerException).StackTrace.ToString();
Line 224: else
Line 225: errMsg = ex.Message + " " + ex.StackTrace.ToString();
Line 226:
Line 227: errMsg = sqlSelect + Environment.NewLine + errMsg;
Line 228:
Line 229: DateTime error_time = DateTime.Now;
Line 230: string error_message = errMsg;
Line 231:
Line 232: _command = new OdbcCommand(sqlQry, _connection);
Line 233: _command.CommandType = CommandType.StoredProcedure;
Line 234: _command.Parameters.Add("@error_time", OdbcType.DateTime).Value = error_time;
Line 235: _command.Parameters.Add("@error_message", OdbcType.VarChar).Value = error_message.Length > 2000 ? error_message.Substring(0, 2000) : error_message;
Line 236:
Line 237: _connection.Open();
Line 238:
Line 239: _command.ExecuteNonQuery();
Line 240:
Line 241: }
Line 242: catch (OdbcException odbcex)
Line 243: {
Line 244: throw odbcex;
Line 245: }
Line 246: finally
Line 247: {
Line 248: _connection.Close();
Line 249: }
Line 250:
Line 251: }
Line 252: }
Line 253: }
Line 254:
|