Пример #1
0
        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;
        }
Пример #2
0
 private void DbDo(DbOperation opt)
 {
     using (var sqlConnection = new SqlConnection(GetConnectionString()))
     {
         sqlConnection.Open();
         opt(sqlConnection);
     }
 }
Пример #3
0
        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);
        }
Пример #4
0
 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}");
 }
Пример #5
0
 public frmInstallDB(IPLInstall install, DbOperation type)
 {
     _FWInstall = install;
     InitializeComponent();
     Check();
     if (type == DbOperation.Install)
         InstallDb();
     else
         UninstallDb();
 }
Пример #6
0
        /// <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);
        }
Пример #7
0
        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);
        }
Пример #8
0
        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)
            };
        }
Пример #9
0
        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)));
            }
        }
Пример #10
0
        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));
            }
        }
Пример #11
0
        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);
        }
Пример #12
0
        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));
            }
        }
Пример #13
0
        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());
                }
            }
        }
Пример #14
0
 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);
     }
 }
Пример #15
0
        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));
            }
        }
Пример #16
0
        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));
            }
        }
Пример #17
0
        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);
            }
        }
Пример #18
0
        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;
        }
Пример #19
0
        // 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));
        }
Пример #20
0
        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))));
            }
        }
Пример #21
0
        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);
            }
        }
Пример #22
0
        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;
        }
Пример #23
0
        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;
        }
Пример #24
0
        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));
            }
        }
Пример #25
0
        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);
        }
Пример #26
0
        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);
            }
        }
Пример #27
0
        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);
            }
        }
Пример #28
0
        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))));
            }
        }
Пример #29
0
        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));
            }
        }
Пример #30
0
        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);
            }
        }
Пример #31
0
 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;
 }
Пример #32
0
        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)));
            }
        }
Пример #33
0
        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)));
            }
        }
Пример #34
0
        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);
        }
Пример #35
0
        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);
            }
        }
Пример #36
0
        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();
        }
Пример #37
0
        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));
            }
        }
Пример #38
0
        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;

        }
Пример #39
0
        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");
        }
Пример #40
0
        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);
        }
Пример #41
0
 private void ProcessRecord(IDataRecord record, DbOperation dbOperation)
Пример #42
0
        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);
        }
Пример #43
0
        /// <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
        }
Пример #44
0
        /// <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
        }