/// <summary>
/// adds a parmater to client's application paramater table. Mostly used by SetupClient
/// </summary>
/// <param name="app"></param>
/// <param name="parm"></param>
/// <param name="value"></param>
/// <returns></returns>
public static void Add(string app, string parm, string value)
{
string p = ApplicationParameters.GetParm(app, parm);
if (p != null) {
if (p == value)
return;
throw new Exception("parm already exists - cant change value");
}
string i = String.Format("insert into ApplicationParameter (app, parm, [value]) values ('{0}', '{1}', '{2}')",
app, parm, value);
SqlUtil sql = new SqlUtil(TDatabase.Client);
sql.ExecuteNoResultSetSQLQuery(i);
}
private void ConstructMe(string tableName, TDatabase db, string IdColumn, SqlUtil sql)
{
if (db == TDatabase.Unknown)
db = this.DefaultDatabase;
if (string.IsNullOrEmpty(tableName))
tableName = this.DefaultTableName; // allows a subclass to define a tablename by method rather than passing in the constructor.
if (IdColumn == null)
IdColumn = "ID";
mIdColumn = IdColumn;
if (sql != null)
mSqlUtil = sql;
else
mSqlUtil = SqlUtil.Get(db);
if (tableName.StartsWith("["))
mTableName = tableName;
else
mTableName = "[" + tableName + "]";
mFullTablePath = mSqlUtil.FullTablePath(tableName);
mTableDef = GetTableDef();
mChangeList = new Dictionary<string, object>();
if (mTableDef == null) {
Exception x = new Exception("no ID field");
throw x;
}
if (mIdColumn != (string)mIdColumnHash[mFullTablePath]) {
Exception x = new Exception("cant redefined id column");
throw x;
}
mHasIdentity = (bool)mHasIdentityCache[mFullTablePath];
mData = new Dictionary<string, object>(mTableDef.Count + 3);
//init all fields to null
foreach (string f in mTableDef.Keys)
mData[f] = System.DBNull.Value;
mNewRecord = true;
}
private bool ConcurrencyViolationCheck(bool ThrowException)
{
if (!mConcurrencyCheck)
return false;
SqlUtil sql;
if (AuditHistoryDatabase == TDatabase.Unknown)
sql = this.sqlUtil;
else
sql = new SqlUtil(AuditHistoryDatabase);
string q = "select top 1 ID, LogTime from AuditHeader where DataType = @type and DataId = @id order by id desc";
SqlParameterCollection parms = (new SqlCommand()).Parameters;
parms.AddWithValue("@id", this.ID);
parms.AddWithValue("@type", TableName + ":Audit");
DataTable dt = sql.ExecuteSingleResultSetSQLQuery(q, parms);
if (dt.Rows.Count == 0)
return false;
object IdObject = dt.Rows[0][0];
if (IdObject == null)
return false;
object TimeObject = dt.Rows[0][1];
if (TimeObject == null)
return false;
//dateTime.Now can change underneath us via NTP, and we therefore do not want to rely on the timestamp. So, if we have an Auditid,we use it
bool err = false;
if (mLastAuditID > 0) {
if ((long)IdObject > mLastAuditID)
err = true;
else
return false;
}
if (err || (DateTime)TimeObject > mLoadTime) {
if (ThrowException)
throw new Exception(string.Format("concurrency violation for table {0}:id {1}, LoadTime {2}, LastUpdateTime from audit {3}. AuditId {4}, Last AuditID from audit {5}",
TableName, ID, mLoadTime, TimeObject, mLastAuditID, IdObject));
return true;
}
return false;
}
public GenericTable(SqlUtil sql, string TableName, string IdColumn)
{
//server and DB are ignored by construct me if sql is supplied
ConstructMe(TableName, sql.Database, IdColumn, sql);
}
public static SqlUtil Get(TDatabase Database, string appName)
{
bool NoCaching = true;
if (NoCaching) {
return new SqlUtil(Database, appName);
}
//save a copy of original for later use, in case first call to getDatabaseServer modifies it
string db = Database.ToString();
string svr = DBLocator.getDatabaseServer(ref db);
string k = string.Format("{0}:{1}:{2}", Process.GetCurrentProcess().Id, svr, db);
lock (xDBLock) {
if (mRegisteredConnection[k] == null) {
mRegisteredConnection[k] = new SqlUtil(Database, appName);
}
return (SqlUtil)mRegisteredConnection[k];
}
}
/// <summary>
/// paramater are gotten from the ApplicationParamater table in the client and Shared DBs. the client DB can have values that are global to that client or
/// parms for spcific application.
/// </summary>
private static void LoadParms()
{
mParms = mAllClientParms[DBLocator.ActiveClientName];
if (mParms != null)
return;
mParms = new Dictionary<string, Dictionary<string, string>>();
mAllClientParms[DBLocator.ActiveClientName] = mParms;
Dictionary<string, string> SharedClientParms = new Dictionary<string, string>();
Dictionary<string, string> GlobalParms = new Dictionary<string, string>();
mParms[".client"] = SharedClientParms;
mParms[".global"] = GlobalParms;
SqlUtil sql = new SqlUtil(TDatabase.Client);
string TableName = "ApplicationParameter";
//need to specila case Shared since the applicationparater table in Shared is used for default values for all client for all apps
//the problem is Shared has aps of its own that its needs to configure.
if (DBLocator.ActiveClientName == "Shared")
TableName = "Shared_" + TableName;
DataTable dt = sql.ExecuteSingleResultSetSQLQuery("select * from " + TableName + " order by App");
string AppName;
string parm;
string val;
Dictionary<string, string> dict;
foreach (DataRow r in dt.Rows) {
AppName = (string)DataUtils.BlankIfNull(r["App"]);
parm = (string)r["Parm"];
val = (string)r["Value"];
if (AppName == "")
dict = SharedClientParms;
else {
dict = mParms[AppName];
if (dict == null) {
dict = new Dictionary<string, string>();
mParms[AppName] = dict;
}
}
dict[parm.ToLower()] = val;
}
//go to shared db and only get the GLOBAL
sql = new SqlUtil(TDatabase.Shared);
dt = sql.ExecuteSingleResultSetSQLQuery("select * from ApplicationParameter");
foreach (DataRow r in dt.Rows) {
AppName = (string)DataUtils.BlankIfNull(r["App"]);
parm = ((string)r["Parm"]).ToLower();
val = (string)r["Value"];
if (AppName == "global")
dict = GlobalParms;
else {
dict = mParms[AppName];
if (dict == null) {
dict = new Dictionary<string, string>();
mParms[AppName] = dict;
}
//dont allow parms found in the shared app parms table to override client settings
if (dict.ContainsKey(parm))
continue;
}
dict[parm] = val;
}
}
private static List<AuditLog> GetAuditList(string Q, SqlParameterCollection oParams, SqlUtil sql, TDatabase AuditDB)
{
if (sql == null)
sql = SqlUtil.Get(AuditDB);
List<AuditLog> headers = new List<AuditLog>();
if ((long)oParams["@ID"].Value == 0)
return headers;
DataTable oTable = sql.ExecuteSingleResultSetSQLQuery(Q, oParams);
if (oTable.Rows.Count == 0)
return headers;
//each unique header has a list of the details
long CurrId = -1;
long id;
AuditFields fields = null;
foreach (DataRow r in oTable.Rows) {
id = DataUtils.LongZeroIfNull(r["ID"]);
if (id != CurrId) {
AuditLog a = new AuditLog(r, false);
headers.Add(a);
fields = a.mFieldChanges;
CurrId = id;
}
fields.Add(r);
}
return headers;
}
/// <summary>
/// you can supply your own SQL object and force the audit to go to that database. Used by GT
/// </summary>
/// <param name="status"></param>
/// <param name="CompletionMsg"></param>
/// <param name="SQL"></param>
/// <returns></returns>
public long Save(CompletionStatus status, string CompletionMsg, SqlUtil SQL)
{
//save it - just in case of recovery
mCompletionStatus = status;
mCompletionMsg = CompletionMsg;
string sql;
SqlParameterCollection oParameters = new SqlCommand().Parameters;
long ID = -1;
oParameters.AddWithValue("@iDataType", DataUtils.DBNullIfNull(mDataType));
oParameters.AddWithValue("@iDataID", DataUtils.DBNullIfNull(mDataID));
oParameters.AddWithValue("@iCorrespondenceID", DataUtils.DBNullIfNull(mCorrespondenceID));
oParameters.AddWithValue("@iEventID", DataUtils.DBNullIfNull(mEventID));
oParameters.AddWithValue("@iEventDescription", DataUtils.DBNullIfNull(mEventDescription));
oParameters.AddWithValue("@iCompletionMsg", DataUtils.DBNullIfNull(CompletionMsg));
oParameters.AddWithValue("@iCompletionStatus", DataUtils.DBNullIfNull(status));
if (mLogTime == DateTime.MinValue)
mLogTime = DateTimeUtility.ServerDateNoRefresh();
oParameters.AddWithValue("@iTime", mLogTime);
sql = "set ansi_warnings off;INSERT INTO AuditHeader (DataType,DataID,CorrespondenceID,EventID,EventDescription,CompletionMsg,CompletionStatus,LogTime)"
+ " VALUES (@iDataType,@iDataID,@iCorrespondenceID,@iEventID,@iEventDescription,@iCompletionMsg,@iCompletionStatus,@iTime)";
sql += "; select @@IDENTITY as ID";
DataTable oTable;
try {
oTable = SQL.ExecuteSingleResultSetSQLQuery(sql, oParameters);
} catch (Exception x) {
if (mRecover && SqlRecover.SaveForRecovery(x, true, this, "auditheader"))
return 0;
else
throw new Exception("sql error", x);
}
if (oTable.Rows.Count == 0)
return -1;
ID = DataUtils.LongZeroIfNull(oTable.Rows[0]["ID"]);
if (ID < 1)
return -1;
if (mFieldChanges != null) {
string o;
string n;
oParameters = new SqlCommand().Parameters;
foreach (string field in mFieldChanges.UpdateList.Keys) {
mFieldChanges.GetAuditValue(field, out o, out n);
oParameters.AddWithValue("@iAuditID", ID);
oParameters.AddWithValue("@iField", DataUtils.DBNullIfNull(field));
oParameters.AddWithValue("@iOldVal", DataUtils.DBNullIfNull(o));
oParameters.AddWithValue("@iNewVal", DataUtils.DBNullIfNull(n));
sql = "set ansi_warnings off;INSERT INTO AuditDetail(AuditID,Field, OldValue,NewValue) " +
" VALUES (@iAuditID,@iField, @iOldVal,@iNewVal);";
SQL.ExecuteNoResultSetSQLQuery(sql, oParameters);
oParameters.Clear();
}
}
return ID;
}
//Seperate Connection is not used?
public long Save(CompletionStatus status, string CompletionMsg, bool SeperateConnection)
{
SqlUtil sql = new SqlUtil(mDatabase);
sql.BypassTransaction = true;
return Save(status, CompletionMsg, sql);
}
public void DeleteAudit()
{
SqlParameterCollection parms = new SqlCommand().Parameters;
parms.AddWithValue("@ID", this.ID);
SqlUtil sql = new SqlUtil(mDatabase);
sql.ExecuteNoResultSetSQLQuery("delete from auditdetail where auditid = @ID", parms);
parms = new SqlCommand().Parameters;
parms.AddWithValue("@ID", this.ID);
sql.ExecuteNoResultSetSQLQuery("delete from auditheader where id = @ID", parms);
}
/// <summary>
/// get the audit list for datatypes supplied and optionaly adds in the audits for any correspondeces
/// </summary>
/// <param name="type"></param>
/// <param name="DataId"></param>
/// <param name="IncludeCorrespondenceLog"></param>
/// <param name="NoErrors">do not include audit errors</param>
/// <param name="sql">An sqlutil object of the database that conatin the audit table you want to search since audit exists in almost every DB</param>
/// <returns></returns>
public static List<AuditLog> GetAuditListByDataType(string[] type, long DataId, bool IncludeCorrespondenceLog, bool NoErrors, SqlUtil sql)
{
SqlParameterCollection oParameters = new SqlCommand().Parameters;
oParameters.AddWithValue("@ID", DataId);
string TypeList = String.Join("','", type);
string query;
string noError;
if (NoErrors)
noError = " and h1.completionstatus > -1 ";
else
noError = "";
if (IncludeCorrespondenceLog) {
query = "select h1.* into #t from auditheader h1 with (readpast) " +
"where h1.DataType in ('" + TypeList + "') and h1.DataId = @ID " + noError +
"select * from auditheader h1 with (readpast) " +
"left join auditdetail d on h1.id = d.auditid " +
"where h1.id in (select id from #t) " +
"union " +
"select h1.*, d.* from auditheader h1 with (nolock) " +
"left join auditdetail d with (nolock) on h1.id = d.auditid " +
"where h1.correspondenceid in (select distinct correspondenceid from #t where correspondenceid > 0) " +
"order by h1.id asc " +
"drop table #t";
} else {
query = "select * from auditheader h1 with (readpast) " +
"left join auditdetail d on h1.id = d.auditid " +
"where h1.DataType in ('" + TypeList + "') and h1.DataId = @ID " + noError +
"order by h1.id asc";
}
return GetAuditList(query, oParameters, sql, sql.Database);
}
