Esempio n. 1
0
        //         10        20        30
        // 123456789012345678901234567890123456
        // 61FFB6F0-AE23-4588-A673-BA6674A7EB17

        public static void LogActivity(string pUserID, string pPageName, LogActivityCategory pCategory, string pEventName, long pTimeElapsed, string pDetails, DbTask pDbt)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("INSERT INTO [ActivityLog] ([UserID],[PageName],[Category],[EventName],[TimeElapsed],[Details],[ConnString]) ");
            sb.AppendLine("VALUES (");
            sb.Append(DbUtility.DelimitText(pUserID) + ",");
            sb.Append(DbUtility.DelimitText(pPageName) + ",");
            sb.Append("'" + pCategory.ToString() + "',");
            sb.Append("'" + pEventName + "',");
            sb.Append(DbUtility.DelimitNonText(pTimeElapsed) + ",");
            sb.Append(DbUtility.DelimitText(pDetails) + ",");
            sb.Append(DbUtility.DelimitText(pDbt.ConnectionName));
            sb.Append(")");
            string queryString = sb.ToString();
            var    dbt         = new DbTask("Aw_ActivityLog", queryString);

            dbt.DbTaskSummaryOn = false;
            DbWorker.ExecuteQuery(ref dbt, delegate(int pCount, Exception pExc)
            {
                if (pExc != null)
                {
                    Debug.WriteLine("Error Writing ActivityLog");
                    Debug.WriteLine("Sql=");
                    Debug.WriteLine(queryString);
                    Debug.WriteLine(DBMaria.PrettyException(pExc));
                }
            });
        }
Esempio n. 2
0
        public static bool PingConnection(string pConnString)
        {
            var Dbt = new DbTask(pConnString);

            ConnectionOpen(pConnString, ref Dbt);
            return(Dbt.ConnectionWasSuccessful);
        }
Esempio n. 3
0
        public static void ExecuteQuery(ref DbTask dbt, QueryFinished pEvent)
        {
            var sw = TimerStart();

            try
            {
                dbt.DbQueryType = DbQueryType.ExecQuery;
                SqlParameterSetup(ref dbt);
                if (ConnectionNotOpenHappened(ref dbt, MethodBase.GetCurrentMethod()))
                {
                    pEvent(0, new Exception("ExecuteQuery not attempted. Connection was not open."));
                }
                int count = dbt.SqlCmd.ExecuteNonQuery();
                //dbt.DebugExtendedInfo = string.Format("Rows affected={0}", count);
                pEvent(count, null);
            }
            catch (Exception exc1)
            {
                dbt.ExceptionQuery = exc1;
                pEvent(0, exc1);
            }
            finally
            {
                TimerEnd(dbt, sw);
                DebugSql(dbt);
                if (dbt.SqlCmd.Connection.State == ConnectionState.Open)
                {
                    dbt.SqlCmd.Connection.Close();
                }
                dbt.SqlCmd.Connection.Dispose();
            }
        }
Esempio n. 4
0
        public static void GetDataTable(ref DbTask dbt, DataTableArrived pEvent)
        {
            DataTable dtblReturn = new DataTable();

            DbWorker.GetDataReader(ref dbt, delegate(SqlDataReader reader, Exception exc)
            {
                dtblReturn.Load(reader);
            });
            pEvent(dtblReturn, null);
        }
Esempio n. 5
0
        private static bool ConnectionNotOpenHappened(ref DbTask pDbt, MethodBase pMB)
        {
            if (pDbt.ConnectionWasSuccessful == true)
            {
                return(false);
            }

            if (pDbt.ConnectionWasSuccessful == false)
            {
                // TODO: ActivityLog entry
                Debug.WriteLine("Connection Did Not Open No Database Operation Attempted");
                Debug.WriteLine(DBMaria.PrettyMethodBase(pMB));
                Debug.WriteLine(DBMaria.PrettyException(pDbt.ConnectionFailedException));
                return(true);
            }

            return(true);
        }
Esempio n. 6
0
        private static void SqlParameterSetup(ref DbTask dbt)
        {
            SqlConnection conn = ConnectionOpen(dbt.ConnectionName, ref dbt);

            if (ConnectionNotOpenHappened(ref dbt, MethodBase.GetCurrentMethod()))
            {
                return;
            }

            dbt.SqlCmd.Connection = conn;
            if (dbt.Sql != null)
            {
                dbt.SqlCmd.CommandText = dbt.Sql;
                dbt.SqlCmd.CommandType = DbUtility.IsAdHocSql(dbt.Sql) == false ? CommandType.StoredProcedure : CommandType.Text;
            }
            bool parametersExist = true;
            var  parmSP          = dbt.QueryParameters;

            if (parmSP == null)
            {
                parametersExist = false;
            }
            if (parmSP.Length == 0)
            {
                parametersExist = false;
            }
            if (!parametersExist)
            {
                return;
            }
            if (parmSP.Length % 2 != 0)
            {
                throw new Exception("parameters are not paired");
            }

            for (int countParams = 0; countParams < parmSP.Length; countParams += 2)
            {
                SqlParameter param = dbt.SqlCmd.CreateParameter();
                param.ParameterName = parmSP[countParams].ToString();
                param.Direction     = ParameterDirection.Input;
                param.Value         = parmSP[countParams + 1];
                dbt.SqlCmd.Parameters.Add(param);
            }
        }
Esempio n. 7
0
        private static SqlConnection ConnectionOpen(string pConnString, ref DbTask pDbt)
        {
            SqlConnection conn = null;

            try
            {
                string connString = System.Configuration.ConfigurationManager.ConnectionStrings[pConnString].ConnectionString;
                conn = new SqlConnection(connString);
                conn.Open();
                pDbt.ConnectionWasSuccessful   = true;
                pDbt.ConnectionFailedException = null;
            }
            catch (Exception exc1)
            {
                Debug.WriteLine("ConnectionOpen exception =>" + exc1.Message);
                pDbt.ConnectionWasSuccessful   = false;
                pDbt.ConnectionFailedException = exc1;
            }
            return(conn);
        }
Esempio n. 8
0
        public static void GetDataReader(ref DbTask dbt, DataReaderArrived pEvent)
        {
            SqlDataReader rdr = null;
            var           sw  = TimerStart();

            try
            {
                dbt.DbQueryType = DbQueryType.DataReader;
                SqlParameterSetup(ref dbt);
                if (ConnectionNotOpenHappened(ref dbt, MethodBase.GetCurrentMethod()))
                {
                    pEvent(null, new Exception("GetDataReader ignored. Connection never opened."));
                    Debug.WriteLine("Connection Blewup!");
                    return;
                }
                if (dbt.LogDataCapture)
                {
                    var dtblCapture = new DataTable();
                    var rdrTemp     = dbt.SqlCmd.ExecuteReader();
                    dtblCapture.Load(rdrTemp);
                    rdrTemp.Close();
                    if (dbt.LogCaptureDataset == null)
                    {
                        dbt.LogCaptureDataset = new DataSet();
                    }

                    dbt.LogCaptureDataset.Tables.Add(dtblCapture);
                }
                rdr        = dbt.SqlCmd.ExecuteReader();
                dbt.Schema = rdr.GetSchemaTable();
                StringBuilder sbFieldList = new StringBuilder();
                for (int i = 0; i < rdr.FieldCount; i++)
                {
                    sbFieldList.Append(rdr.GetName(i) + "|");
                }
                dbt.DebugExtendedInfo = string.Format("ColCount={0}; {1}", rdr.FieldCount, sbFieldList.ToString());
                try
                {
                    pEvent(rdr, null);
                }
                catch (Exception excDel)
                {
                    dbt.ExceptionDelegate = excDel;
                }
            }
            catch (Exception exc1)
            {
                dbt.ExceptionQuery = exc1;
                try
                {
                    pEvent(null, exc1);
                }
                catch (Exception excDel)
                {
                    dbt.ExceptionDelegate = excDel;
                    if (excDel.Message == "Invalid attempt to read when no data is present.")
                    {
                        // TODO is this best approach
                        // Don't throw this error just make it a warning
                        //dbt.ExceptionDelegate = new Exception("DataReader was empty but code attempted to read data", excDel);
                        dbt.ExceptionDelegate = null;
                        Debug.WriteLine("Warning DataReader was empty but code attempted to read data");
                    }
                }
            }
            finally
            {
                TimerEnd(dbt, sw);
                DebugSql(dbt);
                if (rdr != null)
                {
                    rdr.Dispose();
                }
                if (dbt.SqlCmd.Connection.State == ConnectionState.Open)
                {
                    dbt.SqlCmd.Connection.Close();
                }
                dbt.SqlCmd.Connection.Dispose();
            }
        }
Esempio n. 9
0
 private static void TimerEnd(DbTask pDbt, Stopwatch psw)
 {
     pDbt.SW = psw;
     psw.Stop();
 }
Esempio n. 10
0
        public static T ExecuteScalar <T>(ref DbTask dbt)
        {
            var sw = TimerStart();

            try
            {
                dbt.DbQueryType = DbQueryType.ExecScalar;
                SqlParameterSetup(ref dbt);
                if (ConnectionNotOpenHappened(ref dbt, MethodBase.GetCurrentMethod()))
                {
                    return(default(T));
                }

                object objReturn = dbt.SqlCmd.ExecuteScalar();
                if (objReturn == null)
                {
                    dbt.DebugExtendedInfo = dbt.ScalarNull.ToString() + " scalar returned null value";
                    return((T)dbt.ScalarNull);
                }

                //bool conversionComplete = false;
                if (typeof(T) == typeof(System.Boolean))
                {
                    bool temp = false;
                    bool.TryParse(objReturn.ToString(), out temp);
                    objReturn = temp;
                }

                if (typeof(T) == typeof(System.Int32))
                {
                    Int32 temp = 0;
                    Int32.TryParse(objReturn.ToString(), out temp);
                    objReturn = temp;
                }

                if (typeof(T) == typeof(System.Int64))
                {
                    Int64 temp = 0;
                    Int64.TryParse(objReturn.ToString(), out temp);
                    objReturn = temp;
                }

                if (typeof(T) == typeof(System.DateTime))
                {
                    DateTime temp = DateTime.MinValue;
                    DateTime.TryParse(objReturn.ToString(), out temp);
                    objReturn = temp;
                }

                if (typeof(T) == typeof(System.String))
                {
                    objReturn = objReturn.ToString();
                }

                dbt.DebugExtendedInfo = objReturn.ToString();
                return((T)objReturn);
            }
            catch (System.NullReferenceException)
            {
                dbt.DebugExtendedInfo = dbt.ScalarNull.ToString() + " scalar returned null reference";
                return((T)dbt.ScalarNull);
            }
            catch (Exception exc1)
            {
                dbt.ExceptionQuery    = exc1;
                dbt.DebugExtendedInfo = default(T).ToString() + " scalar threw exception returned default type";
                return(default(T));
            }
            finally
            {
                TimerEnd(dbt, sw);
                DebugSql(dbt);
                if (dbt.SqlCmd.Connection.State == ConnectionState.Open)
                {
                    dbt.SqlCmd.Connection.Close();
                }

                dbt.SqlCmd.Connection.Dispose();
            }
        }
Esempio n. 11
0
        public static void DebugSql(DbTask pDbt)
        {
            //if (ConnectionNotOpenHappened(ref pDbt,MethodBase.GetCurrentMethod())) return;

            var DBDeets = new DbTaskSummary {
                QueryRaw = pDbt.Sql
            };

            //if (pDbt.Sql.ToLower().IndexOf("insert into [activitylog]") > -1) return;

            if (pDbt.Sql != null & DbUtility.IsAdHocSql(pDbt.Sql))
            {
                DBDeets.QueryType = TypeOfQuery.AdHocSQL;
            }

            if (DBDeets.QueryType == TypeOfQuery.AdHocSQL)
            {
                DBDeets.QueryRaw              = pDbt.Sql;
                DBDeets.QueryPrettyFormatted  = DBMaria.PrettySQL(pDbt.Sql);
                DBDeets.QueryAsExecutableText = DBDeets.QueryPrettyFormatted;
            }
            else
            {
                DBDeets.QueryType = TypeOfQuery.SProc;

                // TODO function in Utility
                StringBuilder sbSprocCall = new StringBuilder();
                sbSprocCall.Append("EXEC " + pDbt.SqlCmd.CommandText + " ");
                foreach (SqlParameter parm in pDbt.SqlCmd.Parameters)
                {
                    sbSprocCall.AppendFormat(" {0}={1}, ", parm.ParameterName,
                                             DbUtility.DelimitObject(parm.Value));
                }
                string sprocCallExpanded = sbSprocCall.ToString();
                // remove the trailing comma
                if (sprocCallExpanded.EndsWith(", "))
                {
                    sprocCallExpanded = sprocCallExpanded.Substring(0, sprocCallExpanded.Length - 2);
                }
                DBDeets.QueryPrettyFormatted  = sprocCallExpanded;
                DBDeets.QueryAsExecutableText = DBDeets.QueryPrettyFormatted;
            }

            if (pDbt.IsSqlCommand)
            {
                DBDeets.QueryType             = TypeOfQuery.CommandObject;
                DBDeets.QueryAsExecutableText = pDbt.SqlCmd.TSqlFromFromCommand();
            }
            DBDeets.MthdBsPretty = DBMaria.PrettyMethodBase(pDbt.MethodBase);


            // timing transfer
            DBDeets.MilliSecondsElapsed = pDbt.SW.ElapsedMilliseconds;
            DBDeets.TimingPretty        = DBMaria.PrettyDBtTiming(pDbt);

            if (pDbt.DbQueryType == DbQueryType.DataReader || pDbt.DbQueryType == DbQueryType.DataTable)
            {
                // TODO: may need tweaking with multiple result sets
                DBDeets.BlackBoxAsText = DBMaria.PrettyDataTable(pDbt.LogCaptureDataset.Tables[0], Schema: pDbt.Schema);
                DBDeets.BlackBox       = pDbt.LogCaptureDataset;
            }

            DBDeets.RawExceptionQuery    = pDbt.ExceptionQuery;
            DBDeets.RawExceptionDelegate = pDbt.ExceptionDelegate;
            DBDeets.ExceptionPretty      = string.Empty;
            if (DBDeets.RawExceptionQuery != null)
            {
                DBDeets.ExceptionPretty = "QueryException=" + DBMaria.PrettyException(pDbt.ExceptionQuery);
            }
            if (DBDeets.RawExceptionDelegate != null)
            {
                DBDeets.ExceptionPretty += System.Environment.NewLine + "DelegateException=" + DBMaria.PrettyException(pDbt.ExceptionDelegate);
            }
            Debug.WriteLine(DBMaria.PrettyDBDeets(DBDeets));
        }
Esempio n. 12
0
 public static string PrettyDBtTiming(DbTask pDbt)
 {
     return(string.Format("{0} took {1}ms. Extended Info={2}", pDbt.DbQueryType.ToString(),
                          pDbt.SW.ElapsedMilliseconds, pDbt.DebugExtendedInfo));
 }