/// <summary>
/// Execute une commande et retourne un reader.
/// </summary>
/// <param name="commandName">Nom de la commande.</param>
/// <param name="tableName">Nom de la table.</param>
/// <param name="criteria">Critère de recherche.</param>
/// <param name="maxRows">Nombre maximum d'enregistrements (BrokerManager.NoLimit = pas de limite).</param>
/// <param name="queryParameter">Paramètre de tri des résultats et de limit des résultats.</param>
/// <returns>DataReader contenant le résultat de la commande.</returns>
protected override IReadCommand GetCommand(string commandName, string tableName, FilterCriteria criteria, int maxRows, QueryParameter queryParameter)
{
SqlServerCommand command = new SqlServerCommand(this.DataSourceName, commandName, CommandType.Text);
command.QueryParameters = queryParameter;
StringBuilder commandText = new StringBuilder("select ");
if (maxRows != BrokerManager.NoLimit)
{
commandText.Append("top(@top) ");
command.Parameters.AddWithValue("top", maxRows);
}
string order = null;
if (queryParameter != null && !string.IsNullOrEmpty(queryParameter.SortCondition))
{
order = queryParameter.SortCondition;
}
// Todo : brancher le tri.
AppendSelectParameters(commandText, tableName, criteria, order, command);
// Set de la requête
command.CommandText = commandText.ToString();
return(command);
}
public async Task Query()
{
SqlServerCommand command = new SqlServerCommand(ConnectionString);
ModelResponse <List <ResultTable> > result = await command.Query(@"SELECT 1, 2, 3;");
Assert.True(result.Correct);
}
public void TestConstructeur()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
Assert.IsNotNull(command.Parameters);
}
}
public void TestIListRemove()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameterCollection coll = command.Parameters;
SqlServerParameter param = command.CreateParameter();
param.ParameterName = "Param1";
coll.Add(param);
Assert.IsTrue(coll.Contains(param));
Assert.IsTrue(coll.Contains("Param1"));
Assert.AreEqual(1, coll.Count);
SqlServerParameter param2 = command.CreateParameter();
param2.ParameterName = "Param2";
coll.Add(param2);
Assert.IsTrue(coll.Contains(param2));
Assert.IsTrue(coll.Contains("Param2"));
Assert.AreEqual(2, coll.Count);
((IList)coll).Remove(param);
Assert.IsTrue(coll.Contains(param2));
Assert.IsTrue(coll.Contains("Param2"));
Assert.IsFalse(coll.Contains(param));
Assert.IsFalse(coll.Contains("Param1"));
Assert.AreEqual(1, coll.Count);
}
}
public void TestAddBeanPropertiesNull()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
command.Parameters.AddBeanProperties(null);
}
}
public void TestSetItemByName()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameterCollection coll = command.Parameters;
SqlServerParameter param = command.CreateParameter();
param.ParameterName = "Param1";
coll.Add(param);
Assert.IsTrue(coll.Contains(param));
Assert.IsTrue(coll.Contains("Param1"));
Assert.AreEqual(param, coll[0]);
Assert.AreEqual(param, coll["Param1"]);
Assert.AreEqual(param, ((IList)coll)[0]);
param = command.CreateParameter();
param.ParameterName = "Param2";
coll["Param1"] = param;
Assert.AreEqual(1, coll.Count);
Assert.IsTrue(coll.Contains(param));
Assert.IsTrue(coll.Contains("Param2"));
Assert.AreEqual(param, coll[0]);
Assert.AreEqual(param, coll["Param2"]);
Assert.AreEqual(param, ((IList)coll)[0]);
}
}
public void TestICollectionSyncRoot()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
ICollection coll = command.Parameters;
Assert.IsNull(coll.SyncRoot);
}
}
public void TestICollectionIsSynchronized()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
ICollection coll = command.Parameters;
Assert.IsFalse(coll.IsSynchronized);
}
}
public void TestIListGetEnumerator()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
IList coll = command.Parameters;
Assert.IsNotNull(coll.GetEnumerator());
}
}
public void TestIsNull()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
IDataParameter param = command.CreateParameter();
Assert.IsTrue(param.IsNullable);
}
}
public void TestICollectionIsReadonly()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
ICollection <SqlServerParameter> coll = command.Parameters;
Assert.IsFalse(coll.IsReadOnly);
}
}
public void TestIListIsFixedSize()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
IList coll = command.Parameters;
Assert.IsFalse(coll.IsFixedSize);
}
}
public void TestAddFullTextWithValue()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameterCollection coll = command.Parameters;
coll.AddFullTextWithValue("Param1", "le petit chat");
Assert.AreEqual("(FORMSOF(INFLECTIONAL, \"petit\") OR FORMSOF(THESAURUS, \"petit\")) AND (FORMSOF(INFLECTIONAL, \"chat\") OR FORMSOF(THESAURUS, \"chat\"))", coll["@Param1"].Value);
}
}
public void TestSourceVersion()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
IDataParameter param = command.CreateParameter();
param.SourceVersion = DataRowVersion.Proposed;
Assert.AreEqual(DataRowVersion.Proposed, param.SourceVersion);
}
}
public void TestParameterName()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.ParameterName = "Param1";
Assert.AreEqual("Param1", param.ParameterName);
}
}
public void TestDirection()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.Direction = ParameterDirection.Output;
Assert.AreEqual(ParameterDirection.Output, param.Direction);
}
}
public void TestDbType()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.DbType = DbType.String;
Assert.AreEqual(DbType.String, param.DbType);
}
}
public void TestValueDbNull()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.Value = DBNull.Value;
Assert.IsNull(param.Value);
}
}
public void TestAddBeanPropertiesNonPrimitiveProperty()
{
InheritBean item = new InheritBean();
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
ICollection <SqlServerParameter> cols = command.Parameters.AddBeanProperties(item);
}
}
public void TestSourceColumn()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
IDataParameter param = command.CreateParameter();
param.SourceColumn = "Column1";
Assert.AreEqual("Column1", param.SourceColumn);
}
}
public async Task ResultTableHtmlTable()
{
SqlServerCommand command = new SqlServerCommand(ConnectionString);
ModelResponse <List <ResultTable> > result = await command.Query(@"SELECT Name = 'Israel', Age=27;");
string table = GridBuilder.HtmlTable(result.Model.ElementAt(0));
Assert.True(result.Correct && !table.IsNotValid());
}
/// <summary>
/// Insère un nouvel enregistrement.
/// </summary>
/// <param name="commandName">Nom de la commande.</param>
/// <param name="bean">Bean à insérér.</param>
/// <param name="beanDefinition">Définition du bean.</param>
/// <param name="primaryKey">Définition de la clef primaire.</param>
/// <param name="columnSelector">Selecteur de colonnes à mettre à jour ou à ignorer.</param>
/// <returns>Reader retournant les données du bean inséré.</returns>
protected override IDataReader Insert(string commandName, T bean, BeanDefinition beanDefinition, BeanPropertyDescriptor primaryKey, ColumnSelector columnSelector)
{
string sql = this.BuildInsertQuery(beanDefinition, true, columnSelector);
SqlServerCommand command = new SqlServerCommand(this.DataSourceName, commandName, sql);
command.CommandTimeout = 0;
this.AddInsertParameters(bean, beanDefinition, command.Parameters, columnSelector);
return(command.ExecuteReader());
}
public void TestPrecision()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.DbType = DbType.Decimal;
param.Precision = 5;
Assert.AreEqual(0, param.Precision);
}
}
/// <summary>
/// Retrieves the first entity that
/// compels to the supplied conditions and
/// includes the specified columns during
/// selection
/// </summary>
/// <param name="conditions">Query Conditions</param>
/// <param name="columns">Columns to include</param>
/// <returns>ComplexReponse T</returns>
public async Task <ModelResponse <T> > GetFirst(List <QueryCondition <T> > conditions, string[] columns = null)
{
//Verify select columns
if (columns.IsNotValid())
{
columns = Columns;
}
//Verify integrity Columns (All the supplied columns must exists inside the current entity)
if (!columns.ToList().TrueForAll(Columns.Contains))
{
return(new ModelResponse <T>(false, @"The supplied columns does not exist in the current entity."));
}
//Inicialize query conditions
if (conditions.IsNotValid())
{
conditions = new List <QueryCondition <T> >();
}
else
{
//Validate query conditions integrity (All the supplied property-column query condition reference must exists inside the current entity)
if (!conditions.Select(c => c.Property).ToList().TrueForAll(columns.Contains))
{
return(new ModelResponse <T>(false, @"The supplied columns does not exist in the current entity."));
}
}
ModelResponse <T> response;
try
{
SqlServerCommand command = new SqlServerCommand(ConnectionString);
//Get the user supplied conditions
string userSqlConditions = conditions.ToSqlQuery(out SqlParameter[] parameters);
string sql = $@"Select Top 1 [{string.Join(@"], [", columns)}] From [dbo].[{TableName}] Where {userSqlConditions} And ([Deleted] Is Null);";
ModelResponse <List <ResultTable> > commandResult = await command.Query(sql, parameters);
if (commandResult.Correct)
{
T model = commandResult.Model
.FirstOrDefault()
.Rows.ElementAt(0)
.ToEntity <T>();
response = new ModelResponse <T>(model);
}
else
{
response = new ModelResponse <T>(false, commandResult.Message);
}
}
catch (Exception ex)
{
response = new ModelResponse <T>(ex);
}
return(response);
}
/// <summary>
/// Prépare la chaîne SQL et les paramètres de commandes pour appliquer un FilterCriteria.
/// </summary>
/// <param name="filter">Critères de filtrage.</param>
/// <param name="command">Commande.</param>
/// <param name="commandText">Texte de la commande.</param>
protected void PrepareFilterCriteria(FilterCriteria filter, SqlServerCommand command, StringBuilder commandText)
{
if (filter == null)
{
throw new ArgumentNullException("filter");
}
if (command == null)
{
throw new ArgumentNullException("command");
}
if (commandText == null)
{
throw new ArgumentNullException("commandText");
}
int pos = 0;
Dictionary <string, int> mapParameters = new Dictionary <string, int>();
foreach (FilterCriteriaParam criteriaParam in filter.Parameters)
{
commandText.Append(pos == 0 ? " where " : " and ");
commandText.Append(GetColumnCriteriaByColumnName(criteriaParam.ColumnName));
string parameterName = null;
if (!mapParameters.ContainsKey(criteriaParam.ColumnName))
{
parameterName = criteriaParam.ColumnName;
mapParameters.Add(criteriaParam.ColumnName, 1);
}
else
{
mapParameters[criteriaParam.ColumnName] = mapParameters[criteriaParam.ColumnName] + 1;
parameterName = criteriaParam.ColumnName + mapParameters[criteriaParam.ColumnName].ToString(CultureInfo.InvariantCulture);
}
if (criteriaParam.Expression == Expression.Between)
{
DateTime[] dateValues = (DateTime[])criteriaParam.Value;
command.Parameters.AddWithValue(parameterName + "T1", dateValues[0]);
command.Parameters.AddWithValue(parameterName + "T2", dateValues[0]);
}
else
{
command.Parameters.AddWithValue(parameterName, criteriaParam.Value);
}
commandText.Append(GetSqlString(parameterName, criteriaParam));
++pos;
}
}
public void TestParseConstant()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
TestDbProviderFactory.DefinedNextResult(new List <Bean>());
SqlServerCommand command = new SqlServerCommand("test", SqlResource.ResourceManager, "SqlTestConst");
command.ExecuteReader();
Assert.IsTrue(command.CommandText.Contains("'5' = 5"));
command.Dispose();
}
}
public void TestParseCurrentUserId()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
TestDbProviderFactory.DefinedNextResult(new List <Bean>());
SqlServerCommand command = new SqlServerCommand("test", SqlResource.ResourceManager, "SqlTestUserId");
command.ExecuteReader();
Assert.IsTrue(command.CommandText.Contains(":CURRENT_USER_ID IS NOT NULL"));
command.Dispose();
}
}
public void TestAdd()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameterCollection coll = command.Parameters;
SqlServerParameter param = command.CreateParameter();
param.ParameterName = "Param1";
coll.Add(param);
Assert.IsTrue(coll.Contains(param));
Assert.IsTrue(coll.Contains("Param1"));
Assert.IsTrue(((IList)coll).Contains(param));
}
}
/// <summary>
/// Ajoute tous les champs retournée par la commande comme propriété du document.
/// </summary>
/// <param name="command">Commande Sql.</param>
public void AddProperties(SqlServerCommand command)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
using (IDataReader reader = command.ExecuteReader()) {
if (reader.Read())
{
this.AddProperties(reader);
}
}
}
public void TestAddBeanProperties()
{
PkBean item = new PkBean()
{
Pk = 1000
};
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
ICollection <SqlServerParameter> cols = command.Parameters.AddBeanProperties(item);
Assert.IsTrue(command.Parameters.Contains("@BEA_PK"));
Assert.AreEqual(command.Parameters["@BEA_PK"].Value, item.Pk);
}
}
