/// <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);
        }
Example #2
0
        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;
        }
Example #3
0
        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;
        }
Example #4
0
 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);
 }
Example #5
0
        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;
            }
        }
Example #7
0
        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;
        }
Example #8
0
        /// <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;
        }
Example #9
0
 //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);
 }
Example #10
0
        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);
        }
Example #11
0
        /// <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);
        }