public void TestGetThemeWeightByOrgStc()
{
var act = DbAccessor.GetThemeWeightByOrgStc(2).AsEnumerable();
Assert.AreEqual(50, Convert.ToInt32(act.Where(r => r.Field <int>("ThemeId") == 1).First()["Weight"]));
Assert.AreEqual(50, Convert.ToInt32(act.Where(r => r.Field <int>("ThemeId") == 2).First()["Weight"]));
}
public async Task <TriggerPropertyBundle> LoadExtendedTriggerProperties(
ConnectionAndTransactionHolder conn,
TriggerKey triggerKey,
CancellationToken cancellationToken = default(CancellationToken))
{
using (var cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix, SchedNameLiteral)))
{
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (var rs = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
if (await rs.ReadAsync(cancellationToken).ConfigureAwait(false))
{
string cronExpr = rs.GetString(AdoConstants.ColumnCronExpression);
string timeZoneId = rs.GetString(AdoConstants.ColumnTimeZoneId);
CronScheduleBuilder cb = CronScheduleBuilder.CronSchedule(cronExpr);
if (timeZoneId != null)
{
cb.InTimeZone(TimeZoneUtil.FindTimeZoneById(timeZoneId));
}
return(new TriggerPropertyBundle(cb, null, null));
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix, SchedNameLiteral));
}
}
public UpdateResult CreateTable()
{
var result = new UpdateResult();
try
{
string createQuery = @"CREATE TABLE IF NOT EXISTS
domain (
domain_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
domain_name VARCHAR(2048) NULL,
bounty_url VARCHAR(2048) NULL,
bounty_end_date datetime NULL,
last_scan datetime NULL,
private boolean NULL,
platform VARCHAR(50) NULL)";
DbAccessor connection = new DbAccessor();
connection.SetUpTable(createQuery);
}
catch (Exception ex)
{
result.Error = true;
result.Message = ex.Message;
if (ex.InnerException != null)
{
result.Message += " Inner: " + ex.InnerException.Message;
}
}
return(result);
}
public void TestGetOrgStcByStruct()
{
var act = DbAccessor.GetOrgStcByStruct(71).Rows[0];
Assert.AreEqual(65, act["OrgStcId"]);
Assert.AreEqual(71, act["StcId"]);
}
public void SetUp()
{
_dbAccessor =
new DbAccessor(
new DatabaseConnectionStringProvider(
"Data Source=SIDESWIPE;Database=myDataBase;Initial Catalog=Test;User ID=leadmarket_user;Password=Cooki35"));
}
public async Task <ObservableCollection <Announcement> > GetAnnouncementsAsync()
{
DbAccessor db = new DbAccessor();
var source = db.GetTableItemsObservable <Announcement>();
return(source);
}
public JsonResult Delete(string id)
{
MyJsonResult mjr = new MyJsonResult();
using (DbAccessor dba = DbAccessor.Create())
{
try
{
dba.BeginTran();
_menuService.DeleteRecursive(id, dba);
dba.CommitTran();
mjr.Success = true;
}
catch (Exception ex)
{
dba.RollbackTran();
mjr.Success = false;
mjr.Message = ex.Message;
}
}
return(Json(mjr));
}
public static DbPaging GetDbPaging(DbAccessor dba)
{
DbPaging paging = null;
if (dba.GetType() == typeof(SqlServerAccessor))
{
paging = new SqlServerPaging();
}
else if (dba.GetType() == typeof(OracleAccessor))
{
paging = new OraclePaging();
}
else if (dba.GetType() == typeof(MySqlAccessor))
{
paging = new MySqlPaging();
}
if (paging != null)
{
return paging;
}
else
{
throw new Exception(string.Format("“{0}”没有对应的分页实现！", dba.GetType()));
}
}
public Task <DataResult <bool> > ValidateUser(UserModel user)
{
var dbUser = DbAccessor.GetItem <UserModel>(Constants.TABLE_USERS, user.KeyName);
if (dbUser != null)
{
user.Password = dbUser.Password;
user.TokenDate = dbUser.TokenDate;
var userToken = GetToken(user);
var dbUserToken = GetToken(dbUser);
if (userToken == user.Token && dbUserToken == dbUser.Token && userToken == dbUserToken && (DateTime.Now - dbUser.TokenDate) < TimeSpan.FromDays(7))
{
user.Role = dbUser.Role;
return(Task.FromResult(new DataResult <bool>(Name, new List <bool>()
{
true
})));
}
}
return(Task.FromResult(new DataResult <bool>(Name, new List <bool>()
{
false
})));
}
public async Task <int> AddCompanyUser(CompanyUserDetails newUser)
{
int newUserId = 0;
// ****** TODO *******
// call api service to add user
DbAccessor db = new DbAccessor();
var item = db.GetTableItems <CompanyUser>().Where(x => x.UserId == newUser.UserId).FirstOrDefault();
if (item != null)
{
item.JSON = JsonConvert.SerializeObject(newUser);
db.UpdateRecord <CompanyUser>(item);
newUserId = item.UserId;
}
else
{
// ****** TODO *******
// GET new user id from API and populate the new user with it
newUser.UserId = newUserId = dummyUserId;
// now save it
db.InsertRecord <CompanyUser>(new CompanyUser {
UserId = newUser.UserId, UserRole = newUser.AssignedRole, JSON = JsonConvert.SerializeObject(newUser)
});
}
return(newUserId);
}
// mechanism to get default values for dropdowns
public async Task <ObservableCollection <AppValues> > GetDefaultValuesAsync(string Category, string parentKey = "")
{
// grab data from database
DbAccessor db = new DbAccessor();
var Source = UtilHelper.ConvertToObservable <AppValues>(db.GetTableItemsObservable <AppValues>().Where(x => x.CatType == Category).ToList());
// filter list if we just need root parent values.
if (!string.IsNullOrEmpty(parentKey))
{
Source = UtilHelper.ConvertToObservable <AppValues>(Source.Where(x => x.ParentKey == parentKey).ToList());
}
// grab the current culture here and set the value field
var cult = this.GetCurrentCulture();
if (cult == "ar")
{
foreach (var item in Source)
{
item.Value = item.Arabic;
}
}
else
{
foreach (var item in Source)
{
item.Value = item.English;
}
}
return(Source);
}
public async Task <bool> UpdateEmployerJob(EmployerJobDetail job) // add new or existing job
{
DbAccessor db = new DbAccessor();
// update job in both database and api to server if connection.
var vjob = db.GetTableItems <EmployerJobs>().Where(x => x.JobPostId == job.JobPostId).FirstOrDefault();
if (vjob != null)
{
vjob.JSON = JsonConvert.SerializeObject(job);
// update here for db
db.UpdateRecord <EmployerJobs>(vjob);
// push update to api here
// ***************** TODO ***************
}
else
{
var jb = new EmployerJobs {
JSON = JsonConvert.SerializeObject(job), JobPostId = job.JobPostId
};
db.InsertRecord <EmployerJobs>(jb);
}
// temp return
return(true);
}
/// <summary>
/// Initializes the singleton application object. This is the first line of authored code
/// executed, and as such is the logical equivalent of main() or WinMain().
/// </summary>
public App()
{
this.InitializeComponent();
this.Suspending += OnSuspending;
Container = ConfigureDependencyInjection();
DbAccessor.RubDb();
}
public TriggerPropertyBundle LoadExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
{
using (IDbCommand cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix, SchedNameLiteral)))
{
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (IDataReader rs = cmd.ExecuteReader())
{
if (rs.Read())
{
string cronExpr = rs.GetString(AdoConstants.ColumnCronExpression);
string timeZoneId = rs.GetString(AdoConstants.ColumnTimeZoneId);
CronScheduleBuilder cb = CronScheduleBuilder.CronSchedule(cronExpr);
if (timeZoneId != null)
{
cb.InTimeZone(TimeZoneInfo.FindSystemTimeZoneById(timeZoneId));
}
return(new TriggerPropertyBundle(cb, null, null));
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix, SchedNameLiteral));
}
}
public async Task <TriggerPropertyBundle> LoadExtendedTriggerProperties(
ConnectionAndTransactionHolder conn,
TriggerKey triggerKey,
CancellationToken cancellationToken = default)
{
using (var cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, TablePrefix)))
{
DbAccessor.AddCommandParameter(cmd, "schedulerName", SchedName);
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (var rs = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
if (await rs.ReadAsync(cancellationToken).ConfigureAwait(false))
{
int repeatCount = rs.GetInt32(AdoConstants.ColumnRepeatCount);
TimeSpan repeatInterval = DbAccessor.GetTimeSpanFromDbValue(rs[AdoConstants.ColumnRepeatInterval]) ?? TimeSpan.Zero;
int timesTriggered = rs.GetInt32(AdoConstants.ColumnTimesTriggered);
SimpleScheduleBuilder sb = SimpleScheduleBuilder.Create()
.WithRepeatCount(repeatCount)
.WithInterval(repeatInterval);
string[] statePropertyNames = { "timesTriggered" };
object[] statePropertyValues = { timesTriggered };
return(new TriggerPropertyBundle(sb, statePropertyNames, statePropertyValues));
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, TablePrefix));
}
}
public async Task <ObservableCollection <EventDetails> > GetEventsAsync()
{
// grab data from database
DbAccessor db = new DbAccessor();
var Source = db.GetTableItemsObservable <EventDetails>();
return(Source);
}
public async Task <ObservableCollection <Notifications> > GetNotificationsAsync()
{
// grab data from database
DbAccessor db = new DbAccessor();
var notifySource = db.GetTableItemsObservable <Notifications>();
return(notifySource);
}
/// <summary>
/// Gets the filters SQL.
/// </summary>
/// <param name="accessor">The accessor.</param>
/// <param name="args"></param>
/// <param name="filterNode">The filter node.</param>
/// <returns></returns>
public static string GetFiltersSql(DbAccessor accessor, List <object> args, DbFilterGroup filterNode)
{
var sb = new StringBuilder();
var builder = new DbFilterBuilder(sb, accessor, args);
builder.Build(filterNode, false);
return(sb.ToString());
}
private object[] buildWhere(StringBuilder sb, DbAccessor accessor)
{
var args = new List <object>();
var builder = new DbFilterBuilder(sb, accessor, args);
builder.Build(FilterGroup, true);
return(args.ToArray());
}
public Task <DataResult <bool> > UpdateInfoList(IEnumerable <ImageInfoModel> items)
{
var result = DbAccessor.UpdateItems(Constants.TABLE_IMAGES, items);
return(Task.FromResult(new DataResult <bool>(Name, new List <bool> {
result
})));
}
internal DbScope(IDb db, DbAccessor accessor)
{
m_Connection = db.CreateConnection();
m_Accessor = accessor;
m_Connection.Open();
m_Transaction = m_Connection.BeginTransaction();
}
private static void AddAccessorSelectors(DbAccessor connection)
{
connection.Select("sub_domain_id", DbType.Int32);
connection.Select("sub_domain_name", DbType.String);
connection.Select("domain_id", DbType.Int32);
connection.Select("date_found", DbType.DateTime);
connection.Select("found_type", DbType.DateTime);
}
public async Task <IActionResult> AddDashboard(Dashboard newDashboard)
{
DbAccessor accessor = new DbAccessor();
accessor.InsertDashboard(newDashboard);
return(Redirect("/AdminTools/DashManager"));
}
public async Task <EventDetails> GetEventSingleAsync(string eventId)
{
// grab data from database
DbAccessor db = new DbAccessor();
var Source = db.GetTableItemsObservable <EventDetails>().Where(x => x.EventId.ToString() == eventId).FirstOrDefault();
return(Source);
}
public async Task <IActionResult> DeleteDashboard(int DashboardId)
{
DbAccessor accessor = new DbAccessor();
accessor.DeleteDashboard(DashboardId);
return(Redirect("/AdminTools/DashManager"));
}
public void BeginTran()
{
using (DbAccessor dba = DbAccessor.Create())
{
dba.BeginTran();
dba.ExecuteNonQuery("update sys_product set ProductName='abc' where ProductId=#ProductId#", new { ProductId = 1 });
dba.RollbackTran();
}
}
public Task <DataResult <bool> > DeleteItem(UserModel item)
{
var result = DbAccessor.DeleteItem(Constants.TABLE_USERS, item);
return(Task.FromResult(new DataResult <bool>(Name, new List <bool>()
{
result
})));
}
public Task <DataResult <UserModel> > GetItem(string name)
{
var result = DbAccessor.GetItem <UserModel>(Constants.TABLE_USERS, name);
return(Task.FromResult(new DataResult <UserModel>(Name, new List <UserModel>()
{
result
})));
}
public JsonResult Save(SecUser model)
{
MyJsonResult mjr = new MyJsonResult();
using (var dba = DbAccessor.Create())
{
try
{
dba.BeginTran();
model.ModifiedById = SecurityContext.Current.User.UserId;
model.ModifiedDate = DateTime.Now;
if (model.UserId == null)
{
#region 校验数据
dynamic count = _dba.ExecuteScalar <dynamic>("SecuritySettings.User.CountByLoginName",
new { LoginName = model.LoginName, Status = (int)StatusType.Enabled });
if (count > 0)
{
mjr.Success = false;
mjr.Message = "登录名已存在！";
goto End;
}
#endregion
model.UserId = Guid.NewGuid().ToString();
model.Status = (int)StatusType.Enabled;
model.CreatedById = SecurityContext.Current.User.UserId;
model.CreatedDate = DateTime.Now;
dba.Insert <SecUser>(model);
}
else
{
dba.UpdateFields(model, "LoginName", "Password", "Name", "Sex", "Birthday",
"MobilePhone", "OrganizationId",
"ModifiedById", "ModifiedDate");
}
dba.CommitTran();
mjr.Success = true;
mjr.Message = "保存成功！";
}
catch (Exception ex)
{
dba.RollbackTran();
mjr.Success = false;
mjr.Message = ex.Message;
}
}
End:
return(Json(mjr));
}
public int DeleteExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
{
using (IDbCommand cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlDeleteCronTrigger, TablePrefix, SchedNameLiteral)))
{
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
return(cmd.ExecuteNonQuery());
}
}
public void DeleteRecursive(string id, DbAccessor dba)
{
string strSql = "select * from Sec_Menu where ParentId=#ParentId#";
var childrenMenu = dba.QueryEntities<SecMenu>(strSql, new { ParentId = id });
foreach (var item in childrenMenu)
{
DeleteRecursive(item.MenuId, dba);
}
dba.ExecuteNonQuery("Security.Menu.Delete", new { MenuId = id });
}
public async Task <int> DeleteExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
{
using (var cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlDeleteCronTrigger, TablePrefix, SchedNameLiteral)))
{
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
return(await cmd.ExecuteNonQueryAsync().ConfigureAwait(false));
}
}
public void Trace()
{
DbAccessor dba = DbAccessor.Create();
dba.PreCommandExecute += new TraceHandler().PrintSqlByConsole;
var dt = _dba.QueryDataTable("select top 10 * from sys_product");
Console.WriteLine(DataTableUtil.ToString(dt));
}
//public static void SetDbAccessorDbProviderName(DbAccessor dba, DbProviderName dbProviderName)
//{
// EnvValRepository.Instance.AddEvnVal(dba.GetHashCode().ToString(), dbProviderName.ToString());
//}
//public static string GetDbAccessorDbProviderName(DbAccessor dba)
//{
// var result = EnvValRepository.Instance.GetEvnVal(dba.GetHashCode().ToString());
// return result == null ? null : result.ToString();
//}
public static void SetDefaultDbAccessorDbProviderName(DbAccessor dba)
{
if (GetDefaultDbAccessorDbProviderName() != null) return;
lock (_objLock)
{
if (GetDefaultDbAccessorDbProviderName() == null)
{
EnvValRepository.Instance.AddEvnVal(DefaultDbAccessorDbProviderName_Key, dba.DbProviderName.ToString());
}
}
}
public static DbAccessor GetDbAccessor()
{
if (_dba == null)
{
lock (_objLock)
{
if (_dba == null)
{
_dba = CreateDbAccessor();
_dba.IsSingleton = true;
}
}
}
return _dba;
}
public static DbBulkCopy GetDbBulkCopy(DbAccessor dba,string connectionString)
{
DbBulkCopy dbBulkCopy = null;
if (dba.GetType() == typeof(SqlServerAccessor))
{
dbBulkCopy = new SqlServerBulkCopy(connectionString);
}
if (dbBulkCopy != null)
{
return dbBulkCopy;
}
else
{
throw new Exception(string.Format("“{0}”没有对应的DbBulkCopy实现！", dba.GetType()));
}
}
public Client(DbAccessorFactory factory)
{
dbAccessorFactory = factory;
dbAccessor = dbAccessorFactory.GetDbAccessor();
}
public static void SetDbAccessorDbProviderName(DbAccessor dba, DbProviderName dbProviderName)
{
EvnValRepository.Instance.AddEvnVal(dba.GetHashCode().ToString(), dbProviderName.ToString());
}
public static string GetDbAccessorDbProviderName(DbAccessor dba)
{
var result = EvnValRepository.Instance.GetEvnVal(dba.GetHashCode().ToString());
return result == null ? null : result.ToString();
}
public static void SetDefaultDbAccessorHashCode(DbAccessor dba)
{
EvnValRepository.Instance.AddEvnVal(DefaultDbAccessorHashCode_Key, dba.GetHashCode().ToString());
}
