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);
}
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);
}
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();
}
}
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);
}
}
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);
}
}
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);
}
}
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);
}
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();
}
}
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);
}
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);
}
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);
}
}
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();
}
}
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
});
}
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();
}
}
