/// <summary>
/// The list of results.
/// </summary>
/// <param name="maxResults">The max results.</param>
/// <param name="start">The start</param>
/// <param name="finish">The finish</param>
/// <param name="orderBy">The order by.</param>
/// <returns>The result set</returns>
public override IEnumerable<Guid> List(
int maxResults,
DateTime? start = null,
DateTime? finish = null,
ListResultsOrder orderBy = ListResultsOrder.Descending)
{
var builder = new SqlBuilder();
var t = builder.AddTemplate("select Id from MiniProfilers /**where**/ /**orderby**/ LIMIT(" + maxResults + ")");
if (start != null)
{
builder.Where("Started > @start", new { start });
}
if (finish != null)
{
builder.Where("Started < @finish", new { finish });
}
builder.OrderBy(orderBy == ListResultsOrder.Descending ? "Started desc" : "Started asc");
using (var conn = GetOpenConnection())
{
return conn.Query<Guid>(t.RawSql, t.Parameters).ToList();
}
}
public void BuilderSelectClause()
{
using (var dbConn = dbFactory.OpenDbConnection())
using (var dbCmd = dbConn.CreateCommand())
{
var rand = new Random(8675309);
var data = new List<User>();
for (var i = 0; i < 100; i++)
{
var nU = new User { Age = rand.Next(70), Id = i, Name = Guid.NewGuid().ToString() };
data.Add(nU);
dbCmd.Insert(nU);
nU.Id = (int) dbCmd.GetLastInsertId();
}
var builder = new SqlBuilder();
var justId = builder.AddTemplate("SELECT /**select**/ FROM Users");
var all = builder.AddTemplate("SELECT /**select**/, Name, Age FROM Users");
builder.Select("Id");
var ids = dbCmd.Query<int>(justId.RawSql, justId.Parameters);
var users = dbCmd.Query<User>(all.RawSql, all.Parameters);
foreach (var u in data)
{
Assert.That(ids.Any(i => u.Id == i), "Missing ids in select");
Assert.That(users.Any(a => a.Id == u.Id && a.Name == u.Name && a.Age == u.Age), "Missing users in select");
}
}
}
public static IEnumerable<BusOperator> GetAlls()
{
var builder = new SqlBuilder();
var template = builder.AddTemplate(@"
select *
from BusOperator bo
order by bo.Name");
return Current.DB.Query<BusOperator>(template.RawSql, template.Parameters);
}
/// <summary>
/// Creates a TopClause with the given topCount and withTies.
/// </summary>
/// <param name="topCount"></param>
/// <param name="withTies"></param>
internal TopClause(int topCount, bool withTies)
{
SqlBuilder sqlBuilder = new SqlBuilder();
sqlBuilder.Append(topCount.ToString(CultureInfo.InvariantCulture));
this.topCount = sqlBuilder;
this.withTies = withTies;
}
internal override PreparedDatabaseCommand Prepare(DocumentStore store, Guid etag, int uniqueParameterIdentifier)
{
var sql = new SqlBuilder()
.Append("delete from {0} where {1} = @Id{2}",
store.Database.FormatTableNameAndEscape(table.Name),
table.IdColumn.Name,
uniqueParameterIdentifier)
.Append(!lastWriteWins,
"and {0} = @CurrentEtag{1}",
table.EtagColumn.Name,
uniqueParameterIdentifier)
.ToString();
var parameters = new Dictionary<string, Parameter>();
AddTo(parameters, "@Id" + uniqueParameterIdentifier, key, SqlTypeMap.Convert(table.IdColumn).DbType, null);
if (!lastWriteWins)
{
AddTo(parameters, "@CurrentEtag" + uniqueParameterIdentifier, currentEtag, SqlTypeMap.Convert(table.EtagColumn).DbType, null);
}
return new PreparedDatabaseCommand
{
Sql = sql,
Parameters = parameters.Values.ToList(),
ExpectedRowCount = 1
};
}
public override void Execute(IDatabase database)
{
if (!Table.Columns.Any())
{
throw new InvalidOperationException("Cannot create a table with no columns.");
}
var sql = new SqlBuilder();
sql.Append("if not ({0}) begin create table {1} (",
GetTableExistsSql(database, Table.Name),
database.FormatTableNameAndEscape(Table.Name));
var i = 0;
foreach (var column in Table.Columns)
{
var sqlBuilder = new SqlBuilder()
.Append(i > 0, ",")
.Append(database.Escape(column.Name))
.Append(GetColumnSqlType(column, i.ToString()));
sql.Append(sqlBuilder);
i++;
}
sql.Append("); end;");
database.RawExecute(sql.ToString());
}
private void InitChannels()
{
listViewChannels.Clear();
listViewChannels.BeginUpdate();
try
{
SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof (Channel));
if (checkBoxGuideChannels.Checked)
{
sb.AddConstraint(Operator.Equals, "visibleInGuide", 1);
}
sb.AddConstraint(Operator.Equals, "isTv", 1);
sb.AddOrderByField(true, "sortOrder");
sb.AddOrderByField(true, "displayName");
SqlStatement stmt = sb.GetStatement(true);
IList<Channel> channels = ObjectFactory.GetCollection<Channel>(stmt.Execute());
for (int i = 0; i < channels.Count; i++)
{
// TODO: add imagelist with channel logos from MP :)
ListViewItem curItem = new ListViewItem(channels[i].DisplayName);
curItem.Tag = channels[i];
listViewChannels.Items.Add(curItem);
}
}
finally
{
listViewChannels.EndUpdate();
}
mpButtonOk.Enabled = (listViewChannels.Items.Count > 0);
}
/// <summary>
/// Remove exported merge lists
/// </summary>
public static void RemoveExportedMergeList(string ListAccountIds)
{
SqlBuilder objSqlBuilder = new SqlBuilder();
objSqlBuilder.CreateDeleteTable("list_accounts", null);
objSqlBuilder.CreateWhere("id", ListAccountIds.ToString(), SqlBuilder.eSqlConditionType.And, SqlBuilder.eSqlValueType.In);
string sqlDeleteListAccounts = objSqlBuilder.BuildSqlQuery(SqlBuilder.eSqlTransactionType.Delete);
objSqlBuilder = new SqlBuilder();
objSqlBuilder.CreateDeleteTable("merge_list_list_accounts", null);
objSqlBuilder.CreateWhere("list_account_id", ListAccountIds.ToString(), SqlBuilder.eSqlConditionType.And, SqlBuilder.eSqlValueType.In);
string sqlDeleteMergeListListAccounts = objSqlBuilder.BuildSqlQuery(SqlBuilder.eSqlTransactionType.Delete);
// delete the list items in list_accounts table
SqlCommand objSqlCommand = new SqlCommand(sqlDeleteListAccounts);
objSqlCommand.CommandType = CommandType.Text;
DatabaseUtility.ExecuteSqlCommand(objSqlCommand);
// delete the list items in merge_list_list_accounts table
objSqlCommand = new SqlCommand(sqlDeleteMergeListListAccounts);
objSqlCommand.CommandType = CommandType.Text;
DatabaseUtility.ExecuteSqlCommand(objSqlCommand);
// delete lists that has no record items
objSqlCommand = new SqlCommand("bvDeleteEmptyRecordLists_sp");
objSqlCommand.CommandType = CommandType.StoredProcedure;
DatabaseUtility.ExecuteSqlCommand(objSqlCommand);
}
public void SqlPaging()
{
var b = new SqlBuilder()
.Select("*")
.From("Orders o")
.Where("o.ShipCountry = @country ", new {country = "USA"})
.OrderBy("CustomerID DESC");
var paging = new
{
Page = 1,
PageSize = 25
};
var t = b.AddTemplate(
Templates.Combine(Templates.SqlServer.PagedSelection, Templates.Count),
paging);
using (var db = new SqlConnection(ConnectionStrings.Northwind))
{
Console.WriteLine(t.RawSql);
var results = db.QueryResultsSql<dynamic, int>(t.RawSql, t.Parameters);
results.Set1.Should().HaveCount(paging.PageSize, "because thats how many are in the page");
results.Set2.Single().Should().Be(122, "because thats how many there are");
}
}
public static IEnumerable<Dictionary<string, object>> Query(this SqliteConnection connection, SqlBuilder sql, SqliteTransaction transaction = null, object sqlParams = null)
{
if (sql == null)
throw new ArgumentNullException("sql", "sql cannot be null.");
return Query(connection, sql.GetSql(), transaction, sqlParams);
}
public void TestCRUD()
{
// skip test if GUIDs are not supported by database backend
if( runTest )
{
o1 = new GuidHolder( 42 );
// insert
o1.Persist();
// select
o2 = GuidHolder.Retrieve( o1.Id );
// verify select/insert
Assert.IsNotNull( o2.Id, "The object could not be retrieved from the database!" );
Assert.AreEqual( o1.Id, o2.Id, "The object could not be retrieved from the database!" );
Assert.AreEqual( o1.SomeValue, o2.SomeValue, "The object could not be retrieved from the database!" );
// update
o2.SomeValue = 1234;
o2.Persist();
// verify update
o1 = GuidHolder.Retrieve( o2.Id );
Assert.AreEqual( o2.Id, o1.Id, "The object could not be retrieved from the database!" );
// delete
o2.Remove();
// verify delete by counting the number of rows
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(GuidHolder) );
sb.AddConstraint( Operator.Equals, "Id", o1.Id );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
Assert.AreEqual( 0, sr.Count, "Object not removed" );
}
}
public void TestGreaterThanOrEqualsConstraint()
{
SqlBuilder builder = new SqlBuilder( StatementType.Count, typeof(MailingList) );
builder.AddConstraint( Operator.GreaterThanOrEquals, "Id", 3 );
int count = GetCount( builder );
Assert.AreEqual( 1, count );
}
private int GetCount( SqlBuilder builder )
{
SqlStatement statement = builder.GetStatement();
SqlResult result = statement.Execute();
int count = result.Count;
return count;
}
internal override PreparedDatabaseCommand Prepare(DocumentStore store, Guid etag, int uniqueParameterIdentifier)
{
var values = ConvertAnonymousToProjections(table, projections);
values[table.EtagColumn] = etag;
values[table.ModifiedAtColumn] = DateTimeOffset.Now;
var sql = new SqlBuilder()
.Append("update {0} set {1} where {2}=@Id{3}",
store.Database.FormatTableNameAndEscape(table.Name),
string.Join(", ", from column in values.Keys select column.Name + "=@" + column.Name + uniqueParameterIdentifier),
table.IdColumn.Name,
uniqueParameterIdentifier)
.Append(!lastWriteWins, "and {0}=@CurrentEtag{1}",
table.EtagColumn.Name,
uniqueParameterIdentifier)
.ToString();
var parameters = MapProjectionsToParameters(values, uniqueParameterIdentifier);
AddTo(parameters, "@Id" + uniqueParameterIdentifier, key, SqlTypeMap.Convert(table.IdColumn).DbType, null);
if (!lastWriteWins)
{
AddTo(parameters, "@CurrentEtag" + uniqueParameterIdentifier, currentEtag, SqlTypeMap.Convert(table.EtagColumn).DbType, null);
}
return new PreparedDatabaseCommand
{
Sql = sql,
Parameters = parameters.Values.ToList(),
ExpectedRowCount = 1
};
}
public void TestLessThanConstraint()
{
SqlBuilder builder = new SqlBuilder( StatementType.Count, typeof(MailingList) );
builder.AddConstraint( Operator.LessThan, "Id", 3 );
int count = GetCount( builder );
Assert.AreEqual( 2, count );
}
/// <summary>
/// gets a value from the database table "Setting"
/// </summary>
/// <returns>A Setting object with the stored value, if it doesnt exist the given default string will be the value</returns>
private Setting GetSetting(string tagName, string defaultValue)
{
if (defaultValue == null)
{
return null;
}
if (tagName == null)
{
return null;
}
if (tagName == "")
{
return null;
}
SqlBuilder sb;
try
{
sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Setting));
}
catch (TypeInitializationException)
{
return new Setting(tagName,defaultValue);
}
sb.AddConstraint(Operator.Equals, "tag", tagName);
SqlStatement stmt = sb.GetStatement(true);
IList<Setting> settingsFound = ObjectFactory.GetCollection<Setting>(stmt.Execute());
if (settingsFound.Count == 0)
{
Setting set = new Setting(tagName, defaultValue);
set.Persist();
return set;
}
return settingsFound[0];
}
public void Init()
{
GentleSettings.CacheObjects = false;
CacheManager.Clear();
SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(MultiType) );
Broker.Execute( sb.GetStatement( true ) );
}
public Form BuildForm(SqlBuilder Builder, FormLayouts FormLayout = FormLayouts.Vertical, SectionLayouts SectionLayout = SectionLayouts.VerticalTwoColumns)
{
Form form = new Form();
form.FormLayout = FormLayout;
for (int i = 0; i < Builder.Tables.Count; i++)
{
FormSection section = new FormSection() { SectionLayout = SectionLayout };
Table table = Builder.Tables[i];
MetadataTable mt = Builder.Metadata.FindTable(table.FullName);
// section.Legend = StringMap.Default.GetText(SqlBuilder.DefaultCulture.LCID, mt.Fullname, mt.Name);
section.Legend = mt.DisplayName;
string TableName = mt.Fullname;
if (i == 0)
{
form.TitleColumn = mt.GuessTitleColumn();
}
foreach (Field field in table.FieldList)
{
string name = field.Name;
MetadataColumn mc;
if (mt.Columns.TryGetValue(name, out mc))
{
MetadataColumn includedFrom = mt.Columns.Values.FirstOrDefault(x => x.IncludeColumns != null && x.IncludeColumns.Contains(field.Alias ?? field.Name));
BuildField(mc, TableName, field.Alias, section, includedFrom, i > 0);
}
}
if (section.Fields.Count > 0)
{
form.Sections.Add(section);
}
}
form.Sections[0].Fields.Insert(0, new FormField() { Name = "__TABLE", ID = "__TABLE", FieldType = FieldTypes.Input, InputType = InputTypes.text, IsHidden = true });
return form;
}
/// <summary>
/// GET: /search
/// GET: /search/index
/// </summary>
/// <returns></returns>
public ActionResult Index(string input, int? page, int? pageSize)
{
ViewBag.Input = input;
page = Math.Max(page ?? 1, 1); // Adjust.
pageSize = Math.Max(Math.Min(pageSize ?? 10, 100), 10); // Adjust.
int start = ((page.Value - 1) * pageSize.Value) + 1;
int finish = page.Value * pageSize.Value;
var builder = new SqlBuilder();
SqlBuilder.Template pager = null, counter = null;
pager = builder.AddTemplate("SELECT * FROM (SELECT /**select**/, ROW_NUMBER() OVER (/**orderby**/) AS RowNumber FROM Questions Q /**where**/) AS Results WHERE RowNumber BETWEEN @start AND @finish ORDER BY RowNumber",
new { start = start, finish = finish });
counter = builder.AddTemplate("SELECT COUNT(*) FROM Questions Q /**where**/");
builder.Select("Q.*");
builder.OrderBy("Q.Time DESC");
builder.Where("Q.Title LIKE @searchInput OR Q.Answer LIKE @searchInput", new { searchInput = "%" + input + "%" });
var results = Current.DB.QueryMultiple(pager.RawSql, pager.Parameters).Read<Question>();
var totals = Current.DB.Query<int>(counter.RawSql, counter.Parameters).First();
ViewData["Href"] = "/search?";
return View(new PagedList<Question>(results, page.Value, pageSize.Value, false, totals));
}
public void Test4()
{
var sqlBuilder = new SqlBuilder();
var temp = sqlBuilder.AddTemplate("select * from test where id = @0", 1);
Assert.AreEqual(1, temp.Parameters.Length);
Assert.AreEqual("select * from test where id = @0", temp.RawSql);
}
public void Final()
{
if( runTest )
{
SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(MemberPicture) );
Broker.Execute( sb.GetStatement( true ) );
}
}
public void Init()
{
GentleSettings.CacheObjects = false;
CacheManager.Clear();
// make sure table is empty before we start
SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(PropertyHolder) );
Broker.Execute( sb.GetStatement( true ) );
}
public void TestSqlBuilder()
{
TestModel model = new TestModel();
SqlBuilder builder = new SqlBuilder();
string expected = "SELECT OrderType, OrderNbr FROM SOOrder";
string result = builder.Select(model).ToString();
Assert.IsTrue(expected.ToLower().Trim() == result.ToLower().Trim());
}
public string DeleteRecord(DatasetInfo table)
{
var sql = new SqlBuilder(adapter);
sql.Delete(new SchemaObject(table.Name), GetKeyFilter(table));
return sql.ToString();
}
public override void Execute(IDatabase database)
{
var sql = new SqlBuilder();
sql.Append("alter table {0} add {1}", database.FormatTableNameAndEscape(Tablename), database.Escape(Column.Name));
sql.Append(GetColumnSqlType(Column));
database.RawExecute(sql.ToString());
}
public void Final()
{
// make sure we have only the default 4 members
SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(MemberSD) );
sb.AddConstraint( Operator.GreaterThan, "Id", 4 );
Broker.Execute( sb.GetStatement( true ) );
// remove the list
list.Remove();
}
private SqlResult GetRawTableData()
{
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(PHNullableType) );
SqlStatement stmt = sb.GetStatement( true );
// override generated statement to make sure we execute this without any type association
stmt = new SqlStatement( StatementType.Select, Broker.Provider.GetCommand(), stmt.Sql );
SqlResult sr = stmt.Execute();
return sr;
}
public void The_sql_builder_should_return_a_sql_string_equal_to_the_input()
{
var inputSql = "input";
var sqlBuilder = new SqlBuilder();
sqlBuilder.Add( inputSql );
var resultSql = sqlBuilder.GetSql();
Assert.AreEqual( inputSql, resultSql );
}
public void CleanUp()
{
if( runTests )
{
SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(ReservedWords) );
SqlStatement stmt = sb.GetStatement( true );
stmt.Execute();
}
}
public async Task <object[]> GetBallots(
AccountParameter sender,
Int32Parameter level,
DateTimeParameter timestamp,
Int32Parameter epoch,
Int32Parameter period,
ProtocolParameter proposal,
SortParameter sort,
OffsetParameter offset,
int limit,
string field,
Symbols quote)
{
var columns = new HashSet <string>(4);
var joins = new HashSet <string>(3);
switch (field)
{
case "id": columns.Add(@"o.""Id"""); break;
case "level": columns.Add(@"o.""Level"""); break;
case "timestamp": columns.Add(@"o.""Timestamp"""); break;
case "hash": columns.Add(@"o.""OpHash"""); break;
case "delegate": columns.Add(@"o.""SenderId"""); break;
case "rolls": columns.Add(@"o.""Rolls"""); break;
case "vote": columns.Add(@"o.""Vote"""); break;
case "proposal":
columns.Add(@"proposal.""Hash"" as ""ProposalHash""");
columns.Add(@"proposal.""Metadata""->> 'alias' as ""ProposalAlias""");
joins.Add(@"INNER JOIN ""Proposals"" as proposal ON proposal.""Id"" = o.""ProposalId""");
break;
case "period":
columns.Add(@"o.""Epoch""");
columns.Add(@"o.""Period""");
columns.Add(@"period.""Kind""");
columns.Add(@"period.""FirstLevel""");
columns.Add(@"period.""LastLevel""");
joins.Add(@"INNER JOIN ""VotingPeriods"" as period ON period.""Index"" = o.""Period""");
break;
case "block":
columns.Add(@"b.""Hash""");
joins.Add(@"INNER JOIN ""Blocks"" as b ON b.""Level"" = o.""Level""");
break;
case "quote": columns.Add(@"o.""Level"""); break;
}
if (period != null)
{
joins.Add(@"INNER JOIN ""VotingPeriods"" as period ON period.""Index"" = o.""Period""");
}
if (proposal != null)
{
joins.Add(@"INNER JOIN ""Proposals"" as proposal ON proposal.""Id"" = o.""ProposalId""");
}
if (columns.Count == 0)
{
return(Array.Empty <object>());
}
var sql = new SqlBuilder($@"SELECT {string.Join(',', columns)} FROM ""BallotOps"" as o {string.Join(' ', joins)}")
.Filter("SenderId", sender)
.FilterA(@"o.""Level""", level)
.FilterA(@"o.""Timestamp""", timestamp)
.FilterA(@"o.""Epoch""", epoch)
.FilterA(@"o.""Period""", period)
.FilterA(@"proposal.""Hash""", proposal)
.Take(sort, offset, limit, x => x == "level" ? ("Id", "Level") : ("Id", "Id"), "o");
using var db = GetConnection();
var rows = await db.QueryAsync(sql.Query, sql.Params);
//TODO: optimize memory allocation
var result = new object[rows.Count()];
var j = 0;
switch (field)
{
case "id":
foreach (var row in rows)
{
result[j++] = row.Id;
}
break;
case "level":
foreach (var row in rows)
{
result[j++] = row.Level;
}
break;
case "block":
foreach (var row in rows)
{
result[j++] = row.Hash;
}
break;
case "timestamp":
foreach (var row in rows)
{
result[j++] = row.Timestamp;
}
break;
case "hash":
foreach (var row in rows)
{
result[j++] = row.OpHash;
}
break;
case "period":
foreach (var row in rows)
{
result[j++] = new PeriodInfo
{
Index = row.Period,
Epoch = row.Epoch,
Kind = PeriodKinds.ToString(row.Kind),
FirstLevel = row.FirstLevel,
LastLevel = row.LastLevel
}
}
;
break;
case "proposal":
foreach (var row in rows)
{
result[j++] = new ProposalAlias
{
Hash = row.ProposalHash,
Alias = row.ProposalAlias
}
}
;
break;
case "delegate":
foreach (var row in rows)
{
result[j++] = await Accounts.GetAliasAsync(row.SenderId);
}
break;
case "rolls":
foreach (var row in rows)
{
result[j++] = row.Rolls;
}
break;
case "vote":
foreach (var row in rows)
{
result[j++] = Votes.ToString(row.Vote);
}
break;
case "quote":
foreach (var row in rows)
{
result[j++] = Quotes.Get(quote, row.Level);
}
break;
}
return(result);
}
private void VisitFunction_NewGuid(DbFunctionExpression e)
{
Debug.Assert(e.ParamList.Count == 0);
SqlBuilder.Append("UUID()");
}
public JoinClause(SqlBuilder <T> builder)
{
_builder = builder;
}
public static string GetSqlDbTypeString(this IColumn column)
{
return(column.SqlDbType.ToString().ToUpper(CultureInfo.InvariantCulture) + SqlBuilder.GetSizeScale(column.Size, column.Scale));
}
public AndStatment(SqlBuilder context, string condition)
: base(context)
{
this.StatementBock = string.Format(" and {0}", condition);
}
public override String GetSQLRequest()
{
String returnSql = "";
if (m_data.id == 0 || isError)
{
return(returnSql);
}
if (IsCheckboxChecked("locale"))
{
LocaleConstant localeIndex = (LocaleConstant)Properties.Settings.Default.localIndex;
if (localeIndex != 0)
{
m_gameobjectLocalesBuilder = new SqlBuilder("gameobject_template_locale", "entry");
m_gameobjectLocalesBuilder.SetFieldsNames("locale", "name");
m_gameobjectLocalesBuilder.AppendFieldsValue(m_data.id, localeIndex.ToString(), m_data.name);
returnSql += m_gameobjectLocalesBuilder.ToString() + "\n";
}
else
{
m_gameobjectLocalesBuilder = new SqlBuilder("gameobject_template", "entry");
m_gameobjectLocalesBuilder.SetFieldsNames("name");
m_gameobjectLocalesBuilder.AppendFieldsValue(m_data.id, m_data.name);
returnSql += m_gameobjectLocalesBuilder.ToString() + "\n";
}
}
if (IsCheckboxChecked("loot") && m_gameobjectLootDatas != null)
{
m_gameobjectLootBuilder = new SqlBuilder("gameobject_loot_template", "entry", SqlQueryType.DeleteInsert);
m_gameobjectLootBuilder.SetFieldsNames("Item", "Reference", "Chance", "QuestRequired", "LootMode", "GroupId", "MinCount", "MaxCount", "Comment");
returnSql += "UPDATE gameobject_template SET data1 = " + m_data.id + " WHERE entry = " + m_data.id + " AND type IN (3, 50);\n";
foreach (GameObjectLootParsing gameobjectLootData in m_gameobjectLootDatas)
{
GameObjectLootCurrencyParsing currentLootCurrencyData = null;
try
{
currentLootCurrencyData = (GameObjectLootCurrencyParsing)gameobjectLootData;
}
catch (Exception ex) { }
int idMultiplier = currentLootCurrencyData != null ? -1 : 1;
if (idMultiplier < 1)
{
continue;
}
int minLootCount = gameobjectLootData.stack.Length >= 1 ? gameobjectLootData.stack[0] : 1;
int maxLootCount = gameobjectLootData.stack.Length >= 2 ? gameobjectLootData.stack[1] : minLootCount;
m_gameobjectLootBuilder.AppendFieldsValue(m_data.id, // Entry
gameobjectLootData.id * idMultiplier, // Item
0, // Reference
gameobjectLootData.percent, // Chance
gameobjectLootData.questRequired, // QuestRequired
1, // LootMode
0, // GroupId
minLootCount, // MinCount
maxLootCount, // MaxCount
""); // Comment
}
returnSql += m_gameobjectLootBuilder.ToString() + "\n";
}
if (IsCheckboxChecked("herbalism") && m_gameobjectHerbalismDatas != null)
{
m_gameobjectHerbalismBuilder = new SqlBuilder("gameobject_loot_template", "entry", SqlQueryType.InsertIgnore);
m_gameobjectHerbalismBuilder.SetFieldsNames("Item", "Reference", "Chance", "QuestRequired", "LootMode", "GroupId", "MinCount", "MaxCount", "Comment");
returnSql += "UPDATE gameobject_template SET data1 = " + m_data.id + " WHERE entry = " + m_data.id + " AND type IN (3, 50);\n";
foreach (GameObjectLootParsing gameobjectHerbalismData in m_gameobjectHerbalismDatas)
{
m_gameobjectHerbalismBuilder.AppendFieldsValue(m_data.id, // Entry
gameobjectHerbalismData.id, // Item
0, // Reference
gameobjectHerbalismData.percent, // Chance
0, // QuestRequired
1, // LootMode
0, // GroupId
gameobjectHerbalismData.stack[0], // MinCount
gameobjectHerbalismData.stack[1], // MaxCount
""); // Comment
}
returnSql += m_gameobjectHerbalismBuilder.ToString() + "\n";
}
if (IsCheckboxChecked("mining") && m_gameobjectMiningDatas != null)
{
m_gameobjectMiningBuilder = new SqlBuilder("gameobject_loot_template", "entry", SqlQueryType.InsertIgnore);
m_gameobjectMiningBuilder.SetFieldsNames("Item", "Reference", "Chance", "QuestRequired", "LootMode", "GroupId", "MinCount", "MaxCount", "Comment");
returnSql += "UPDATE gameobject_template SET data1 = " + m_data.id + " WHERE entry = " + m_data.id + " AND type IN (3, 50);\n";
foreach (GameObjectLootParsing gameobjectMiningData in m_gameobjectMiningDatas)
{
m_gameobjectMiningBuilder.AppendFieldsValue(m_data.id, // Entry
gameobjectMiningData.id, // Item
0, // Reference
gameobjectMiningData.percent, // Chance
0, // QuestRequired
1, // LootMode
0, // GroupId
gameobjectMiningData.stack[0], // MinCount
gameobjectMiningData.stack[1], // MaxCount
""); // Comment
}
returnSql += m_gameobjectMiningBuilder.ToString() + "\n";
}
return(returnSql);
}
private int AddFederatedIdValues(TransactionContext tc, V7StagingTable table)
{
_log.DebugFormat("Adding federated Id values to history table: {0}", table.Name);
var numChanges = 0;
foreach (var c in table.FederatedIdCols)
{
Entity entity = c.Entity;
var origColumnName = c.OriginalColName;
var fedColumnName = c.FederationIdColName;
if (entity == Entity.Unknown)
{
// this is a generic column such as "resource_id" where we don't know
// the entity type without reference to the value stored in its corresponding
// 'type' column (e.g. "resource_type")...
var entityDefCol = c.EntityDefinitionColName;
var entityTypesUsed = GetEntityTypedUsed(tc, table.Name, entityDefCol);
foreach (var ct7EType in entityTypesUsed)
{
// e.g.
// update h
// set h.federated_resource_id = f.ctds_id
// from HISTORY.CT_AT_AUX_MARK h
// inner join
// FEDERATION.ROOM f
// on h.resource_id = f.item_id and h.src_timetable_id = f.src_timetable_id
// and h.resource_type = 604
// and h.history_federated = 0
// and h.history_status in ('I', 'U')
var et = EntityUtils.FromCt7Entity(ct7EType);
var sb = new SqlBuilder();
sb.Append("update h");
sb.AppendFormat("set h.{0} = f.{1}", fedColumnName, FederationSchema.MasterIdColName);
sb.AppendFormat(
"from {0} h", DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, table.Name));
sb.Append("inner join");
string qualifiedFedTable = DatabaseUtils.GetQualifiedTableName(
FederationSchema.FederationSchemaName, EntityUtils.ToFederationTableName(et));
sb.AppendFormat("{0} f", qualifiedFedTable);
sb.AppendFormat(
"on h.{0} = f.{1} and h.{2} = f.{2}",
origColumnName,
FederationSchema.ItemIdCol,
ColumnConstants.SrcTimetableIdColumnName);
sb.AppendFormat("and h.{0} = {1}", entityDefCol, (int)ct7EType);
sb.AppendFormat("and h.{0} = 0", HistorySchema.HistoryFederatedColumnName);
sb.AppendFormat("and h.{0} in ({1})", HistorySchema.HistoryStatusColumnName, RestrictedStatusValuesAsCsv());
numChanges += DatabaseUtils.ExecuteSql(tc, sb.ToString(), _timeoutSecs);
}
}
else
{
// e.g.
// update h
// set h.federated_dept_id = f.ctds_id
// from HISTORY.CT_ROOM h
// inner join
// FEDERATION.DEPT f
// on h.dept_id = f.item_id and h.src_timetable_id = f.src_timetable_id
// and h.history_federated = 0
// and h.history_status in ('I', 'U')
var sb = new SqlBuilder();
sb.Append("update h");
sb.AppendFormat("set h.{0} = f.{1}", fedColumnName, FederationSchema.MasterIdColName);
sb.AppendFormat(
"from {0} h", DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, table.Name));
sb.Append("inner join");
string qualifiedFedTable = DatabaseUtils.GetQualifiedTableName(
FederationSchema.FederationSchemaName, EntityUtils.ToFederationTableName(entity));
sb.AppendFormat("{0} f", qualifiedFedTable);
sb.AppendFormat(
"on h.{0} = f.{1} and h.{2} = f.{2}",
origColumnName,
FederationSchema.ItemIdCol,
ColumnConstants.SrcTimetableIdColumnName);
sb.AppendFormat("and h.{0} = 0", HistorySchema.HistoryFederatedColumnName);
sb.AppendFormat("and h.{0} in ({1})", HistorySchema.HistoryStatusColumnName, RestrictedStatusValuesAsCsv());
numChanges += DatabaseUtils.ExecuteSql(tc, sb.ToString(), _timeoutSecs);
}
}
return(numChanges);
}
public override String GetSQLRequest()
{
String returnSql = "";
if (m_creatureTemplateData.id == 0 || isError)
{
return(returnSql);
}
// Creature Template
if (IsCheckboxChecked("template"))
{
m_creatureTemplateBuilder = new SqlBuilder("creature_template", "entry");
m_creatureTemplateBuilder.SetFieldsNames("minlevel", "maxlevel", "name", "subname", "modelid1", "rank", "type", "family");
m_creatureTemplateBuilder.AppendFieldsValue(m_creatureTemplateData.id, m_creatureTemplateData.minlevel, m_creatureTemplateData.maxlevel, m_creatureTemplateData.name, m_subname ?? "", m_modelid, m_isBoss ? "3" : "0", m_creatureTemplateData.type, m_creatureTemplateData.family);
returnSql += m_creatureTemplateBuilder.ToString() + "\n";
}
// faction
if (IsCheckboxChecked("simple faction"))
{
SqlBuilder m_creatureFactionBuilder = new SqlBuilder("creature_template", "entry", SqlQueryType.Update);
m_creatureFactionBuilder.SetFieldsNames("faction");
m_creatureFactionBuilder.AppendFieldsValue(m_creatureTemplateData.id, m_faction);
returnSql += m_creatureFactionBuilder.ToString() + "\n";
}
// Creature Template
if (IsCheckboxChecked("money"))
{
SqlBuilder m_creatureMoneyBuilder = new SqlBuilder("creature_template", "entry", SqlQueryType.Update);
m_creatureMoneyBuilder.SetFieldsNames("mingold", "maxgold");
m_creatureMoneyBuilder.AppendFieldsValue(m_creatureTemplateData.id, m_creatureTemplateData.minGold, m_creatureTemplateData.maxGold);
returnSql += m_creatureMoneyBuilder.ToString() + "\n";
}
// Locales
if (IsCheckboxChecked("locale"))
{
LocaleConstant localeIndex = (LocaleConstant)Properties.Settings.Default.localIndex;
String localeName = localeIndex.ToString();
if (localeIndex != 0)
{
m_creatureLocalesBuilder = new SqlBuilder("creature_template_locales", "entry");
m_creatureLocalesBuilder.SetFieldsNames("locale", "Name", "Title");
m_creatureLocalesBuilder.AppendFieldsValue(m_creatureTemplateData.id, localeIndex.ToString(), m_creatureTemplateData.name, m_subname ?? "");
returnSql += m_creatureLocalesBuilder.ToString() + "\n";
}
else
{
m_creatureLocalesBuilder = new SqlBuilder("creature_template", "entry");
m_creatureLocalesBuilder.SetFieldsNames("name", "subname");
m_creatureLocalesBuilder.AppendFieldsValue(m_creatureTemplateData.id, m_creatureTemplateData.name, m_subname ?? "");
returnSql += m_creatureLocalesBuilder.ToString() + "\n";
}
}
if (IsCheckboxChecked("vendor") && m_npcVendorDatas != null)
{
m_npcVendorBuilder = new SqlBuilder("npc_vendor", "entry", SqlQueryType.DeleteInsert);
m_npcVendorBuilder.SetFieldsNames("slot", "item", "maxcount", "incrtime", "ExtendedCost", "type", "PlayerConditionID");
foreach (NpcVendorParsing npcVendorData in m_npcVendorDatas)
{
m_npcVendorBuilder.AppendFieldsValue(m_creatureTemplateData.id, npcVendorData.slot, npcVendorData.id, npcVendorData.avail, npcVendorData.incrTime, npcVendorData.integerExtendedCost, 1, 0);
}
returnSql += m_npcVendorBuilder.ToString() + "\n";
}
if (IsCheckboxChecked("loot") && m_creatureLootDatas != null)
{
bool referenceAdded = false;
int maxReferenceLoot = 2; // A voir si on peut trouver
int templateEntry = m_creatureTemplateData.id;
m_creatureLootBuilder = new SqlBuilder("creature_loot_template", "entry", SqlQueryType.DeleteInsert);
m_creatureLootBuilder.SetFieldsNames("Item", "Reference", "Chance", "QuestRequired", "LootMode", "GroupId", "MinCount", "MaxCount", "Comment");
m_creatureReferenceLootBuilder = new SqlBuilder("reference_loot_template", "entry", SqlQueryType.DeleteInsert);
m_creatureReferenceLootBuilder.SetFieldsNames("item", "ChanceOrQuestChance", "lootmode", "groupid", "mincountOrRef", "maxcount", "itemBonuses");
bool atLeastOneNormal = false;
returnSql += "UPDATE creature_template SET lootid = " + templateEntry + " WHERE entry = " + templateEntry + " AND lootid = 0;\n";
foreach (CreatureLootParsing creatureLootData in m_creatureLootDatas)
{
List <int> entryList = new List <int>();
CreatureLootItemParsing creatureLootItemData = null;
try
{
creatureLootItemData = (CreatureLootItemParsing)creatureLootData;
}
catch (Exception ex) { }
CreatureLootCurrencyParsing creatureLootCurrencyData = null;
try
{
creatureLootCurrencyData = (CreatureLootCurrencyParsing)creatureLootData;
}
catch (Exception ex) { }
switch (creatureLootData.mode)
{
default:
entryList.Add(templateEntry);
break;;
}
foreach (int entry in entryList)
{
int idMultiplier = creatureLootCurrencyData != null ? -1 : 1;
if (idMultiplier < 1)
{
continue;
}
int minLootCount = creatureLootData.stack.Length >= 1 ? creatureLootData.stack[0]: 1;
int maxLootCount = creatureLootData.stack.Length >= 2 ? creatureLootData.stack[1]: minLootCount;
m_creatureLootBuilder.AppendFieldsValue(entry, // Entry
creatureLootData.id * idMultiplier, // Item
0, // Reference
creatureLootData.percent, // Chance
creatureLootData.questRequired, // QuestRequired
1, // LootMode
0, // GroupId
minLootCount, // MinCount
maxLootCount, // MaxCount
""); // Comment
}
}
returnSql += m_creatureLootBuilder.ToString() + "\n";
returnSql += m_creatureReferenceLootBuilder.ToString() + "\n";
}
if (IsCheckboxChecked("skinning") && m_creatureSkinningDatas != null)
{
m_creatureLootBuilder = new SqlBuilder("skinning_loot_template", "entry", SqlQueryType.DeleteInsert);
m_creatureLootBuilder.SetFieldsNames("Item", "Reference", "Chance", "QuestRequired", "LootMode", "GroupId", "MinCount", "MaxCount", "Comment");
returnSql += "UPDATE creature_template SET skinloot = " + m_creatureTemplateData.id + " WHERE entry = " + m_creatureTemplateData.id + " AND skinloot = 0;\n";
foreach (CreatureLootParsing creatureSkinningData in m_creatureSkinningDatas)
{
m_creatureSkinningBuilder.AppendFieldsValue(m_creatureTemplateData.id, // Entry
creatureSkinningData.id, // Item
0, // Reference
creatureSkinningData.percent, // Chance
0, // QuestRequired
1, // LootMode
0, // GroupId
creatureSkinningData.stack[0], // MinCount
creatureSkinningData.stack[1], // MaxCount
""); // Comment
}
returnSql += m_creatureSkinningBuilder.ToString() + "\n";
}
if (IsCheckboxChecked("trainer") && m_creatureTrainerDatas != null)
{
m_creatureTrainerBuilder = new SqlBuilder("npc_trainer", "entry", SqlQueryType.DeleteInsert);
m_creatureTrainerBuilder.SetFieldsNames("spell", "spellcost", "reqskill", "reqskillvalue", "reqlevel");
returnSql += "UPDATE creature_template SET npc_flag = 16 WHERE entry = " + m_creatureTemplateData.id + ";\n";
foreach (CreatureTrainerParsing creatureTrainerData in m_creatureTrainerDatas)
{
int reqskill = creatureTrainerData.learnedat > 0 ? creatureTrainerData.skill[0] : 0;
m_creatureTrainerBuilder.AppendFieldsValue(m_creatureTemplateData.id, creatureTrainerData.id, creatureTrainerData.trainingcost, reqskill, creatureTrainerData.learnedat, creatureTrainerData.level);
}
returnSql += m_creatureTrainerBuilder.ToString() + "\n";
}
if (IsCheckboxChecked("quest starter") && m_creatureQuestStarterDatas != null)
{
m_creatureQuestStarterBuilder = new SqlBuilder("creature_queststarter", "entry", SqlQueryType.InsertIgnore);
m_creatureQuestStarterBuilder.SetFieldsNames("quest");
foreach (QuestStarterEnderParsing creatureQuestStarterData in m_creatureQuestStarterDatas)
{
m_creatureQuestStarterBuilder.AppendFieldsValue(m_creatureTemplateData.id, creatureQuestStarterData.id);
}
returnSql += m_creatureQuestStarterBuilder.ToString() + "\n";
}
if (IsCheckboxChecked("quest ender") && m_creatureQuestEnderDatas != null)
{
m_creatureQuestEnderBuilder = new SqlBuilder("creature_questender", "entry", SqlQueryType.InsertIgnore);
m_creatureQuestEnderBuilder.SetFieldsNames("quest");
foreach (QuestStarterEnderParsing creatureQuestEnderData in m_creatureQuestEnderDatas)
{
m_creatureQuestEnderBuilder.AppendFieldsValue(m_creatureTemplateData.id, creatureQuestEnderData.id);
}
returnSql += m_creatureQuestEnderBuilder.ToString() + "\n";
}
return(returnSql);
}
private int AddConsolidatedIdValues(TransactionContext tc, V7StagingTable table)
{
_log.DebugFormat("Adding consolidated Id values to history table: {0}", table.Name);
var numChanges = 0;
foreach (var c in table.ConsolidatedIdCols)
{
var ctype = c.ConsolidationType;
var consolidatedColumnName = c.ConsolidationIdColName;
var federatedColumnName = FindFederatedColumnName(c, table);
if (ctype == ConsolidationType.None)
{
// this is a generic column such as "resource_id" where we don't know
// the entity type without reference to the value stored in its corresponding
// 'type' column (e.g. "resource_type")...
var entityDefCol = c.EntityDefinitionColName;
var entityTypesUsed = GetEntityTypedUsed(tc, table.Name, entityDefCol);
foreach (var ct7EType in entityTypesUsed)
{
// e.g.
// update h
// set h.consolidated_resource_id = cd.consolidation_id
// from HISTORY.CT_AT_AUX_MARK h
// inner join
// FEDERATION.CONSOLIDATION_DETAIL_ROOM cd
// on h.federated_resource_id = cd.ctds_id
// and h.history_federated = 0
// and h.resource_type = 604
// and h.history_status in ('I', 'U')
var sb = new SqlBuilder();
sb.Append("update h");
sb.AppendFormat("set h.{0} = cd.{1}", consolidatedColumnName, FederationSchema.ConsolidationIdColName);
sb.AppendFormat(
"from {0} h", DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, table.Name));
sb.Append("inner join");
string qualifiedConsolidationDetailTable =
DatabaseUtils.GetQualifiedTableName(
FederationSchema.FederationSchemaName, EntityUtils.ToConsolidationDetailTableName(EntityUtils.FromCt7Entity(ct7EType)));
sb.AppendFormat("{0} cd", qualifiedConsolidationDetailTable);
sb.AppendFormat("on h.{0} = cd.{1}", federatedColumnName, FederationSchema.MasterIdColName);
sb.AppendFormat("and h.{0} = 0", HistorySchema.HistoryFederatedColumnName);
sb.AppendFormat("and h.{0} = {1}", entityDefCol, (int)ct7EType);
sb.AppendFormat("and h.{0} in ({1})", HistorySchema.HistoryStatusColumnName, RestrictedStatusValuesAsCsv());
numChanges += DatabaseUtils.ExecuteSql(tc, sb.ToString(), _timeoutSecs);
}
}
else
{
// e.g.
// update h
// set h.consolidated_dept_id = cd.consolidation_id
// from HISTORY.CT_ROOM h
// inner join
// FEDERATION.CONSOLIDATION_DETAIL_DEPT cd
// on h.federated_dept_id = cd.ctds_id
// and h.history_federated = 0
// and h.history_status in ('I', 'U')
var sb = new SqlBuilder();
sb.Append("update h");
sb.AppendFormat("set h.{0} = cd.{1}", consolidatedColumnName, FederationSchema.ConsolidationIdColName);
sb.AppendFormat(
"from {0} h", DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, table.Name));
sb.Append("inner join");
var qualifiedConsolidationDetailTable =
DatabaseUtils.GetQualifiedTableName(
FederationSchema.FederationSchemaName,
EntityUtils.ToConsolidationDetailTableName(ConsolidationTypeUtils.ToEntity(ctype)));
sb.AppendFormat("{0} cd", qualifiedConsolidationDetailTable);
sb.AppendFormat("on h.{0} = cd.{1}", federatedColumnName, FederationSchema.MasterIdColName);
sb.AppendFormat("and h.{0} = 0", HistorySchema.HistoryFederatedColumnName);
sb.AppendFormat("and h.{0} in ({1})", HistorySchema.HistoryStatusColumnName, RestrictedStatusValuesAsCsv());
numChanges += DatabaseUtils.ExecuteSql(tc, sb.ToString(), _timeoutSecs);
}
}
return(numChanges);
}
public IUpdateable <T> UpdateColumns(Expression <Func <T, bool> > columns)
{
var binaryExp = columns.Body as BinaryExpression;
Check.Exception(!binaryExp.NodeType.IsIn(ExpressionType.Equal), "No support {0}", columns.ToString());
Check.Exception(!(binaryExp.Left is MemberExpression) && !(binaryExp.Left is UnaryExpression), "No support {0}", columns.ToString());
Check.Exception(ExpressionTool.IsConstExpression(binaryExp.Left as MemberExpression), "No support {0}", columns.ToString());
var expResult = UpdateBuilder.GetExpressionValue(columns, ResolveExpressType.WhereSingle).GetResultString().Replace("))", ") )").Replace("((", "( (").Trim().TrimStart('(').TrimEnd(')');
string key = SqlBuilder.GetNoTranslationColumnName(expResult);
UpdateBuilder.SetValues.Add(new KeyValuePair <string, string>(SqlBuilder.GetTranslationColumnName(key), expResult));
this.UpdateBuilder.DbColumnInfoList = this.UpdateBuilder.DbColumnInfoList.Where(it => UpdateBuilder.SetValues.Any(v => SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.DbColumnName, StringComparison.CurrentCultureIgnoreCase) || SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.PropertyName, StringComparison.CurrentCultureIgnoreCase)) || it.IsPrimarykey == true).ToList();
return(this);
}
public IUpdateable <T> UpdateColumns(Expression <Func <T, T> > columns)
{
var expResult = UpdateBuilder.GetExpressionValue(columns, ResolveExpressType.Update);
var resultArray = expResult.GetResultArray();
Check.ArgumentNullException(resultArray, "UpdateColumns Parameter error, UpdateColumns(it=>new T{ it.id=1}) is valid, UpdateColumns(it=>T) is error");
if (resultArray.HasValue())
{
foreach (var item in resultArray)
{
string key = SqlBuilder.GetNoTranslationColumnName(item);
UpdateBuilder.SetValues.Add(new KeyValuePair <string, string>(SqlBuilder.GetTranslationColumnName(key), item));
}
}
this.UpdateBuilder.DbColumnInfoList = this.UpdateBuilder.DbColumnInfoList.Where(it => UpdateBuilder.SetValues.Any(v => SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.DbColumnName, StringComparison.CurrentCultureIgnoreCase) || SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.PropertyName, StringComparison.CurrentCultureIgnoreCase)) || it.IsPrimarykey == true).ToList();
return(this);
}
private void VisitFunction_UtcNow(DbFunctionExpression e)
{
Debug.Assert(e.ParamList.Count == 0);
SqlBuilder.Append("UTC_TIMESTAMP()");
}
public static async Task TestAsync(string connString)
{
int count = 0;
var user = new User {
UniqueId = 1, UserName = "******", Age = 28, Sex = Sex.Male, DeptId = 1, UpdatedAt = DateTime.Now
};
var user1 = new User {
UniqueId = 2, UserName = "******", Age = 24, Sex = Sex.Female, DeptId = 2, UpdatedAt = DateTime.Now
};
var dept = new Dept {
UniqueId = 1, DeptName = "IT", PersonTotal = 1, UpdatedAt = DateTime.Now
};
var dept1 = new Dept {
UniqueId = 2, DeptName = "HR", PersonTotal = 1, UpdatedAt = DateTime.Now
};
var repository = new Repository <User>(connString);
//删除
count = await repository.DeleteAsync(user);
count = await repository.DeleteAsync(user1);
//创建
count = await repository.CreateAsync(user);
count = await repository.CreateAsync(user1);
//获取
user = await repository.QueryFirstAsync("SELECT Id UniqueId,UserName,Sex FROM Coin_User WHERE Id=@UniqueId", user);
user = await repository.GetAsync(user);
//更新
user.UserName = "******";
user.Sex = Sex.Female;
count = await repository.UpdateAsync(f => f.Sex, user);
count = await repository.UpdateAsync(f => new { f.UserName, f.Sex }, user);
count = await repository.UpdateAsync(user);
//动态SQL更新
var builder = new SqlBuilder();
builder.RawSql("UPDATE Coin_User SET UserName=@UserName", user.UserName)
.AddField(user.Sex.HasValue, "Sex=@Sex", user.Sex)
.RawSql("WHERE Id=@UniqueId", user.UniqueId)
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt", user.UpdatedAt);
count = await repository.UpdateAsync(builder.BuildSql(), user);
count = await repository.UpdateAsync(f => f.RawSql("UPDATE Coin_User SET UserName=@UserName", user.UserName)
.AddField(user.Sex.HasValue, "Sex=@Sex", user.Sex)
.RawSql("WHERE Id=@UniqueId", user.UniqueId)
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt", user.UpdatedAt));
//查询&动态SQL
var list = await repository.QueryAsync(f =>
{
f.RawSql("SELECT * FROM Coin_User")
.AndWhere(user.Sex.HasValue, "Sex=@Sex")
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt")
.RawSql("ORDER BY UpdatedAt DESC");
});
//分页
var userInfoList = await repository.QueryPageAsync <UserInfo>("SELECT Id UniqueId,UserName,Sex FROM Coin_User WHERE Id>@UniqueId", 0, 10, "ORDER BY Id", user);
//事务
var context = new RepositoryContext(connString);
try
{
var repository1 = context.RepositoryFor();
var repositoryUser = context.RepositoryFor <User>();
var repositoryDept = context.RepositoryFor <Dept>();
context.Begin();
count = await repositoryDept.CreateAsync(dept);
count = await repositoryDept.CreateAsync(dept1);
var deptInfo = await repository1.QueryFirstAsync <DeptInfo>("SELECT A.DeptId,B.PersonTotal FROM Coin_User A,Coin_Dept B WHERE A.DeptId=B.Id AND A.Id=@UniqueId", new { UniqueId = 1 });
count = await repositoryUser.DeleteAsync(new User { UniqueId = 1 });
count = await repositoryDept.UpdateAsync(f => f.PersonTotal, new Dept { UniqueId = deptInfo.DeptId, PersonTotal = deptInfo.PersonTotal - 1 });
context.Commit();
}
catch
{
context.Rollback();
}
//多结果集
var order = new Order {
Id = 1
};
var orderRepository = new Repository <Order>(connString);
var sql = "SELECT * FROM Coin_Order WHERE Id=@Id;SELECT * FROM Coin_OrderLine WHERE OrderId=@Id";
List <OrderLine> orderLineList = new List <OrderLine>();
orderLineList.Add(new OrderLine
{
LineId = 1,
ProductId = 1,
Price = 15.5,
Quantity = 2,
Amount = 15.5 * 2
});
orderLineList.Add(new OrderLine
{
LineId = 2,
ProductId = 2,
Price = 20,
Quantity = 2,
Amount = 40
});
var order1 = new Order {
Id = 1, BuyerId = 1, Number = "12345", Lines = orderLineList
};
count = await orderRepository.DeleteAsync(order1);
count = await orderRepository.CreateAsync(order1);
var reader = await orderRepository.QueryMultipleAsync(sql, order);
order = reader.Read <Order>();
order.Lines = reader.ReadList <OrderLine>();
order = await orderRepository.QueryMapAsync(map =>
{
var result = map.Read <Order>();
result.Lines = map.ReadList <OrderLine>();
return(result);
}, sql, order);
order.Number = "123456789";
await orderRepository.UpdateAsync(f => f.Number, order);
}
public ParametrosRepository(IConfiguration configuration) : base(configuration)
{
_sqlBuilder = new SqlBuilder <Parametro, ParametrosMap>();
}
private void edit_gdxfx2()
{
// 1. 查合同信息
string responseError = "{success: false}";
string sql = string.Format("select * from sq8szxlx.zpgl where id='{0}'", Request.Params["id"]);
RowObject r1 = DBHelper.GetRow(sql);
if (r1 == null)
{
Response.Write(responseError);
Response.End();
return;
}
string gyy_mc = r1["所属工业园"].ToString();
string fclx = r1["房产类型"].ToString();
string htbh = r1["编码"].ToString();
// 2. 查zpgl_lx_lb看是否有记录
sql = string.Format("select * from sq8szxlx.zpgl_lx_lb where 合同编号='{0}'", htbh);
ResultObject zpgl_lx_lb = DBHelper.GetResult(sql);
if (zpgl_lx_lb.Count <= 0) // 没有记录则从gyy_lb_fclx_lb_xflx复制一份
{
sql = string.Format("select * from sq8szxlx.gyy_lb_fclx_lb_xflx where 工业园名称='{0}' and 房产类型='{1}' order by 序号 asc",
gyy_mc, fclx);
ResultObject gyy_lb_fclx_lb_xflx = DBHelper.GetResult(sql);
foreach (RowObject item in gyy_lb_fclx_lb_xflx)
{
NameValueCollection nvc = new NameValueCollection();
nvc.Add("合同编号", htbh);
nvc.Add("客户编码", r1["客户编码"].ToString());
nvc.Add("所属工业园", gyy_mc);
nvc.Add("房产类型", fclx);
nvc.Add("所属房产", r1["所属房产"].ToString());
nvc.Add("客户名称", r1["客户名称"].ToString());
nvc.Add("消费项目", item["消费项目"].ToString());
nvc.Add("消费类型", item["消费类型"].ToString());
nvc.Add("值", item["值"].ToString());
nvc.Add("倍率", item["倍率"].ToString());
nvc.Add("损耗", item["损耗"].ToString());
nvc.Add("滞纳金", item["滞纳金"].ToString());
nvc.Add("说明", item["说明"].ToString());
string insertSql = SqlBuilder.NameValueToSql(nvc, "sq8szxlx.zpgl_lx_lb", "id", true);
DBHelper.ExecuteSql(insertSql);
}
zpgl_lx_lb = DBHelper.GetResult(sql);// 重新查询
}
// 合成数据
int i = 1;
foreach (RowObject item in zpgl_lx_lb)
{
string xfxm = item["消费项目"].ToString();
string rq = r1["合同开始时间"].ToString();
sql = string.Format("select * from sq8szxlx.user_sf_lb where 合同编号='{0}' and 收费项目='{1}' and 日期='{2}'", htbh, xfxm, rq);
RowObject r4 = DBHelper.GetRow(sql);
if (r4 == null)
{
item.Add("编号", i);
item.Add("前期读数", item["消费类型"].ToString() == "动态" ? "-" : "");
item.Add("读数导入", "×");
item.Add("项目导入", "×");
i++;
}
else
{
item.Add("编号", i);
item.Add("前期读数", item["消费类型"].ToString() == "动态" ? r4["读数"] : "-");
item.Add("读数导入", (r4 != null && r4["录入状态"].ToString() == "已录入") ? "√" : "×");
item.Add("项目导入", (r4 != null && r4["值"] != "") ? "√" : "×");
i++;
}
}
Response.Write(string.Format("{{'success': true, 'data':{0}}}", JsonConvert.SerializeObject(zpgl_lx_lb)));
}
public IDeleteable <T> Where(List <T> deleteObjs)
{
if (deleteObjs == null || deleteObjs.Count() == 0)
{
Where(SqlBuilder.SqlFalse);
return(this);
}
string tableName = this.Context.EntityProvider.GetTableName <T>();
var primaryFields = this.GetPrimaryKeys();
var isSinglePrimaryKey = primaryFields.Count == 1;
Check.Exception(primaryFields.IsNullOrEmpty(), string.Format("Table {0} with no primarykey", tableName));
if (isSinglePrimaryKey)
{
List <object> primaryKeyValues = new List <object>();
var primaryField = primaryFields.Single();
foreach (var deleteObj in deleteObjs)
{
var entityPropertyName = this.Context.EntityProvider.GetPropertyName <T>(primaryField);
var columnInfo = EntityInfo.Columns.Single(it => it.PropertyName.Equals(entityPropertyName, StringComparison.CurrentCultureIgnoreCase));
var value = columnInfo.PropertyInfo.GetValue(deleteObj, null);
primaryKeyValues.Add(value);
}
if (primaryKeyValues.Count < 10000)
{
var inValueString = primaryKeyValues.ToArray().ToJoinSqlInVals();
Where(string.Format(DeleteBuilder.WhereInTemplate, SqlBuilder.GetTranslationColumnName(primaryFields.Single()), inValueString));
}
else
{
if (DeleteBuilder.BigDataInValues == null)
{
DeleteBuilder.BigDataInValues = new List <object>();
}
DeleteBuilder.BigDataInValues.AddRange(primaryKeyValues);
DeleteBuilder.BigDataFiled = primaryField;
}
}
else
{
StringBuilder whereInSql = new StringBuilder();
foreach (var deleteObj in deleteObjs)
{
StringBuilder orString = new StringBuilder();
var isFirst = deleteObjs.IndexOf(deleteObj) == 0;
if (!isFirst)
{
orString.Append(DeleteBuilder.WhereInOrTemplate + UtilConstants.Space);
}
int i = 0;
StringBuilder andString = new StringBuilder();
foreach (var primaryField in primaryFields)
{
if (i != 0)
{
andString.Append(DeleteBuilder.WhereInAndTemplate + UtilConstants.Space);
}
var entityPropertyName = this.Context.EntityProvider.GetPropertyName <T>(primaryField);
var columnInfo = EntityInfo.Columns.Single(it => it.PropertyName == entityPropertyName);
var entityValue = columnInfo.PropertyInfo.GetValue(deleteObj, null);
if (this.Context.CurrentConnectionConfig.DbType == DatabaseType.Oracle)
{
andString.AppendFormat(DeleteBuilder.WhereInEqualTemplate, primaryField.ToUpper(), entityValue);
}
else
{
andString.AppendFormat(DeleteBuilder.WhereInEqualTemplate, primaryField, entityValue);
}
++i;
}
orString.AppendFormat(DeleteBuilder.WhereInAreaTemplate, andString);
whereInSql.Append(orString);
}
Where(string.Format(DeleteBuilder.WhereInAreaTemplate, whereInSql.ToString()));
}
return(this);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["admin_id"] == null)
{
throw new SessionLostException();
}
string action = Request.Params["action"];
if (String.IsNullOrEmpty(action))
{
return;
}
Response.ContentType = "application/json";
if (action == "list")
{
string select = string.Format(@"select top {0} * ", Request["limit"]);
string from = " from sq8szxlx.zpgl ";
string where = " where 合同结束时间_年 is not null ";
string order = " order by 所属工业园,房产类型,所属房产 asc ";
if (Common.hasValue(Request.Params["iFieldName"]))
{
where += string.Format(" and 客户名称 like '%{0}%' ", Request.Params["iFieldName"]);
}
if (Common.hasValue(Request.Params["iFieldNo"]))
{
where += string.Format(" and 编码 like '%{0}%' ", Request.Params["iFieldNo"]);
}
if (Common.hasValue(Request.Params["gyy"]))
{
where += string.Format(" and 所属工业园='{0}' ", Request.Params["gyy"]);
}
if (Common.hasValue(Request.Params["leix"]))
{
where += string.Format(" and 房产类型='{0}' ", Request.Params["leix"]);
}
// 2. 获取总数
string count = DBHelper.GetVar("select count(*) " + from + where).ToString();
if (count == null)
{
return;
}
string sql = string.Format(@"{0} {1} {2} and id not in (select top {3} id {1} {2} {4}) {4}",
select, from, where, Request.Params["start"], order);
// 4. 拼装结果
ResultObject ro = DBHelper.GetResult(sql);
foreach (RowObject row in ro)
{
row["所属房产"] = row["房产类型"].ToString() + "-" + row["所属房产"].ToString();
}
string data = ro.ToJson();
string result = string.Format("success:true,totalProperty:{0},data:", count, sql);
result = "{" + result + data + "}";
Response.Write(result);
}
else if (action == "update" || action == "add")
{
Dictionary <string, object> dict = Common.CopyFormToDict(Request.Form);
if (dict["编码"].ToString() == "自动产生")
{
string dt = DateTime.Now.ToString("yyyyMMddhhmmssffff");
dict["编码"] = "ht" + dt;
}
string admin_id = (string)Session["admin_id"];
string fc = dict["所属房产"].ToString();
string[] fcl = fc.Split('-');
dict["房产类型"] = fcl[0];
dict["所属房产"] = fcl[1];
DateTime dt1 = DateTime.Parse(dict["合同开始时间"].ToString());
DateTime dt2 = DateTime.Parse(dict["合同结束时间"].ToString());
dict.Add("合同开始时间_年", dt1.Year);
dict.Add("合同开始时间_月", dt1.Month);
dict.Add("合同开始时间_日", dt1.Day);
dict.Add("合同结束时间_年", dt2.Year);
dict.Add("合同结束时间_月", dt2.Month);
dict.Add("合同结束时间_日", dt2.Day);
dict.Add("客户编码", Common.getKhbh(dict["客户名称"].ToString()));
dict.Add("操作人", admin_id);
string sql = SqlBuilder.NameValueToSql(dict, "sq8szxlx.zpgl", "id", action == "add");
DBHelper.ExecuteSql(sql);
//新增，修改合同时 修改 gyy_fc_lb里的出租状态和业主编号
string khbm = Common.getKhbh(dict["客户名称"].ToString());
string sql1 = string.Format("update sq8szxlx.gyy_fc_lb set 业主='{0}',状态='已租' where 工业园名称='{1}' and 房产类型='{2}' and 房号='{3}' ", khbm, dict["所属工业园"].ToString(), dict["房产类型"].ToString(), dict["所属房产"].ToString());
DBHelper.ExecuteSql(sql1);
if (action == "add") //新增合同时添加 导入消费项
{
string fclx = dict["房产类型"].ToString();
string ssgyy = dict["所属工业园"].ToString();
string ssfc = dict["所属房产"].ToString();
string kumc = dict["客户名称"].ToString();
string sql2 = string.Format("select * from sq8szxlx.gyy_lb_fclx_lb_xflx where 工业园名称='{0}' and 房产类型='{1}' ", ssgyy, fclx);
ResultObject fcxflx = DBHelper.GetResult(sql2);
foreach (RowObject item in fcxflx)
{
NameValueCollection fcxf = new NameValueCollection();
fcxf.Add("所属工业园", item["工业园名称"].ToString());
fcxf.Add("房产类型", item["房产类型"].ToString());
fcxf.Add("所属房产", ssfc);
fcxf.Add("消费项目", item["消费项目"].ToString());
fcxf.Add("消费类型", item["消费类型"].ToString());
fcxf.Add("值", item["值"].ToString());
fcxf.Add("倍率", item["倍率"].ToString());
fcxf.Add("损耗", item["损耗"].ToString());
fcxf.Add("滞纳金", item["滞纳金"].ToString());
fcxf.Add("说明", item["说明"].ToString());
fcxf.Add("客户名称", kumc);
fcxf.Add("合同编号", dict["编码"].ToString());
fcxf.Add("客户编码", Common.getKhbh(kumc));
string insertSql = SqlBuilder.NameValueToSql(fcxf, "sq8szxlx.zpgl_lx_lb", "id", true);
DBHelper.ExecuteSql(insertSql);
}
}
Response.Write("{success: true}");
}
else if (action == "delete")
{
RowObject row = DBHelper.GetRow("select * from sq8szxlx.zpgl where id=" + Request.Form["id"]);
// 修改房产状态
Common.updateFCZT(row["所属工业园"].ToString(), row["房产类型"].ToString(), row["所属房产"].ToString(), "无", "未租");
string sql = "delete from sq8szxlx.zpgl where id=" + Request.Form["id"];
DBHelper.ExecuteSql(sql);
Response.Write("{success: true}");
}
else if (action == "gyy_fc_lb")
{
string gyy = Request.Params["gyy"];
string sql = string.Format("select 房产类型+'-'+房号 as fc from sq8szxlx.gyy_fc_lb where 工业园名称='{0}' and 状态='未租' ", gyy);
ResultObject r = DBHelper.GetResult(sql);
Response.Write(r.ToJson());
}
else if (action == "fclx_list")
{
string sql = "select distinct 工业园名称 as gyyName from sq8szxlx.gyy_lb_fclx_lb ";
ResultObject r = DBHelper.GetResult(sql);
Response.Write(r.ToJson());
}
else if (action == "find_gyy_fclx")
{
string gyy = Request.Params["gyy"];
string sql = string.Format("select 房产类型 as lx from sq8szxlx.gyy_lb_fclx_lb where 工业园名称='{0}'", gyy);
Response.Write(DBHelper.GetResult(sql).ToJson());
}
else if (action == "edit_gdxfx") // 编辑固定消费项
{
edit_gdxfx2();
}
else if (action == "import_gdxfx") // 导入固定消费项到合同
{
string responseError = "{success: false}";
//JSONArray ja = JSONConvert.DeserializeArray(Request.Form["data"]);
JArray ja = JArray.Parse(Request.Form["data"]);
string sql1 = string.Format("select * from sq8szxlx.zpgl where id='{0}'", Request.Params["id"]);
RowObject r1 = DBHelper.GetRow(sql1);
if (r1 == null)
{
Response.Write(responseError);
Response.End();
return;
}
string gyy_mc = r1["所属工业园"].ToString();
string fclx = r1["房产类型"].ToString();
string ssfc = r1["所属房产"].ToString();
string htbh = r1["编码"].ToString();
string khbm = r1["客户编码"].ToString();
string khmc = r1["客户名称"].ToString();
// 清除数据
sql1 = string.Format("delete from sq8szxlx.zpgl_lx_lb where 合同编号='{0}'", htbh);
DBHelper.ExecuteSql(sql1);
sql1 = string.Format("delete from sq8szxlx.user_sf_lb where 单据编号='{0}'", htbh + "_1");
DBHelper.ExecuteSql(sql1);
sql1 = string.Format("delete from sq8szxlx.user_sf_zb where 单据编号='{0}'", htbh + "_1");
DBHelper.ExecuteSql(sql1);
// 新增数据
//string sql2 = string.Format("select * from sq8szxlx.gyy_lb_fclx_lb_xflx where 工业园名称='{0}' and 房产类型='{1}' order by 序号 asc",
// gyy_mc, fclx);
//ResultObject r2 = DBHelper.GetResult(sql2);
//for (int i = 0; i < r2.Count; i++)
for (int i = 0; i < ja.Count; i++)
{
//JSONObject jo = (JSONObject)ja[i];
JToken jo = ja[i];
//RowObject item = r2[i];
// zpgl_lx_lb
Dictionary <string, object> nv1 = new Dictionary <string, object>();
nv1.Add("合同编号", htbh);
nv1.Add("客户编码", khbm);
nv1.Add("所属工业园", gyy_mc);
nv1.Add("房产类型", fclx);
nv1.Add("所属房产", ssfc);
nv1.Add("客户名称", khmc);
nv1.Add("消费项目", jo["消费项目"].ToString());
nv1.Add("消费类型", jo["消费类型"].ToString());
nv1.Add("值", jo["值"]);
nv1.Add("损耗", jo["消费类型"].ToString() == "动态" ? jo["损耗"] : 0);
nv1.Add("倍率", jo["倍率"]);
nv1.Add("滞纳金", jo["滞纳金"]);
nv1.Add("说明", jo["说明"]);
string sql3 = SqlBuilder.NameValueToSql(nv1, "sq8szxlx.zpgl_lx_lb", "id", true);
DBHelper.ExecuteSql(sql3);
// user_sf_lb
Dictionary <string, object> nv2 = new Dictionary <string, object>();
nv2.Add("合同编号", htbh);
nv2.Add("单据编号", htbh + "_1");
nv2.Add("客户编号", khbm);
nv2.Add("日期年", r1["合同开始时间_年"]);
nv2.Add("日期月", r1["合同开始时间_月"]);
nv2.Add("日期日", r1["合同开始时间_日"]);
nv2.Add("日期", r1["合同开始时间"]);
nv2.Add("收费项目", jo["消费项目"]);
nv2.Add("收费类型", jo["消费类型"]);
nv2.Add("值", jo["值"]);
nv2.Add("录入状态", (jo["消费类型"].ToString() == "动态" && (jo["前期读数"] == null || jo["前期读数"].ToString() == "-")) ? "未录入" : "已录入");
nv2.Add("缴费状态", "不要交费");
nv2.Add("损耗", jo["消费类型"].ToString() == "动态" ? jo["损耗"] : 0);
if (jo["消费类型"].ToString() == "动态" && (jo["前期读数"] != null && jo["前期读数"].ToString() != "-"))
{
nv2.Add("读数", jo["前期读数"]);
}
else
{
nv2.Add("读数", 0);
}
nv2.Add("倍率", jo["倍率"]);
nv2.Add("滞纳金", jo["滞纳金"]);
nv2.Add("费用", 0);
nv2.Add("金额", 0);
string sql4 = SqlBuilder.NameValueToSql(nv2, "sq8szxlx.user_sf_lb", "id", true);
DBHelper.ExecuteSql(sql4);
// user_sf_zb
Dictionary <string, object> nv3 = new Dictionary <string, object>();
nv3.Add("合同编号", htbh);
nv3.Add("单据编号", htbh + "_1");
nv3.Add("客户编号", khbm);
nv3.Add("日期年", r1["合同开始时间_年"]);
nv3.Add("日期月", r1["合同开始时间_月"]);
nv3.Add("日期日", r1["合同开始时间_日"]);
nv3.Add("日期", r1["合同开始时间"]);
nv3.Add("总费用", 0);
nv3.Add("缴费金额", 0);
nv3.Add("余额", 0);
nv3.Add("录入状态", "已录入");
nv3.Add("缴费状态", "不要交费");
string sql5 = SqlBuilder.NameValueToSql(nv3, "sq8szxlx.user_sf_zb", "id", true);
DBHelper.ExecuteSql(sql5);
}
}
else if (action == "delete_xfx")
{
string sql = "delete from sq8szxlx.zpgl_lx_lb where id=" + Request.Params["id"];
DBHelper.ExecuteSql(sql);
}
else if (action == "add_fcxf")
{
Dictionary <string, object> dict = Common.CopyFormToDict(Request.Form);
string aa = Request.Form["所属房产"].ToString();
string[] a = aa.Split('-');
dict["所属房产"] = a[1];
string sql = SqlBuilder.NameValueToSql(dict, "sq8szxlx.zpgl_lx_lb", "id", true);
DBHelper.ExecuteSql(sql);
Response.Write("{success: true}");
}
Response.End();
}
public IUpdateable <T> SetColumns(string fieldName, object fieldValue)
{
ThrowUpdateByObject();
var columnInfo = this.EntityInfo.Columns.FirstOrDefault(it => it.PropertyName.EqualCase(fieldName));
if (columnInfo != null)
{
fieldName = columnInfo.DbColumnName;
}
var parameterName = this.SqlBuilder.SqlParameterKeyWord + "Const" + this.UpdateBuilder.LambdaExpressions.ParameterIndex;
this.UpdateBuilder.LambdaExpressions.ParameterIndex = this.UpdateBuilder.LambdaExpressions.ParameterIndex + 1;
if (UpdateBuilder.Parameters == null)
{
UpdateBuilder.Parameters = new List <SugarParameter>();
}
UpdateBuilder.Parameters.Add(new SugarParameter(parameterName, fieldValue));
UpdateBuilder.SetValues.Add(new KeyValuePair <string, string>(SqlBuilder.GetTranslationColumnName(fieldName), $"{SqlBuilder.GetTranslationColumnName(fieldName)}={parameterName}"));
this.UpdateBuilder.DbColumnInfoList = this.UpdateBuilder.DbColumnInfoList.Where(it => (UpdateParameterIsNull == false && IsPrimaryKey(it)) || UpdateBuilder.SetValues.Any(v => SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.DbColumnName, StringComparison.CurrentCultureIgnoreCase) || SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.PropertyName, StringComparison.CurrentCultureIgnoreCase)) || it.IsPrimarykey == true).ToList();
AppendSets();
return(this);
}
private static Tuple <string, Param[], List <object> > GetCommitStatement(ChangeNode rootNode, DbConnection connection)
{
var dialect = DialectFactory.GetProvider(connection);
var dataEntities = new List <object>();
var sql = new StringBuilder();
var statementId = 0;
// Inserts
var newNodes = GetChanges(rootNode, ObjectState.New);
var tableCreated = false;
var tempTableName = dialect.GetTemporaryTableName("ID");
var allParameters = new List <Param>();
foreach (var newNode in newNodes.Where(n => n.IsObject))
{
var item = newNode.Value;
if (item != null)
{
dataEntities.Add(item);
}
else
{
continue;
}
var objectType = newNode.Value.GetType();
var propertyMap = Reflector.GetPropertyMap(objectType);
var autoGenProperty = propertyMap.Where(p => p.Key.CanWrite && p.Value != null && p.Value.IsAutoGenerated).Select(p => p.Key).FirstOrDefault();
var autoGenType = DbType.String;
if (!tableCreated)
{
if (autoGenProperty != null)
{
autoGenType = Reflector.ClrToDbType(autoGenProperty.PropertyType);
}
sql.AppendFormat(dialect.CreateTemporaryTable("ID", new Dictionary <string, DbType> {
{ "StatementId", DbType.Int32 }, { "GeneratedId", autoGenType }, { "ParameterName", DbType.AnsiString }, { "PropertyName", DbType.AnsiString }
})).AppendLine();
tableCreated = true;
}
var parameters = ObjectExtensions.GetInsertParameters(newNode.Value, propertyMap, statementId);
allParameters.AddRange(parameters);
if (parameters.Length > 0)
{
string commandName = SqlBuilder.GetInsertStatement(objectType, parameters, dialect);
var autoGenParameter = parameters.FirstOrDefault(p => p.IsAutoGenerated);
if (autoGenParameter != null)
{
sql.AppendFormat(dialect.DeclareVariable(autoGenParameter.Name, autoGenType)).AppendLine();
sql.AppendFormat(dialect.AssignVariable(autoGenParameter.Name, autoGenParameter.Type.GetDefault())).AppendLine();
}
sql.AppendLine(commandName);
if (autoGenParameter != null)
{
sql.AppendFormat(dialect.ComputeAutoIncrement(autoGenParameter.Name, () => SqlBuilder.GetTableNameForSql(objectType, dialect))).AppendLine();
sql.AppendFormat("INSERT INTO " + tempTableName + " ({4}StatementId{5}, {4}GeneratedId{5}, {4}ParameterName{5}, {4}PropertyName{5}) VALUES ({0}, {1}, '{2}', '{3}')", statementId, dialect.EvaluateVariable(dialect.ParameterPrefix + autoGenParameter.Name), autoGenParameter.Name, autoGenProperty.Name, dialect.IdentifierEscapeStartCharacter, dialect.IdentifierEscapeEndCharacter).AppendLine();
}
statementId++;
}
}
if (newNodes.Count > 0)
{
sql.AppendLine("SELECT * FROM " + tempTableName);
}
if (tableCreated && !dialect.SupportsTemporaryTables)
{
sql.AppendLine("DROP TABLE " + tempTableName);
}
// Updates
var dirtyNodes = GetChanges(rootNode, ObjectState.Dirty);
var dirtyNodeParents = dirtyNodes.Where(n => n.IsSimpleLeaf).Select(n => n.Parent).Distinct();
foreach (var dirtyNode in dirtyNodeParents)
{
var objectType = dirtyNode.Value.GetType();
var propertyMap = Reflector.GetPropertyMap(objectType).ToDictionary(p => p.Key.Name, p => p);
var parameters = new List <Param>();
foreach (var change in dirtyNode.Nodes.Where(n => n.IsSimpleLeaf))
{
KeyValuePair <PropertyInfo, ReflectedProperty> map;
if (propertyMap.TryGetValue(change.PropertyName, out map))
{
var property = map.Key;
var parameterName = change.PropertyName;
if (map.Value != null && !string.IsNullOrEmpty(map.Value.ParameterName))
{
parameterName = map.Value.ParameterName;
}
parameters.Add(new Param {
Name = parameterName + "_" + statementId, Value = change.Value, Source = MapColumnAttribute.GetMappedColumnName(property)
});
}
}
var primaryKey = new List <Param>();
foreach (var primaryKeyMap in propertyMap.Values.Where(p => p.Value.IsPrimaryKey))
{
var value = ((IDataEntity)dirtyNode.Value).Property(primaryKeyMap.Key.Name);
var parameterName = primaryKeyMap.Key.Name;
if (primaryKeyMap.Value != null && !string.IsNullOrEmpty(primaryKeyMap.Value.ParameterName))
{
parameterName = primaryKeyMap.Value.ParameterName;
}
primaryKey.Add(new Param {
Name = parameterName + "_" + statementId, Value = value, Source = MapColumnAttribute.GetMappedColumnName(primaryKeyMap.Key)
});
}
var commandName = SqlBuilder.GetUpdateStatement(objectType, parameters, primaryKey, dialect);
allParameters.AddRange(parameters);
allParameters.AddRange(primaryKey);
sql.Append(commandName).AppendLine();
statementId++;
}
// Deletes
var deletedNodes = GetChanges(rootNode, ObjectState.Deleted);
foreach (var deletedNode in deletedNodes)
{
var objectType = deletedNode.Value.GetType();
var propertyMap = Reflector.GetPropertyMap(objectType);
var parameters = ObjectExtensions.GetDeleteParameters(deletedNode.Value, propertyMap, statementId);
var commandName = SqlBuilder.GetDeleteStatement(objectType, parameters, dialect);
allParameters.AddRange(parameters);
sql.Append(commandName).AppendLine();
statementId++;
}
return(Tuple.Create(sql.ToString(), allParameters.ToArray(), dataEntities));
}
public LoginInfo Login(string userId, string password, bool quitOther = false)
{
LoginInfo loginInfo = new LoginInfo();
SqlBuilder builder = new SqlBuilder("axp.User");
string sql = builder.GetQuerySql(0, "A.PERSONID,A.PERSONNAME,A.ROLEID,A.WALLPAPER,A.WALLPAPERSTRETCH", string.Format("A.USERID={0} And A.USERPASSWORD={1} And A.ISUSE=1", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(password)));
LibDataAccess dataAccess = new LibDataAccess();
string roleId = string.Empty;
bool exists = false;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
loginInfo.PersonId = LibSysUtils.ToString(reader[0]);
loginInfo.PersonName = LibSysUtils.ToString(reader[1]);
roleId = LibSysUtils.ToString(reader[2]);
loginInfo.Wallpaper = LibSysUtils.ToString(reader[3]);
loginInfo.Stretch = LibSysUtils.ToBoolean(reader[4]);
exists = true;
}
}
if (exists)
{
LibHandle handle = LibHandleCache.Default.IsExistsHandle(LibHandeleType.PC, userId);
if (handle != null)
{
if (quitOther)
{
LibHandleCache.Default.RemoveHandle(handle.Handle);
}
else
{
loginInfo.IsUsed = true;
}
}
if (!loginInfo.IsUsed)
{
long currentCount = LibHandleCache.Default.GetCount();
long maxUserCount = (long)LibHandleCache.Default.MaxUserCount;
if (maxUserCount != -1 && maxUserCount < currentCount)
{
loginInfo.IsOverUser = true;
}
else
{
string loginIp = string.Empty;
//Zhangkj20161219 增加LoginIp
System.ServiceModel.OperationContext context = System.ServiceModel.OperationContext.Current;
//对于非WCF的访问context为null
if (context != null)
{
System.ServiceModel.Channels.MessageProperties properties = context.IncomingMessageProperties;
System.ServiceModel.Channels.RemoteEndpointMessageProperty endpoint = properties[System.ServiceModel.Channels.RemoteEndpointMessageProperty.Name] as System.ServiceModel.Channels.RemoteEndpointMessageProperty;
loginIp = endpoint.Address + ":" + endpoint.Port.ToString();
}
//创建新的Handle
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, userId, loginInfo.PersonId, loginInfo.PersonName, roleId, loginIp);
if (handle != null)
{
loginInfo.Handle = handle.Handle;
}
}
}
}
return(loginInfo);
}
public IUpdateable <T> SetColumns(Expression <Func <T, bool> > columns)
{
ThrowUpdateByObject();
var binaryExp = columns.Body as BinaryExpression;
Check.Exception(!binaryExp.NodeType.IsIn(ExpressionType.Equal), "No support {0}", columns.ToString());
Check.Exception(!(binaryExp.Left is MemberExpression) && !(binaryExp.Left is UnaryExpression), "No support {0}", columns.ToString());
Check.Exception(ExpressionTool.IsConstExpression(binaryExp.Left as MemberExpression), "No support {0}", columns.ToString());
var expResult = UpdateBuilder.GetExpressionValue(columns, ResolveExpressType.WhereSingle).GetResultString().Replace(")", " )").Replace("(", "( ").Trim().TrimStart('(').TrimEnd(')');
if (expResult.EndsWith(" IS NULL "))
{
expResult = Regex.Split(expResult, " IS NULL ")[0] + " = NULL ";
}
string key = SqlBuilder.GetNoTranslationColumnName(expResult);
if (EntityInfo.Columns.Where(it => it.IsJson || it.IsTranscoding).Any(it => it.DbColumnName.EqualCase(key) || it.PropertyName.EqualCase(key)))
{
CheckTranscodeing();
}
UpdateBuilder.SetValues.Add(new KeyValuePair <string, string>(SqlBuilder.GetTranslationColumnName(key), expResult));
this.UpdateBuilder.DbColumnInfoList = this.UpdateBuilder.DbColumnInfoList.Where(it => (UpdateParameterIsNull == false && IsPrimaryKey(it)) || UpdateBuilder.SetValues.Any(v => SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.DbColumnName, StringComparison.CurrentCultureIgnoreCase) || SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.PropertyName, StringComparison.CurrentCultureIgnoreCase)) || it.IsPrimarykey == true).ToList();
AppendSets();
return(this);
}
public static void Test(string connString)
{
int count = 0;
var user = new User {
UniqueId = 1, UserName = "******", Age = 28, Sex = Sex.Male, DeptId = 1, UpdatedAt = DateTime.Now
};
var user1 = new User {
UniqueId = 2, UserName = "******", Age = 24, Sex = Sex.Female, DeptId = 2, UpdatedAt = DateTime.Now
};
var repository = new Repository <User>(connString);
//删除
count = repository.Delete(user);
count = repository.Delete(user1);
//创建
count = repository.Create(user);
count = repository.Create(user1);
//获取
user = repository.QueryFirst("SELECT Id UniqueId,UserName,Sex FROM Coin_User WHERE Id=@UniqueId", user);
user = repository.Get(user);
//更新
user.UserName = "******";
user.Sex = Sex.Female;
count = repository.Update(f => f.Sex, user);
count = repository.Update(f => new { f.UserName, f.Sex }, user);
count = repository.Update(user);
//动态SQL更新
var builder = new SqlBuilder();
builder.RawSql("UPDATE Coin_User SET UserName=@UserName")
.AndWhere(user.Sex.HasValue, "Sex=@Sex")
.RawSql("WHERE Id=@UniqueId")
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt");
count = repository.Update(builder.BuildSql(), user);
count = repository.Update(f =>
{
f.RawSql("UPDATE Coin_User SET UserName=@UserName", user.UserName)
.AddField(user.Sex.HasValue, "Sex=@Sex", user.Sex)
.RawSql("WHERE Id=@UniqueId", user.UniqueId)
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt", user.UpdatedAt);
});
//查询&动态SQL
var list = repository.Query(f =>
f.RawSql("SELECT * FROM Coin_User")
.AndWhere(user.Sex.HasValue, "Sex=@Sex", user.Sex)
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt", user.UpdatedAt)
.RawSql("ORDER BY UpdatedAt DESC"));
//分页
var userInfoList = repository.QueryPage <UserInfo>("SELECT Id UniqueId,UserName,Sex FROM Coin_User WHERE Id>@UniqueId", 0, 10, "ORDER BY Id", user);
//事务
var context = new RepositoryContext(connString);
try
{
var repository1 = context.RepositoryFor();
var repositoryUser = context.RepositoryFor <User>();
var repositoryDept = context.RepositoryFor <Dept>();
context.Begin();
var deptInfo = repository1.QueryFirst <DeptInfo>("SELECT A.DeptId,B.PersonTotal FROM Coin_User A,Coin_Dept B WHERE A.DeptId=B.Id AND A.Id=@UniqueId", new { UniqueId = 1 });
repositoryUser.Delete(new User {
UniqueId = 1
});
repositoryDept.Update(f => f.PersonTotal, new Dept {
UniqueId = deptInfo.DeptId, PersonTotal = deptInfo.PersonTotal - 1
});
context.Commit();
}
catch (Exception ex)
{
context.Rollback();
}
//多结果集
var order = new Order {
Id = 1
};
var orderRepository = new Repository <Order>(connString);
var sql = "SELECT * FROM Coin_Order WHERE Id=@Id;SELECT * FROM Coin_OrderLine WHERE OrderId=@Id";
var reader = orderRepository.QueryMultiple(sql, order);
order = reader.Read <Order>();
order.Lines = reader.ReadList <OrderLine>();
order = orderRepository.QueryMap(map =>
{
var result = map.Read <Order>();
result.Lines = map.ReadList <OrderLine>();
return(result);
}, sql, order);
order.Number = "123456789";
orderRepository.Update(f => f.Number, order);
}
public int LoadCsv(string path, TableName tname, string[] columns)
{
//create column schema list
TableSchema schema = new TableSchema(tname);
IColumn[] _columns;
try
{
_columns = GetColumnSchema(schema, columns);
}
catch (Exception ex)
{
cerr.WriteLine(ex.Message);
return(0);
}
//read .csv file
int count = 0;
using (var reader = new StreamReader(path))
{
while (!reader.EndOfStream)
{
string line = reader.ReadLine();
//Processor header
if (count == 0)
{
string[] __columns = line.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
try
{
_columns = GetColumnSchema(schema, __columns);
if (_columns.Length != 0)
{
count++;
continue;
}
}
catch (Exception)
{
}
if (_columns.Length == 0)
{
_columns = schema.Columns.ToArray();
}
}
object[] values = parseLine(_columns, line);
if (values == null)
{
return(count);
}
var builder = new SqlBuilder().INSERT(tname, columns).VALUES(values);
try
{
new SqlCmd(builder).ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
cerr.WriteLine(ex.AllMessages(builder.ToString()));
return(count);
}
count++;
}
}
return(count);
}
public IUpdateable <T> SetColumns(Expression <Func <T, T> > columns)
{
var expResult = UpdateBuilder.GetExpressionValue(columns, ResolveExpressType.Update);
var resultArray = expResult.GetResultArray();
Check.ArgumentNullException(resultArray, "UpdateColumns Parameter error, UpdateColumns(it=>new T{ it.id=1}) is valid, UpdateColumns(it=>T) is error");
if (resultArray.HasValue())
{
foreach (var item in resultArray)
{
string key = SqlBuilder.GetNoTranslationColumnName(item);
var value = item;
if (value.Contains("= \"SYSDATE\""))
{
value = value.Replace("= \"SYSDATE\"", "= SYSDATE");
}
UpdateBuilder.SetValues.Add(new KeyValuePair <string, string>(SqlBuilder.GetTranslationColumnName(key), value));
}
}
this.UpdateBuilder.DbColumnInfoList = UpdateBuilder.DbColumnInfoList.Where(it => (UpdateParameterIsNull == false && IsPrimaryKey(it)) || UpdateBuilder.SetValues.Any(v => SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.DbColumnName, StringComparison.CurrentCultureIgnoreCase) || SqlBuilder.GetNoTranslationColumnName(v.Key).Equals(it.PropertyName, StringComparison.CurrentCultureIgnoreCase)) || it.IsPrimarykey == true).ToList();
CheckTranscodeing();
AppendSets();
return(this);
}
public LoginInfo AppLogin(string userId, string password, string clientId, int clientType, bool quitOther = false)
{
LoginInfo loginInfo = new LoginInfo();
//检查是否具有 AXPUSERAPP数据表，用于判定是否支持移动端App登录
LibSqlModel sqlModel = LibSqlModelCache.Default.GetSqlModel("axp.User");
bool hasAXPUSERAPP = false;
if (sqlModel != null && sqlModel.Tables.Count > 1 && sqlModel.Tables[1].TableName.Equals("AXPUSERAPP"))
{
hasAXPUSERAPP = true;
}
if (hasAXPUSERAPP == false)
{
return(loginInfo);//如果没有需要的相关字段则直接返回
}
SqlBuilder builder = new SqlBuilder("axp.User");
string sql = string.Format(@"SELECT
A.PERSONID,
A.ROLEID,
A.WALLPAPER,A.WALLPAPERSTRETCH,B.PERSONNAME,B.PHONENO,B.CORNET,B.HEADPORTRAIT,B.MAIL
FROM AXPUSER A LEFT JOIN COMPERSON B ON B.PERSONID=A.PERSONID
WHERE A.USERID={0} and A.USERPASSWORD={1} AND A.ISUSE=1", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(password));
//builder.GetQuerySql(0, "A.PERSONID,A.PERSONNAME,A.PHONENO,A.CORNET,A.HEADPORTRAIT,A.MAIL,A.ROLEID,A.WALLPAPER,A.WALLPAPERSTRETCH", string.Format("A.USERID={0} And A.USERPASSWORD={1} And A.ISUSE=1", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(password)));
LibDataAccess dataAccess = new LibDataAccess();
string roleId = string.Empty;
bool exists = false;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
loginInfo.PersonId = LibSysUtils.ToString(reader["PERSONID"]);
loginInfo.PersonName = LibSysUtils.ToString(reader["PERSONNAME"]);
roleId = LibSysUtils.ToString(reader["ROLEID"]);
loginInfo.Wallpaper = LibSysUtils.ToString(reader["WALLPAPER"]);
loginInfo.Stretch = LibSysUtils.ToBoolean(reader["WALLPAPERSTRETCH"]);
//20170214 施卢威 增加头像 Email 短号信息
loginInfo.Headportrait = LibSysUtils.ToString(reader["HEADPORTRAIT"]);
loginInfo.UserEMail = LibSysUtils.ToString(reader["MAIL"]);
loginInfo.Cornet = LibSysUtils.ToString(reader["CORNET"]);
loginInfo.UserPhone = LibSysUtils.ToString(reader["PHONENO"]);
exists = true;
}
}
if (exists)
{
#region 帐号与登录设备关联
string appSql = string.Empty;
//查询帐号是否已有设备标识
int isAPPClient = LibSysUtils.ToInt32(dataAccess.ExecuteScalar(string.Format("SELECT COUNT(*) from AXPUSERAPP WHERE USERID = '{0}' and CLIENTTYPE={1}", userId, clientType)));
if (isAPPClient > 0)
{
//更新设备标识信息
appSql = string.Format("UPDATE AXPUSERAPP SET CLIENTID={1} WHERE USERID={0} AND CLIENTTYPE={2}", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(clientId), clientType);
}
else
{
int curMaxRowId = LibSysUtils.ToInt32(dataAccess.ExecuteScalar(string.Format("SELECT Max(ROW_ID) from AXPUSERAPP WHERE USERID='{0}'", userId)));
//插入账户对应的App设备标识信息。
appSql = string.Format("insert into AXPUSERAPP(USERID,ROW_ID,ROWNO,CLIENTTYPE,CLIENTID) values('{0}',{1},{2},{3},'{4}')",
userId, curMaxRowId + 1, curMaxRowId + 1, clientType, clientId, 1);
}
dataAccess.ExecuteNonQuery(appSql);
#endregion
LibHandle handle = LibHandleCache.Default.IsExistsHandle(LibHandeleType.PC, userId);
if (handle != null)
{
if (quitOther)
{
LibHandleCache.Default.RemoveHandle(handle.Handle);
}
else
{
loginInfo.IsUsed = true;
}
}
if (!loginInfo.IsUsed)
{
long currentCount = LibHandleCache.Default.GetCount();
long maxUserCount = (long)LibHandleCache.Default.MaxUserCount;
if (maxUserCount != -1 && maxUserCount < currentCount)
{
loginInfo.IsOverUser = true;
}
else
{
string loginIp = string.Empty;
//Zhangkj20161219 增加LoginIp
System.ServiceModel.OperationContext context = System.ServiceModel.OperationContext.Current;
//对于非WCF的访问context为null
if (context != null)
{
System.ServiceModel.Channels.MessageProperties properties = context.IncomingMessageProperties;
System.ServiceModel.Channels.RemoteEndpointMessageProperty endpoint = properties[System.ServiceModel.Channels.RemoteEndpointMessageProperty.Name] as System.ServiceModel.Channels.RemoteEndpointMessageProperty;
loginIp = endpoint.Address + ":" + endpoint.Port.ToString();
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, userId, loginInfo.PersonId, loginInfo.PersonName, roleId, loginIp);
}
else
{
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, userId, loginInfo.PersonId, loginInfo.PersonName, roleId);
}
//创建新的Handle
if (handle != null)
{
loginInfo.Handle = handle.Handle;
}
}
}
}
return(loginInfo);
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (m_FunctionArgumentValue != null)
{
sqlBuilder.ApplyArgumentValue(DataSource, OperationTypes.None, m_FunctionArgumentValue);
}
if (m_SelectClause == null)
{
var desired = materializer.DesiredColumns();
if (desired == Materializer.AutoSelectDesiredColumns)
{
desired = Materializer.AllColumns;
}
sqlBuilder.ApplyDesiredColumns(desired);
}
//Support check
if (!Enum.IsDefined(typeof(SqlServerLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"SQL Server does not support limit option {(LimitOptions)m_LimitOptions}");
}
if (m_LimitOptions == SqlServerLimitOption.TableSampleSystemRows || m_LimitOptions == SqlServerLimitOption.TableSampleSystemPercentage)
{
throw new NotSupportedException($"SQL Server does not support limit option {(LimitOptions)m_LimitOptions} with table-valued functions");
}
if (m_Seed.HasValue)
{
throw new NotSupportedException($"SQL Server does not setting a random seed for table-valued functions");
}
//Validation
if (m_Skip < 0)
{
throw new InvalidOperationException($"Cannot skip {m_Skip} rows");
}
if (m_Skip > 0 && !m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a Skip operation with out a sort expression.");
}
if (m_Skip > 0 && m_LimitOptions != SqlServerLimitOption.Rows)
{
throw new InvalidOperationException($"Cannot perform a Skip operation with limit option {m_LimitOptions}");
}
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
if ((m_LimitOptions == SqlServerLimitOption.RowsWithTies || m_LimitOptions == SqlServerLimitOption.PercentageWithTies) && !m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a WITH TIES operation without sorting.");
}
//SQL Generation
List <SqlParameter> parameters;
var sql = new StringBuilder();
string topClause = null;
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (!m_SortExpressions.Any())
{
topClause = $"TOP (@fetch_row_count_expression) ";
}
break;
case SqlServerLimitOption.Percentage:
topClause = $"TOP (@fetch_row_count_expression) PERCENT ";
break;
case SqlServerLimitOption.PercentageWithTies:
topClause = $"TOP (@fetch_row_count_expression) PERCENT WITH TIES ";
break;
case SqlServerLimitOption.RowsWithTies:
topClause = $"TOP (@fetch_row_count_expression) WITH TIES ";
break;
}
if (m_SelectClause != null)
{
sql.Append($"SELECT {topClause} {m_SelectClause} ");
}
else
{
sqlBuilder.BuildSelectClause(sql, "SELECT " + topClause, null, null);
}
sqlBuilder.BuildFromFunctionClause(sql, $" FROM {m_Table.Name.ToQuotedString()} (", " ) ");
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <SqlParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (m_SortExpressions.Any())
{
sql.Append(" OFFSET @offset_row_count_expression ROWS ");
parameters.Add(new SqlParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" FETCH NEXT @fetch_row_count_expression ROWS ONLY");
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
}
}
else
{
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
}
break;
case SqlServerLimitOption.Percentage:
case SqlServerLimitOption.PercentageWithTies:
case SqlServerLimitOption.RowsWithTies:
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
break;
}
sql.Append(";");
return(new SqlServerCommandExecutionToken(DataSource, "Query Function " + m_Table.Name, sql.ToString(), parameters));
}
private void VisitFunction_CountRows(DbFunctionExpression e)
{
Debug.Assert(e.ParamList.Count == 1);
SqlBuilder.Append("COUNT(*)");
}
/// <summary>
/// Gets the SQL.
/// </summary>
public string GetSql()
{
return(SqlBuilder.ToString().Trim());
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["admin_id"] == null)
{
throw new SessionLostException();
}
string action = Request.Params["action"];
if (String.IsNullOrEmpty(action))
{
return;
}
Response.ContentType = "application/json";
if (action == "list")
{
Response.Write(DBHelper.GetResult("select * from sq8szxlx.admin_admin").ToJson());
}
else if (action == "add")
{
NameValueCollection nvc = new NameValueCollection();
for (int i = 0; i < Request.Form.Count; i++)
{
if (Request.Form.GetKey(i) == "admin_pwd")
{
string md5 = Common.MakeMD5(Request.Form.Get(i));
nvc.Add(Request.Form.GetKey(i), md5);
}
else
{
nvc.Add(Request.Form.GetKey(i), Request.Form.Get(i));
}
}
string sql = SqlBuilder.NameValueToSql(nvc, "sq8szxlx.admin_admin", "id", true);
DBHelper.ExecuteSql(sql);
Response.Write("{success: true}");
}
else if (action == "update")
{
NameValueCollection nvc = new NameValueCollection();
for (int i = 0; i < Request.Form.Count; i++)
{
if (Request.Form.GetKey(i) == "admin_pwd")
{
string md5 = Common.MakeMD5(Request.Form.Get(i));
nvc.Add(Request.Form.GetKey(i), md5);
}
else
{
nvc.Add(Request.Form.GetKey(i), Request.Form.Get(i));
}
}
string sql = SqlBuilder.NameValueToSql(nvc, "sq8szxlx.admin_admin", "id", false);
DBHelper.ExecuteSql(sql);
Response.Write("{success: true}");
}
else if (action == "delete")
{
string sql = "delete from sq8szxlx.admin_admin where id=" + Request.Form["id"];
DBHelper.ExecuteSql(sql);
Response.Write("{success: true}");
}
else if (action == "updata_self")
{
string admin_id = (string)Session["admin_id"];
string password = Request.Params["admin_pwd"];
password = Common.MakeMD5(password);
if (admin_id != null)
{
string sql = string.Format("update sq8szxlx.admin_admin set admin_pwd='{0}' where admin_id='{1}' ", password, admin_id);
DBHelper.ExecuteSql(sql);
Response.Write("{success: true}");
}
}
else if (action == "glygl_js")
{
Response.Write(DBHelper.GetResult("select role_name as js from sq8szxlx.role").ToJson());
}
Response.End();
}
private void PreToSql()
{
UpdateBuilder.PrimaryKeys = GetPrimaryKeys();
#region IgnoreColumns
if (this.Context.IgnoreColumns != null && this.Context.IgnoreColumns.Any())
{
var currentIgnoreColumns = this.Context.IgnoreColumns.Where(it => it.EntityName == this.EntityInfo.EntityName).ToList();
this.UpdateBuilder.DbColumnInfoList = this.UpdateBuilder.DbColumnInfoList.Where(it =>
{
return(!currentIgnoreColumns.Any(i => it.PropertyName.Equals(i.PropertyName, StringComparison.CurrentCulture)));
}).ToList();
}
#endregion
if (this.IsSingle)
{
foreach (var item in this.UpdateBuilder.DbColumnInfoList)
{
if (this.UpdateBuilder.Parameters == null)
{
this.UpdateBuilder.Parameters = new List <SugarParameter>();
}
if (this.UpdateBuilder.SetValues.Any(it => this.SqlBuilder.GetNoTranslationColumnName(it.Key) == item.PropertyName))
{
continue;
}
this.UpdateBuilder.Parameters.Add(new SugarParameter(this.SqlBuilder.SqlParameterKeyWord + item.DbColumnName, item.Value, item.PropertyType));
}
}
#region Identities
List <string> identities = GetIdentityKeys();
if (identities != null && identities.Any())
{
this.UpdateBuilder.DbColumnInfoList.ForEach(it =>
{
var mappingInfo = identities.SingleOrDefault(i => it.DbColumnName.Equals(i, StringComparison.CurrentCultureIgnoreCase));
if (mappingInfo != null && mappingInfo.Any())
{
it.IsIdentity = true;
}
});
}
#endregion
List <string> primaryKey = GetPrimaryKeys();
if (primaryKey != null && primaryKey.Count > 0)
{
this.UpdateBuilder.DbColumnInfoList.ForEach(it =>
{
var mappingInfo = primaryKey.SingleOrDefault(i => it.DbColumnName.Equals(i, StringComparison.CurrentCultureIgnoreCase));
if (mappingInfo != null && mappingInfo.Any())
{
it.IsPrimarykey = true;
}
});
}
if (this.UpdateBuilder.Parameters.HasValue() && this.UpdateBuilder.SetValues.IsValuable())
{
this.UpdateBuilder.Parameters.RemoveAll(it => this.UpdateBuilder.SetValues.Any(v => (SqlBuilder.SqlParameterKeyWord + SqlBuilder.GetNoTranslationColumnName(v.Key)) == it.ParameterName));
}
}
public void BasicContrivedTest()
{
var aParam = new SqlParameter("@a", 123);
var cParam = new SqlParameter("@c", "Kramer");
var dParam = new SqlParameter("@d", "Seinfeld");
var b = new SqlBuilder<SqlParameter>()
.Select("This, That")
.Select("TheOther")
.From("MyTable")
.Where("Character = @c", cParam)
.Where("Director = @d", dParam)
.OrderBy(new[] { "This", "That", "TheOther" });
var t = b.AddTemplate(Templates.Selection);
var t2 = b.AddTemplate(Templates.Count, aParam);
var expected =
"SELECT This, That , TheOther " +
"FROM MyTable " +
"WHERE Character = @c AND Director = @d " +
"ORDER BY This , That , TheOther";
Assert.AreEqual(expected.CleanupSql(), t.RawSql.CleanupSql(), "1");
expected = "SELECT Count(*) " +
"FROM MyTable " +
"WHERE Character = @c AND Director = @d";
Assert.AreEqual(expected.CleanupSql(), t2.RawSql.CleanupSql(), "2");
CollectionAssert.AreEqual(new[] { cParam, dParam }, t.Parameters);
CollectionAssert.AreEqual(new[] { aParam, cParam, dParam }, t2.Parameters);
}
