public override async Task <List <T> > SelectMany(Expression <Func <T, bool> > where, int skip = 0, int take = 100, bool isIncludeDeleted = false,
List <OrderByInfo <T> > orderByInfos = null)
{
var sb = GetStringBuilderWithSelectFrom();
var parameters = new List <NpgsqlParameter>();
var prmSkip = new NpgsqlParameter <int>(SQLConstants.SKIP_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = skip
};
parameters.Add(prmSkip);
var prmTake = new NpgsqlParameter <int>(SQLConstants.TAKE_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = take
};
parameters.Add(prmTake);
AppendWhere(where, parameters, sb, isIncludeDeleted);
AppendOrderByFields(orderByInfos, sb);
sb.Append($"{PostgreSQLConstants.LIMIT} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.TAKE_PARAMETER_NAME} {PostgreSQLConstants.OFFSET} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.SKIP_PARAMETER_NAME} {Environment.NewLine}");
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
public override async Task <List <long> > SelectIds(Expression <Func <T, bool> > @where, bool isIncludeDeleted = false)
{
var sb = new StringBuilder();
sb.Append($"{SQLConstants.SELECT} {_sqlConstants.IdFieldName}{Environment.NewLine}");
sb.Append($"{SQLConstants.FROM} {_sqlConstants.TableFullName}{Environment.NewLine}");
if (isIncludeDeleted)
{
sb.Append($"{SQLConstants.WHERE} {_entityUtils.GetFieldNameFromPropertyName(nameof(BaseEntity.IsDeleted))} = true");
}
else
{
sb.Append($"{SQLConstants.WHERE} {_entityUtils.GetFieldNameFromPropertyName(nameof(BaseEntity.IsDeleted))} = false");
}
var parameters = new List <NpgsqlParameter>();
if (where != null)
{
var prmDictionary = new Dictionary <string, DbParameterInfo>();
var conditions = _expressionUtils.GetConditions(where.Body, prmDictionary);
AddToParameters(parameters, prmDictionary);
sb.Append($" AND {conditions}");
}
var result = await SQLExecutor.ExecuteSqlReturningList <long>(sb.ToString(), parameters);
return(result);
}
public override string doPatch(AppVersion dbVersion)
{
if (dbVersion != null && !dbVersion.before(this.getVersion()))
{
return("Don't patch " + this.version);
}
OleDbConnection conn = this.connPool.getConnection();
StringBuilder sb = new StringBuilder();
IList <string> sqls = new List <string>();
sqls.Add("ALTER TABLE project_media ADD COLUMN seq_num Integer;");
sqls.Add("CREATE TABLE app_version (version text(255));");
sqls.Add("INSERT INTO app_version (version) values ('1.0.0');");
SQLExecutor sqlExecutor = new SQLExecutor(ConnectionPool.getInstance().getConnection(), sqls);
string rs = sqlExecutor.execute();
IList <Project> projects = projectDao.queryAll();
foreach (Project project in projects)
{
MediaQueryCondition mediaQC = new MediaQueryCondition();
mediaQC.overview_project_id = project.id;
mediaQC.media_type = Media.MEDIA_TYPE_PIC;
IList <Media> medias = this.mediaDao.query(mediaQC);
IList <string> mediaIds = new List <string>();
foreach (Media m in medias)
{
mediaIds.Add(m.id);
}
projectDao.removeAllOverviewMedias(project.id);
projectDao.addMedias(project.id, mediaIds);
}
return(rs);
}
public override async Task <List <T> > SelectAll(Expression <Func <T, bool> > @where, Expression <Func <T, object> > orderByColumn = null, bool isAscending = true, bool isIncludeDeleted = false)
{
var sb = new StringBuilder();
sb.Append($"{SQLConstants.SELECT} *{Environment.NewLine}");
sb.Append($"{SQLConstants.FROM} {_sqlConstants.TableFullName}{Environment.NewLine}");
var orderColumn = _sqlConstants.IdFieldName;
if (orderByColumn != null)
{
orderColumn = _expressionUtils.GetField(orderByColumn.Body);
}
var ascOrDesc = SQLConstants.DESC;
if (isAscending)
{
ascOrDesc = SQLConstants.ASC;
}
var parameters = new List <NpgsqlParameter>();
AppendWhere(where, parameters, sb, isIncludeDeleted);
sb.Append($"{SQLConstants.ORDER_BY} {orderColumn} {ascOrDesc}{Environment.NewLine}");
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
private void ExecuteScripts(IEnumerable <string> scripts, bool throwExceptions)
{
if (scripts == null)
{
return;
}
var sqlExecutor = new SQLExecutor(BootstrapServices);
IList <string> errors = new List <string>();
foreach (var sql in scripts)
{
try {
if (ExecuteScriptsWithoutTransaction)
{
sqlExecutor.ExecuteNonQueryWithoutTransaction(sql);
}
else
{
sqlExecutor.ExecuteNonQuery(sql);
}
} catch (Exception e) {
errors.Add(e.Message);
}
}
if (throwExceptions && errors.Count > 0)
{
throw new Exception(
string.Format("Error executing scripts for provider '{0}' with connection string '{1}':{2}{3}",
BootstrapServices.DatabaseConfiguration.DatabaseProvider.Properties.DisplayName,
BootstrapServices.DatabaseConfiguration.ConnectionString,
Environment.NewLine,
string.Join(Environment.NewLine, errors.ToArray())));
}
}
public void Insert(T model)
{
string sql = ORMUtil.ConstructInsertStatement(model.GetType());
var objs = GetStatementParamArray(model);
SQLExecutor.ExecuteNonQuery(sql, _connectionString, objs.ToArray());
}
private static IDatabase _OpenDatabase(string databaseId)
{
try
{
var info = ORMConfig.ORMConfiguration.GetDatabaseInfo(databaseId);
IDbConnection cnn = CoreFactory.CreateDbProvider(databaseId).CreateConnection(info.ConnectString);
ISQLExecutor exe = new SQLExecutor(databaseId, cnn);
IObjectMapInfoCache cache = CoreFactory.ObjectMapInfoCache;
var dialect = CoreFactory.CreateDialectProvider(databaseId);
IModelSQLEmit emit = dialect.CreateModelSQLEmit(cache);
IMetaQuery meta = dialect.CreateMetaQuery(exe);
if (cnn.State != ConnectionState.Open)
{
cnn.Open();
}
return(new Database(exe, emit, meta));
}
catch (Exception ex)
{
throw ex.CreateWrapException <SessionException>();
}
}
protected DbGenerator(EntityUtils entityUtils,
SQLExecutor <TConnection, TCommand, TParameter> sqlExecutorMaster,
SQLExecutor <TConnection, TCommand, TParameter> sqlExecutor)
{
_entityUtils = entityUtils;
_sqlExecutorMaster = sqlExecutorMaster;
_sqlExecutor = sqlExecutor;
}
private static void AssertValues <T>(IDatabaseServices services, string sqlFunctionFragment, T expectedValue)
{
var sqlExecutor = new SQLExecutor(services);
string sql = string.Format(SQL_FORMAT, sqlFunctionFragment, MachineName);
T result = sqlExecutor.ExecuteScalar(sql).RuntimeValue <T>();
AssertEqual(expectedValue, result, string.Format(ERROR_MESSAGE_FORMAT, sqlFunctionFragment, sql));
}
protected IList <T> ReadByKey(IDictionary <string, object> conditions)
{
StringBuilder sb = new StringBuilder();
sb.Append(ORMUtil.ConstructSelectStatement(typeof(T)));
sb.Append(ConstructWhereByDictionary(conditions));
return(ORMUtil.ConvertDataTableToModel <T>(SQLExecutor.GetDataTable(sb.ToString(), _connectionString, conditions.Values.ToArray())));
}
public void Update(T model)
{
string sql = ORMUtil.ConstructUpdateStatement(model.GetType());
var objs = GetAllParamArrays(model);
SQLExecutor.ExecuteNonQuery(sql, _connectionString, objs.ToArray());
}
protected void DeleteByKey(IDictionary <string, object> conditions)
{
StringBuilder sb = new StringBuilder();
sb.Append(ORMUtil.ConstructDeleteStatement(typeof(T)));
sb.Append(ConstructWhereByDictionary(conditions));
SQLExecutor.ExecuteNonQuery(sb.ToString(), _connectionString, conditions.Values.ToArray());
}
private void AssertValue <T>(IDatabaseServices services, string defaultValueName, string defaultValue, object expectedValue)
{
var sqlExecutor = new SQLExecutor(services);
string sql = "SELECT " + defaultValue + " o1 FROM DUMMY" + MachineName;
object result = sqlExecutor.ExecuteScalar(sql).RuntimeValue <T>();
AssertEqual(expectedValue, result, string.Format(errorMessageFormat, defaultValueName, sql));
}
private void ApplicationChecksGridPrettyNames()
{
for (int i = 0; i < this.applicationChecksGrid.Columns.Count; i++)
{
string colName = this.applicationChecksGrid.Columns[i].HeaderText;
string prettyName = SQLExecutor.GetPrettyName("V_APPLICATION_CHECKS", colName);
this.applicationChecksGrid.Columns[i].HeaderText = prettyName;
}
}
public void ApplicationsGridPrettyNames()
{
for (int i = 0; i < this.applicationsGrid.Rows.Count; i++)
{
string colName = this.applicationsGrid.Rows[i].Cells[1].Value.ToString();
string prettyName = SQLExecutor.GetPrettyName("V_APPLICATION_CHECKS", colName);
this.applicationsGrid.Rows[i].Cells[0].Value = prettyName;
}
}
private void PrettyColumnNames()
{
for (int i = 0; i < this.applicationsGrid.Columns.Count; i++)
{
string colName = applicationsGrid.Columns[i].HeaderText;
string prettyName = SQLExecutor.GetPrettyName("B_APPLICATIONS", colName);
applicationsGrid.Columns[i].HeaderText = prettyName;
}
}
private void LoadApplicationChecksGrid(int results)
{
this.applicationChecksGrid = new DataGridView();
this.applicationChecksGrid.ReadOnly = true;
DataTable dataTable = new DataTable();
OracleDataReader dataCursor = SQLExecutor.GetRows("V_APPLICATION_CHECKS", "APL_CODE", this.applicationsCode);
dataTable.Load(dataCursor);
applicationChecksGrid.DataSource = dataTable;
}
private void LoadApplicationsGrid(int results)
{
this.applicationsGrid = new DataGridView();
this.applicationsGrid.ReadOnly = true;
DataTable table = new DataTable();
OracleDataReader dataCursor = SQLExecutor.GetResults("B_APPLICATIONS", results);
table.Load(dataCursor);
applicationsGrid.DataSource = table;
}
private void CommitChanges()
{
DataTable changesTable = this.checkSuitsDataTable.GetChanges();
if (changesTable != null)
{
changesTable.TableName = this.checkSuitsDataTable.TableName;
SQLExecutor.CommitChanges(changesTable);
}
}
public void TestParameterPrefix(DatabaseProviderTestCase tc)
{
var databaseServices = tc.Services;
var sqlExecutor = new SQLExecutor(databaseServices);
string sql = string.Format("SELECT VAL FROM DUMMY" + MachineName + " WHERE ID = {0}", sqlExecutor.GetParameterName(0, typeof(int)));
int value = sqlExecutor.ExecuteScalar(sql, 1).RuntimeValue <int>();
Assert.AreEqual(1, value, "ParameterPrefix didn't work as expected. SQL: " + sql);
}
public void TestBinaryDataDefaultValue(DatabaseProviderTestCase tc)
{
var databaseServices = tc.Services;
var sqlExecutor = new SQLExecutor(databaseServices);
IDMLDefaultValues dmlDefaultValues = databaseServices.DMLService.DefaultValues;
string sql = "SELECT " + dmlDefaultValues.BinaryData + " o1 FROM DUMMY" + MachineName;
byte[] result = sqlExecutor.ExecuteScalar(sql).RuntimeValue <byte[]>();
AssertEqual(0, (result ?? new byte[0]).Length, string.Format(errorMessageFormat, "Null", sql));
}
public void TestEscapeIdentifier(DatabaseProviderTestCase tc)
{
var databaseServices = tc.Services;
var sqlExecutor = new SQLExecutor(databaseServices);
IDMLIdentifiers dmlIdentifiers = databaseServices.DMLService.Identifiers;
string sql = "SELECT " + dmlIdentifiers.EscapeIdentifier("DUMMY" + MachineName) + "." + dmlIdentifiers.EscapeIdentifier("SELECT") + " FROM " + dmlIdentifiers.EscapeIdentifier("DUMMY" + MachineName);
int value = sqlExecutor.ExecuteScalar(sql).RuntimeValue <int>();
AssertEqual(123, value, "Escape function didn't work as expected. SQL: " + sql);
}
public LocalSQLUserBase(string databasePath, string serverString)
{
DatabasePath = databasePath;
DatabaseName = Path.GetFileNameWithoutExtension(DatabasePath);
ConnectionString = "Data Source=" + serverString + ";AttachDbFilename=" + databasePath + ";Integrated Security=True";
ServerConnectionString = "Data Source=" + serverString + ";Initial Catalog=master; Integrated Security=true";
sqlExecutor = new SQLExecutor(ConnectionString);
InitializeDatabase();
}
/// <summary>
/// Выполнить SQL скрипт
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExecute_Click(object sender, EventArgs e)
{
if (SQLExecutor.ConnectionStringTest())
{
SQLExecutor.Execute(rtextboxSQLQuery.Text);
}
else
{
MessageBox.Show("Нет соединения с базой данных.\r\nПроверьте строку подключения.", "Невозможно выполнить запрос.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private int applicationsCode; // Set this if this is form has objects with parents
private void LoadApplicationsGrid(int results)
{
this.applicationsGrid = new DataGridView();
this.applicationsGrid.ReadOnly = true;
DataTable dataTable = new DataTable();
OracleDataReader dataCursor = SQLExecutor.GetRows("B_APPLICATIONS", "APL_CODE", this.applicationsCode);
dataTable.Load(dataCursor);
dataTable = Utils.TransposeDataTable(dataTable); //Transpose for gridView
applicationsGrid.DataSource = dataTable;
}
private static void AssertValue <T>(IDatabaseServices services, string funcName, string sql, T expectedValue, params object[] parameters)
{
var sqlExecutor = new SQLExecutor(services);
if (parameters.Length != 0)
{
sql = string.Format(sql, parameters.Select <object, object>((o, i) => sqlExecutor.GetParameterName(i, o.GetType())).ToArray());
}
T result = sqlExecutor.ExecuteScalar(sql, parameters).RuntimeValue <T>();
AssertEqual(expectedValue, result, string.Format(ERROR_MESSAGE_FORMAT, funcName, string.Join(", ", parameters.Select <object, string>(o => o.ToString()).ToArray()), sql));
}
public ActionResult Index()
{
var executor = new SQLExecutor();
var model = new SQLResult
{
ConnectionString = _sqlCommandServices.GetConnectionString(),
Histories = _sqlCommandServices.GetHistories(),
ReadOnly = true,
Tables = executor.GetTableNames()
};
return(View(model));
}
public ActionResult Index()
{
var executor = new SQLExecutor();
var model = new SQLResult
{
ConnectionString = _sqlCommandService.GetConnectionString(),
Histories = _sqlCommandService.GetHistories(EzCMSContants.DefaultHistoryStart, EzCMSContants.DefaultHistoryLength),
ReadOnly = true,
Tables = executor.GetTableNames()
};
return(View(model));
}
public static void IntialDB()
{
string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
string sql = @"if not exists (select * from dbo.sysobjects where id = object_id(N'[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE Employee(
Id INT IDENTITY NOT NULL PRIMARY KEY ,
FirstName VARCHAR(100) NOT NULL,
MiddleName VARCHAR(100) NULL,
LastName VARCHAR(100) NULL,
DateOfBirth DATETIME NOT NULL
);";
SQLExecutor.ExecuteNonQuery(sql, connectionString);
}
public override async Task <List <T> > SelectAll(Expression <Func <T, bool> > where, bool isIncludeDeleted = false, List <OrderByInfo <T> > orderByInfos = null)
{
var sb = GetStringBuilderWithSelectFrom();
var parameters = new List <NpgsqlParameter>();
AppendWhere(where, parameters, sb, isIncludeDeleted);
AppendOrderByFields(orderByInfos, sb);
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
