public void UsingWithDapperAndFabricate()
        {
            string connection   = "Host = localhost; Username =postgres; Password =roman; Database =MIR";
            var    dbConnection = Fabricate.CreateConnection(connection, ConnectionType.Npgsql);
            DbLink dbLink       = new DbLink(dbConnection);
            var    t            = dbLink.GetConnection().Query <DataSource>("select * from data_source").First();

            Console.WriteLine(t.ident);
        }
        //find
        public static fin_field FindId(DbLink dbLink, string ident)
        {
            string query =
                string.Format(@"select * from fin_field t
                    where t.ident = upper('{0}');", ident);
            var result = dbLink.GetConnection().QueryFirstOrDefault <fin_field>(query);

            return(result);
        }
        public Provider(string connection)
        {
            _connection = connection;
            var tempConnection = DataBaseLink.Fabricate.CreateConnection(_connection, ConnectionType.Npgsql);

            _dbLink = new DbLink(tempConnection);

            _mapping = new Mapping();
        }
        public static cashflow_types FindId(DbLink dbLink, string ident)
        {
            string query =
                string.Format(@"select * from cashflow_types t
                    where t.ident = upper('{0}');", ident);
            var result = dbLink.GetConnection().QueryFirstOrDefault <cashflow_types>(query);

            return(result);
        }
        //find
        public static data_source FindId(DbLink dbLink, string ident)
        {
            string query =
                string.Format(@"select * from data_source t
                    where t.ident = upper('{0}')", ident);
            var result = dbLink.GetConnection().QueryFirstOrDefault <data_source>(query);

            return(result);
        }
        //remove
        public static void Remove(DbLink dbLink, ffd ffd)
        {
            string query = string.Format("delete from ffd t where t.fi_id = {0} and t.ds_id = {1} and t.fif_id = {2}",
                                         ffd.fi_id,
                                         ffd.ds_id,
                                         ffd.fif_id);

            dbLink.GetConnection().Execute(query);
        }
        public static fisd_dq FindId(DbLink dbLink, int fisd_id, DateTime dat)
        {
            string query =
                string.Format(@"select * from fisd_dq t
                    where t.fisd_id = {0} and t.dat = to_date('{1}', 'dd.mm.yyyy')",
                              fisd_id, dat.ToString("dd.MM.yyyy"));
            var result = dbLink.GetConnection().QueryFirstOrDefault <fisd_dq>(query);

            return(result);
        }
        public static void Update(DbLink dbLink, fisd_dq fisd_dq)
        {
            string query =
                string.Format(
                    @"update fisd_dq fd 
                    set val = @val 
                    where fd.dat = @dat and fd.fisd_id = @fisd_id");

            dbLink.GetConnection().Execute(query, fisd_dq);
        }
Example #9
0
        public List <RAOModel> FillRAOModel()
        {
            DbLink          link        = new DbLink();
            List <RAOModel> RAO         = new List <RAOModel>();
            string          janThisYear = DateTime.Today.Year + "-01-01";

            RAO = link.FillRAOModel(janThisYear, DateTime.Today.ToString("yyyy-MM-dd"));

            return(RAO);
        }
        //find
        public static fcs Find(DbLink dbLink, int fi_id, int ds_id, int ct_id)
        {
            string query =
                string.Format(@"select * from fcs t
                    where t.fi_id = {0} and t.ds_id = {1} and ct_id = {2}",
                              fi_id, ds_id, ct_id);
            var result = dbLink.GetConnection().QueryFirstOrDefault <fcs>(query);

            return(result);
        }
        //find
        public static dict_item FindId(DbLink dbLink, string ident, int fif_id)
        {
            string query =
                string.Format(@"select * from dict_item t
                    where (t.key_v = upper('{0}') or t.val = upper('{0}')) and t.fif_id = {1}",
                              ident, fif_id);
            var result = dbLink.GetConnection().QueryFirstOrDefault <dict_item>(query);

            return(result);
        }
        //insert
        public static void Insert(DbLink dbLink, data_source dataSource)
        {
            string queryAmount = "select count(*) from data_source";
            int    ord_id      = dbLink.GetConnection().QueryFirstOrDefault <int>(queryAmount);

            dataSource.ord_id = ord_id + 1;
            string query =
                string.Format(@"insert into data_source(ds_id, ident, description, ord_id)
                    values(nextval('mir_sequence'), @ident, @description, @ord_id)");

            dbLink.GetConnection().Execute(query, dataSource);
        }
Example #13
0
        public void EditAccount(AccountGridModel acct)
        {
            DbLink link = new DbLink();

            using (SqlConnection conn = link.InitializeSqlConnection())
            {
                // Insert to the AB table.
                SqlCommand comm = new SqlCommand("UPDATE dbo.tbl_AB SET AB_Amount = " + acct.AB + " WHERE Acct_Code = '" + acct.AcctCode + "'", conn);
                conn.Open();
                comm.ExecuteNonQuery();
            }
        }
Example #14
0
        private void QueryData()
        {
            IDbLink            idal  = new DbLink();
            IList <DbLinkInfo> ilist = idal.DbLinkGetList();

            foreach (DbLinkInfo info in ilist)
            {
                string dbName = string.IsNullOrEmpty(info.DbAbbreviation) ? info.DbName : info.DbName + "(" + info.DbAbbreviation + ")";
                DBToolStripComboBox.Items.Add(new ListItem(info.ID.ToString(), dbName));
            }
            DBToolStripComboBox.SelectedIndex = 0;
        }
        public void TestInsertFinType()
        {
            var tempConnection = DataBaseLink.Fabricate.CreateConnection(_connection, DataBaseLink.ConnectionType.Npgsql);

            DataBaseLink.DbLink dbLink   = new DbLink(tempConnection);
            fin_type            fin_type = new fin_type("FX_RATE", "Валютный курс");

            if (FinType.FindId(dbLink, fin_type.ident) == null)
            {
                FinType.Insert(dbLink, fin_type);
            }
        }
Example #16
0
        public void TestInsertFinInstrument()
        {
            var tempConnection = DataBaseLink.Fabricate.CreateConnection(_connection, ConnectionType.Npgsql);

            DataBaseLink.DbLink dbLink         = new DbLink(tempConnection);
            fin_instrument      fin_instrument = new fin_instrument("roman", 13, "bushuev");

            if (FinInstrument.FindId(dbLink, fin_instrument.ident) == null)
            {
                FinInstrument.Insert(dbLink, fin_instrument);
            }
        }
Example #17
0
        public void TestInsertQuote()
        {
            //вставим несколько значений за разные даты
            var tempConnection = DataBaseLink.Fabricate.CreateConnection(_connection, ConnectionType.Npgsql);

            DataBaseLink.DbLink dbLink = new DbLink(tempConnection);
            //создать финансовый инструмент
            fin_instrument fin_instrument = new fin_instrument("roman", 13, "bushuev");

            if (FinInstrument.FindId(dbLink, fin_instrument.ident) == null)
            {
                FinInstrument.Insert(dbLink, fin_instrument);
            }

            var result = FinInstrument.FindId(dbLink, fin_instrument.ident);

            ffd ffd = new ffd()
            {
                ds_id  = 17,
                fi_id  = result.fi_id,
                fif_id = 3
            };

            if (FFD.Find(dbLink, result.fi_id, ffd.ds_id, ffd.fif_id) == null)
            {
                FFD.Insert(dbLink, ffd);
            }
            var ffdResult = FFD.Find(dbLink, result.fi_id, ffd.ds_id, ffd.fif_id);

            fisd_dq fisd_dq = new fisd_dq()
            {
                dat     = DateTime.Today.AddDays(-1),
                fisd_id = ffdResult.fisd_id,
                val     = 10.0m
            };

            if (FisdDq.FindId(dbLink, fisd_dq.fisd_id, fisd_dq.dat) == null)
            {
                FisdDq.Insert(dbLink, fisd_dq);
            }

            fisd_dq = new fisd_dq()
            {
                dat     = DateTime.Today,
                fisd_id = ffdResult.fisd_id,
                val     = 11.0m
            };

            if (FisdDq.FindId(dbLink, fisd_dq.fisd_id, fisd_dq.dat) == null)
            {
                FisdDq.Insert(dbLink, fisd_dq);
            }
        }
Example #18
0
 public static bool Insert(DbLink dbLink, string title)
 {
     try
     {
         string query = @"insert into colors (title) values(@title)";
         dbLink.GetConnection().Execute(query, new { title = title });
         return(true);
     }
     catch (Exception ex)
     {
         return(false);
     }
 }
        private DataBaseInfo GetDbInfo()
        {
            IDbLink    dal    = new DbLink();
            DbLinkInfo dlinfo = dal.DbLinkGetInfo(dinfo.DbLinkID);

            IDataBase     dbDal         = new CurrencyDal.CodeMaker.DataBase();
            string        rstmsg        = "";
            List <string> tableNameList = new List <string>();

            tableNameList.Add(dinfo.Name);
            DataBaseInfo dbinfo = dbDal.DataBaseGetInfo(dlinfo, tableNameList, out rstmsg);

            return(dbinfo);
        }
        private DataBaseInfo GetDbInfo(int ID)
        {
            IDbLink    dal    = new DbLink();
            DbLinkInfo dlinfo = dal.DbLinkGetInfo(ID);

            IDataBase      dbDal         = new  CurrencyDal.CodeMaker.DataBase();
            string         rstmsg        = "";
            List <string>  tableNameList = new List <string>();
            string         tableName     = "";
            DbDataTypeEnum dtype         = DbDataType.GetDbDataType(dinfo.NameType);

            if (dtype == DbDataTypeEnum.表)
            {
                tableName = dinfo.Name;
                tableNameList.Add(tableName);
            }
            DataBaseInfo dbinfo = dbDal.DataBaseGetInfo(dlinfo, tableNameList, out rstmsg);

            //DataBaseInfo2 info = new DataBaseInfo2();

            //try
            //{
            //switch (dlinfo.DbType)
            //{

            //    case 1:
            //        System.Data.Common.DbConnection connSql = new System.Data.SqlClient.SqlConnection();
            //        connSql.ConnectionString = "Data Source=" + dlinfo.DataSource + ";Initial Catalog=" + dlinfo.DbName + ";User ID=" + dlinfo.UserName + ";Password="******"Data Source=" + dlinfo.DbName + ";User ID=" + dlinfo.UserName + ";Password="******"";
            //        info.GetOracleDb(connOracle);
            //        break;
            //    case 3:
            //        string ConnString = dlinfo.DataSource;
            //        info.LoadFromAccess2000(ConnString);
            //        break;

            //}
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show("操作失败。" + ex.Message);
            //}

            return(dbinfo);
        }
Example #21
0
        public void InsertPayee(string payeeNumber, string payeeName, string payeePos, string payeeOfficeCode)
        {
            DbLink Link = new DbLink();

            using (SqlConnection conn = Link.InitializeSqlConnection())
            {
                conn.Open();

                SqlCommand comm = new SqlCommand("INSERT INTO dbo.tbl_Payee (Employee_Number, Employee_Name, Employee_Pos, Office_Code) " +
                                                 "VALUES ('" + payeeNumber + "', '" + payeeName + "', '" + payeePos + "', '" + payeeOfficeCode + "')", conn);

                comm.ExecuteNonQuery();
            }
        }
Example #22
0
        private void ExportInsert()
        {
            string sqlmsg = "";
            string rstmsg = "";
            string sql    = textEditorControl.ActiveTextAreaControl.TextArea.SelectionManager.SelectedText.Trim();

            if (sql == "")
            {
                sql = textEditorControl.Text.Trim();
            }
            if (sql == "")
            {
                return;
            }
            sql = GetSqlZC(sql);
            sql = sql.Replace("\r\n", " ");

            ListItem item = DBToolStripComboBox.SelectedItem as ListItem;

            int DbLinkID = int.Parse(item.ID.ToString());

            if (DbLinkID == 0)
            {
                MessageBox.Show("请选择数据库");
            }
            IDbLink    ldal   = new DbLink();
            DbLinkInfo dlinfo = ldal.DbLinkGetInfo(DbLinkID);
            DataSet    ds     = dal.Run(dlinfo, sql, out sqlmsg, out rstmsg);

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows.Count > 0)
            {
                DataBaseTypeEnum dbtype       = DataBaseTypeEnum.SQLServer;
                string           TargetDbType = toolStripComboBox_TargetDbType.Text;

                if (TargetDbType == "SQLServer")
                {
                    dbtype = DataBaseTypeEnum.SQLServer;
                }
                else if (TargetDbType == "Oracle")
                {
                    dbtype = DataBaseTypeEnum.Oracle;
                }
                else if (TargetDbType == "MySql")
                {
                    dbtype = DataBaseTypeEnum.MySql;
                }
                string strSql = GetInserSql(ds.Tables[0], dbtype, out rstmsg);
                Clipboard.SetDataObject(strSql);
            }
        }
        public void TestRemoveFisdDate()
        {
            var tempConnection = DataBaseLink.Fabricate.CreateConnection(_connection, ConnectionType.Npgsql);

            DataBaseLink.DbLink dbLink = new DbLink(tempConnection);
            //создать финансовый инструмент
            fin_instrument fin_instrument = new fin_instrument("roman", 13, "bushuev");

            if (FinInstrument.FindId(dbLink, fin_instrument.ident) == null)
            {
                FinInstrument.Insert(dbLink, fin_instrument);
            }

            var result = FinInstrument.FindId(dbLink, fin_instrument.ident);

            //fisd_id
            ffd ffd = new ffd()
            {
                ds_id  = 17,
                fi_id  = result.fi_id,
                fif_id = 11,
            };

            if (FFD.Find(dbLink, result.fi_id, ffd.ds_id, ffd.fif_id) == null)
            {
                FFD.Insert(dbLink, ffd);
            }
            var ffdResult = FFD.Find(dbLink, result.fi_id, ffd.ds_id, ffd.fif_id);

            //создать fisd_date
            fisd_date fisdDate = new fisd_date()
            {
                fisd_id  = ffdResult.fisd_id,
                dat_from = new DateTime(2018, 03, 25),
                val      = new DateTime(2018, 03, 25)
            };

            if (FisdDate.FindId(dbLink, fisdDate.fisd_id, fisdDate.dat_from) == null)
            {
                FisdDate.Insert(dbLink, fisdDate);
                var one = FisdDate.FindId(dbLink, fisdDate.fisd_id, fisdDate.dat_from);
                FisdDate.Remove(dbLink, fisdDate.fisd_id, one.dat_from);
            }
            else
            {
                var one = FisdDate.FindId(dbLink, fisdDate.fisd_id, fisdDate.dat_from);
                FisdDate.Remove(dbLink, fisdDate.fisd_id, one.dat_from);
            }
        }
        public void TestRemoveFisdNum()
        {
            var tempConnection = DataBaseLink.Fabricate.CreateConnection(_connection, ConnectionType.Npgsql);

            DataBaseLink.DbLink dbLink         = new DbLink(tempConnection);
            fin_instrument      fin_instrument = new fin_instrument("roman", 13, "bushuev");

            if (FinInstrument.FindId(dbLink, fin_instrument.ident) == null)
            {
                FinInstrument.Insert(dbLink, fin_instrument);
            }

            var result = FinInstrument.FindId(dbLink, fin_instrument.ident);

            //fisd_id
            ffd ffd = new ffd()
            {
                ds_id  = 17,
                fi_id  = result.fi_id,
                fif_id = 11,
            };

            if (FFD.Find(dbLink, result.fi_id, ffd.ds_id, ffd.fif_id) == null)
            {
                FFD.Insert(dbLink, ffd);
            }
            var ffdResult = FFD.Find(dbLink, result.fi_id, ffd.ds_id, ffd.fif_id);

            fisd_num fisd_num = new fisd_num()
            {
                fisd_id  = ffdResult.fisd_id,
                dat_from = new DateTime(2018, 03, 24),
                val      = 320.0m
            };


            if (FisdNum.FindId(dbLink, ffdResult.fisd_id, fisd_num.dat_from) == null)
            {
                FisdNum.Insert(dbLink, fisd_num);
                var one = FisdNum.FindId(dbLink, fisd_num.fisd_id, fisd_num.dat_from);
                FisdNum.Remove(dbLink, fisd_num.fisd_id, fisd_num.dat_from);
            }
            else
            {
                FisdNum.Insert(dbLink, fisd_num);
                var one = FisdNum.FindId(dbLink, fisd_num.fisd_id, fisd_num.dat_from);
                FisdNum.Remove(dbLink, fisd_num.fisd_id, one.dat_from);
            }
        }
        //insert
        public static void Insert(DbLink dbLink, dict_item dict_item)
        {
            string value = string.Format("select count(*) from dict_item t where t.fif_id = {0}",
                                         dict_item.fif_id);

            int ord_id = dbLink.GetConnection().ExecuteScalar <int>(value);

            dict_item.ord_id = ord_id + 1;

            string query =
                @"insert into dict_item(fif_id, key_v, val, ord_id)
                    values(@fif_id, @key_v, @val, @ord_id)";

            dbLink.GetConnection().Execute(query, dict_item);
        }
Example #26
0
        public static bool Exist(DbLink dbLink, string title)
        {
            if (string.IsNullOrEmpty(title))
            {
                return(false);
            }

            string query = @"select * from colors t
                where t.title = @title";

            var result = dbLink.GetConnection().QueryFirstOrDefault(query,
                                                                    new { title = title });

            return(result != null);
        }
Example #27
0
        public void TestInsertCurve()
        {
            var tempConnection = DataBaseLink.Fabricate.CreateConnection(_connection, DataBaseLink.ConnectionType.Npgsql);

            DataBaseLink.DbLink dbLink = new DbLink(tempConnection);
            curve curve = new curve()
            {
                description = "debt_zcc_rub", ident = "debt_zcc_rub", title = "debt_zcc_rub"
            };

            if (Curves.FindId(dbLink, curve.ident) == null)
            {
                Curves.Insert(dbLink, curve);
            }
        }
Example #28
0
        public void DeleteLink(Guid linkId)
        {
            using (var context = new RelayContext())
            {
                var itemToDelete = new DbLink
                {
                    Id = linkId
                };

                context.Links.Attach(itemToDelete);
                context.Links.Remove(itemToDelete);

                context.SaveChanges();
            }
        }
Example #29
0
 private Link GetLinkFromDbLink(DbLink link)
 {
     return(new Link
     {
         Id = link.Id,
         CreationDate = link.CreationDate,
         ForwardOnPremiseTargetErrorResponse = link.ForwardOnPremiseTargetErrorResponse,
         IsDisabled = link.IsDisabled,
         MaximumLinks = link.MaximumLinks,
         Password = link.Password,
         SymbolicName = link.SymbolicName,
         UserName = link.UserName,
         AllowLocalClientRequestsOnly = link.AllowLocalClientRequestsOnly
     });
 }
Example #30
0
        public void UpdateStaff(string staffNumber, string staffType, string staffPic)
        {
            DbLink Link = new DbLink();

            using (SqlConnection conn = Link.InitializeSqlConnection())
            {
                conn.Open();

                SqlCommand comm = new SqlCommand("UPDATE dbo.tbl_BO_Staff " +
                                                 "SET Discriminator = '" + staffType + "', PicURL = '" + staffPic + "' " +
                                                 "WHERE BStaff_Number = '" + staffNumber + "'", conn);

                comm.ExecuteNonQuery();
            }
        }