/// <summary>
/// Create instance of SQLTable and use given System.Data.DataTable.
/// </summary>
/// <param name="tableName">Name of the table on SQL Server.</param>
/// <param name="dataTable">DataTable.</param>
/// <param name="sqlDB">Destination SQLDatabase.</param>
public SQLTable(string tableName, DataTable dataTable, SQLDataBase sqlDatabase)
{
this.schema = DEFAULT_SCHEMA;
this.tableName = tableName;
this.dataTable = dataTable;
this.sqlDB = sqlDatabase;
}
/// <summary>
/// Clear all data and call "DataTable.Dispose()" method (E2MKI)
/// </summary>
public void Dispose()
{
FreeMemory(dataTable);
// - Clear reference -
dataTable = null;
sqlDB = null;
tableName = null;
}
/// <summary>
/// Create instance of SQLTable and read definition from SQL Server.
/// </summary>
/// <param name="tableName">Name of the table on SQL Server.</param>
/// <param name="tableName">Name of the schema.</param>
/// <param name="sqlDatabase">Destination SQLDatabase.</param>
public SQLTable(SQLDataBase sqlDB, string schema, string tableName, bool withKeyInfo = true)
{
// - Save arguments -
this.sqlDB = sqlDB;
this.schema = schema;
this.tableName = tableName;
// - Display warning as this new method could produce bugs -
System.Diagnostics.Debug.Print("Warning : GetEmptyResult method used !");
// - Get table definition -
this.dataTable = sqlDB.GetEmptyResult("SELECT * FROM {0}.{1}", schema, tableName);
//// - Read definition from SQL Server -
//this.ReadTableSchema();
}
static public void SetUpdateDate(string databaseNameToUpdate, string tableNameUpdated, SQLDataBase anySqlDB)
{
try
{
// - Init -
bool rowExist = false;
// - Init SQL Query -
string statement = "SELECT CASE WHEN EXISTS (SELECT * FROM [E2MKI-MasterData].dbo.TD_UpdateDate WHERE DatabaseName='{0}' AND TableName='{1}') THEN 1 ELSE 0 END";
statement = string.Format(statement, databaseNameToUpdate, tableNameUpdated);
// - Run SQL Query -
if ((int)anySqlDB.ExecuteScalar(statement) == 1)
{
rowExist = true;
}
// - If row exists Update else Insert -
if (rowExist == true)
{
statement = "UPDATE [E2MKI-MasterData].dbo.TD_UpdateDate SET UpdateDate=GETDATE() WHERE DatabaseName='{0}' AND TableName='{1}'";
}
else
{
statement = "INSERT INTO [E2MKI-MasterData].dbo.TD_UpdateDate (DatabaseName, TableName, UpdateDate) VALUES ('{0}', '{1}', GETDATE())";
}
// - Prepare statement -
statement = string.Format(statement, databaseNameToUpdate, tableNameUpdated);
// - Execute Statement -
anySqlDB.ExecuteNonQuery(statement);
}
catch (Exception e)
{
Console.WriteLine("Error during SetUpdateDate()\n{0}", e.Message);
}
}
public static DataTable GetDataTable(string tableName, SQLDataBase sqlDB)
{
SQLTable sqlTable = new SQLTable(tableName, sqlDB);
return(sqlTable.ReadAll());
}
/// <summary>
/// Check if a row match the WHERE criteria. (SQL Query / Include already the WHERE keyword)
/// </summary>
/// <param name="anyDB"></param>
/// <param name="anyTableName"></param>
/// <param name="whereClause"></param>
/// <returns></returns>
public static bool Exists(SQLDataBase anyDB, string tableName, string whereClause)
{
// - Return true if at least 1 line meat the where criteria -
return((int)anyDB.ExecuteScalar(string.Format("SELECT CASE WHEN EXISTS (SELECT * FROM {0} WHERE {1}) THEN 1 ELSE 0 END", tableName, whereClause)) == 1);
}
/// <summary>
/// Check if a row match the WHERE criteria. (SQL Query / Include already the WHERE keyword)
/// </summary>
/// <param name="anyDB"></param>
/// <param name="tableName"></param>
/// <param name="whereClause"></param>
/// <param name="args"></param>
/// <returns></returns>
public static bool Exists(SQLDataBase anyDB, string tableName, string whereClause, params object[] args)
{
return(Exists(anyDB, tableName, string.Format(whereClause, args)));
}
public static void Truncate(SQLDataBase sqlDB, string schema, string tableName)
{
sqlDB.ExecuteNonQuery("TRUNCATE TABLE {0}.{1}", schema, tableName);
}
public static void Truncate(SQLDataBase sqlDB, string tableName)
{
Truncate(sqlDB, DEFAULT_SCHEMA, tableName);
}
/// <summary>
/// Create instance of SQLTable and read definition from SQL Server.
/// </summary>
/// <param name="tableName"></param>
/// <param name="sqlDatabase"></param>
public SQLTable(string tableName, SQLDataBase sqlDB) : this(sqlDB, DEFAULT_SCHEMA, tableName)
{
}
