public override void Delete(Expression <Func <T, bool> > where)
{
SQLQueryProvider sqlQueryProvider = new SQLQueryProvider(m_dbProviderFactory, m_dbConnectionStr,
m_objectMapper.TableName, m_objectMapper.SetValue);
string whereStr = sqlQueryProvider.GetQueryText(where);
using (IDbConnection connection = m_dbProviderFactory.CreateConnection())
{
connection.ConnectionString = m_dbConnectionStr;
connection.Open();
using (IDbTransaction trans = connection.BeginTransaction())
{
try
{
IDbCommand deleteCommand = connection.CreateCommand();
deleteCommand.Transaction = trans;
deleteCommand.CommandText = "delete from " + m_objectMapper.TableName + " where " + whereStr;
deleteCommand.ExecuteNonQuery();
trans.Commit();
}
catch (Exception excp)
{
trans.Rollback();
logger.Error("Exception SQLAssetPersistor Delete (for " + typeof(T).Name + "). " +
excp.Message);
throw;
}
}
}
}
public void SelectWithBooleanTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
Expression <Func <MockSIPAsset, bool> > whereClause = (asset) => asset.Expired;
string querytext = queryProvider.GetQueryText(whereClause);
Console.WriteLine("Query: " + querytext + ".");
Assert.IsTrue(querytext == "expired = '1'", "The query text was incorrect.");
}
public void SelectWithAndOperatorTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
Expression <Func <MockSIPAsset, bool> > whereClause = (asset) => asset.AdminId == "1234" && asset.Username == "abcd";
string querytext = queryProvider.GetQueryText(whereClause);
Console.WriteLine("Query: " + querytext);
Assert.IsTrue(querytext == "adminid = '1234' and username = '******'", "The query text was incorrect.");
}
public void SelectWithNotEqualMemberVariableOperatorTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
var myObj = new { Name = "xyz" };
Expression <Func <MockSIPAsset, bool> > whereClause = (asset) => asset.Username != myObj.Name;
string querytext = queryProvider.GetQueryText(whereClause);
Console.WriteLine("Query: " + querytext);
Assert.IsTrue(querytext == "username != '" + myObj.Name + "'", "The query text was incorrect.");
}
public void SelectWithLocallyScopedVariableOperatorTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
string username = "******";
Expression <Func <MockSIPAsset, bool> > whereClause = (asset) => asset.Username == username;
string querytext = queryProvider.GetQueryText(whereClause);
Console.WriteLine("Query: " + querytext);
Assert.IsTrue(querytext == "username = '******'", "The query text was incorrect.");
}
public void SimpleSelectOnDateTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
DateTime checkDate = DateTime.Now;
Expression <Func <MockSIPAsset, bool> > whereClause = (asset) => asset.Inserted >= checkDate;
string querytext = queryProvider.GetQueryText(whereClause);
Console.WriteLine("Query: " + querytext);
Assert.IsTrue(querytext == "inserted >= '" + checkDate.ToString("o") + "'", "The query text was incorrect.");
}
public void SimpleSelectOnIdTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
Guid id = Guid.NewGuid();
Expression <Func <MockSIPAsset, bool> > whereClause = (asset) => asset.Id == id;
string querytext = queryProvider.GetQueryText(whereClause);
Console.WriteLine("Query: " + querytext);
Assert.IsTrue(querytext == "id = '" + id + "'", "The query text was incorrect.");
}
public void SelectWithNotCombinedWithAndOperatorTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
DateTime checkDate = DateTime.Now;
Expression <Func <MockSIPAsset, bool> > whereClause = (asset) => !asset.Expired && asset.Inserted >= checkDate;
string querytext = queryProvider.GetQueryText(whereClause);
Console.WriteLine("Query: " + querytext + ".");
Assert.IsTrue(querytext == "not (expired = '1') and inserted >= '" + checkDate.ToString("o") + "'", "The query text was incorrect.");
}
public void SelectWithMemberVariableCombinedWithAndClauseOperatorTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
var myObj = new { Name = "xyz" };
DateTime checkDate = DateTime.Now;
Expression <Func <MockSIPAsset, bool> > whereClause = (asset) => asset.Username == myObj.Name && !asset.Expired && asset.Inserted >= checkDate;
string querytext = queryProvider.GetQueryText(whereClause);
Console.WriteLine("Query: " + querytext);
Assert.IsTrue(querytext == "username = '******' and not (expired = '1') and inserted >= '" + checkDate.ToString("o") + "'", "The query text was incorrect.");
}
public void OrderedSelectTest()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
SQLQueryProvider queryProvider = new SQLQueryProvider(null, null, null, null);
Query <MockSIPAsset> assetList = new Query <MockSIPAsset>(queryProvider);
DateTime checkDate = DateTime.Now;
var dummyResult = from asset in assetList orderby asset.Inserted select asset;
string querytext = queryProvider.GetQueryText(dummyResult.Expression);
Console.WriteLine("Query: " + querytext);
//Assert.IsTrue(querytext == "username = '******' and not (expired = '1') and inserted >= '" + checkDate.ToString("o") + "'", "The query text was incorrect.");
}