public bool Migrate(string connectionString, string providerName, IEnumerable<Type> entities, Action<MigratorResult, Type> callbackOnChanges)
{
var bResult = false;
var provider = ProviderFactory.GetProvider(connectionString, providerName);
foreach (var entity in entities)
{
var batch = new BatchQuery(provider);
var m = new Migrator(Assembly.GetExecutingAssembly());
var commands = m.MigrateFromModel(entity, provider);
bResult |= commands.Length > 0;
try
{
foreach (var s in commands)
batch.QueueForTransaction(new QueryCommand(PrepareCommand(s), provider));
batch.ExecuteTransaction();
}
catch(Exception ex)
{
var sb = new StringBuilder(ex.Message);
sb.Append(": ");
foreach (var command in commands)
sb.Append(command);
throw new Exception(sb.ToString(), ex);
}
if (callbackOnChanges == null) continue;
callbackOnChanges(commands.Length > 0 ? MigratorResult.ChangesMadeToEntity : MigratorResult.NoChanges,
entity);
}
if (callbackOnChanges != null)
callbackOnChanges(MigratorResult.ChangesMadeToAllEntities, null);
return bResult;
}
public void Batch_Should_Build_Batched_SQL()
{
BatchQuery qry = new BatchQuery(provider);
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(1));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(2));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(3));
string sql = qry.BuildSqlStatement();
Assert.Equal(3, sql.FindMatches("SELECT").Count);
}
public void Batch_Should_Build_Batched_SQL_With_Replaced_Command_Parameters()
{
BatchQuery qry = new BatchQuery(provider);
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(1));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(2));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(3));
QueryCommand cmd = qry.GetCommand();
Assert.Equal("@0", cmd.Parameters[0].ParameterName);
Assert.Equal("@1", cmd.Parameters[1].ParameterName);
Assert.Equal("@2", cmd.Parameters[2].ParameterName);
}
public void Batch_Should_Build_Batched_SQL_Using_Linq()
{
BatchQuery qry = new BatchQuery(provider);
var pquery = new Query <Product>(provider);
qry.Queue(from p in pquery where p.ProductID == 1 select p);
qry.Queue(from p in pquery where p.ProductID == 2 select p);
qry.Queue(from p in pquery where p.ProductID == 3 select p);
string sql = qry.BuildSqlStatement();
Assert.Equal(3, sql.FindMatches("SELECT").Count);
}
/// <summary>
/// Deletes 1 or more items.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="items">The items.</param>
/// <returns></returns>
public int DeleteMany <T>(IEnumerable <T> items) where T : class, new()
{
BatchQuery batch = new BatchQuery(_provider);
int result = 0;
foreach (var item in items)
{
batch.QueueForTransaction(item.ToDeleteQuery(_provider));
result++;
}
batch.ExecuteTransaction();
return(result);
}
public void Batch_Should_Build_Batched_SQL_With_Replaced_SQL_Parameters()
{
BatchQuery qry = new BatchQuery(provider);
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(1));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(2));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(3));
string sql = qry.BuildSqlStatement();
Assert.True(sql.FindMatches("@0").Count == 1);
Assert.True(sql.FindMatches("@1").Count == 1);
Assert.True(sql.FindMatches("@2").Count == 1);
}
public List <Action> PullData()
{
var from = DateTime.Now.AddDays(-5);
var api = CreateLogin(ClientId);
var batch = new BatchQuery <Action>(new BatchOptions
{
Write = 512,
Read = 512
}, api, new LoadActionsInBatches(from, DateTime.Now), ActionLoad);
batch.Execute();
return(Actions);
}
public void Batch_Should_Build_Batched_SQL_With_Replaced_Command_Parameters()
{
BatchQuery qry = new BatchQuery(provider);
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(1));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(2));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(3));
QueryCommand cmd = qry.GetCommand();
Assert.Equal("@0", cmd.Parameters[0].ParameterName);
Assert.Equal("@1", cmd.Parameters[1].ParameterName);
Assert.Equal("@2", cmd.Parameters[2].ParameterName);
}
protected internal virtual IList <Batch> executePaginatedQuery(BatchQuery query, int?firstResult, int?maxResults)
{
if (firstResult == null)
{
firstResult = 0;
}
if (maxResults == null)
{
maxResults = int.MaxValue;
}
return(query.listPage(firstResult, maxResults));
}
/// <summary>
/// Updates the passed-in T
/// </summary>
public int Update(IEnumerable <T> items, IDataProvider provider)
{
BatchQuery bQuery = new BatchQuery(provider);
int result = 0;
foreach (T item in items)
{
var query = item.ToUpdateQuery(provider);
bQuery.Queue(query);
}
result = bQuery.Execute();
return(result);
}
public void Batch_Should_Build_Batched_SQL_With_Replaced_SQL_Parameters()
{
BatchQuery qry = new BatchQuery(provider);
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(1));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(2));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(3));
string sql = qry.BuildSqlStatement();
Assert.True(sql.FindMatches("@0").Count == 1);
Assert.True(sql.FindMatches("@1").Count == 1);
Assert.True(sql.FindMatches("@2").Count == 1);
}
public void MigrateNamespaceToDatabase(string modelNamespace, Assembly assembly)
{
var m = new Migrator(assembly);
var migrationSql = m.MigrateFromModel(modelNamespace, this);
BatchQuery query = new BatchQuery(this);
foreach (var s in migrationSql)
{
query.QueueForTransaction(new QueryCommand(s.Trim(), this));
}
//pop the transaction
query.ExecuteTransaction();
}
/// <summary>
/// Adds a lot of the items using a transaction.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="items">The items.</param>
public void AddMany <T>(IEnumerable <T> items) where T : class, new()
{
if (_options.Contains(SimpleRepositoryOptions.RunMigrations))
{
Migrate <T>();
}
BatchQuery batch = new BatchQuery(_provider);
foreach (var item in items)
{
batch.QueueForTransaction(item.ToInsertQuery(_provider));
}
batch.ExecuteTransaction();
}
/// <summary>
/// Migrates this instance.
/// </summary>
/// <typeparam name="T"></typeparam>
private void Migrate <T>() where T : class, new()
{
Type type = typeof(T);
if (!migrated.Contains(type))
{
BatchQuery batch = new BatchQuery(_provider);
Migrator m = new Migrator(Assembly.GetExecutingAssembly());
var commands = m.MigrateFromModel(type, _provider);
foreach (var s in commands)
{
batch.QueueForTransaction(new QueryCommand(s, _provider));
}
batch.ExecuteTransaction();
migrated.Add(type);
}
}
public void Batch_Query_Should_Allow_Single_Quotes_In_Query()
{
var containsNames = new[] { "Products's One and Only", "Products's Second" };
Query <Product> products = new Query <Product>(provider);
var query = from product in products
where containsNames.Contains(product.ProductName)
select product;
var result = query.ToList();
BatchQuery batch = new BatchQuery(provider);
batch.Queue(query);
Assert.DoesNotThrow(() => batch.Execute());
}
private static void Initialize(string connectString, string modelSpaceName)
{
IDataProvider provider = ProviderFactory.GetProvider(connectString);
BatchQuery query = new BatchQuery(provider);
Assembly assembly = Assembly.Load(Const.FRAME_MODEL_MASTER);
var migrator = new SubSonic.Schema.Migrator(assembly);
string[] commands = migrator.MigrateFromModel(modelSpaceName, provider);
foreach (var s in commands)
{
query.QueueForTransaction(new QueryCommand(s.Trim(), provider));
}
query.ExecuteTransaction();
}
/// <summary>
/// Updates lots of items using a transaction.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="items">The items.</param>
/// <returns></returns>
public int UpdateMany <T>(IEnumerable <T> items) where T : class, new()
{
if (_options.Contains(SimpleRepositoryOptions.RunMigrations))
{
Migrate <T>();
}
BatchQuery batch = new BatchQuery(_provider);
int result = 0;
foreach (var item in items)
{
batch.QueueForTransaction(item.ToUpdateQuery(_provider));
result++;
}
batch.ExecuteTransaction();
return(result);
}
static void Main(string[] args)
{
var clientId = "224";
var api = Login("9408065009082", "yase191!", clientId);
var start = new DateTime(2017, 10, 25, 00, 00, 00);
var end = new DateTime(2017, 10, 25, 23, 59, 59);
var batch = new BatchQuery <Action>(new BatchOptions
{
Write = 512,
Read = 512
}, api, new LoadActionsInBatches(start, end), WriteToFile);
batch.Execute();
Console.ReadLine();
}
private static void BatchInsertInto()
{
Console.WriteLine("Batch Inserting Genres using Insert.Into with unescaped values.");
IList<Genre> genres = new List<Genre>
{
new Genre { Name = "Genre's Third" },
new Genre { Name = "Genre's Fourth" }
};
BatchQuery bq = new BatchQuery(db.Provider);
foreach (Genre genre in genres)
{
bq.Queue(db.Insert.Into<Genre>(g => g.Name).Values(genre.Name));
}
ExecuteQuery(bq);
}
public static void UpdateMsts(string _clientId)
{
var api = CreateLogin(_clientId);
var batch = new BatchQuery <Entity>(new BatchOptions
{
Write = 512,
Read = 512
}, api, new LoadEntitiesInBatches(), EntityCallBack);
batch.Execute();
var batch2 = new BatchQuery <OLocation>(new BatchOptions
{
Write = 512,
Read = 512
}, api, new LoadLocationsInBatches(), DecoLookup);
batch2.Execute();
}
public static void GetPersonnelInfo(Api api)
{
try
{
var header = $"FirstName, FullName, Nature, Type, IdentityNumber{Environment.NewLine}";
var path = $@"C:\Users\YaseenH\Desktop\Adhoc\personnel.csv";
File.AppendAllText(path, header);
var batch = new BatchQuery <Personnel>(new BatchOptions
{
Write = 512,
Read = 512
}, api, new LoadPersonnelInBatches(), PersonnelLookup);
batch.Execute();
}
catch (Exception e)
{
Console.WriteLine($"{e.ToString()}...");
}
}
//JAVA TO C# CONVERTER TODO TASK: Most Java annotations will not have direct .NET equivalent attributes:
//ORIGINAL LINE: @Test public void testBatchQueryBySuspendedBatches()
public virtual void testBatchQueryBySuspendedBatches()
{
// given
Batch batch1 = helper.migrateProcessInstancesAsync(1);
Batch batch2 = helper.migrateProcessInstancesAsync(1);
helper.migrateProcessInstancesAsync(1);
// when
managementService.suspendBatchById(batch1.Id);
managementService.suspendBatchById(batch2.Id);
managementService.activateBatchById(batch1.Id);
// then
BatchQuery query = managementService.createBatchQuery().suspended();
Assert.assertEquals(1, query.count());
Assert.assertEquals(1, query.list().size());
Assert.assertEquals(batch2.Id, query.singleResult().Id);
}
public void CtorShouldExcludeNullStatements()
{
// Arrange
IEnumerable <IQuery> queries = new IQuery[]
{
InsertInto("SuperHero")
.Values(Select("firstname", "lastname", "nickname").From("DC_SuperHero").Build()),
null,
Delete("DC_SuperHero")
};
// Act
BatchQuery batchQuery = new BatchQuery(queries.ToArray());
// Assert
batchQuery.Statements.Should()
.HaveCount(2).And
.NotContainNulls();
}
public void CtorPreserveStatementOrder()
{
// Arrange
IEnumerable <IQuery> queries = new IQuery[]
{
Select(1.Literal()),
Select(2.Literal()),
Select(3.Literal())
};
// Act
BatchQuery batchQuery = new BatchQuery(queries.ToArray());
// Assert
batchQuery.Statements.Should()
.Equal(new []
{
Select(1.Literal()),
Select(2.Literal()),
Select(3.Literal())
});
}
public static void AddNewBatch(SqlConnection connection)
{
Batch batch = new Batch();
Console.WriteLine("Please, enter the batch details: ");
Console.Write("Date: ");
batch.Date = ConsoleInput.InputDate();
Console.Write("Select batch type: ");
batch.BatchType = SelectBatchType();
BatchQuery batchQuery = new BatchQuery(connection);
batchQuery.AddNewRecord(batch);
int lastId = batchQuery.GetLastId();
Console.WriteLine("Please, add products to the batch (input product's Id and quantity): ");
ProductBatchQuery productBatchQuery = new ProductBatchQuery(connection);
AddRecordsIntoProductBatch(lastId, productBatchQuery);
}
static void Main(string[] args)
{
var filePath = @"Data\Cori_June_Actions.csv";
var content = ReadContent(filePath);
var csvReader = new CsvReader();
var lines = csvReader.Read(content);
var actionLines = lines.Where(x => x != null).Select(x => new ActionLine(x)).ToList();
_actionsLines = actionLines;
//var superRoute = CreateRoute(RouteName);
var api = Login("9408065009082", "yase191!", "206");
var batch = new BatchQuery <DitAddress>(new BatchOptions
{
Write = 512,
Read = 512
}, api, new LoadDitAddressesInBatches(), Callback);
batch.Execute();
Console.ReadLine();
}
public virtual IList <BatchDto> getBatches(UriInfo uriInfo, int?firstResult, int?maxResults)
{
BatchQueryDto queryDto = new BatchQueryDto(ObjectMapper, uriInfo.QueryParameters);
BatchQuery query = queryDto.toQuery(ProcessEngine);
IList <Batch> matchingBatches;
if (firstResult != null || maxResults != null)
{
matchingBatches = executePaginatedQuery(query, firstResult, maxResults);
}
else
{
matchingBatches = query.list();
}
IList <BatchDto> batchResults = new List <BatchDto>();
foreach (Batch matchingBatch in matchingBatches)
{
batchResults.Add(BatchDto.fromBatch(matchingBatch));
}
return(batchResults);
}
public void Batch_Should_Execute_Reader_And_Return_Typed_Lists_Using_Linq()
{
BatchQuery qry = new BatchQuery(provider);
var pquery = new Query <Product>(provider);
qry.Queue(from p in pquery where p.ProductID == 1 select p);
qry.Queue(from p in pquery where p.ProductID == 2 select p);
qry.Queue(from p in pquery where p.ProductID == 3 select p);
int sets = 1;
bool canRead = false;
List <Product> result1 = null;
List <Product> result2 = null;
List <Product> result3 = null;
using (IDataReader rdr = qry.ExecuteReader())
{
result1 = rdr.ToList <Product>();
canRead = true;
if (rdr.NextResult())
{
result2 = rdr.ToList <Product>();
}
if (rdr.NextResult())
{
result3 = rdr.ToList <Product>();
}
canRead = rdr.NextResult();
}
Assert.True(result1.Count > 0);
Assert.True(result2.Count > 0);
Assert.True(result3.Count > 0);
Assert.False(canRead);
}
public static string Serialize(BatchQuery batchQuery)
{
var itemsPart = string.Join(",", batchQuery.Queries.Select(SerializeBatchQueryItem));
return("{\"items\":[" + itemsPart + $"],\"includeProcessExecutionData\":true,\"instanceId\":\"{Guid.NewGuid()}\"" + "}");
}
public void LoadTestData()
{
var batch = new BatchQuery(_provider);
//load some test data - 100 records
string city = "City1";
string company = "Company1";
for (int i = 1; i <= 100; i++)
{
if (i % 10 == 0)
{
city = "City" + i;
company = "Company" + i;
}
Customer c = new Customer();
c.Address = i + "Street";
c.City = city;
c.Region = "CA";
c.Country = "US";
c.CompanyName = company;
c.ContactName = "Charlie";
c.CustomerID = "TEST" + i;
var qry = c.ToInsertQuery(_provider);
batch.QueueForTransaction(qry);
}
batch.ExecuteTransaction();
for (int i = 1; i <= 5; i++)
{
Category c = new Category();
c.CategoryID = i;
c.CategoryName = "Category" + i;
batch.QueueForTransaction(c.ToInsertQuery(_provider));
}
batch.ExecuteTransaction();
//Products
int categoryCounter = 1;
for (int i = 1; i <= 100; i++)
{
Product p = new Product();
p.Sku = Guid.NewGuid();
p.ProductID = i;
p.ProductName = "Product" + i;
p.UnitPrice = 1.245M * i / 5.22M * 8.09M;
p.Discontinued = i % 2 == 0 ? true : false;
p.CategoryID = categoryCounter;
batch.QueueForTransaction(p.ToInsertQuery(_provider));
categoryCounter++;
if (categoryCounter > 5)
{
categoryCounter = 1;
}
}
batch.ExecuteTransaction();
//Orders
int customerID = 1;
for (int i = 1; i <= 100; i++)
{
Order o = new Order();
o.OrderDate = DateTime.Parse("1/1/2008").AddDays(-i * 12 + 23);
o.RequiredDate = DateTime.Parse("1/1/2008").AddDays(-i * 12 + 28);
o.ShippedDate = DateTime.Parse("1/1/2008").AddDays(-i * 12 + 32);
o.CustomerID = "TEST" + customerID;
o.OrderID = i;
//5 items per order
for (int j = 1; j <= 5; j++)
{
OrderDetail detail = new OrderDetail();
detail.Discount = i * 1 / 4M;
detail.OrderID = i;
detail.ProductID = i;
detail.Quantity = 2;
detail.UnitPrice = 2 * (1.245M * i / 5.22M * 8.09M);
batch.QueueForTransaction(detail.ToInsertQuery(_provider));
}
customerID++;
if (customerID > 5)
{
customerID = 1;
}
batch.QueueForTransaction(o.ToInsertQuery(_provider));
}
batch.ExecuteTransaction();
}
/// <summary>
/// (有事务)批量删除,通过sql语句进行删除
/// </summary>
/// <param name="sqls">执行sql语句集</param>
/// <returns>被删除的记录数</returns>
public int Delete(params String[] sqls)
{
try
{
//创建事务
var batch = new BatchQuery(provider);
//遍历添加
foreach (String item in sqls)
{
QueryCommand qcmd = new QueryCommand(item, provider);
batch.QueueForTransaction(qcmd);
}
//获取执行语句
_execSql = string.Join(",", sqls);
//执行事务
batch.ExecuteTransaction();
return batch.QueryCount;
}
catch (Exception e)
{
//return 0;
throw new Exception("删除数据异常,执行语句" + _execSql, e);
}
}
public override string Render(IQuery query)
{
string result = string.Empty;
ReplaceParameterBySelectQueryVisitor visitor = new ReplaceParameterBySelectQueryVisitor(
v => Select(Null("RealValue".Field(), "IntegerValue".Field(), "BlobValue".Field(), "TextValue".Field()))
.From(VariablesTempTablename)
.Where(ParameterFieldName.Field().EqualTo(v.Name))
.Paginate(pageIndex: 1, pageSize: 1)
.Build()
);
switch (query)
{
case SelectQueryBase selectQueryBase:
if (selectQueryBase is SelectQuery sq)
{
visitor.Visit(sq);
result = Render(sq);
}
result = Render(selectQueryBase);
break;
case CreateViewQuery createViewQuery:
result = Render(createViewQuery);
break;
case DeleteQuery deleteQuery:
visitor.Visit(deleteQuery);
result = Render(deleteQuery);
break;
case UpdateQuery updateQuery:
result = Render(updateQuery);
break;
case TruncateQuery truncateQuery:
result = Render(truncateQuery);
break;
case InsertIntoQuery insertIntoQuery:
result = Render(insertIntoQuery);
break;
case BatchQuery batchQuery:
result = Render(batchQuery);
break;
default:
result = base.Render(query);
break;
}
StringBuilder sbParameters = new StringBuilder(visitor.Variables.Count() * 100);
if (visitor.Variables.Any())
{
#if DEBUG
Debug.Assert(visitor.Variables.All(x => x.Value != null), $"{nameof(visitor)}.{nameof(visitor.Variables)} must not contains variables with null value");
#endif
IList <InsertIntoQuery> insertParameters = new List <InsertIntoQuery>(visitor.Variables.Count());
IList <UpdateQuery> updateParameters = new List <UpdateQuery>(visitor.Variables.Count());
foreach (Variable variable in visitor.Variables)
{
insertParameters.Add(
InsertInto(VariablesTempTablename).Values(ParameterFieldName.Field().InsertValue(variable.Name.Literal()))
.Build()
);
switch (variable.Type)
{
case VariableType.Numeric:
updateParameters.Add(
Update(VariablesTempTablename).Set("RealValue".Field().UpdateValueTo(Convert.ToInt64(variable.Value)))
.Where(ParameterFieldName.Field().EqualTo(variable.Name))
);
break;
case VariableType.Date:
case VariableType.String:
updateParameters.Add(
Update(VariablesTempTablename).Set("TextValue".Field().UpdateValueTo(variable.Value.ToString()))
.Where(ParameterFieldName.Field().EqualTo(variable.Name))
);
break;
case VariableType.Boolean:
updateParameters.Add(
Update(VariablesTempTablename).Set("IntegerValue".Field().UpdateValueTo(Convert.ToInt32(variable.Value)))
.Where(ParameterFieldName.Field().EqualTo(variable.Name))
);
break;
default:
throw new ArgumentOutOfRangeException($"Unsupported {variable.Type} as variable type");
}
}
BatchQuery batch = new BatchQuery()
.AddStatement("BEGIN".AsNative())
.AddStatement("PRAGMA temp_store = 2".AsNative())
.AddStatement($"CREATE TEMP TABLE {RenderTablename(VariablesTempTablename.Table(), renderAlias:false)}(ParameterName TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT)"
.AsNative()
)
.AddStatements(insertParameters)
.AddStatements(updateParameters)
.AddStatement($"{result}".AsNative())
.AddStatement($"DROP TABLE {RenderTablename(VariablesTempTablename.Table(), renderAlias: false)}".AsNative())
.AddStatement("END".AsNative());
result = Render(batch);
}
return(result);
}
public void Batch_Should_Execute_Reader()
{
BatchQuery qry = new BatchQuery(provider);
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(1));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(2));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(3));
int sets = 1;
bool canRead = false;
using(IDataReader rdr = qry.ExecuteReader())
{
canRead = true;
if(rdr.NextResult())
sets = 2;
if(rdr.NextResult())
sets = 3;
canRead = rdr.NextResult();
}
Assert.Equal(3, sets);
Assert.False(canRead);
}
public void Batch_Query_Should_Allow_Single_Quotes_In_Query()
{
var containsNames = new[] { "Products's One and Only", "Products's Second" };
Query<Product> products = new Query<Product>(provider);
var query = from product in products
where containsNames.Contains(product.ProductName)
select product;
var result = query.ToList();
BatchQuery batch = new BatchQuery(provider);
batch.Queue(query);
Assert.DoesNotThrow(() => batch.Execute());
}
protected void Page_Load(object sender, EventArgs e)
{
//---------------------------------------------------------
// 1、新增记录
//---------------------------------------------------------
var branch = new Branch();
branch.DeptCode = SPs.P_Branch_GetMaxBranchDeptCode(2, 1).ExecuteScalar() + "";
branch.Name = "客服部";
branch.Comment = "客户售后服务维护部门";
branch.ParentId = 1;
branch.Sort = 7;
branch.Depth = 2;
//AddDate这个属性不用赋值，ORM框架生成更新语句时会自动过滤该字段，更新时将会使用数据库中设置的默认值
//branch.AddDate = DateTime.Now;
//保存数据入库
branch.Save();
WriteLine("记录添加成功,新增Id为：" + branch.Id);
//----------------------------------------------------------
// 2、修改记录
//----------------------------------------------------------
var branchModel = Branch.SingleOrDefault(x => x.Id == branch.Id);
//也可以这样用
//var branchModel = new Branch(x => x.Id == branch.Id);
//打印读取出来的数据
WriteLine(branchModel.ToString());
//修改名称为“售后服务部”
branchModel.Name = "售后服务部";
//保存
branchModel.Save();
//重新从数据库中读取出来并打印到输出窗口
WriteLine((new Branch(x => x.Id == branch.Id)).ToString());
//----------------------------------------------------------
// 3、判断刚刚修改的记录是否存在
//----------------------------------------------------------
if (Branch.Exists(x => x.Id == branch.Id))
{
WriteLine("Id为" + branch.Id + "的记录存在！");
}
else
{
WriteLine("Id为" + branch.Id + "的记录不存在！");
}
//---------------------------------------------------------
// 4、删除记录
//---------------------------------------------------------
//删除当前记录
Branch.Delete(x => x.Id == branch.Id);
//也可以这样操作：
//branchModel.Delete();
WriteLine("删除Id为" + branch.Id + "的记录成功！");
//--------------------------------------------------------
// 5、判断刚刚删除的记录是否存在
//--------------------------------------------------------
if (Branch.Exists(x => x.Id == branch.Id))
{
WriteLine("Id为" + branch.Id + "的记录存在！");
}
else
{
WriteLine("Id为" + branch.Id + "的记录不存在！");
}
//--------------------------------------------------------
// 6、使用类实体附带的函数查询
//--------------------------------------------------------
//查找出所有记录 -- 使用All()
var list = Branch.All();
foreach (var item in list)
{
//打印记录到输出窗口
WriteLine(item.ToString());
}
//查询指定条件的记录 -- 使用Find()
IList <Branch> il = Branch.Find(x => x.Id > 2);
foreach (var item in il)
{
WriteLine(item.ToString());
}
//获取第二页记录（每页3条记录）
il = Branch.GetPaged("Id Asc", 2, 3);
foreach (var item in il)
{
WriteLine(item.ToString());
}
//使用Id倒序排序，获取第三页记录（每页3条记录）
il = Branch.GetPaged("Id Desc", 3, 3);
foreach (var item in il)
{
WriteLine(item.ToString());
}
//--------------------------------------------------
// 7、使用Select类查询
//--------------------------------------------------
//创建Select对象
//var select = new Select();
//显示指定的列
var select = new Select(new string[] { BranchTable.Id, BranchTable.Name, BranchTable.DeptCode });
//指定查询表
select.From <Branch>();
//运行完上面这条语句后，SQL已经生成出来了，在Debug状态将鼠标指向select就可以看到，往下继续执行时，每添加一个属性都会添加在生成的SQL语句中
//添加查询条件 -- Id大于2且编号头两位为01的部门
select.Where(BranchTable.Id).IsGreaterThanOrEqualTo(2).And(BranchTable.DeptCode).StartsWith("01");
//查询时括号添加列子
//select.OpenExpression().Where("").IsEqualTo(0).Or("").IsEqualTo(11).CloseExpression().And("").IsEqualTo(3);
//设置去重复 -- SubSonic没有去重复选项，需要自己手动修改DLL源码
select.Distinct(true);
//或
//select.IsDistinct = true;
//设置查询数量
select.Top("5");
//添加排序 -- 倒序
select.OrderDesc(BranchTable.Sort);
//或
//List<string> orderByList = new List<string>();
//orderByList.Add(BranchTable.Sort + " Desc");
//orderByList.Add(BranchTable.Id + " Desc");
//select.OrderBys = orderByList;
//设置分页，获取第一页记录(每页10条记录）
select.Paged(1, 10);
//执行查询 -- 返回DataTable
var dt = select.ExecuteDataTable();
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
WriteLine(dt.Rows[i][BranchTable.Id] + " " + dt.Rows[i][BranchTable.Name]);
}
}
//执行查询 -- 返回List
var bl = select.ToList <Branch>();
foreach (var item in bl)
{
WriteLine(item.ToString());
}
//查询总记录数
var recordCount = select.GetRecordCount();
WriteLine("记录数量：" + recordCount.ToString());
//-------------------------------------------------------------
// 8、HotelDBDB查询类的使用方式
//-------------------------------------------------------------
//使用数据库名称+DB作为名称的类，可以直接调用聚合函数
var db = new SubSonicTestDB();
//平均值
WriteLine("平均值：" + db.Avg <Branch>(x => x.Id).Where <Branch>(x => x.Id < 10).ExecuteScalar() + "");
//计算数量
WriteLine("计算数量：" + db.Count <Branch>(x => x.Id).ExecuteScalar() + "");
//计算合计数量
WriteLine("计算合计数量：" + db.Sum <Branch>(x => x.Id).ExecuteScalar() + "");
//最大值
WriteLine("最大值：" + db.Max <Branch>(x => x.Id).ExecuteScalar() + "");
//最小值
WriteLine("最小值：" + db.Min <Branch>(x => x.Id).ExecuteScalar() + "");
//---------------------------------------------------------------
// 9、存储过程调用方法
//---------------------------------------------------------------
//使用SPs.存储过程名称(参数1，参数2，参数3）就可以调用存储过程
var obj = SPs.P_Branch_GetMaxBranchDeptCode(1, 0).ExecuteScalar();
WriteLine(obj + "");
//---------------------------------------------------------------
// 10、直接执行QueryCommand的方式
//---------------------------------------------------------------
//获取数据源 -- 主要用于绑定连接的服务器，如果有多台服务器多个数据库时，可使用不同的数据源来进行绑定查找
var provider = SubSonic.DataProviders.ProviderFactory.GetProvider();
//定义事务，给后面的事务调用
var batch = new BatchQuery(provider);
var sql = string.Format("select * from {0}", BranchTable.TableName);
//例一：获取影响记录数
QueryCommand qcCommand = new QueryCommand(sql, provider);
WriteLine(qcCommand.Provider.ExecuteQuery(qcCommand) + "");
//例二：获取DataTable
var q = new SubSonic.Query.QueryCommand(sql, provider);
var table = q.Provider.ExecuteDataSet(q).Tables[0];
if (dt != null && table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
WriteLine(table.Rows[i][BranchTable.Id] + " " + table.Rows[i][BranchTable.Name]);
}
}
//例三：使用事务执行
batch.QueueForTransaction(qcCommand);
batch.ExecuteTransaction();
//例四：直接使用数据源执行
provider.ExecuteQuery(qcCommand);
//----------------------------------------------------------------
// 11、Linq查询
//----------------------------------------------------------------
//Linq查询方式
var query = new Query <Branch>(db.provider);
var posts = from p in query
where p.DeptCode.StartsWith("0101")
select p;
foreach (var item in posts)
{
WriteLine(item.ToString());
}
query = db.GetQuery <Branch>();
posts = from p in query
where p.Id > 3 && p.Id < 6
select p;
foreach (var item in posts)
{
WriteLine(item.ToString());
}
//获取查询结果2
List <Branch> li2 = query.ToList <Branch>();
foreach (var item in li2)
{
WriteLine(item.ToString());
}
//---------------------------------------------------------------------
// 12、Linq多表关联查询
//---------------------------------------------------------------------
//方法一
var query5 = from p in Position.All()
join b in Branch.All() on p.Branch_Id equals b.Id
where b.DeptCode == "0101"
select p;
foreach (var item in query5)
{
WriteLine(item.ToString());
}
//方法二
var qry = (from p in db.Position
join b in db.Branch on p.Branch_Id equals b.Id
where b.DeptCode == "0101"
select new ListView
{
PositionName = p.Name,
BranchName = p.Branch_Name,
DeptCode = b.DeptCode
});
foreach (var item in qry)
{
WriteLine(item.ToString());
}
//-------------------------------------------------------------------------
// 13、使用事务
//-------------------------------------------------------------------------
//例一
//从部门表中查询出编号为0102的Id、名称与说明三列
var query1 = new SubSonic.Query.Select(provider, BranchTable.Id, BranchTable.Name, BranchTable.Comment).From(
BranchTable.TableName).Where <Branch>(x => x.DeptCode == "0102");
//加入事务
batch.QueueForTransaction(query1);
//查询部门编号为0102且职位名称后面两个字为主管的所有职位
var query2 = new SubSonic.Query.Select(provider).From <Position>().Where <Position>(x => x.Branch_DeptCode == "0102").And(
PositionTable.Name).EndsWith("主管");
//加入事务
batch.QueueForTransaction(query2);
//运行事务,不返回任何信息
batch.ExecuteTransaction();
//例二
batch = new BatchQuery();
batch.Queue(query1);
batch.Queue(query2);
//执行事务，并返回数据
using (IDataReader rdr = batch.ExecuteReader())
{
if (rdr.Read())
{
//query1 results
for (int i = 0; i < rdr.FieldCount; i++)
{
WriteLine(rdr[i] + "");
}
}
rdr.NextResult();
if (rdr.Read())
{
//query2 results
for (int i = 0; i < rdr.FieldCount; i++)
{
WriteLine(rdr[i] + "");
}
}
}
//例三
batch = new BatchQuery(provider);
var query3 = from p in db.Branch
where p.Id > 1 && p.Id < 10
select p;
batch.Queue(query3);
var query4 = from p in db.Position
where p.Branch_DeptCode == "0103"
select p;
batch.Queue(query4);
//执行事务，并返回数据
using (var rdr = batch.ExecuteReader())
{
if (rdr.Read())
{
//query3 results
for (int i = 0; i < rdr.FieldCount; i++)
{
WriteLine(rdr[i] + "");
}
}
rdr.NextResult();
if (rdr.Read())
{
//query4 results
for (int i = 0; i < rdr.FieldCount; i++)
{
WriteLine(rdr[i] + "");
}
}
}
}
public void Batch_Should_Execute_Reader_And_Return_Typed_Lists_Using_Linq()
{
BatchQuery qry = new BatchQuery(provider);
var pquery = new Query<Product>(provider);
qry.Queue(from p in pquery where p.ProductID == 1 select p);
qry.Queue(from p in pquery where p.ProductID == 2 select p);
qry.Queue(from p in pquery where p.ProductID == 3 select p);
bool canRead = false;
List<Product> result1 = null;
List<Product> result2 = null;
List<Product> result3 = null;
using(IDataReader rdr = qry.ExecuteReader())
{
result1 = rdr.ToList<Product>();
canRead = true;
if(rdr.NextResult())
result2 = rdr.ToList<Product>();
if(rdr.NextResult())
result3 = rdr.ToList<Product>();
canRead = rdr.NextResult();
}
Assert.True(result1.Count > 0);
Assert.True(result2.Count > 0);
Assert.True(result3.Count > 0);
Assert.False(canRead);
}
public void Batch_Should_Build_Batched_SQL_Using_Linq()
{
BatchQuery qry = new BatchQuery(provider);
var pquery = new Query<Product>(provider);
qry.Queue(from p in pquery where p.ProductID == 1 select p);
qry.Queue(from p in pquery where p.ProductID == 2 select p);
qry.Queue(from p in pquery where p.ProductID == 3 select p);
string sql = qry.BuildSqlStatement();
Assert.Equal(3, sql.FindMatches("SELECT").Count);
}
private static void BatchToInsert()
{
Console.WriteLine("Batch Inserting Genres using Insert.Into with unescaped values.");
IList<Genre> genres = new List<Genre>
{
new Genre { Name = "Genre's First" },
new Genre { Name = "Genre's Second" }
};
BatchQuery bq = new BatchQuery(db.Provider);
foreach (Genre genre in genres)
{
bq.Queue(genre.ToInsertQuery<Genre>(db.Provider));
}
ExecuteQuery(bq);
}
public void Batch_Should_Execute_Batched_SQL_With_Replaced_Command_Parameters_For_In_Operator()
{
BatchQuery qry = new BatchQuery(provider);
qry.Queue(new Select(provider).From("Products").Where("ProductID").In(new int[] { 1, 2, 3 }));
qry.Queue(new Select(provider).From("Products").Where("ProductID").In(new int[] { 1, 2, 3 }));
qry.Queue(new Select(provider).From("Products").Where("ProductID").In(new int[] { 1, 2, 3 }));
int sets = 1;
bool canRead = false;
using (IDataReader rdr = qry.ExecuteReader())
{
canRead = true;
if (rdr.NextResult())
sets = 2;
if (rdr.NextResult())
sets = 3;
canRead = rdr.NextResult();
}
Assert.Equal(3, sets);
Assert.False(canRead);
}
private static void SelectUsingContains(IEnumerable<string> genreContains)
{
Console.WriteLine("Loading genres that contain {0}.", genreContains);
Query<Genre> genres = new Query<Genre>(db.Provider);
var query = from genre in genres
where genreContains.Contains(genre.Name)
select genre;
BatchQuery batch = new BatchQuery(db.Provider);
batch.Queue(query);
ExecuteQuery(batch);
}
private void DatabaseInitialize()
{
//Console.WriteLine("Database Initialize...");
#region Create tables
BatchQuery query = new BatchQuery(provider);
Assembly assembly = Assembly.Load("TYD.Logging");
string spacename = "TYD.Logging.Models";
var migrator = new SubSonic.Oracle.Schema.Migrator(assembly);
string[] commands = migrator.MigrateFromModel(spacename, provider);
foreach (var s in commands)
{
query.QueueForTransaction(new QueryCommand(s.Trim(), provider));
}
query.ExecuteTransaction();
#endregion
}
public void Batch_Should_Execute_Reader_And_Return_Typed_Lists()
{
BatchQuery qry = new BatchQuery(provider);
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(1));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(2));
qry.Queue(new Select(provider).From("Products").Where("ProductID").IsEqualTo(3));
bool canRead = false;
List<Product> result1 = null;
List<Product> result2 = null;
List<Product> result3 = null;
using(IDataReader rdr = qry.ExecuteReader())
{
result1 = rdr.ToList<Product>();
canRead = true;
if(rdr.NextResult())
result2 = rdr.ToList<Product>();
if(rdr.NextResult())
result3 = rdr.ToList<Product>();
canRead = rdr.NextResult();
}
Assert.True(result1.Count > 0);
Assert.True(result2.Count > 0);
Assert.True(result3.Count > 0);
Assert.False(canRead);
}
