/// <summary>
/// Retourne le IDbConnection par factory de la bonne librairie SGBD
/// </summary>
public static System.Data.IDbConnection ConnectionFactory(string engineName)
{
ConnectorConstants.ConnectorEngineEnum engine = FindEngine(engineName);
if (engine == ConnectorConstants.ConnectorEngineEnum.NA)
{
throw new Exception("EngineName empty");
}
Type retour = null;
//System.Data.OleDb.OleDbConnection
if (engine == ConnectorConstants.ConnectorEngineEnum.MSSQL)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.SqlClient.SqlConnection, System.Data");
if (retour == null)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.SqlClient.SqlConnection, System.Data.SqlClient");
}
}
else if (engine == ConnectorConstants.ConnectorEngineEnum.POSTGRESQL)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("Npgsql.NpgsqlConnection, Npgsql");
}
else if (engine == ConnectorConstants.ConnectorEngineEnum.SQLITE)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.SQLite.SQLiteConnection, System.Data.SQLite");
}
else if (engine == ConnectorConstants.ConnectorEngineEnum.ORACLE)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.OracleClient.OracleConnection, System.Data.OracleClient");
}
else if (engine == ConnectorConstants.ConnectorEngineEnum.ACCESS)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.OleDb.OleDbConnection, System.Data");
if (retour == null)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.OleDb.OleDbConnection, System.Data.OleDb");
}
if (retour == null)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.OleDb.OleDbConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"); // !!!
}
}
else
{
throw new Exception("EngineName not found (IDbConnection)");
}
if (retour == null)
{
throw new Exception(string.Format("Engine/DLL IDbConnection for {0} not found. Please include DLL for this engine in your project", engineName));
}
return(Nglib.APP.CODE.ReflectionTools.NewInstance <System.Data.IDbConnection>(retour));
}
public static System.Data.IDataAdapter DataAdapterFactory(string engineName, System.Data.IDbCommand cmd)
{
ConnectorConstants.ConnectorEngineEnum engine = FindEngine(engineName);
Type retour = null;
if (engine == ConnectorConstants.ConnectorEngineEnum.MSSQL)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.SqlClient.SqlDataAdapter, System.Data");
if (retour == null)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.SqlClient.SqlDataAdapter, System.Data.SqlClient");
}
}
else if (engine == ConnectorConstants.ConnectorEngineEnum.POSTGRESQL)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("Npgsql.NpgsqlDataAdapter, Npgsql");
}
else if (engine == ConnectorConstants.ConnectorEngineEnum.SQLITE)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.SQLite.SQLiteDataAdapter, System.Data.SQLite");
}
else if (engine == ConnectorConstants.ConnectorEngineEnum.ORACLE)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.OracleClient.OracleDataAdapter, System.Data.OracleClient");
}
else if (engine == ConnectorConstants.ConnectorEngineEnum.ACCESS)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.OleDb.OleDbDataAdapter, System.Data");
if (retour == null)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.OleDb.OleDbDataAdapter, System.Data.OleDb");
}
if (retour == null)
{
retour = Nglib.APP.CODE.ReflectionTools.GetTypeByReflexion("System.Data.OleDb.OleDbDataAdapter, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"); // !!!
}
}
if (retour == null)
{
throw new Exception(string.Format("Engine/DLL IDataAdapter for {0} not found. Please include DLL for this engine in your project", engineName));
}
System.Data.IDbDataAdapter val = Nglib.APP.CODE.ReflectionTools.NewInstance <System.Data.IDbDataAdapter>(retour);
val.SelectCommand = cmd;
return(val);
}
public SqlBuilder(string tablename, ConnectorConstants.ConnectorEngineEnum SqlEngine = ConnectorConstants.ConnectorEngineEnum.POSTGRESQL)
{
this.SqlEngine = SqlEngine; /// ConnectorConstants.ConnectorEngineEnum.POSTGRESQL; // par default
sqlCommandType = SqlCommandTypeEnum.SELECT;
this.TableName = tablename;
}
/// <summary>
/// Insertion
/// </summary>
/// <param name="connector"></param>
/// <param name="subtabl"></param>
/// <param name="SpecialTimeOut"></param>
/// <param name="AutoIncrementColumn"></param>
/// <returns></returns>
private List <long> InsertTableSub(System.Data.DataTable subtabl, int SpecialTimeOut = 600, string AutoIncrementColumn = null)
{
//Obtien le SQL
var sqlAndDatas = SqlTools.GenerateSqlMultiInsert(subtabl);
string sql = sqlAndDatas.Item1;
ConnectorConstants.ConnectorEngineEnum connectorEngine = ConnectorTools.FindEngine(this.EngineName);
// Complete la requette pour obtenir les id du champs auto incrémenté (en un seul appel SQL)
if (!string.IsNullOrWhiteSpace(AutoIncrementColumn))
{
if (connectorEngine == ConnectorConstants.ConnectorEngineEnum.POSTGRESQL)
{
sql += " RETURNING " + AutoIncrementColumn;
}
else if (connectorEngine == ConnectorConstants.ConnectorEngineEnum.SQLITE)
{
sql += "; select last_insert_rowid();";
}
else if (connectorEngine == ConnectorConstants.ConnectorEngineEnum.MYSQL)
{
sql += "; SELECT LAST_INSERT_ID();"; // Calling last_insert_id() gives you the id of the FIRST row inserted in the last batch. All others inserted, are guaranteed to be sequential.
}
else if (connectorEngine == ConnectorConstants.ConnectorEngineEnum.MSSQL)
{
sql += "; SELECT SCOPE_IDENTITY();"; // OUTPUT Inserted.ID https://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert
}
else if (connectorEngine == ConnectorConstants.ConnectorEngineEnum.ORACLE)
{
sql += ";"; //
}
else if (connectorEngine == ConnectorConstants.ConnectorEngineEnum.ACCESS)
{
sql += "; SELECT @@IDENTITY;";
}
}
//INSERT
System.Data.DataTable ret = this.Query(sql, sqlAndDatas.Item2);
// ---- OBTENIR LES ID INSERES ----
List <long> retourIncremented = new List <long>();
if (!string.IsNullOrWhiteSpace(AutoIncrementColumn))
{
if (connectorEngine == ConnectorConstants.ConnectorEngineEnum.POSTGRESQL)
{
if (subtabl.Rows.Count != ret.Rows.Count)
{
throw new Exception("AutoIncrementColumn Rows Error");
}
foreach (System.Data.DataRow row in ret.Rows)
{
retourIncremented.Add(Convert.ToInt64(row[0]));
}
}
else if (ret.Rows.Count == 1)
{
// on obtient le dernier ID et on décompte les autres
long lastid = Convert.ToInt64(ret.Rows[0][0]);
int totalrow = subtabl.Rows.Count;
for (int i = 0; i < totalrow; i++)
{
retourIncremented.Add(lastid - (totalrow - 1) + i);
}
}
}
return(retourIncremented);
}