public static async Task <System.Data.DataSet> QueryDataSetAsync(this CONNECTOR.IDataConnector connector, SqlBuilder sqlBuilder)
{
sqlBuilder.SqlEngine = ConnectorTools.FindEngine(connector.EngineName);
QueryContext query = new QueryContext(sqlBuilder.ToString(), sqlBuilder.SqlInputParameters);
return(await connector.QueryDataSetAsync(query));
}
public static System.Data.DataSet QueryDataSet(this CONNECTOR.IDataConnector connector, SqlBuilder sqlBuilder)
{
sqlBuilder.SqlEngine = ConnectorTools.FindEngine(connector.EngineName);
QueryContext query = new QueryContext(sqlBuilder.ToString(), sqlBuilder.SqlInputParameters);
return(connector.QueryDataSetAsync(query).GetAwaiter().GetResult());
}
public static async Task <System.Data.DataTable> QueryAsync(this CONNECTOR.IDataConnector connector, SqlBuilder sqlBuilder)
{
sqlBuilder.SqlEngine = ConnectorTools.FindEngine(connector.EngineName);
QueryContext query = new QueryContext(sqlBuilder.ToString(), sqlBuilder.SqlInputParameters);
System.Data.DataSet retset = await connector.QueryDataSetAsync(query);
if (retset.Tables.Count == 0)
{
return(null);
}
else
{
return(retset.Tables[0]);
}
}
protected virtual void InitCommandSetParameter(QueryContext query, System.Data.IDbCommand cmd, string parametername, object parameterValue)
{
IDataParameter sqlparam = null;
//sqlparam = ConnectorTools.AddDataParameterWithValue(cmd, "@" + fieldKey, obj); //NpgsqlCommand cmd = conn.CreateCommand();
sqlparam = cmd.CreateParameter();
sqlparam.ParameterName = "@" + parametername;
sqlparam.Value = parameterValue;
// Détection du type
if (ConnectorTools.FindEngine(this.EngineName) == ConnectorConstants.ConnectorEngineEnum.POSTGRESQL)
{
// !!! A revoir
//https://github.com/npgsql/Npgsql/issues/177
if (parameterValue != null && parameterValue != DBNull.Value && parameterValue is string && ((string)parameterValue).StartsWith("<?xml", StringComparison.OrdinalIgnoreCase))
{
sqlparam.DbType = DbType.Xml;
}
}
cmd.Parameters.Add(sqlparam);
}
public static ConnectorConstants.ConnectorEngineEnum GetEngine(this CONNECTOR.IDataConnector connector)
{
return(ConnectorTools.FindEngine(connector.EngineName));
}
/// <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);
}
protected virtual System.Data.IDbConnection ConnectionFactory()
{
return(ConnectorTools.ConnectionFactory(this.EngineName));
}
protected virtual System.Data.IDataAdapter DataAdapterFactory(IDbCommand cmd)
{
return(ConnectorTools.DataAdapterFactory(this.EngineName, cmd));
}