public void ResetSettings()
{
var setting = new DbBuildingSettings(Settings.Current.Building.BuilderConnectionString);
setting.Reset();
var builder = new DbBuilder(Settings.Current.Building.BuilderConnectionString);
if (Settings.Current.Builder.Id.HasValue)
{
builder.Reset(Settings.Current.Builder.Id.Value);
}
}
/// <summary>
/// 根据主键值删除数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public virtual int Delete <T>(object id)
{
int result = 0;
var dbEntity = DbBuilder.Delete <T>(id);
if (dbEntity == null)
{
return(result);
}
result = DbHelper.ExecuteNonQuery(dbEntity.CommandText, dbEntity.DbParams);
HandelExecuteDbCallBack(dbEntity.CommandText, dbEntity.DbParams);
return(result);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory,
ApplicationDbContext context, IServiceProvider serviceProvider, IOptions <SecureAppConfig> secureConfig)
{
//Save reference to the mail client
//MailClient.Client = serviceProvider.GetService<SparkPostClient>();
loggerFactory.AddConsole(Program.FileConfig.GetSection("Logging"));
loggerFactory.AddDebug();
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseDatabaseErrorPage();
app.UseBrowserLink();
}
else
{
app.UseStatusCodePagesWithRedirects("/Home/Error");
app.UseExceptionHandler("/Home/Error");
}
app.UseStaticFiles();
app.UseIdentity();
// Add external authentication middleware below. To configure them please see https://go.microsoft.com/fwlink/?LinkID=532715
app.UseGoogleAuthentication(new GoogleOptions()
{
ClientId = secureConfig.Value.GoogleClientId,
ClientSecret = secureConfig.Value.GoogleClientSecret
});
app.UseCors("AllowEverything");
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Home}/{action=Index}/{id?}");
});
#if DEBUG
// Seed database if not running in production
if (Program.AppConfig.DatabaseReset)
{
DbBuilder.Rebuild(context);
}
#endif
}
public void TestGetAllProducts()
{
//Get an instance of the actual api controller
var apiController = CreateDressFinderApiControllerInstance();
//Calculate the actual return value
var returnValue = apiController.GetAllProducts();
//Calculate the expected return value
var expectedValue = new JsonResult(DbBuilder.GenerateProductsAsList());
//Check if its result matches the expected output
Assert.Equal(JsonConvert.SerializeObject(returnValue), JsonConvert.SerializeObject(expectedValue));
}
/// <summary>
/// 添加单条实体对象数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public virtual object Insert <T>(T entity)
{
object result = default(object);
var dbEntity = DbBuilder.Insert <T>(entity);
if (dbEntity == null)
{
return(result);
}
result = dbEntity.TableEntity.IsGetIncrementValue ?
DbHelper.ExecuteScalar(dbEntity.CommandText, dbEntity.DbParams) :
DbHelper.ExecuteNonQuery(dbEntity.CommandText, dbEntity.DbParams);
return(result);
}
/// <summary>
/// 根据sql语句、参数执行查询实体对应数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">sql语句或存储过程</param>
/// <param name="objParams">参数,例:new {Uname="joyet"}</param>
/// <returns></returns>
public virtual List <T> Query <T>(string sql, object objParams)
{
List <T> result = null;
var dbEntity = DbBuilder.Query <T>(sql, objParams);
if (dbEntity == null)
{
return(result);
}
using (var reader = DbHelper.ExecuteReader(dbEntity.CommandText, dbEntity.DbParams))
{
result = DataReaderToEntityList <T>(reader);
}
return(result);
}
/// <summary>
/// 根据主键查询实体对应数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public virtual T QueryById <T>(object id)
{
T result = default(T);
var dbEntity = DbBuilder.QueryById <T>(id);
if (dbEntity == null)
{
return(result);
}
using (var reader = DbHelper.ExecuteReader(dbEntity.CommandText, dbEntity.DbParams))
{
result = DataReaderToEntity <T>(reader);
}
return(result);
}
/// <summary>
/// 根据查询字段、过滤条件Sql、过滤条件参数(参数名和参数值)查询数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryColumns">要查询字段</param>
/// <param name="whereSql">过滤条件Sql</param>
/// <param name="whereParam">过滤条件参数(参数名和参数值),例:new {Uname="joyet",Age = 110}</param>
/// <returns></returns>
public virtual List <T> Query <T>(string queryColumns, string whereSql, object whereParam)
{
List <T> result = null;
var dbEntity = DbBuilder.Query <T>(queryColumns, whereSql, whereParam);
if (dbEntity == null)
{
return(result);
}
using (var reader = DbHelper.ExecuteReader(dbEntity.CommandText, dbEntity.DbParams))
{
result = DataReaderToEntityList <T>(reader);
}
HandelExecuteDbCallBack(dbEntity.CommandText, dbEntity.DbParams);
return(result);
}
public static List <T> GetList <T>(PageInfo <T> model)
{
if (string.IsNullOrWhiteSpace(model.StrSelect))
{
model.StrSelect = "*";
}
if (model.Config == null)
{
throw new Exception("Config不能为空！");
}
if (string.IsNullOrWhiteSpace(model.StrFrom))
{
throw new Exception("StrFrom不能为空！");
}
if (string.IsNullOrWhiteSpace(model.StrOrder))
{
throw new Exception("StrOrder不能为空！");
}
var getModel = model.GetModel;
var list = new List <T>();
using (var db = new DbBuilder(model.Config))
{
string sql = "select count(1) as tcount from (" + model.StrFrom + ") where 1=1 " + model.StrWhere;
var recordCount = (int)new DbBuilder(model.Config).GetSingle(sql, model.Parameters);
sql = SqlHelper.GetSqlString(model.StrFrom, model.PageSize, model.PageIndex, recordCount, "*", model.StrWhere, model.StrOrder, model.Config);
var reader = db.GetDataReader(sql, model.Parameters);
if (getModel != null)
{
while (reader.Read())
{
list.Add(getModel(reader));
}
}
else
{
var builder = EntityBuilder <T> .CreateBuilder(reader);
while (reader.Read())
{
list.Add(builder.Build(reader));
}
}
}
return(list);
}
/// <summary>
/// 单表分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryColumns">要查询字段</param>
/// <param name="sortColumn">排序字段</param>
/// <param name="sortType">排序方式</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">第几页</param>
/// <param name="whereSql">过滤条件Sql</param>
/// <param name="whereParam">过滤条件字段名及字段值参数,例:new {Uname="joyet",Age = 110}</param>
/// <returns></returns>
public virtual PageResultEntity QueryPageList <T>(string queryColumns, string sortColumn, string sortType, int pageSize, int pageIndex, string whereSql, object whereParam)
{
PageResultEntity result = new PageResultEntity();
if (pageSize <= 0 || pageIndex <= 0)
{
return(result);
}
result.PageSize = pageSize;
result.CurrentPageIndex = pageIndex;
#region 为了分页查询效率，查询第一页时才会查询所有条数
if (result.CurrentPageIndex == 1)
{
var totalPageDbEntity = DbBuilder.QueryTotalPageCount <T>(whereSql, whereParam);
if (totalPageDbEntity == null)
{
return(result);
}
var objTotalCount = DbHelper.ExecuteScalar(totalPageDbEntity.CommandText, totalPageDbEntity.DbParams);
if (objTotalCount == null)
{
return(result);
}
result.TotalCount = Convert.ToInt64(objTotalCount);
if (result.TotalCount <= 0)
{
return(result);
}
}
#endregion
var dbEntity = DbBuilder.QueryPageList <T>(queryColumns, sortColumn, sortType, pageSize, pageIndex, whereSql, whereParam);
if (dbEntity == null)
{
return(result);
}
using (var reader = DbHelper.ExecuteReader(dbEntity.CommandText, dbEntity.DbParams))
{
var datas = DataReaderToEntityList <T>(reader);
result.Data = datas;
result = SetPageListResult <T>(result);
}
HandelExecuteDbCallBack(dbEntity.CommandText, dbEntity.DbParams);
return(result);
}
private void StoreDataToDb(object sender, EventArgs e)
{
const string tableName = "myTable";
const string dbName = "temp.sqlite";
string dbPath = Application.StartupPath + "\\" + dbName;
if (File.Exists(dbPath))
{
File.Delete(dbPath);
}
var sqliteService = new SqliteService(tableName, dbPath);
var dbBuilder = new DbBuilder(tableName, sqliteService);
dbBuilder.CreateTable(_dataTable.Columns);
dbBuilder.InsertData(_dataTable.Rows);
}
////////////////////////////////////////////////////////////////////////////////////////////////
/*--------------------------------------------------------------------------------------------*/
public static void BuildBaseDb(ISession pSess)
{
Console.WriteLine("Building WordNet engine...");
string root = Directory.GetCurrentDirectory();
Engine = new WordNetEngine(root + "/../../../../Data/WordNetDb-3.1", true);
Console.WriteLine("WordNet engine complete.");
Console.WriteLine("");
DbBuilder.EraseAndRebuildDatabase();
SetDbStateBeforeBatchInsert(pSess);
using (ITransaction tx = pSess.BeginTransaction()) {
BuildBaseDbInserts(pSess);
tx.Commit();
}
SetDbStateAfterBatchInsert(pSess);
}
/// <summary>
/// Adding filters to an existing configuration
/// </summary>
private void AddFilters(ICollection <FilterClause> filters, DmTable dmTable, DbBuilder builder)
{
if (filters != null && filters.Count > 0)
{
var tableFilters = filters.Where(f => dmTable.TableName.Equals(f.TableName, StringComparison.InvariantCultureIgnoreCase));
foreach (var filter in tableFilters)
{
var columnFilter = dmTable.Columns[filter.ColumnName];
if (columnFilter == null)
{
throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {dmTable.TableName}");
}
builder.FilterColumns.Add(new FilterClause(filter.TableName, filter.ColumnName));
}
}
}
private void View_OnStoreDb(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
if (File.Exists(DbPath))
{
File.Delete(DbPath);
}
var dbBuilder = new DbBuilder(
DbPath,
TableName,
ExcelTable,
ColumnInfos.Where(info => info.Keep).ToList(),
View.XCoordinateHeader,
View.YCoordinateHeader,
View.Projection
);
dbBuilder.CreateTable();
dbBuilder.InsertData();
}
public IndexModel(DbBuilder myDB)
{
_myDB = myDB;
}
public PreviewModel(DbBuilder myDB, IConfiguration myConfiguration)
{
_myDB = myDB;
_myConfiguration = myConfiguration;
}
public void TestAccessOrm()
{
//ORM数据映射
DbConfig.UseDefaultConfig(new TModelDbConfig(GetDbPath()));
Console.WriteLine("Start loadding...");
Console.WriteLine(new cms_category().Query(m => m.name == "城市").ToCount());
var cat = new cms_category().Query(m => m.name == "城市").SortAsc(m => m.name).ToModel();
Console.WriteLine(cat.name);
//设置只更新部分
//cat.SetPartHandled();
//cat.description = "test";
//cat.Update(m=>m.id == 1);
Console.WriteLine(cat.ToValue(m => m.name));
Console.WriteLine(new cms_category().Query(m => m.name == "城市").ToList()[0].name);
Console.WriteLine(new cms_category().Query(m => m.name == "城市" && m.id > 0 && m.name == "" || (m.id == 0 || m.name == "")).ToCount());
//指定条件规则查询
Console.WriteLine(new cms_category().Query(m => (m.name == "城市" && (m.id > 0 || m.name == "")) || (m.id == 0 || m.name == "")).ToCount());
var cityList = new List <string> {
"城市", "b", "c"
};
var layer = new LayerModel {
List = cityList
};
Console.WriteLine(new cms_category().Query(m => m.name == "城市" || cityList.Contains(m.name) || m.parent_id == Status.Success).ToCount());
Console.WriteLine(new cms_category().Query(m => m.name == "城市" || layer.List.Contains(m.name)).ToCount());
//获取全部
var datsList = new cms_category().Query().ToList();
Console.WriteLine(datsList.Count);
//获取N条
datsList = new cms_category().Query().ToList(6);
Console.WriteLine(datsList.Count);
//获取部分
var partList = new cms_category().Query().ToPartList(6, "id", "name").Select(m => new cms_category
{
id = int.Parse(m[0]),
name = m[1]
}).ToList();
Console.WriteLine(partList.Count);
//分页查询
var mapper = new cms_category().Query();
var dataCount = mapper.ToCount();
datsList = mapper.ToList(20, 1, dataCount);
Console.WriteLine(datsList.Count);
//条件拼接查询
mapper.And(m => m.name == "test")
.And(m => m.id > 0)
.Or(m => m.parent_id > 0);
mapper.Or(m => m.parent_id > 0);
var channels = new cms_channel().Query().ToList();
Console.WriteLine(channels.Count);
var grade = new ucl_grade {
id = 5
};
grade.grade_name = "新手1";
var dal = new UclGradeDataAccess(grade);
//保持数据库连接
using (var db = new DbBuilder(new TModelDbConfig(GetDbPath())).KeepConnect())
{
//使用数据库db操作并跟踪实体修改状态
dal.UseDatabase(db).SetPartHandled();
grade.grade = 8;
grade.grade_name = "新手";
dal.Update();
}
//db销毁后重连数据库
Console.WriteLine(dal.ToValue(m => m.grade_name));
//使用事务(在事务中处理)
using (var db = new DbBuilder(new TModelDbConfig(GetDbPath())).KeepConnect())
{
try
{
db.BeginTransaction();
//TODO:something
//使用数据库db操作并跟踪实体修改状态
dal.UseDatabase(db).SetPartHandled();
grade.grade = 8;
grade.grade_name = "新手";
dal.Update();
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
}
//使用事务(批处理事务)
var parList = new List <DbParamInfo>();
//添加到批处理事务中，如果执行失败则回滚事务
parList.Add(dal.GetUpdateDbParamInfo().UseVerifyExecResult());
//TODO:添加其他操作到parList
var execCount = new DbBuilder(new TModelDbConfig(GetDbPath())).ExecuteSqlTran(parList);
Console.WriteLine(execCount);
}
public CompleteModel(DbBuilder myDB)
{
_myDB = myDB;
}
public IndexModel(DbBuilder myDB, IConfiguration myConfiguration)
{
_myDB = myDB;
_myConfiguration = myConfiguration;
}
public ReadModel(DbBuilder myDB)
{
_myDB = myDB;
}
protected void GetDatabase()
{
var client = new MongoClient(DbBuilder.GetConnectionString());
MongoDatabase = client.GetDatabase(DbBuilder.GetDatabaseName());
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, ApplicationDbContext context)
{
// Set the environment based on the appsettings.json
Program.SetEnvironment(env);
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseDatabaseErrorPage();
}
else
{
app.UseExceptionHandler("/Home/Error");
}
app.UseAuthentication();
// Serve backend files
app.UseStaticFiles(new StaticFileOptions
{
FileProvider = new PhysicalFileProvider(
Path.Combine(Directory.GetCurrentDirectory(), "./wwwroot")
),
RequestPath = ""
});
// Determine which hosts are allowed, for proper CORS configuration
AppConfig config = new AppConfig();
String[] allowedHosts = config.GetProperty("Web.AllowedHosts").Split(',');
// Report the hosts we're allowing CORS on
foreach (String host in allowedHosts)
{
Console.WriteLine("Allowing CORS request for: {0}", host);
}
// Configure CORS with the proper hosts
app.UseCors(corsPolicyBuilder =>
corsPolicyBuilder.WithOrigins(allowedHosts)
.AllowAnyMethod()
.AllowAnyHeader()
);
// TODO: remove this when we're done with it
/* // Create a new example group */
/* var group = new Group(); */
/* group.Name = "My new fancy group"; */
/* context.Groups.Add(group); */
/* context.SaveChanges(); */
// Define the routes
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Home}/{action=Index}/{id?}"
);
});
/* // TODO: reenable this once the DbBuilder is complete */
/* // Seed database if not running in production */
if (Program.AppConfig.DatabaseReset)
{
DbBuilder.Rebuild(context);
}
}
public UpdateModel(DbBuilder myDB)
{
_myDB = myDB;
}
public BuilderSettings(string machineName)
{
MachineName = machineName;
dbBuilder = new DbBuilder(Settings.Current.Building.BuilderConnectionString);
}
public void Initialize()
{
sqliteService = new Mock <ISqliteService>();
dbBuilder = new DbBuilder("myTable", sqliteService.Object);
}
public BaseMapper <T> UseDatabase(DbBuilder dbBuilder)
{
_shareDbBuilder = dbBuilder;
return(this);
}
