public void ShouldBeAbleToAliasJoinPartsWithNoLock()
{
//--------------- Arrange -------------------
var table1 = GetRandomAlphaString();
var alias1 = GetRandomAlphaString();
var field = GetRandomAlphaString();
var table2 = GetRandomAlphaString();
var alias2 = GetRandomAlphaString();
var expected =
$"select [{field}] from [{table1}] WITH (NOLOCK) as [{alias1}] inner join [{table2}] WITH (NOLOCK) as [{alias2}] on [{alias1}].[{field}]=[{alias2}].[{field}]";
//--------------- Assume ----------------
//--------------- Act ----------------------
var result = SelectStatementBuilder.Create()
.WithDatabaseProvider(DatabaseProviders.SQLServer)
.WithNoLock()
.WithTable(table1, alias1)
.WithField(field)
.WithInnerJoin(alias1, field, Condition.EqualityOperators.Equals, table2, alias2, field)
.Build();
//--------------- Assert -----------------------
Console.WriteLine(result);
Console.WriteLine(expected);
Expect(result).To.Equal(expected);
}
public void BuildSelectStringWithForeignTableSqlServer()
{
// arrange
var table = new Table("Hello", "Kitty", "hk");
var column = new NormalColumn("Test", table);
var foreignColumn = new ForeignColumn("Sample");
var joinedTable = new JoinedTable("Something", "Somewhere", "ss", table, column, foreignColumn);
var builder = new SelectStatementBuilder(
new List<ISqlConstituent> { column, foreignColumn },
table,
new List<ISqlConstituent> { joinedTable });
// act
var selectStatement = builder.Build();
// assert
const string ExpectedResult = @"SELECT
[hk].[Test],
[ss].[Sample]
FROM [Hello].[Kitty] hk
LEFT OUTER JOIN [Something].[Somewhere] ss
ON [hk].[Test] = [ss].[Sample]";
AssertSameCharactersWithoutSpaces(ExpectedResult, selectStatement);
}
private void LoadExportItem()
{
if (DropDownListExportFile.SelectedItem != null)
{
m_exportItem = new ExportItem();
m_exportItem.SelectStatementBuilder = SelectStatementBuilder.LoadSelectStatementBuilder(DropDownListExportFile.SelectedItem.Text);
LoadExportedItem(m_exportItem.SelectStatementBuilder);
DropDownListLanguage.SelectedValue = m_exportItem.SelectStatementBuilder.LanguageId;
if (m_exportItem.SelectStatementBuilder.FTPFile)
{
DropDownListFTPSites.SelectedValue = m_exportItem.SelectStatementBuilder.FTPSite;
}
else
{
DropDownListFTPSites.SelectedValue = "0";
}
CheckBoxExportEnabled.Checked = m_exportItem.SelectStatementBuilder.ExportEnabled;
DropDownRunTime.SelectedValue = m_exportItem.SelectStatementBuilder.RunTime.ToString();
m_originalItem = new ExportItem();
m_originalItem.SelectStatementBuilder = SelectStatementBuilder.LoadSelectStatementBuilder(DropDownListExportFile.SelectedItem.Text);
LoadExportedItem(m_originalItem.SelectStatementBuilder);
TextBoxLinkToFile.Text = SERVER_EXPORTFILE_PATH + DropDownListExportFile.SelectedItem.Text + ".csv";
}
}
public void BuildSelectStringWithForeignTableOracle()
{
// arrange
var table = new Table("Hello", "Kitty", "hk", SqlType.Oracle);
var column = new NormalColumn("Test", table, SqlType.Oracle);
var foreignColumn = new ForeignColumn("Sample", SqlType.Oracle);
var joinedTable = new JoinedTable("Something", "Somewhere", "ss", table, column, foreignColumn, sqlType: SqlType.Oracle);
var builder = new SelectStatementBuilder(
new List<ISqlConstituent> { column, foreignColumn },
table,
new List<ISqlConstituent> { joinedTable });
// act
var selectStatement = builder.Build();
// assert
const string ExpectedResult = @"SELECT
hk.Test,
ss.Sample
FROM Hello.Kitty hk
LEFT OUTER JOIN Something.Somewhere ss
ON hk.Test = ss.Sample";
AssertSameCharactersWithoutSpaces(ExpectedResult, selectStatement);
}
public void ShouldBeAbleToPerformMultiKeyJoin()
{
// Arrange
var table1 = "table1";
var table2 = "table2";
var wantField = "wantField";
var leftField1 = "left1";
var leftField2 = "left2";
var rightField1 = "right1";
var rightField2 = "right2";
var expected =
$"select [{wantField}] from [{table1}] inner join [{table2}] on ([{table1}].[{leftField1}]=[{table2}].[{rightField1}] and [{table1}].[{leftField2}]=[{table2}].[{rightField2}])";
// Pre-Assert
// Act
var result = SelectStatementBuilder.Create()
.WithDatabaseProvider(DatabaseProviders.SQLServer)
.WithTable(table1)
.WithField(wantField)
.WithJoin(table1,
table2,
JoinDirections.Inner,
condition(Condition.EqualityOperators.Equals, table1, leftField1, table2, rightField1),
condition(Condition.EqualityOperators.Equals, table1, leftField2, table2, rightField2)
)
.Build();
// Assert
Expect(result).To.Equal(expected);
}
private void LoadExportItem()
{
if (DropDownListExportFile.SelectedItem != null)
{
//m_exportItem = new ExportItem();
s_exportItem = CoreDataLibrary.Data.Get.GetExportItemByName(DropDownListExportFile.SelectedItem.Text);
s_exportItem.StatementBuilder = SelectStatementBuilder.StaticLoadSelectStatementBuilder(DropDownListExportFile.SelectedItem.Text);
LoadExportedItem(s_exportItem.StatementBuilder);
DropDownListLanguage.SelectedValue = s_exportItem.StatementBuilder.LanguageId;
DropDownListExportType.SelectedValue = s_exportItem.ExportType;
DropDownListExportEncoding.SelectedValue = s_exportItem.ExportEncoding;
if (s_exportItem.StatementBuilder.FTPFile)
{
DropDownListFTPSites.SelectedValue = s_exportItem.StatementBuilder.FTPSite;
}
else
{
DropDownListFTPSites.SelectedValue = "0";
}
CheckBoxExportEnabled.Checked = s_exportItem.StatementBuilder.ExportEnabled;
DropDownRunTime.SelectedValue = s_exportItem.StatementBuilder.RunTime.ToString();
s_originalItem = new ExportItem();
s_originalItem.StatementBuilder = SelectStatementBuilder.StaticLoadSelectStatementBuilder(DropDownListExportFile.SelectedItem.Text);
LoadExportedItem(s_originalItem.StatementBuilder);
TextBoxLinkToFile.Text = SERVER_EXPORTFILE_PATH + DropDownListExportFile.SelectedItem.Text + "." + DropDownListExportType.SelectedItem;
}
}
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List <string> queryRequiredFields)
{
SqlStatement sql = ssb.GetNormalSelectSqlStatement(this, queryRequiredFields);
sql.SqlCommandText = string.Format("SELECT * FROM ( SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( {0} ) ROW_ WHERE ROWNUM <= {1} ) WHERE ROWNUM_ >= {2}",
sql.SqlCommandText, ssb.Range.EndIndex, ssb.Range.StartIndex);
return(sql);
}
public static ICursor Select(this IRequest request, Action <ISelectStatementBuilder> select)
{
var selectBuilder = new SelectStatementBuilder();
select(selectBuilder);
return(request.Select((SelectStatement)selectBuilder.Build().First()));
}
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List <string> queryRequiredFields)
{
SqlStatement sql = ssb.GetNormalSelectSqlStatement(this, queryRequiredFields);
sql.SqlCommandText = string.Format("{0} LIMIT {1}, {2}",
sql.SqlCommandText, ssb.Range.Offset, ssb.Range.Rows);
return(sql);
}
public virtual SqlStatement GetSumStatement(Condition iwc, string columnName)
{
var builder = new SelectStatementBuilder(this.Context.Info.From, null, null);
builder.Where.Conditions = iwc;
builder.SetSumColumn(columnName);
return(builder.ToSqlStatement(this.Context));
}
public void TestSelectSentenceBuilder1A()
{
var ssb = new SelectStatementBuilder("UserTable", null, new Range(1, 10));
ssb.Keys.Add(new KeyValuePair <string, string>("a", null));
const string s = "SELECT TOP 10 [a] FROM [UserTable];\n<Text><60>()";
Assert.AreEqual(s, ssb.ToSqlStatement(dd, null).ToString());
}
public void TestSelectSentenceBuilder2()
{
var ssb = new SelectStatementBuilder("UserTable", null, new Range(1, 10));
ssb.Keys.Add(new KeyValuePair <string, string>("a", null));
ssb.Where.Conditions = new OrClause("ID", 5, 3, 2);
const string s = "SELECT TOP 10 [a] FROM [UserTable] WHERE ([ID] = @ID_0) OR ([ID] = @ID_1) OR ([ID] = @ID_2);\n<Text><60>(@ID_0=5:Int32,@ID_1=3:Int32,@ID_2=2:Int32)";
TesterHelper.AssertSqlSentenceEqual(s, ssb.ToSqlStatement(dd, null).ToString());
}
public void TestSelectSentenceBuilder3()
{
var ssb = new SelectStatementBuilder("UserTable", null, new Range(1, 10));
ssb.Keys.Add(new KeyValuePair <string, string>("a", null));
ssb.Where.Conditions = new OrClause("ID", 5, 3, 2);
ssb.Where.Conditions = new AndClause(ssb.Where.Conditions, new KeyValueClause("UserName", "l'lf", CompareOpration.Equal, ColumnFunction.None));
const string s = "SELECT TOP 10 [a] FROM [UserTable] WHERE (([ID] = @ID_0) OR ([ID] = @ID_1) OR ([ID] = @ID_2)) AND ([UserName] = @UserName_3);\n<Text><60>(@ID_0=5:Int32,@ID_1=3:Int32,@ID_2=2:Int32,@UserName_3=l'lf:String)";
TesterHelper.AssertSqlSentenceEqual(s, ssb.ToSqlStatement(dd, null).ToString());
}
private IStatementBuildProvider CreateProvider()
{
var selectStatementBuilder = new SelectStatementBuilder();
var insertStatementBuilder = new InsertStatementBuilder();
var updateStatementBuilder = new UpdateStatementBuilder();
var conditionBuilder = new ConditionBuilder();
return(new SqlStatementBuildProvider(selectStatementBuilder,
insertStatementBuilder,
updateStatementBuilder,
conditionBuilder));
}
private void LoadExportedItem(SelectStatementBuilder ssb)
{
CheckBoxPropertyReferenceId.Checked = ssb.ExcludePropertyReferenceId;
CheckBoxTTI.Checked = ssb.ExcludeTTI;
CheckBoxPropertyName.Checked = ssb.ExcludePropertyName;
CheckBoxRating.Checked = ssb.ExcludeRating;
CheckBoxPropertyType.Checked = ssb.ExcludePropertyType;
CheckBoxIATACode.Checked = ssb.ExcludeIATACode;
CheckBoxResortId.Checked = ssb.ExcludeResortId;
CheckBoxRegionId.Checked = ssb.ExcludeRegionId;
CheckBoxCountryId.Checked = ssb.ExcludeCountryId;
CheckBoxResort.Checked = ssb.ExcludeResort;
CheckBoxRegion.Checked = ssb.ExcludeRegion;
CheckBoxCountry.Checked = ssb.ExcludeCountry;
CheckBoxAddress.Checked = ssb.ExcludeAddress;
CheckBoxTownCity.Checked = ssb.ExcludeTownCity;
CheckBoxCounty.Checked = ssb.ExcludeCounty;
CheckBoxPostcodeZip.Checked = ssb.ExcludePostcodeZip;
CheckBoxTelephone.Checked = ssb.ExcludeTelephone;
CheckBoxFax.Checked = ssb.ExcludeFax;
CheckBoxLatitude.Checked = ssb.ExcludeLatitude;
CheckBoxLongitude.Checked = ssb.ExcludeLongitude;
CheckBoxStrapline.Checked = ssb.ExcludeStrapline;
CheckBoxDescription.Checked = ssb.ExcludeDescription;
CheckBoxDistanceFromAirport.Checked = ssb.ExcludeDistanceFromAirport;
CheckBoxTransferTime.Checked = ssb.ExcludeTransferTime;
CheckBoxRightChoice.Checked = ssb.ExcludeRightChoice;
CheckBoxLocationAndResort.Checked = ssb.ExcludeLocationAndResort;
CheckBoxSwimmingPools.Checked = ssb.ExcludeSwimmingPools;
CheckBoxEatingAndDrinking.Checked = ssb.ExcludeEatingAndDrinking;
CheckBoxAccomodation.Checked = ssb.ExcludeAccomodation;
CheckBoxSuitableFor.Checked = ssb.ExcludeSuitableFor;
CheckBoxUrl.Checked = ssb.ExcludeUrl;
CheckBoxMainImage.Checked = ssb.ExcludeMainImage;
CheckBoxMainImageThumbnail.Checked = ssb.ExcludeMainImageThumbnail;
CheckBoxImageList.Checked = ssb.ExcludeImageList;
CheckBoxFacilitiesList.Checked = ssb.ExcludeFacilitiesList;
RadioButtonOwnStock.Checked = ssb.OwnStock;
RadioButtonOwnStockLive.Checked = ssb.OwnStockLive;
CheckBoxNotOwnStock.Checked = ssb.NotOwnStock;
ListBoxExclusions.Items.Clear();
ListBoxInclusions.Items.Clear();
foreach (ListItem item in ssb.Exclusions)
{
ListBoxExclusions.Items.Add(item);
}
foreach (ListItem item in ssb.Inclusions)
{
ListBoxInclusions.Items.Add(item);
}
CheckBoxExportEnabled.Checked = ssb.ExportEnabled;
}
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List <string> queryRequiredFields)
{
var dpc = new DataParameterCollection();
string sqlString = string.Format("SELECT TOP {4} {0} FROM {1}{2}{3}",
ssb.GetColumns(this),
ssb.From.ToSqlText(dpc, this, true),
ssb.Where.ToSqlText(dpc, this, queryRequiredFields),
(ssb.Order == null || ssb.Keys.Count == 0) ? "" : ssb.Order.ToSqlText(dpc, this, true),
ssb.Range.EndIndex
);
return(new TimeConsumingSqlStatement(CommandType.Text, sqlString, dpc));
}
public virtual SqlStatement GetGroupBySumStatement(Condition iwc, OrderBy order, string groupbyColumnName, string sumColumnName)
{
var builder = new SelectStatementBuilder(Context.Info.From, order, null);
builder.Where.Conditions = iwc;
var list = groupbyColumnName.Split(',');
foreach (string s in list)
{
builder.Keys.Add(new KeyValuePair <string, string>(s, null));
builder.SetAsGroupBySum(groupbyColumnName, sumColumnName);
}
return(builder.ToSqlStatement(Context));
}
private void SetSelectColumnsForDynamicLinqObject(SelectStatementBuilder sb, Type returnType)
{
var handler = DynamicLinqObjectHandler.Factory.GetInstance(returnType);
handler.Init(Context.Info);
foreach (MemberHandler fi in handler.GetMembers())
{
string value = null;
if (fi.Name != fi.MemberInfo.Name)
{
value = fi.MemberInfo.Name;
}
sb.Keys.Add(new KeyValuePair <string, string>(fi.Name, value));
}
}
private void SetupExport()
{
if (!CoreDataLib.IsLive())
{
_serverPath = @"\\SVRsql4\E$\CoreData\ExportFiles\Test\";
_exportServerTemp = @"E:\CoreData\ExportFiles\Test\Temp\";
_tempServerPath = _serverPath + @"Temp\";
}
else
{
_serverPath = "E:\\CoreData\\ExportFiles\\";
_exportServerTemp = @"E:\CoreData\ExportFiles\Temp\";
_tempServerPath = _serverPath + @"Temp\";
}
_pathAndFileName = _tempServerPath + ExportItem.ExportItemName + ".csv";
_selectClause = ExportItem.SelectStatementBuilder.SelectStatement();
_selectStatementBuilder = ExportItem.SelectStatementBuilder;
}
protected override void DoLoad()
{
var ctx = Owner.Context;
object key = ctx.Handler.GetKeyValue(Owner);
var sb = new SelectStatementBuilder(ctx.Info.From, null, null);
string kn = ctx.Info.KeyMembers[0].Name;
sb.Where.Conditions = CK.K[kn] == key;
sb.Keys.Add(new KeyValuePair <string, string>(RelationName, null));
SqlStatement sql = sb.ToSqlStatement(ctx);
object o = ctx.Provider.ExecuteScalar(sql);
if (o == DBNull.Value)
{
o = null;
}
m_Value = (T)o;
}
public void ShouldBeAbleToAliasTableNames()
{
//--------------- Arrange -------------------
var tableName = GetRandomAlphaString();
var tableAlias = GetRandomAlphaString();
var fieldName = GetRandomAlphaString();
var fieldAlias = GetRandomAlphaString();
//--------------- Assume ----------------
//--------------- Act ----------------------
var sql = SelectStatementBuilder.Create()
.WithTable(tableName, tableAlias)
.WithField(fieldName, fieldAlias)
.Build();
//--------------- Assert -----------------------
Expect(sql).To.Equal($"select [{fieldName}] as [{fieldAlias}] from [{tableName}] as [{tableAlias}]");
}
public void TestSelectSentenceBuilder0()
{
var ssb = new SelectStatementBuilder("UserTable");
ssb.SetCountColumn("*");
string s = "SELECT COUNT(*) AS it__count__ FROM [UserTable];\n";
Assert.AreEqual(s, ssb.ToSqlStatement(dd, null).SqlCommandText);
ssb = new SelectStatementBuilder("UserTable");
ssb.SetCountColumn("abc");
s = "SELECT COUNT([abc]) AS it__count__ FROM [UserTable];\n";
Assert.AreEqual(s, ssb.ToSqlStatement(dd, null).SqlCommandText);
ssb = new SelectStatementBuilder("UserTable");
ssb.Keys.Add(new KeyValuePair <string, string>("zzz", null));
ssb.SetCountColumn("abc");
s = "SELECT [zzz],COUNT([abc]) AS it__count__ FROM [UserTable];\n";
Assert.AreEqual(s, ssb.ToSqlStatement(dd, null).SqlCommandText);
}
public virtual SelectStatementBuilder GetSelectStatementBuilder(FromClause from, Condition iwc, OrderBy oc, Range lc, bool isDistinct, bool noLazy, Type returnType, string colName)
{
var sb = new SelectStatementBuilder(from ?? this.Context.Info.From, oc, lc)
{
IsDistinct = isDistinct,
NoLazy = noLazy
};
sb.Where.Conditions = iwc;
if (!colName.IsNullOrEmpty())
{
sb.Keys.Add(new KeyValuePair <string, string>(colName, null));
return(sb);
}
if (returnType.Name.StartsWith("<"))
{
this.SetSelectColumnsForDynamicLinqObject(sb, returnType);
return(sb);
}
this.Context.Handler.SetValuesForSelect(sb, noLazy);
return(sb);
}
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List <string> queryRequiredFields)
{
if (ssb.Order == null || ssb.Keys.Count == 0)
{
throw PagedMustHaveOrder;
}
const string posName = "__rownumber__";
var dpc = new DataParameterCollection();
string sqlString = string.Format(
"SELECT {7} FROM (SELECT {0}, ROW_NUMBER() OVER ({3}) AS {6} FROM {1} {2}) AS T WHERE T.{6} >= {4} AND T.{6} <= {5} ORDER BY T.{6}",
ssb.GetColumns(this),
ssb.From.ToSqlText(dpc, this),
ssb.Where.ToSqlText(dpc, this, queryRequiredFields),
ssb.Order.ToSqlText(dpc, this),
ssb.Range.StartIndex,
ssb.Range.EndIndex,
posName,
ssb.GetColumns(this, false, true)
);
return(new TimeConsumingSqlStatement(CommandType.Text, sqlString, dpc));
}
public virtual SqlStatement GetResultCountStatement(Condition iwc, bool isDistinct)
{
var builder2 = new SelectStatementBuilder(this.Context.Info.From, null, null)
{
IsDistinct = isDistinct
};
SelectStatementBuilder isv = builder2;
isv.Where.Conditions = iwc;
if (isDistinct)
{
this.Context.Handler.SetValuesForSelect(isv, false);
string columnName = isv.GetColumns(this.Context.Provider.Dialect, true, false);
isv.SetCountColumn(columnName);
isv.IsDistinct = false;
isv.Keys.Clear();
}
else
{
isv.SetCountColumn("*");
}
return(isv.ToSqlStatement(this.Context));
}
public void ShouldBeAbleToAliasJoinParts()
{
//--------------- Arrange -------------------
var table1 = GetRandomAlphaString();
var alias1 = GetRandomAlphaString();
var field = GetRandomAlphaString();
var table2 = GetRandomAlphaString();
var alias2 = GetRandomAlphaString();
//--------------- Assume ----------------
//--------------- Act ----------------------
var result = SelectStatementBuilder.Create()
.WithTable(table1, alias1)
.WithField(field)
.WithInnerJoin(alias1, field, Condition.EqualityOperators.Equals, table2, alias2, field)
.Build();
//--------------- Assert -----------------------
Expect(result)
.To.Equal(
$"select [{field}] from [{table1}] as [{alias1}] inner join [{table2}] as [{alias2}] on [{alias1}].[{field}]=[{alias2}].[{field}]"
);
}
public virtual SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List <string> queryRequiredFields)
{
throw DoesNotSupportPagedSelect;
}
public void BuildSelectStringWithNoForeignTableOracle()
{
// arrange
var table = new Table("Hello", "Kitty", "hk", SqlType.Oracle);
var column = new NormalColumn("Test", table, SqlType.Oracle);
var builder = new SelectStatementBuilder(
new List<ISqlConstituent> { column },
table,
new List<ISqlConstituent>());
// act
var selectStatement = builder.Build();
// assert
const string ExpectedResult = @"SELECT
hk.Test
FROM Hello.Kitty hk";
AssertSameCharactersWithoutSpaces(ExpectedResult, selectStatement);
}
public static bool NotInStatement(this string key, SelectStatementBuilder statement)
{
throw new Exception("It's only for Linq");
}
public void TestSelectSentenceBuilder1()
{
var ssb = new SelectStatementBuilder("UserTable", null, new Range(1, 10));
ssb.ToSqlStatement(dd, null);
}
public void ExampleOf_BuildingQueryWithDynamicColumnName()
{
//--------------- Arrange -------------------
const string CONT = "Contract";
const string LUTCC = "tblLUTCellCaptive";
const string POL = "policy";
const string POLT = "tblLUTPolicyType";
const string PR = "tblPolicyRole";
const string CL = "tblClient";
const string C = "Claim";
const string TPOL = "tblPolicy";
const string TA = "tblAsset";
const string TD = "tblDealer";
const string PT = "ProductType";
var sqlQuery = @"SELECT CONT.fCertificate as PolicyNumber," +
"CONT.fAccName as AccountName," +
"CL.fFirstName+''+CL.fSurname as CellCaptive," +
"PT.Description as ProductName," +
"CONT.fTransactionNo as TransactionNumber," +
"TD.fName as Dealer," +
"TPOL.fWebPolicyID as WebPolicyId " +
" FROM Contract (NOLOCK) CONT " +
" JOIN tblLUTCellCaptive(NOLOCK)LUTCC ON LUTCC.fCellCaptiveID = CONT.fCellCaptiveID" +
" JOIN policy (NOLOCK) POL ON POL.ContractIntID = CONT.ContractIntID" +
" JOIN tblLUTPolicyType POLT (NOLOCK) ON POLT.fPolicyTypeID = POL.PolicyTypeID" +
" JOIN tblPolicyRole PR(NOLOCK) ON PR.fPolicyID = POL.PolicyIntID " +
" JOIN tblClient CL(NOLOCK) ON CL.fClientID = PR.fClientID" +
" JOIN Claim C(NOLOCK) ON C.PolicyID = POL.PolicyID" +
" JOIN tblPolicy TPOL(NOLOCK) ON TPOL.fPolicyID = PR.fPolicyID" +
" JOIN tblAsset TA(NOLOCK) on TA.fAssetID = TPOL.fAssetID" +
" JOIN tblDealer(NOLOCK) TD ON TD.fDealerID = TPOL.fDealerID " +
" JOIN ProductType (NOLOCK) PT on PT.ProductTypeID = POLT.ProductTypeID " +
" WHERE @option like '%'+ @searchTerm +'%' ";
var searchOption = new SelectField(CONT, "fCertificate");
var searchTerm = "MKWM000819";
var replaced = sqlQuery.Replace("@option", searchOption.ToString()).Replace("@searchTerm", searchTerm);
//--------------- Assume ----------------
//--------------- Act ----------------------
var sql = SelectStatementBuilder.Create()
.WithDatabaseProvider(DatabaseProviders.SQLServer)
.WithNoLock()
.WithField(FieldFor(CONT, "fAccName", "AccountName"))
.WithField(new ConcatenatedField(
"CellCaptive",
FieldFor(CL, "fFirstName"),
FieldFor(CL, "fSurname")))
.WithField(FieldFor(PT, "Description", "ProductName"))
.WithField(FieldFor(CONT, "fTransactionNo", "TransactionNumber"))
.WithField(FieldFor(TD, "fName", "Dealer"))
.WithField(FieldFor(TPOL, "fWebPolicyID", "WebPolicyId"))
.WithTable(CONT)
.WithInnerJoin(CONT, "fCellCaptiveID", LUTCC)
.WithInnerJoin(CONT, "ContractIntID", POL)
.WithInnerJoin(POL, "PolicyTypeID", POLT, "fPolicyTypeID")
.WithInnerJoin(POL, "PolicyIntID", PR, "fPolicyID")
.WithInnerJoin(PR, "fClientID", CL)
.WithInnerJoin(POL, "PolicyID", C)
.WithInnerJoin(PR, "fPolicyID", TPOL)
.WithInnerJoin(TPOL, "fAssetID", TA)
.WithInnerJoin(TPOL, "fDealerID", TD)
.WithInnerJoin(POLT, "ProductTypeID", PT)
.WithCondition(searchOption, Condition.EqualityOperators.Contains, searchTerm)
.Build();
//--------------- Assert -----------------------
Console.WriteLine(replaced);
Console.WriteLine(@"-----");
Console.WriteLine(sql);
}
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List <string> queryRequiredFields)
{
return(ssb.GetNormalSelectSqlStatement(this, queryRequiredFields));
}
public void TestInitialize()
{
builder = new SelectStatementBuilder();
}