private bool IsSqlEditionSupported(string connectionString)
{
var operation = new DbOperation(connectionString, "SELECT SERVERPROPERTY ( 'EngineEdition' )", _trace);
var edition = (int)operation.ExecuteScalar();
return edition >= SqlEngineEdition.Standard && edition <= SqlEngineEdition.Express;
}
private void DbDo(DbOperation opt)
{
using (var sqlConnection = new SqlConnection(GetConnectionString()))
{
sqlConnection.Open();
opt(sqlConnection);
}
}
private void Receive(object state)
{
var tcs = (TaskCompletionSource<object>)state;
if (!_lastPayloadId.HasValue)
{
var lastPayloadIdOperation = new DbOperation(_connectionString, _maxIdSql, _trace)
{
TracePrefix = _tracePrefix
};
try
{
_lastPayloadId = (long?)lastPayloadIdOperation.ExecuteScalar();
Queried();
// Complete the StartReceiving task as we've successfully initialized the payload ID
tcs.TrySetResult(null);
}
catch (Exception ex)
{
tcs.TrySetException(ex);
return;
}
}
// NOTE: This is called from a BG thread so any uncaught exceptions will crash the process
lock (this)
{
if (_disposed)
{
return;
}
var parameter = _dbProviderFactory.CreateParameter();
parameter.ParameterName = "PayloadId";
parameter.Value = _lastPayloadId.Value;
_dbOperation = new ObservableDbOperation(_connectionString, _selectSql, _trace, parameter)
{
TracePrefix = _tracePrefix
};
}
_dbOperation.Queried += () => Queried();
_dbOperation.Faulted += ex => Faulted(ex);
_dbOperation.Changed += () =>
{
Trace.TraceInformation("{0}Starting receive loop again to process updates", _tracePrefix);
_dbOperation.ExecuteReaderWithUpdates(ProcessRecord);
};
_dbOperation.ExecuteReaderWithUpdates(ProcessRecord);
_trace.TraceWarning("{0}SqlReceiver.Receive returned", _tracePrefix);
}
public void ExecuteTest()
{
var db = new MockDb();
db.Record.json = "{}";
var op = new DbOperation(string.Empty, string.Empty);
op.Transforms.Add(new JsonTransformation("json", "json.test = 123;", format: false));
op.Execute(db);
Assert.AreEqual(db.Record.json, "{\"test\":123}");
}
public frmInstallDB(IPLInstall install, DbOperation type)
{
_FWInstall = install;
InitializeComponent();
Check();
if (type == DbOperation.Install)
InstallDb();
else
UninstallDb();
}
/// <summary>
/// Displayfields provide a minimal name/value context for data binding the row collection of core.counties.
/// </summary>
/// <returns>Returns an enumerable name and value collection for the table core.counties</returns>
/// <exception cref="UnauthorizedException">Thown when the application user does not have sufficient privilege to perform this action.</exception>
public IEnumerable <DisplayField> GetDisplayFields()
{
List <DisplayField> displayFields = new List <DisplayField>();
if (string.IsNullOrWhiteSpace(this._Catalog))
{
return(displayFields);
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.Read, this._LoginId, this._Catalog, false);
}
if (!this.HasAccess)
{
Log.Information("Access to get display field for entity \"County\" was denied to the user with Login ID {LoginId}", this._LoginId);
throw new UnauthorizedException("Access is denied.");
}
}
const string sql = "SELECT county_id AS key, county_code || ' (' || county_name || ')' as value FROM core.counties;";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
using (DataTable table = DbOperation.GetDataTable(this._Catalog, command))
{
if (table?.Rows == null || table.Rows.Count == 0)
{
return(displayFields);
}
foreach (DataRow row in table.Rows)
{
if (row != null)
{
DisplayField displayField = new DisplayField
{
Key = row["key"].ToString(),
Value = row["value"].ToString()
};
displayFields.Add(displayField);
}
}
}
}
return(displayFields);
}
internal static Collection <PgColumn> GetColumns(PgTable pgTable)
{
Collection <PgColumn> columns = new Collection <PgColumn>();
#region sql
string sql = FileHelper.ReadSqlResource("columns.sql");
#endregion
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@SchemaName", pgTable.SchemaName);
command.Parameters.AddWithValue("@TableName", pgTable.Name);
using (DataTable table = DbOperation.GetDataTable(command))
{
if (table.Rows.Count.Equals(0))
{
return(columns);
}
foreach (DataRow row in table.Rows)
{
PgColumn column = new PgColumn
{
SchemaName = pgTable.SchemaName,
TableName = pgTable.Name,
Name = Conversion.TryCastString(row["column_name"]),
OrdinalPosition = Conversion.TryCastInteger(row["ordinal_position"]),
DefaultValue = Conversion.TryCastString(row["column_default"]),
DataType = Conversion.TryCastString(row["data_type"]),
IsNullable = Conversion.TryCastBoolean(row["is_nullable"]),
MaxLength = Conversion.TryCastInteger(row["character_maximum_length"]),
Description = Conversion.TryCastString(row["description"]),
PrimaryKeyConstraintName = Conversion.TryCastString(row["key"]),
ForiegnKeyName = Conversion.TryCastString(row["constraint_name"]),
ForeignSchemaName = Conversion.TryCastString(row["references_schema"]),
ForeignTableName = Conversion.TryCastString(row["references_table"]),
ForeignColumnName = Conversion.TryCastString(row["references_field"])
};
column.IsPrimaryKey = !string.IsNullOrWhiteSpace(column.PrimaryKeyConstraintName);
columns.Add(column);
}
}
}
return(columns);
}
public void Setup()
{
_contacts = SetUpContacts();
_dbEntities = new Mock <ContactsEntities>().Object;
_contactRepository = SetUpContactRepository();
var dbOperation = new Mock <DbOperation>();
dbOperation.SetupGet(s => s.ContactRepository).Returns(_contactRepository);
_dbOperation = dbOperation.Object;
_contactService = new ContactServices(_dbOperation);
_client = new HttpClient {
BaseAddress = new Uri(ServiceBaseURL)
};
}
public static int GetTotalRecords(string catalog, string tableSchema, string tableName)
{
var sql = "SELECT COUNT(*) FROM @TableSchema.@TableName";
using (var command = new NpgsqlCommand())
{
sql = sql.Replace("@TableSchema", DbFactory.Sanitizer.SanitizeIdentifierName(tableSchema));
sql = sql.Replace("@TableName", DbFactory.Sanitizer.SanitizeIdentifierName(tableName));
command.CommandText = sql;
return(Conversion.TryCastInteger(DbOperation.GetScalarValue(catalog, command)));
}
}
private bool CreateDb()
{
string sql = "CREATE DATABASE {0} WITH ENCODING='UTF8' TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C';";
sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(this.Catalog.ToLower()));
string catalog = Factory.MetaDatabase;
string connectionString = ConnectionString.GetSuperUserConnectionString(catalog);
using (var command = new NpgsqlCommand(sql))
{
return(DbOperation.ExecuteNonQuery(this.Catalog, command, connectionString));
}
}
public static Employee SelectLastEmployee()
{
string selectQuery = "SELECT TOP(1)* FROM Employees ORDER BY ID DESC";
DataTable dt = DbOperation.GetTable(selectQuery);
Employee sonEmployee = new Employee();
sonEmployee.Id = (int)dt.Rows[0]["Id"];
sonEmployee.FirstName = dt.Rows[0]["FirstName"].ToString();
sonEmployee.LastName = dt.Rows[0]["LastName"].ToString();
return(sonEmployee);
}
public static bool WithdrawTransaction(long transactionMasterId, int userId, string reason, short status)
{
const string sql = "UPDATE transactions.transaction_master SET verification_status_id=@Status, verified_by_user_id=@UserId, verification_reason=@Reason WHERE transactions.transaction_master.transaction_master_id=@TransactionMasterId;";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@Status", status);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@Reason", reason);
command.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
return(DbOperation.ExecuteNonQuery(command));
}
}
public ActionResult Index(ApplyPassport A)
{
var username = Request.Cookies["UserName"].Value.ToString();
ViewBag.CountryDD = null;
Service1Client PVMS = new Service1Client();
Country[] D = PVMS.FetchCountries();
List <SelectListItem> CountrynewList = new List <SelectListItem>();
CountrynewList.Clear();
CountrynewList.Add(new SelectListItem {
Text = "Select Country", Value = "-1"
});
for (var i = 0; i < D.Length; i++)
{
CountrynewList.Add(new SelectListItem {
Text = D[i].CountryName, Value = D[i].CountryId.ToString()
});
}
ViewBag.CountryDD = CountrynewList.ToList();
if (checkForApplyPassportValidation(A))
{
return(View());
}
int userId = DbOperation.FetchIdByUserName(username);
if (DbOperation.CheckUserHaveApplyPassport(userId))
{
return(Redirect("/ApplyPassportError"));
}
else
{
A.UserId = userId;
bool successful = DbOperation.ApplyPassportNew(A);
if (successful)
{
var json = DbOperation.fetchApplyPassportbyUserId(userId);
Session["PassportNumber"] = json[0].PassportNumber;
Session["Amount"] = json[0].Amount;
Session["successMsg"] = "<b>Need the passport number while giving payment? Please note down your Id \n </b> \n" + json[0].PassportNumber + ".Passport application cost is Rs." + json[0].Amount;
return(Redirect("/ApplyPassportSuccess"));
}
else
{
return(View());
}
}
}
private void btnViewDiary_Click(object sender, EventArgs e)
{
try
{
DbOperation dbops = new DbOperation();
DataSet ds = new DataSet();
ds = dbops.viewDailyDiary(cmbStdID.SelectedItem.ToString(), dateTimeFrom.Value.ToString("yyyy-MM-dd"), dateTimeTo.Value.ToString("yyyy-MM-dd"));
dailyDiaryView.DataSource = ds;
}
catch (Exception E)
{
MessageBox.Show(E.Message);
}
}
public static DataTable GetTable(string catalog, string tableSchema, string tableName, string orderBy)
{
var sql = "SELECT * FROM @TableSchema.@TableName ORDER BY @OrderBy ASC;";
using (var command = new NpgsqlCommand())
{
sql = sql.Replace("@TableSchema", DbFactory.Sanitizer.SanitizeIdentifierName(tableSchema));
sql = sql.Replace("@TableName", DbFactory.Sanitizer.SanitizeIdentifierName(tableName));
sql = sql.Replace("@OrderBy", DbFactory.Sanitizer.SanitizeIdentifierName(orderBy));
command.CommandText = sql;
return(DbOperation.GetDataTable(catalog, command));
}
}
public static bool Save(long loginId, int userId, int officeId, Collection <Models.Reorder> details)
{
string sql = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM transactions.post_purhcase_reorder(transactions.get_value_date(@OfficeId::integer)::date, @LoginId::bigint, @UserId::integer, @OfficeId::integer, ARRAY[{0}]);", CreatePurchaseReorderTypeParameter(details));
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@LoginId", loginId);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.Parameters.AddRange(AddPurchaseReorderTypeParameter(details).ToArray());
return(DbOperation.ExecuteNonQuery(command));
}
}
public void Perform(string catalog, long loginId)
{
const string sql = "SELECT * FROM transactions.perform_eod_operation(@LoginId::bigint);";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@LoginId", loginId);
command.CommandTimeout = 3600;
DbOperation operation = new DbOperation();
operation.Listen += this.Listen;
operation.ListenNonQuery(catalog, command);
}
}
private static void Persist(DataRow r, DbOperation op)
{
//查询语句
StringBuilder strSql = new StringBuilder();
//属性与类型字典
Dictionary <string, DbType> dic = new Dictionary <string, DbType>();
//命令参数列表
DbParameter[] cmdParms = null;
//反射获得函数
Type type = typeof(TEntity);
System.Reflection.MethodInfo method = null;
switch (op)
{
case DbOperation.Insert: method = type.GetMethod("PrepareAddCommand"); break;
case DbOperation.Update: method = type.GetMethod("PrepareUpdateCommand"); break;
case DbOperation.Delete: method = type.GetMethod("PrepareDeleteCommand"); break;
default: break;
}
//准备参数
object[] parms = new object[2];
parms[0] = strSql;
parms[1] = dic;
//调用函数
method.Invoke(null, parms);
//生成数据库执行参数
cmdParms = new DbParameter[dic.Count];
int i = -1;
foreach (string name in dic.Keys)
{
cmdParms[++i] = DataHelper.CreateInDbParameter(name, dic[name], r[i]);
}
//执行数据库命令
DataHelper.ExecuteNonQuery(trans, CommandType.Text, strSql.ToString(), cmdParms);
method = null;
}
// GET: Authentication
//[Authorize]
public ActionResult Index()
{
if (Request.Cookies["UserName"] == null)
{
return(Redirect("/SignIn"));
}
User U = new User();
Service1Client PVMS = new Service1Client();
//HintQuestion[] D = PVMS.FetchHintQuestion();
//ViewBag.HintQuestionDD = D.ToList();
var username = Request.Cookies["UserName"].Value.ToString();
TempData["HintQuestion"] = DbOperation.FetchHintQuestionByUserName(username);
return(View(U));
}
public static DataTable GetDirectCashFlowStatement(string catalog, DateTime from, DateTime to, int userId, int officeId, decimal factor)
{
const string sql = "SELECT transactions.get_cash_flow_statement(@From::date, @To::date, @UserId::integer, @OfficeId::integer, @Factor::integer);";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@From", from);
command.Parameters.AddWithValue("@To", to);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.Parameters.AddWithValue("@Factor", factor);
return(JSONHelper.JsonToDataTable(Conversion.TryCastString(DbOperation.GetScalarValue(catalog, command))));
}
}
public virtual void FailedOperation(DbOperation operation)
{
var dbEntityOperation = operation as DbEntityOperation;
if (dbEntityOperation == null)
{
return;
}
var entityOperation = dbEntityOperation;
if (entityOperation.EntityType.IsSubclassOf(typeof(JobEntity)))
{
AcquiredJobs.RemoveJobId(entityOperation.Entity.Id);
}
}
public override void performCleanup()
{
//JAVA TO C# CONVERTER TODO TASK: There is no .NET Dictionary equivalent to the Java 'putAll' method:
deleteOperations.putAll(performProcessCleanup());
if (DmnEnabled)
{
//JAVA TO C# CONVERTER TODO TASK: There is no .NET Dictionary equivalent to the Java 'putAll' method:
deleteOperations.putAll(performDmnCleanup());
}
DbOperation batchCleanup = performBatchCleanup();
deleteOperations[batchCleanup.EntityType] = batchCleanup;
}
private static AuditBase CreateAuditRecordBase(object obj, DbOperation operation, string auditText)
{
var auditRecord = new AuditBase
{
RecordId = GetRecordId(obj),
TableName = ClassConfigContainer.FindClassConfig2(obj).TableName,
Description = auditText,
TimeStamp = DateTime.Now,
Operation = operation.ToString(),
ObjectToAudit = obj
};
return auditRecord;
}
public static DataTable GetBalanceSheet(DateTime previousPeriod, DateTime currentPeriod, int userId, int officeId, int factor)
{
const string sql = "SELECT * FROM transactions.get_balance_sheet(@Previous, @Current, @UserId, @OfficeId, @Factor)";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@Previous", previousPeriod);
command.Parameters.AddWithValue("@Current", currentPeriod);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.Parameters.AddWithValue("@Factor", factor);
return(DbOperation.GetDataTable(command));
}
}
private static AuditBase CreateAuditRecordBase(object obj, DbOperation operation, string auditText)
{
var auditRecord = new AuditBase
{
RecordId = GetRecordId(obj),
TableName = ClassConfigContainer.FindClassConfig2(obj).TableName,
Description = auditText,
TimeStamp = DateTime.Now,
Operation = operation.ToString(),
ObjectToAudit = obj
};
return(auditRecord);
}
public void Run_test()
{
string app_key = ConfigUtil.App_key;
string app_secret = ConfigUtil.App_secret;
string session = ConfigUtil.session;
string iposApiUrl = ConfigUtil.IposApiUrl;
ITopClient topClient = new DefaultTopClient(iposApiUrl, app_key, app_secret, "json");
RetailIfashionSkuinfoGetRequest retailIfashionSkuinfoGetRequest = new RetailIfashionSkuinfoGetRequest();
DbOperation dbOperation = new DbOperation(ConfigUtil.ConnectionString);
string strCmd = "select ID,store_id,item_id,sku_id,sku_bar_code,shop_name,seller_nick,item_title,item_pic,item_price,color,size,short_url,current_amount from storeskulist where 1=0";
DataTable dataTable = dbOperation.ExecuteQuery(strCmd).Tables[0];
dataTable.TableName = "storeskulist";
retailIfashionSkuinfoGetRequest.SkuId = "4374388018263";
retailIfashionSkuinfoGetRequest.ItemId = "601201018038";
RetailIfashionSkuinfoGetResponse retailIfashionSkuinfoGetResponse = topClient.Execute <RetailIfashionSkuinfoGetResponse>(retailIfashionSkuinfoGetRequest, session);
if (retailIfashionSkuinfoGetResponse != null && retailIfashionSkuinfoGetResponse.Result.SuccessAndHasValue && retailIfashionSkuinfoGetResponse.Result.Data != null)
{
RetailIfashionSkuinfoGetResponse.SkuInfoDomain data = retailIfashionSkuinfoGetResponse.Result.Data;
DataTable dataTable2 = JsonHelper.SetDataTableFromQT <RetailIfashionSkuinfoGetResponse.SkuInfoDomain>(data, "storeskulist");
foreach (DataRow dataRow in dataTable2.Rows)
{
DataRow dataRow2 = dataTable.NewRow();
dataRow2.BeginEdit();
foreach (DataColumn dataColumn in dataTable.Columns)
{
if (dataColumn.ColumnName.ToString() != "ID")
{
dataRow2[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName];
}
}
dataRow2.EndEdit();
dataTable.Rows.Add(dataRow2);
}
}
try
{
if (dbOperation.SqlBulkCopy(dataTable, "storeskulist"))
{
LogUtil.WriteInfo(this, "新增成功", "新增商品档案成功");
}
}
catch (Exception ex)
{
LogUtil.WriteError(this, "error:" + ex.Message);
}
}
protected internal virtual void flushDbOperations(IList <DbOperation> operationsToFlush, IList <DbOperation> allOperations)
{
// execute the flush
foreach (DbOperation dbOperation in operationsToFlush)
{
bool doOptimisticLockingException = false;
try
{
persistenceSession.executeDbOperation(dbOperation);
}
catch (Exception e)
{
//some of the exceptions are considered to be optimistic locking exception
doOptimisticLockingException = isOptimisticLockingException(dbOperation, e);
if (!doOptimisticLockingException)
{
throw LOG.flushDbOperationException(allOperations, dbOperation, e);
}
}
if (dbOperation.Failed || doOptimisticLockingException)
{
handleOptimisticLockingException(dbOperation);
}
}
if (Context.ProcessEngineConfiguration.JdbcBatchProcessing)
{
IList <BatchResult> flushResult = new List <BatchResult>();
try
{
flushResult = persistenceSession.flushOperations();
}
catch (Exception e)
{
//some of the exceptions are considered to be optimistic locking exception
DbOperation failedOperation = hasOptimisticLockingException(operationsToFlush, e);
if (failedOperation == null)
{
throw LOG.flushDbOperationsException(allOperations, e);
}
else
{
handleOptimisticLockingException(failedOperation);
}
}
checkFlushResults(operationsToFlush, flushResult);
}
}
public static DataTable GetPLAccount(DateTime from, DateTime to, int userId, int officeId, bool compact, decimal factor)
{
const string sql = "SELECT transactions.get_profit_and_loss_statement(@From::date, @To::date, @UserId, @OfficeId, @Factor, @Compact);";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@From", from);
command.Parameters.AddWithValue("@To", to);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.Parameters.AddWithValue("@Factor", factor);
command.Parameters.AddWithValue("@Compact", compact);
return(JSONHelper.JsonToDataTable(Conversion.TryCastString(DbOperation.GetScalarValue(command))));
}
}
public override void Run()
{
string extractedPath = this.Config.GetExtractDirectoryDestination();
string path = PathHelper.Combine(extractedPath, "db/patch.sql");
if (File.Exists(path))
{
this.OnProgress(new ProgressInfo(this.Description, Labels.PatchingDatabase));
string sql = File.ReadAllText(path, Encoding.UTF8);
DbOperation.ExecuteNonQuery(AppUsers.GetCurrentUserDB(), sql);
this.OnProgress(new ProgressInfo(this.Description, Labels.PatchedDatabase));
}
}
private static void RunInstallScript()
{
bool run = Conversion.TryCastBoolean(ConfigurationManager.AppSettings["RunInstallScript"]);
if (!run)
{
return;
}
string script = ConfigurationManager.AppSettings["InstallScriptPath"];
using (NpgsqlCommand command = new NpgsqlCommand(script))
{
DbOperation.ExecuteNonQuery(command);
}
}
public static void PersistAll(DataTable t, DbOperation op)
{
using (conn = DataHelper.CreateConnection())
{
conn.Open();
trans = conn.BeginTransaction();
foreach (DataRow row in t.Rows)
{
Persist(row, op);
}
trans.Commit();
conn.Close();
}
t.Clear();
t = null;
}
public static string GetEmailAddress(string catalog, TranBook tranBook, SubTranBook subTranBook, long tranId)
{
string sql = "SELECT core.get_email_address_by_party_id(party_id) FROM transactions.non_gl_stock_master WHERE non_gl_stock_master_id=@TranId;";
if (subTranBook == SubTranBook.Direct || subTranBook == SubTranBook.Receipt || subTranBook == SubTranBook.Invoice || subTranBook == SubTranBook.Return)
{
sql = "SELECT core.get_email_address_by_party_id(party_id) FROM transactions.stock_master WHERE transaction_master_id=@TranId;";
}
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@TranId", tranId);
return(Conversion.TryCastString(DbOperation.GetScalarValue(catalog, command)));
}
}
public static bool ValidateItemForReturn(long stockMasterId, int storeId, string itemCode, string unit, int quantity, decimal price)
{
const string sql = "SELECT transactions.validate_item_for_return(@StockMasterId, @StoreId, @ItemCode, @UnitName, @Quantity, @Price);";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@StockMasterId", stockMasterId);
command.Parameters.AddWithValue("@StoreId", storeId);
command.Parameters.AddWithValue("@ItemCode", itemCode);
command.Parameters.AddWithValue("@UnitName", unit);
command.Parameters.AddWithValue("@Quantity", quantity);
command.Parameters.AddWithValue("@Price", price);
return(Conversion.TryCastBoolean(DbOperation.GetScalarValue(command)));
}
}
public static bool UnitExistsByName(string catalog, string unitName)
{
const string sql = "SELECT 1 FROM core.units WHERE core.units.unit_name=@UnitName;";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@UnitName", unitName);
var value = DbOperation.GetScalarValue(catalog, command);
if (value != null)
{
return(value.ToString().Equals("1"));
}
}
return(false);
}
public static void Verify(long tranId, int officeId, int userId, long loginId, short verificationStatusId, string reason)
{
const string sql = "SELECT * FROM transactions.verify_transaction(@TranId::bigint, @OfficeId, @UserId, @LoginId::bigint, @VerificationStatusId::smallint, @Reason);";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@TranId", tranId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@LoginId", loginId);
command.Parameters.AddWithValue("@VerificationStatusId", verificationStatusId);
command.Parameters.AddWithValue("@Reason", reason);
DbOperation.ExecuteNonQuery(command);
}
}
public Task Send(IList<Message> messages)
{
if (messages == null || messages.Count == 0)
{
return TaskAsyncHelper.Empty;
}
var parameter = _dbProviderFactory.CreateParameter();
parameter.ParameterName = "Payload";
parameter.DbType = DbType.Binary;
parameter.Value = SqlPayload.ToBytes(messages);
var operation = new DbOperation(_connectionString, _insertDml, _trace, parameter);
return operation.ExecuteNonQueryAsync();
}
public static DataTable GetDataTable(string catalog, string sql, Collection <KeyValuePair <string, object> > parameters)
{
/**************************************************************************************
* A MixERP report is a developer-only feature.
* But, that does not guarantee that there will be no misuse.
* So, the possible risk factor cannot be ignored altogether in this context.
* Therefore, a review for defense against possible
* SQL Injection Attacks is absolutely required here.
*
* Although, we connect to PostgreSQL Database Server using a login "report_user"
* which has a read-only access for executing the SQL statements to produce the report.
*
* The SQL query is expected to have only the SELECT statement, but there is no
* absolute and perfect way to parse and determine that the query contained
* in the report is actually a "SELECT-only" statement.
*
* Moreover, the prospective damage could occur due to somebody messing up
* with the permission of the database user "report_user" which is restricted by default
* with a read-only access.
*
* This could happen on the DB server, where we cannot "believe"
* that the permissions are perfectly intact.
*
* TODO: Investigate more on how this could be done better.
***************************************************************************************/
if (string.IsNullOrWhiteSpace(sql))
{
return(null);
}
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
if (parameters != null)
{
foreach (KeyValuePair <string, object> p in parameters)
{
command.Parameters.AddWithValue(p.Key, p.Value);
}
}
//A separate connection to database using a restricted login is established here.
string connectionString = DbConnection.ReportConnectionString(catalog);
return(DbOperation.GetDataTable(command, connectionString));
}
}
internal static object CreateAuditRecord(object obj, DbOperation operation, string auditText)
{
var auditRecordBase = CreateAuditRecordBase(obj, operation, auditText);
var databaseSetup = DatabaseSetup.Instance;
if (databaseSetup.AuditRecordType == null)
{
throw new EasylinkException("Can not create audit record because audit record type is not set.");
}
if (!typeof(AuditBase).IsAssignableFrom(databaseSetup.AuditRecordType))
{
throw new EasylinkException("{0} is not inherited from AuditRecordBase.", databaseSetup.AuditRecordType.FullName);
}
var auditRecord = Activator.CreateInstance(databaseSetup.AuditRecordType);
var baseProperties = auditRecordBase.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (var baseProperty in baseProperties)
{
var basePropertyValue = baseProperty.GetValue(auditRecordBase, null);
var property = databaseSetup.AuditRecordType.GetProperty(baseProperty.Name);
property.SetValue(auditRecord, basePropertyValue, null);
}
(auditRecord as AuditBase).PrepareBeforeInsert();
return auditRecord;
}
public void Install()
{
_trace.TraceInformation("Start installing SignalR SQL objects");
if (!IsSqlEditionSupported(_connectionString))
{
throw new PlatformNotSupportedException(Resources.Error_UnsupportedSqlEdition);
}
var script = GetType().Assembly.StringResource("Microsoft.AspNet.SignalR.SqlServer.install.sql");
script = script.Replace("SET @SCHEMA_NAME = 'SignalR';", "SET @SCHEMA_NAME = '" + SqlMessageBus.SchemaName + "';");
script = script.Replace("SET @SCHEMA_TABLE_NAME = 'Schema';", "SET @SCHEMA_TABLE_NAME = '" + SchemaTableName + "';");
script = script.Replace("SET @TARGET_SCHEMA_VERSION = 1;", "SET @TARGET_SCHEMA_VERSION = " + SchemaVersion + ";");
script = script.Replace("SET @MESSAGE_TABLE_COUNT = 3;", "SET @MESSAGE_TABLE_COUNT = " + _tableCount + ";");
script = script.Replace("SET @MESSAGE_TABLE_NAME = 'Messages';", "SET @MESSAGE_TABLE_NAME = '" + _messagesTableNamePrefix + "';");
var operation = new DbOperation(_connectionString, script, _trace);
operation.ExecuteNonQuery();
_trace.TraceInformation("SignalR SQL objects installed");
}
private void ProcessRecord(IDataRecord record, DbOperation dbOperation)
{
var id = record.GetInt64(0);
var payload = SqlPayload.FromBytes(record.GetBinary(1));
if (id != _lastPayloadId + 1)
{
_trace.TraceError("{0}Missed message(s) from SQL Server. Expected payload ID {1} but got {2}.", _tracePrefix, _lastPayloadId + 1, id);
}
if (id <= _lastPayloadId)
{
_trace.TraceInformation("{0}Duplicate message(s) or payload ID reset from SQL Server. Last payload ID {1}, this payload ID {2}", _tracePrefix, _lastPayloadId, id);
}
_lastPayloadId = id;
// Update the Parameter with the new payload ID
dbOperation.Parameters[0].Value = _lastPayloadId;
// Pass to the underlying message bus
_onReceived((ulong)id, payload.Messages);
_trace.TraceVerbose("{0}Payload {1} containing {2} message(s) received", _tracePrefix, id, payload.Messages.Count);
}
private void ProcessRecord(IDataRecord record, DbOperation dbOperation)
private void ProcessRecord(DbDataReader record, DbOperation dbOperation)
#endif
{
var id = record.GetInt64(0);
ScaleoutMessage message = SqlPayload.FromBytes(record);
_logger.LogVerbose(String.Format("{0}SqlReceiver last payload ID={1}, new payload ID={2}", _loggerPrefix, _lastPayloadId, id));
if (id > _lastPayloadId + 1)
{
_logger.LogError(String.Format("{0}Missed message(s) from SQL Server. Expected payload ID {1} but got {2}.", _loggerPrefix, _lastPayloadId + 1, id));
}
else if (id <= _lastPayloadId)
{
_logger.LogInformation(String.Format("{0}Duplicate message(s) or payload ID reset from SQL Server. Last payload ID {1}, this payload ID {2}", _loggerPrefix, _lastPayloadId, id));
}
_lastPayloadId = id;
// Update the Parameter with the new payload ID
dbOperation.Parameters[0].Value = _lastPayloadId;
_logger.LogVerbose(String.Format("{0}Updated receive reader initial payload ID parameter={1}", _loggerPrefix, _dbOperation.Parameters[0].Value));
_logger.LogVerbose(String.Format("{0}Payload {1} containing {2} message(s) received", _loggerPrefix, id, message.Messages.Count));
Received((ulong)id, message);
}
/// <summary>
/// 获得数据库连接
/// </summary>
private void PrepareCommand()
{
// 写入调试信息
#if (DEBUG)
int milliStart = Environment.TickCount;
Trace.WriteLine(DateTime.Now.ToString(BaseSystemInfo.TimeFormat) + " :Begin: " + MethodBase.GetCurrentMethod().ReflectedType.Name + "." + MethodBase.GetCurrentMethod().Name);
#endif
this.sqlOperation = DbOperation.Update;
this.CommandText = string.Empty;
this.TableName = string.Empty;
this.InsertValue = string.Empty;
this.InsertField = string.Empty;
this.UpdateSql = string.Empty;
this.WhereSql = string.Empty;
// 判断是否为空，要区别静态方法与动态调用方法
if (!DbHelper.AutoOpenClose)
{
DbHelper.GetDbCommand().Parameters.Clear();
}
// 写入调试信息
#if (DEBUG)
int milliEnd = Environment.TickCount;
Trace.WriteLine(DateTime.Now.ToString(BaseSystemInfo.TimeFormat) + " Ticks: " + TimeSpan.FromMilliseconds(milliEnd - milliStart).ToString() + " :End: " + MethodBase.GetCurrentMethod().ReflectedType.Name + "." + MethodBase.GetCurrentMethod().Name);
#endif
}
/// <summary>
/// 开始查询语句
/// </summary>
/// <param name="tableName">目标表</param>
/// <param name="dbOperation">语句操作类别</param>
private void Begin(string tableName, DbOperation dbOperation)
{
// 写入调试信息
#if (DEBUG)
int milliStart = Environment.TickCount;
Trace.WriteLine(DateTime.Now.ToString(BaseSystemInfo.TimeFormat) + " :Begin: " + MethodBase.GetCurrentMethod().ReflectedType.Name + "." + MethodBase.GetCurrentMethod().Name);
#endif
this.PrepareCommand();
this.TableName = tableName;
this.sqlOperation = dbOperation;
// 写入调试信息
#if (DEBUG)
int milliEnd = Environment.TickCount;
Trace.WriteLine(DateTime.Now.ToString(BaseSystemInfo.TimeFormat) + " Ticks: " + TimeSpan.FromMilliseconds(milliEnd - milliStart).ToString() + " :End: " + MethodBase.GetCurrentMethod().ReflectedType.Name + "." + MethodBase.GetCurrentMethod().Name);
#endif
}
