/// <summary>
/// Insertion des elements de la table With dans la table Target
/// la liste des PK à utiliser est donné en parametre
/// </summary>
public override int Insert(DatabaseTable targetTableName, DatabaseTable withTableName, string excluded_PKs_Table = null, string included_PKs_Table = null, string date_format = "DMY", int connection_timeout = -1)
{
// ASSERTION
if (!isTableExist(targetTableName))
{
ExceptionLogger.Fatal("Le nom de la table est inconnu :" + targetTableName);
return(0);
}
if (!isTableExist(withTableName))
{
ExceptionLogger.Fatal("Le nom de la table est inconnu :" + withTableName);
return(0);
}
// PRE TRAITEMENT : reperer les colonnes existantes ou non , et celles qui sont Primary Keys
Dictionary <string, ColumnSpec> updated_table_columnsSpec = this.GetColumnsSpec(targetTableName);
IEnumerable <string> with_table_columnsName;
with_table_columnsName = GetColumnsName(withTableName);
List <ColumnSpec> _columns = new List <ColumnSpec>(with_table_columnsName.Count <string>());
List <string> _primaryKeys = new List <string>(with_table_columnsName.Count <string>());
// PRE TRAITEMENT : reperer les colonnes existantes ou non , et celles qui sont Primary Keys
foreach (String colName in with_table_columnsName)
{
// verifier que la colonne existe pour la table à updater
if (updated_table_columnsSpec.Keys.Contains <string>(colName))
{
ColumnSpec col = updated_table_columnsSpec[colName];
_columns.Add(col);
if (col.isPK)
{
_primaryKeys.Add(col.Column);
}
}
}
// test : l ensemble des PKs sont presentes
foreach (ColumnSpec col in updated_table_columnsSpec.Values)
{
if (col.isPK)
{
if (!_primaryKeys.Contains(col.Column))
{
ExceptionLogger.Fatal("la colonne :" + col.Column + " ne fait pas parti de la table " + withTableName);
throw new Exception("Insert impossible because the " + col.Column + " of " + targetTableName + " is not present in " + withTableName);
}
}
}
//Construction de la requete
// modele de requete pour faire un update
StringBuilder _1insertTable = null;
StringBuilder _2whereClauseTable = null;
StringBuilder _3columns = null;
String _4clauseIsNull = null;
foreach (ColumnSpec i in _columns)
{
if (_1insertTable == null)
{
_1insertTable = new StringBuilder("w." + i.Column);
}
else
{
_1insertTable.Append(", w.").Append(i.Column);
}
if (_3columns == null)
{
_3columns = new StringBuilder(i.Column);
}
else
{
_3columns.Append(", ").Append(i.Column);
}
if (i.isPK)
{
if (_2whereClauseTable == null)
{
_2whereClauseTable = new StringBuilder("pk." + i.Column + " = w." + i.Column);
_4clauseIsNull = "pk." + i.Column + " is null ";
}
else
{
_2whereClauseTable.Append(" AND pk.").Append(i.Column).Append(" = w.").Append(i.Column);
}
}
}
string _insertTable = String.Format("INSERT INTO [{0}].[{1}] ({2}) SELECT {3} FROM [{4}].[{5}] as w ",
targetTableName.schema, targetTableName.table,
_3columns, _1insertTable.ToString(),
withTableName.schema, withTableName.table);
string _whereClauseTable;
if (excluded_PKs_Table != null)
{
_whereClauseTable = " LEFT JOIN " + excluded_PKs_Table + " AS pk ON " + _2whereClauseTable + " WHERE " + _4clauseIsNull;
}
else if (included_PKs_Table != null)
{
_whereClauseTable = " INNER JOIN " + excluded_PKs_Table + " AS pk ON " + _2whereClauseTable;
}
else
{
_whereClauseTable = "";
}
int nbLines = 0;
try
{
SqlConnection c = (SqlConnection)Connection;
SqlCommand cmd = c.CreateCommand();
cmd.CommandText = _insertTable + _whereClauseTable;
nbLines = cmd.ExecuteNonQuery();
}
catch (SqlException e1)
{
ExceptionLogger.Fatal("SQL Request failed: " + _insertTable + _whereClauseTable, e1);
throw e1;
}
return(nbLines);
}
/// <summary>
/// MAJ de la table Updated avec les valeurs de la table With
/// avec la liste des PK qui ont été mis a jour dans une table temporaire
/// </summary>
public override int Update(DatabaseTable targetTableName, DatabaseTable withTableName, string updated_PKs_Table = "@updatedPK", string date_format = "DMY", string tableCollation = "COLLATE SQL_Latin1_General_CP1_CI_AS", int connection_timeout = -1)
{
// ASSERTION
if (!isTableExist(targetTableName))
{
ExceptionLogger.Fatal("Le nom de la table est inconnu :" + targetTableName);
return(0);
}
if (!isTableExist(withTableName))
{
ExceptionLogger.Fatal("Le nom de la table est inconnu :" + withTableName);
return(0);
}
// PRE TRAITEMENT : reperer les colonnes existantes ou non , et celles qui sont Primary Keys
Dictionary <string, ColumnSpec> updated_table_columnsSpec = this.GetColumnsSpec(targetTableName);
IEnumerable <string> with_table_columnsName;
with_table_columnsName = GetColumnsName(withTableName);
List <ColumnSpec> _columns = new List <ColumnSpec>(with_table_columnsName.Count <string>());
List <string> _primaryKeys = new List <string>(with_table_columnsName.Count <string>());
// PRE TRAITEMENT : reperer les colonnes existantes ou non , et celles qui sont Primary Keys
foreach (String colName in with_table_columnsName)
{
// verifier que la colonne existe pour la table à updater
if (updated_table_columnsSpec.Keys.Contains <string>(colName))
{
ColumnSpec col = updated_table_columnsSpec[colName];
_columns.Add(col);
if (col.isPK)
{
_primaryKeys.Add(col.Column);
}
}
}
// test : l ensemble des PKs sont presentes
foreach (ColumnSpec col in updated_table_columnsSpec.Values)
{
if (col.isPK)
{
if (!_primaryKeys.Contains(col.Column))
{
ExceptionLogger.Fatal("la colonne :" + col.Column + " ne fait pas parti de la table " + withTableName);
throw new Exception("Update Impossible because the " + col.Column + " of " + targetTableName + " is not present in " + withTableName);
}
}
}
//Construction de la requete
// rmodele de requete pour faire un update
StringBuilder _1createUpdatedPKTable = null;
StringBuilder _2setClauseTable = null;
StringBuilder _3outputCLause = null;
StringBuilder _4joinClauseTable = null;
foreach (ColumnSpec i in _columns)
{
if (i.isPK)
{
if (_4joinClauseTable == null)
{
_4joinClauseTable = new StringBuilder("w.").Append(i.Column).Append(" = u.").Append(i.Column);
}
else
{
_4joinClauseTable.Append(" AND w.").Append(i.Column).Append(" = u.").Append(i.Column);
}
if (_1createUpdatedPKTable == null)
{
_1createUpdatedPKTable = new StringBuilder(i.Column).Append(" ").Append(i.SQLType).Append((i.isSQLCharType ? " " + tableCollation : ""));
}
else
{
_1createUpdatedPKTable.Append(", ").Append(i.Column).Append(" ").Append(i.SQLType).Append((i.isSQLCharType ? " " + tableCollation : ""));
}
if (_3outputCLause == null)
{
_3outputCLause = new StringBuilder("inserted.").Append(i.Column);
}
else
{
_3outputCLause.Append(", inserted.").Append(i.Column);
}
}
else
{
if (_2setClauseTable == null)
{
_2setClauseTable = new StringBuilder(i.Column).Append(" = w.").Append(i.Column);
}
else
{
_2setClauseTable.Append(", ").Append(i.Column).Append(" = w.").Append(i.Column);
}
}
}
string _setClauseTable = String.Format("UPDATE [{0}].[{1}] SET {2} OUTPUT {3} INTO {4} FROM [{5}].[{6}] as u INNER JOIN [{7}].[{8}] as w ON {9}",
targetTableName.schema, //0
targetTableName.table, //1
_2setClauseTable.ToString(), //2
_3outputCLause.ToString(), //3
updated_PKs_Table, //4
targetTableName.schema, //5
targetTableName.table, //6
withTableName.schema, //7
withTableName.table, //8
_4joinClauseTable.ToString() //9
);
string _createUpdatedPKTable;
// La table contenant les PKs qui ont ete mis à jour, est une variable
if (updated_PKs_Table[1] == '@')
{
_createUpdatedPKTable = "SET DATEFORMAT " + date_format + "; DECLARE " + updated_PKs_Table + " TABLE (" + _1createUpdatedPKTable.ToString() + "); ";
}
else
{
_createUpdatedPKTable = "SET DATEFORMAT " + date_format + "; CREATE TABLE " + updated_PKs_Table + " (" + _1createUpdatedPKTable.ToString() + "); ";
}
int nbLines = 0;
try
{
SqlConnection c = (SqlConnection)Connection;
SqlCommand cmd = c.CreateCommand();
cmd.CommandText = _createUpdatedPKTable + _setClauseTable;
nbLines = cmd.ExecuteNonQuery();
}
catch (SqlException e1)
{
ExceptionLogger.Fatal("SQL Request failed: " + _createUpdatedPKTable + _setClauseTable, e1);
throw e1;
}
return(nbLines);
}
/// <summary>
/// Read the Csv File and fill the Table on the given Connection
/// </summary>
/// <param name="connection"></param>
/// <param name="dataTableSchema"></param>
/// <param name="dataTableName">if not exists, create the table with collate option</param>
/// <param name="tableCollation">by default, Latin1_General_CP1</param>
/// <returns></returns>
public int Fill(DBConnectionDelegate connection, DatabaseTable dataTableName, string date_format = "dd/MM/yyyy", string tableCollation = "COLLATE SQL_Latin1_General_CP1_CI_AS")
{
System.Diagnostics.Contracts.Contract.Assert(connection != null, "Connection must be setted");
string createDataTableRequest = "create TABLE [{0}].[{1}] ({2})";
string createDataTableColumns = null;
string insertRequest = "insert into [{0}].[{1}] ({2}) VALUES ({3})";
string insertRequestParameters = null;
string[] fieldHeaders = reader.GetFieldHeaders();
// intersept the file hearders with the columns name of the destination table
Dictionary <string, ColumnSpec> databaseColumns = connection.GetColumnsSpec(dataTableName);
IEnumerable <string> databaseColumnsName = databaseColumns.Keys.AsEnumerable <string>();
IEnumerable <string> headers;
if (databaseColumnsName.Count <string>() > 0)
{
headers = Enumerable.Intersect <string>(fieldHeaders.AsEnumerable <string>(), databaseColumnsName);
}
else
{
headers = fieldHeaders.AsEnumerable <string>();
}
int fieldCount = headers.Count <string>();
int[] headersMaxWidth = new int[fieldCount];
List <SqlParameter[]> wholeParams = new List <SqlParameter[]>();
// TODO pour createDataTableColumns, prevoir une adaptation, car si il y a un objet mapping, create des champs typés, et pas tout le temps
int i = 0;
foreach (string h in headers)
{
if (createDataTableColumns == null)
{
createDataTableColumns = h + " NVARCHAR({" + i + "}) " + tableCollation;
}
else
{
createDataTableColumns += ", " + h + " NVARCHAR({" + i + "}) " + tableCollation;
}
if (insertRequestParameters == null)
{
insertRequestParameters = "{0}" + h;
}
else
{
insertRequestParameters += ", {0}" + h;
}
i++;
}
// Type Management: Each parameter is typed using the destination database columns type.
//
int nbRows = 0;
while (reader.ReadNextRecord())
{
bool emptyRecord = true;
SqlParameter[] entries = new SqlParameter[fieldCount];
i = 0;
foreach (string h in headers)
{
SqlParameter p;
// Get the DbType
ColumnSpec spec = databaseColumns[h];
string fieldContent = reader[h];
int width = fieldContent.Length;
if (width > 0)
{
emptyRecord = false;
if (headersMaxWidth[i] < width)
{
headersMaxWidth[i] = width;
}
if (spec == null)
{// The parameter is in nvarchar
p = new SqlParameter(h, fieldContent);
}
else if (spec.isSQLCharType)
{
p = new SqlParameter(h, spec.Type, spec.MaximumLength);
p.Value = fieldContent;
}
else if (spec.Type.Equals(SqlDbType.Float) || spec.Type.Equals(SqlDbType.Decimal))
{
p = new SqlParameter(h, spec.Type, spec.MaximumLength);
double res;
// If type is float, but impossible to parse => Null Value
if (Double.TryParse(fieldContent, NumberStyles.Float | NumberStyles.Number, CultureInfo.InvariantCulture, out res))
{
p.Value = res;
}
else
{
p.Value = DBNull.Value;
}
}
else if (spec.Type.Equals(SqlDbType.DateTime) || spec.Type.Equals(SqlDbType.DateTime2))
{
p = new SqlParameter(h, spec.Type);
DateTime res;
// If type is datetime, but impossible to parse => Null Value
if (DateTime.TryParseExact(fieldContent, date_format, CultureInfo.InvariantCulture, DateTimeStyles.None, out res))
{
p.Value = res;
}
else
{
p.Value = DBNull.Value;
}
}
else
{
p = new SqlParameter(h, spec.Type, spec.MaximumLength);
p.Value = fieldContent;
}
}
else
{
p = new SqlParameter(h, spec.Type, spec.MaximumLength);
p.Value = DBNull.Value;
}
entries[i] = p;
i++;
}
if (!emptyRecord)
{
wholeParams.Add(entries);
nbRows++;
}
}
// Execution SQL
// creation de la table destination
if (!connection.isTableExist(dataTableName))
{
string[] values = headersMaxWidth.Select(x => x > 0 ? x.ToString() : "1").ToArray();
createDataTableColumns = String.Format(createDataTableColumns, values);
createDataTableRequest = String.Format(createDataTableRequest, dataTableName.schema, dataTableName.table, createDataTableColumns);
connection.Execute(createDataTableRequest);
}
// effectuer les Insertions
string insertParameters1 = String.Format(insertRequestParameters, ' ');
string insertParameters2 = String.Format(insertRequestParameters, '@');
insertRequest = String.Format(insertRequest, dataTableName.schema, dataTableName.table, insertParameters1, insertParameters2);
IDbCommand command = new SqlCommand(insertRequest);
for (i = 0; i < nbRows; i++)
{
IDataParameter[] p = wholeParams[i];
connection.Execute(ref command, p);
}
return(nbRows);
}