Beispiel #1
0
        public DBDataTable(string process, string sql, string connectionKey)
        {
            try
            {
                GetData(sql, connectionKey);
            }
            catch (Exception ex)
            {
                var fromattedSQL = FormatSQL(sql, connectionKey);

                ex.Data.Add("SQL", fromattedSQL);

                var logEntry = "SqlDBDataTable.Constructor(): Error fetching data\r\n" + fromattedSQL + "\r\n" + ex.Message + "\r\n";

                Audit.HandleError(process, logEntry);

                Log4Net.LogError(sql, ex, connectionKey);

                throw new Exception("SQL Error in " + fromattedSQL, ex);
            }
        }
Beispiel #2
0
        public static void Transaction(ref string[] sql, string connectionKey)
        {
            var connection = StartUp.OpenConnection(connectionKey);

            SqlTransaction Trans;
            var            DBCommand = new SqlCommand();

            DBCommand.Connection = connection;

            Trans = connection.BeginTransaction();
            DBCommand.CommandTimeout = StartUp.TimeOut;
            DBCommand.Transaction    = Trans; int i = 0;

            try
            {
                for (i = 0; i < sql.Length; i++)
                {
                    if (sql[i] != null)
                    {
                        DBDataReader.MessageLog("SQL: " + sql[i]);
                        DBCommand.CommandText = sql[i];
                        DBCommand.ExecuteNonQuery();
                        Log4Net.LogInfo(sql[i], connectionKey);
                    }
                }

                Trans.Commit();
            }
            catch (Exception ex)
            {
                Trans.Rollback();
                Audit.HandleError("Sql Error", "SqlDML.Transaction(): Error running sql\r\n" + sql[i] + "\r\n" + ex.Message);
                Log4Net.LogError(sql[i], ex, connectionKey);
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Beispiel #3
0
        public DBDataReader(string process, string sql, string connectionKey, Boolean singleRow)
        {
            var formattedSql = DBDataTable.FormatSql(sql);

            Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSql));
            Log4Net.LogDebug(formattedSql, connectionKey);

            Connection = StartUp.OpenConnection(connectionKey);

            DBCommand = new SqlCommand(formattedSql, Connection);
            DBCommand.CommandTimeout = StartUp.TimeOut;
            ReturnValue = string.Empty;

            try
            {
                DBReader = DBCommand.ExecuteReader(CommandBehavior.SingleRow);

                DBReader.Read();

                for (var i = 0; i < DBReader.FieldCount; i++)
                {
                    ReturnValue += DBReader[i] + "|";
                }
            }
            catch
            {
                try
                {
                    if (Connection.State == ConnectionState.Closed)
                    {
                        Connection.Open();
                    }
                    DBReader = DBCommand.ExecuteReader(CommandBehavior.SingleRow);
                }
                catch (Exception ex)
                {
                    LogException(formattedSql, String.Empty, ex, process);
                }
            }
        }
Beispiel #4
0
        public void GetData(string sql, string connectionKey)
        {
            var formattedSQL = FormatSQL(sql, connectionKey);

            Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSQL));
            Log4Net.LogDebug(formattedSQL, connectionKey);

            var connectionString = StartUp.GetConnectionString(connectionKey);

            DBTable = new DataTable();

            using (var connection = new SqlConnection(connectionString))
            {
                //var conn = new ProfiledDbConnection(connection, MiniProfiler.Current)

                var dbCommand = new SqlCommand(sql, connection);
                dbCommand.CommandTimeout = StartUp.TimeOut;
                var dbAdapter = new SqlDataAdapter(dbCommand);

                //var prdataAdapter = new ProfiledDbDataAdapter(DBAdapter);
                DBTable.BeginLoadData();
                dbAdapter.Fill(DBTable);
                DBTable.EndLoadData();

                DBTable.CaseSensitive = false;

                connection.Close();
            }

            DBDataSet.LogResultCount(DBTable);

            #region log exception
            if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
            {
                DBDataReader.MessageLog("SQL: " + formattedSQL);
            }
            #endregion log exception
        }
Beispiel #5
0
        public DBDataReader(string process, string sql, string connectionKey)
        {
            var formattedSql = DBDataTable.FormatSql(sql);

            try
            {
                Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSql));
                Log4Net.LogDebug(formattedSql, connectionKey);

                Connection = StartUp.OpenConnection(connectionKey);

                DBCommand = new SqlCommand(formattedSql, Connection);
                DBCommand.CommandTimeout = StartUp.TimeOut;

                DBReader = DBCommand.ExecuteReader(CommandBehavior.Default);
            }
            catch (Exception ex)
            {
                LogException(formattedSql, String.Empty, ex, process);
            }

            LogInfo(sql, "Reader");
        }
Beispiel #6
0
        public void GetData(string sql, string connectionKey)
        {
            var formattedSql = DBDataTable.FormatSql(sql);

            Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSql));
            Log4Net.LogDebug(formattedSql, connectionKey);

            var connectionString = StartUp.GetConnectionString(connectionKey);

            using (var connection = new SqlConnection(connectionString))
            {
                var dataAdapter = new SqlDataAdapter(formattedSql, connection);

                dataAdapter.Fill(DBDataset);
                DBDataset.CaseSensitive = false;
            }

            LogResultCount(DBDataset);

            if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
            {
                DBDataReader.MessageLog("SQL: " + sql);
            }
        }
Beispiel #7
0
        public DBDataSet(string process, string sql, string connectionKey)
        {
            try
            {
                GetData(sql, connectionKey);

                //if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate"))
                //{
                //	StartUp.FeedsAudit.AuditDetailInsert(process, "OK", "Retrieved data: " + sql);
                //}
            }
            catch (Exception ex)
            {
                var logEntry = "SqlDBDataTable.Constructor(): Error fetching data\r\n" + sql + "\r\n" + ex.Message + "\r\n";

                Audit.HandleError(process, logEntry);

                Log4Net.LogError(sql, ex, connectionKey);

                var exp = new Exception(ex.Message + "\n" + sql, ex);

                throw exp;
            }
        }
Beispiel #8
0
        public static void RunSQL(string process, string sql, string connectionKey)
        {
            sql = "\r\n" + sql.Replace("@", "\r\n @") + "\r\n";

            var connection = StartUp.OpenConnection(connectionKey);

            var dbCommand = new SqlCommand(sql);

            dbCommand.CommandTimeout = 24000;
            dbCommand.Connection     = connection;

            try
            {
                Debug.WriteLine(dbCommand.CommandText);

                dbCommand.ExecuteNonQuery();

                // sucessful log to file
                if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
                {
                    StartUp.FeedsAudit.AuditDetailInsert(process, "OK", "Ran statement: " + sql);
                }

                Log4Net.LogDebug(sql, connectionKey);
            }
            catch (Exception ex)
            {
                #region log exception to file
                var logEntry = "DML.RunSQL(): Error running SQL\r\n" + sql + "\r\n " + ex.Message + "\r\n";

                if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
                {
                    Audit.HandleError(process, logEntry);
                }
                else
                {
                    try
                    {
                        DBDataReader.MessageLog(logEntry + "\t" + DateTime.Now.ToLongDateString() + "\t" + DateTime.Now.ToLongTimeString());
                    }
                    catch { }
                }
                #endregion log excption to file

                Log4Net.LogError(sql, ex, connectionKey);

                var exp = new Exception(ex.Message + "\n" + sql, ex);

                throw exp;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            // sucessful log to file
            if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
            {
                DBDataReader.MessageLog("SQL: " + sql);
            }

            // var x = DateTime.Now;
            // var y = "Ran sql: " + sql + "\r\n";
            //StartUp.Create(sql);
        }
Beispiel #9
0
        public static object RunScalarSQL(string process, string sql, string connectionKey)
        {
            object result;

            sql = "\r\n" + sql.Replace("@", "\r\n @") + "\r\n";

            var connection = StartUp.OpenConnection(connectionKey);

            var dbCommand = new SqlCommand(sql);

            dbCommand.CommandTimeout = 24000;
            dbCommand.Connection     = connection;
            //dbCommand.Parameters.Add("@NextValue", SqlDbType.Int).Direction = ParameterDirection.Output;

            try
            {
                result = dbCommand.ExecuteScalar();

                // sucessful log to file
                if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
                {
                    StartUp.FeedsAudit.AuditDetailInsert(process, "OK", "Ran statement: " + sql);
                }

                Log4Net.LogDebug(sql, connectionKey);
            }
            catch (Exception ex)
            {
                #region log exception to file
                var logEntry = "DML.RunSQL(): Error running SQL\r\n" + sql + "\r\n " + ex.Message + "\r\n";

                if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
                {
                    Audit.HandleError(process, logEntry);
                }
                else
                {
                    try
                    {
                        DBDataReader.MessageLog(logEntry + "\t" + DateTime.Now.ToLongDateString() + "\t" + DateTime.Now.ToLongTimeString());
                    }
                    catch { }
                }
                #endregion log excption to file

                Log4Net.LogError(sql, ex, connectionKey);

                var exp = new Exception(ex.Message + "\n" + sql, ex);

                throw exp;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            // sucessful log to file
            if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
            {
                DBDataReader.MessageLog("SQL: " + sql);
            }

            // var x = DateTime.Now;
            // var y = "Ran sql: " + sql + "\r\n";
            //StartUp.Create(sql);

            return(result);
        }