// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public bool UploadFile(DBSQLViewModel sender, byte[] buffer, string SQL) { bool Result = true; try { // Initialize SqlCommand object for insert. using (SqlCommand cmd = new SqlCommand(SQL, _Connection)) { // We are passing the image byte data as SQL parameters. //cmd.Parameters.Add(new SqlParameter("@DocData", (object)buffer)); SqlParameter dbParameter = new System.Data.SqlClient.SqlParameter("@DocData", SqlDbType.VarBinary); dbParameter.Size = (int)MaxUploadSize * 1024 * 1024; // sets the maximum size, in bytes, of the data within the column. dbParameter.Value = (object)buffer; cmd.Parameters.Add(dbParameter); // Open connection and execute insert query. Result = (cmd.ExecuteNonQuery() == 1); }; } catch (Exception ex) { sender.LastError = ex.Message; sender.LastException = ex; sender.LastQuery = SQL; Result = false; }; return(Result); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public DbCommand NewCommand(DBSQLViewModel sender, string SQL) { DbCommand Result = null; Result = new SqlCommand(SQL, _Connection); return(Result); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public DbConnection Open(DBSQLViewModel sender, DBType dbType, string ConnectionString) { _Connection = new SqlConnection(ConnectionString); _Connection.Open(); return(_Connection); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public static long BeginWriteDB(DBSQLViewModel dBSQLViewModel, AuditTrailViewModel sender, AuditTrail message) { long Result = -1; try { Result = DB_SQL.InsertGetPK(dBSQLViewModel, message); if (!string.IsNullOrEmpty(dBSQLViewModel.LastError)) { Debug.WriteLine("AuditTrailViewWriter LastError: " + dBSQLViewModel.LastError + Environment.NewLine + "AuditTrailViewWriter LastQuery: " + dBSQLViewModel.LastQuery); Log.Write(new AuditTrail { Message = "AuditTrailViewWriter LastError: " + dBSQLViewModel.LastError + Environment.NewLine + "AuditTrailViewWriter LastQuery: " + dBSQLViewModel.LastQuery }); } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("AuditTrailViewWriter: " + ex.Message); Log.Write(new AuditTrail { Message = "AuditTrailViewWriter: " + ex.Message }); }; return(Result); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public DbDataReader NewReader(DBSQLViewModel sender, DbCommand dbCommand) { DbDataReader Result = null; Result = (dbCommand as SqlCommand).ExecuteReader(); return(Result); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public static void WriteLineDB(DBSQLViewModel dBSQLViewModel, AuditTrailViewModel sender, AuditTrail message) { if (dBSQLViewModel == null) { return; } try { DB_SQL.Insert(dBSQLViewModel, message); if (!string.IsNullOrEmpty(dBSQLViewModel.LastError)) { Debug.WriteLine("AuditTrailViewModel LastError: " + dBSQLViewModel.LastError + Environment.NewLine + "AuditTrailViewModel LastQuery: " + dBSQLViewModel.LastQuery); //Log.Debug(new AuditTrail //{ // Message = "AuditTrailViewWriter LastError: " + dBSQLViewModel.LastError + Environment.NewLine // + "AuditTrailViewWriter LastQuery: " + dBSQLViewModel.LastQuery //}); } ; } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("AuditTrailViewModel: " + ex.Message); //Log.Debug(new AuditTrail //{ // Message = "AuditTrailViewWriter: " + ex.Message //}); }; }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public static ObservableCollection <AuditTrail> LoadAuditTrail(DBSQLViewModel dBSQLViewModel, AuditTrailViewModel sender, bool Filtered = true, long MaxRecords = 500) { var AuditTrail = new List <AuditTrail>(); string Where = GetWhere(dBSQLViewModel.DBType, sender); string SQL = DB_SQL.SelectAll(dBSQLViewModel.DBType, "AuditTrail", Where, MaxRecords); if (dBSQLViewModel != null) { try { AuditTrail = DB_SQL.Query <AuditTrail>(dBSQLViewModel, SQL); if (AuditTrail == null) { AuditTrail = new List <AuditTrail>(); } ; } catch (Exception ex) { Debug.WriteLine(ex.Message); }; } ; return(new ObservableCollection <AuditTrail>(AuditTrail)); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public DbDataAdapter NewDataAdapter(DBSQLViewModel sender, string SQL) { DbDataAdapter Result = null; Result = new SqlDataAdapter(SQL, _Connection); return(Result); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public DbDataAdapter NewDataAdapter(DBSQLViewModel sender, DbCommand dbCommand) { DbDataAdapter Result = null; Result = new SqlDataAdapter(dbCommand as SqlCommand); return(Result); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public void OpenDB(HttpRequest request) { if (DB_SQL._ViewModel == null || !DB_SQL._ViewModel.CheckConnection()) { string ConnectionString = GetConnectionString("ZeConnectionString"); //#if DEBUG if (string.IsNullOrEmpty(ConnectionString)) { //if (request.Host.ToString().ToUpper().Contains("DEV") || request.Host.ToString().ToUpper().Contains("LOCALHOST")) //{ // string Server = "sql6005.site4now.net"; // string DBase = "DB_A44F11_StockAPPro2dev"; // string User = "******"; // string Password = ""; // ConnectionString = $"Data Source={Server};Initial Catalog={DBase};Persist Security Info=True;User ID={User};Password={Password};MultipleActiveResultSets=True"; //} //else { string Server = "sql6007.site4now.net"; string DBase = "DB_A44F11_HorsDA"; string User = "******"; string Password = "******"; ConnectionString = $"Data Source={Server};Initial Catalog={DBase};Persist Security Info=True;User ID={User};Password={Password};MultipleActiveResultSets=True"; }; } ; //#endif if (string.IsNullOrEmpty(ConnectionString)) { MainViewModel.Current.Connection.LastError = "No ConnectionString ..."; return; } ; Connection = new DBSQLViewModel(new SQLServerEngine()); DB_SQL._ViewModel = Connection; Log.Write("", $"{ConnectionString} {(Connection.Open(ConnectionString, true) ? "OK" : "KO")}"); //CleanAuditTrail(); //ToDo: UpdateSessions(); //if (!MainViewModel.Current.CheckDB()) //{ // Log.Write(new AuditTrail() // { // Application = "wsStockAPPro", // Message = "Version de base de données incompatible!", // Level = ErrorLevel.Critical, // }); //}; } ; }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public DataTable QuickQueryDataTable(DBSQLViewModel sender, string SQL, bool NoSchema = true) { if (!NoSchema) { throw new NotSupportedException(); } ; return(ConvertDataReader2DataTable(sender, SQL)); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public DbTransaction Transaction(DBSQLViewModel sender, [CallerMemberName] string CallerName = null) { if (DB_SQL.DoTransactions) { return(_Connection.BeginTransaction()); } else { return(null); }; }
public bool Commit(DBSQLViewModel sender, DbTransaction dbTransaction, [CallerMemberName] string CallerName = null) { if (DB_SQL.DoTransactions) { var t = (dbTransaction as SqlTransaction); t.Commit(); return(true); } else { return(true); }; }
public DBAndFileAuditTrailWriter(DBSQLViewModel dBSQLViewModel, string outputFile, bool DebugOutput = true) { _dBSQLViewModel = dBSQLViewModel; IsDB = AuditTrailWriterHelper.CreateTable(_dBSQLViewModel); if (!string.IsNullOrEmpty(outputFile)) { fileAuditTrailWriter = new FileAuditTrailWriter(outputFile, DebugOutput); } ; this.outputFile = outputFile ?? null; this.DebugOutput = DebugOutput; }
public void Init_AT_PG() { string server = "postgresql-zpf.alwaysdata.net"; string db = "zpf_postgresql"; string user = "******"; string password = "******"; DBSQLViewModel AuditTrailConnection = null; AuditTrailConnection = new DBSQLViewModel(new PostgreSQLEngine()); string AT_ConnectionString = DB_SQL.GenConnectionString(DBType.PostgreSQL, server, db, user, password); AuditTrailConnection.Open(AT_ConnectionString, true); AuditTrailViewModel.Current.Init(new DBAndFileAuditTrailWriter(AuditTrailConnection, string.Format(@"{0}", AuditTrailFileName))); AuditTrailViewModel.Current.MaxLines = 5; AuditTrailViewModel.Current.Clean(); // prend en compte uniquement nombre ligne Assert.AreEqual(true, AuditTrailConnection.CheckConnection()); }
public void Init_AT_SQLServer2() { string server = "SQL6005.site4now.net"; string db = "DB_A44F11_StockAPPro2dev"; string user = "******"; string password = "******"; DBSQLViewModel AuditTrailConnection = null; AuditTrailConnection = new DBSQLViewModel(new SQLServerEngine()); string AT_ConnectionString = DB_SQL.GenConnectionString(DBType.SQLServer, server, db, user, password); AuditTrailConnection.Open(AT_ConnectionString, true); AuditTrailViewModel.Current.Init(new DBAndFileAuditTrailWriter(AuditTrailConnection, string.Format(@"{0}", AuditTrailFileName))); // AuditTrailViewModel.Current.MaxLines = 5; //AuditTrailViewModel.Current.Clean(); // prend en compte uniquement nombre ligne Assert.AreEqual(true, AuditTrailConnection.CheckConnection()); }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public bool Close(DBSQLViewModel sender) { try { Log.Write("SQLServerEngine", $"Close {_Connection.ConnectionString}"); _Connection.Close(); _Connection.Dispose(); _Connection = null; } catch (Exception ex) { Log.Write("SQLServerEngine", $"Close {ex.Message}"); _Connection = null; return(false); }; return(true); }
public bool Rollback(DBSQLViewModel sender, DbTransaction dbTransaction, [CallerMemberName] string CallerName = null) { if (DB_SQL.DoTransactions) { var t = (dbTransaction as SqlTransaction); try { t.Rollback(); } catch (Exception ex) { Debug.WriteLine(ex.Message); }; return(true); } else { return(true); }; }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public static bool EndWriteDB(DBSQLViewModel dBSQLViewModel, Int64 parent, ErrorLevel errorLevel, string message, string dataOutType, string dataOut) { long Ticks = DateTime.Now.Ticks; var at = DB_SQL.QueryFirst <AuditTrail>(dBSQLViewModel, "select * from AuditTrail where PK=" + parent); if (at != null) { at.Level = errorLevel; at.Ticks = Ticks - at.Ticks; at.DataOutType = dataOutType; at.DataOut = dataOut; at.Parent = -1; // ChM 20180514 pour simplifier requetes exploitations begin sans end var endAT = new AuditTrail(); endAT.Application = at.Application; endAT.TerminalID = at.TerminalID; endAT.TerminalIP = at.TerminalIP; endAT.FKUser = at.FKUser; endAT.ItemID = at.ItemID; endAT.ItemType = at.ItemType; endAT.IsBusiness = at.IsBusiness; endAT.Tag = at.Tag; endAT.Parent = parent; endAT.Message = message; endAT.Level = errorLevel; endAT.DataOutType = dataOutType; endAT.DataOut = dataOut; DB_SQL.Insert(dBSQLViewModel, endAT); return(DB_SQL.Update(dBSQLViewModel, at)); } else { return(false); }; }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - private DataTable ConvertDataReader2DataTable(DBSQLViewModel sender, string SQL) { if (false) { // Bug PK: QuickQueryViewUpdate try { // Create a new data adapter based on the specified query. using (DbDataAdapter dataAdapter = NewDataAdapter(sender, SQL)) { if (sender.CurrentTransaction != null) { dataAdapter.SelectCommand.Transaction = sender.CurrentTransaction as SqlTransaction; } ; // Create a command builder to generate SQL update, insert, and // delete commands based on selectCommand. These are used to // update the database. // Populate a new data table and bind it to the BindingSource. using (DataTable table = new DataTable()) { table.Locale = System.Globalization.CultureInfo.InvariantCulture; try { dataAdapter.Fill(table); dataAdapter.Dispose(); } catch (Exception ex) { sender.LastError = ex.Message; sender.LastException = ex; return(null); }; return(table); }; }; } catch (Exception ex) { sender.LastError = ex.Message; sender.LastException = ex; if (Debugger.IsAttached) { Debugger.Break(); } ; return(null); }; } else { try { DbCommand cmd = NewCommand(sender, SQL); if (sender.CurrentTransaction != null) { cmd.Transaction = sender.CurrentTransaction as SqlTransaction; } ; DbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); DataTable dtSchema = dr.GetSchemaTable(); DataTable dt = new DataTable(); // You can also use an ArrayList instead of List<> List <DataColumn> listCols = new List <DataColumn>(); if (dtSchema != null) { foreach (DataRow drow in dtSchema.Rows) { string columnName = System.Convert.ToString(drow["ColumnName"]); DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"])); column.Unique = (bool)drow["IsUnique"]; column.AllowDBNull = (bool)drow["AllowDBNull"]; column.AutoIncrement = (bool)drow["IsAutoIncrement"]; if (listCols.Where(x => x.ColumnName == columnName).Count() > 0) { column.ColumnName = column.ColumnName + listCols.Where(x => x.ColumnName == columnName).Count(); } ; listCols.Add(column); dt.Columns.Add(column); } ; } ; // Read rows from DataReader and populate the DataTable while (dr.Read()) { DataRow dataRow = dt.NewRow(); for (int i = 0; i < listCols.Count; i++) { dataRow[((DataColumn)listCols[i])] = dr[i]; } dt.Rows.Add(dataRow); } return(dt); } catch (Exception ex) { // handle error Debug.WriteLine(ex.Message); }; return(null); }; }
public DBAuditTrailWriter(DBSQLViewModel dBSQLViewModel, bool DebugOutput = true) { _dBSQLViewModel = dBSQLViewModel; AuditTrailWriterHelper.CreateTable(_dBSQLViewModel); this.DebugOutput = DebugOutput; }
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - public static bool CreateTable(DBSQLViewModel _dBSQLViewModel = null) { bool Result = true; if (!_dBSQLViewModel.CheckConnection()) { return(false); } DB_SQL.QuickQueryInt(_dBSQLViewModel, "select PK from AuditTrail where 1=2"); if (DB_SQL._ViewModel != null && DB_SQL._ViewModel.LastError == "") { return(true); } ; if (Result) { // - - - - - - #region Create table & co string SQL = ""; switch (_dBSQLViewModel.DBType) { case DBType.Firebird: return(false); break; // ToDo CHM case DBType.SQLServer: SQL = AuditTrail.PostScript_MSSQL; break; case DBType.SQLite: SQL = AuditTrail.PostScript_SQLite; break; case DBType.PostgreSQL: SQL = AuditTrail.PostScript_PGSQL; break; case DBType.MySQL: SQL = AuditTrail.PostScript_MySQL; break; } ; // - - - - - - DB_SQL.CreateTable(_dBSQLViewModel, typeof(AuditTrail), SQL, ""); #endregion // - - - - - - if (!Result) { Log.Write(new AuditTrail() { Level = ErrorLevel.Error, Message = _dBSQLViewModel.LastError + Environment.NewLine + _dBSQLViewModel.LastQuery }); if (Debugger.IsAttached) { Debugger.Break(); } ; return(false); } } ; return(Result); }