// - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
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);
}
