//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);
}
//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 void Remove(DbLink dbLink, string ident)
{
string query =
string.Format("delete from data_source t where t.ident = {0}", ident);
dbLink.GetConnection().Execute(query);
}
//remove
public static void Remove(DbLink dbLink, int ft_id)
{
string query =
string.Format("delete from fin_type t where t.ft_id = {0}", ft_id);
dbLink.GetConnection().Execute(query);
}
public void TestConnection()
{
var connection = ConfigurationManager.AppSettings.Get("postgre");
var postgre = Fabricate.CreateConnection(connection, ConnectionType.Npgsql);
DbLink dbLink = new DbLink(postgre);
var t = dbLink.GetConnection();
}
public static void Insert(DbLink dbLink, cashflow cashflow)
{
string query = @"insert into cashflow(cf_id, val, dat, valid_dat)
values(@cf_id, @val, @dat, @valid_dat)";
dbLink.GetConnection().Execute(query, cashflow);
}
public static void Remove(DbLink dbLink, int ds_id)
{
string query =
string.Format("delete from data_source t where t.ds_id = {0}", ds_id);
dbLink.GetConnection().Execute(query);
}
//insert
public static void Insert(DbLink dbLink, fin_field fin_filed)
{
string query =
@"insert into fin_field(fif_id, ident, title, description)
values(nextval('mir_sequence'), @ident, @title, @description);";
dbLink.GetConnection().Execute(query, fin_filed);
}
//insert
public static void Insert(DbLink dbLink, fisd_dq fisd_dq)
{
string query =
@"insert into fisd_dq(val, dat, fisd_id)
values(@val, @dat, @fisd_id)";
dbLink.GetConnection().Execute(query, fisd_dq);
}
//remove
public static void Remove(DbLink dbLink, int cur_id, int fi_id, DateTime dat)
{
string query =
string.Format(@"delete from curve_list t where t.cur_id = {0} and fi_id = {1} and dat = {2};",
cur_id, fi_id, dat.ToString("dd.MM.yyyy"));
dbLink.GetConnection().Execute(query);
}
//insert
public static void Insert(DbLink dbLink, curve_list curveList)
{
string query =
@"insert into curve_list(cur_id, fi_id, ident, ord_id, term, dat)
values(@cur_id, @fi_id, @ident, @ord_id, @term, @dat)";
dbLink.GetConnection().Execute(query, curveList);
}
//remove
public static void Remove(DbLink dbLink, int cur_id)
{
string query =
string.Format(@"delete from curves t where t.cur_id ={0}",
cur_id);
dbLink.GetConnection().Execute(query);
}
//insert
public static void Insert(DbLink dbLink, fin_type fin_type)
{
string query =
string.Format(@"insert into fin_type(ft_id, ident, title)
values(nextval('mir_sequence'), @ident, @title);");
dbLink.GetConnection().Execute(query, fin_type);
}
//remove
public static void Remove(DbLink dbLink, int fisd_id, DateTime dat)
{
string query =
string.Format(@"delete 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"));
dbLink.GetConnection().Execute(query);
}
//insert
public static void Insert(DbLink dbLink, fcs ffd)
{
string query =
@"insert into fcs(cf_id, fi_id, ds_id, ct_id)
values(nextval('mir_sequence'), @fi_id, @ds_id, @ct_id);";
dbLink.GetConnection().Execute(query, ffd);
}
//insert
public static void Insert(DbLink dbLink, fin_instrument finInstrument)
{
string query =
@"insert into fin_instrument(fi_id,ident,ft_id,title)
values(nextval('mir_sequence'), @ident, @ft_id, @title);";
dbLink.GetConnection().Execute(query, finInstrument);
}
//remove
public static void Remove(DbLink dbLink, int fin_id)
{
string query =
string.Format(@"delete from fin_instrument t where t.fi_id = {0}",
fin_id);
dbLink.GetConnection().Execute(query);
}
//insert
public static void Insert(DbLink dbLink, curve curve)
{
string query =
@"insert into curves(cur_id, ident, description, title)
values(nextval('mir_sequence'), @ident, @description, @title);";
dbLink.GetConnection().Execute(query, curve);
}
//remove
public static void Remove(DbLink dbLink, int fif_id, string key_v)
{
string query =
string.Format(@"delete from dict_item t where t.fif_id = {0} and t.key_v = upper('{1}')",
fif_id, key_v);
dbLink.GetConnection().Execute(query);
}
//find
public static curve FindId(DbLink dbLink, string ident)
{
string query =
string.Format(@"select * from curves t
where t.ident = upper('{0}');", ident);
var result = dbLink.GetConnection().QueryFirstOrDefault <curve>(query);
return(result);
}
//find
public static List <curve_list> FindId(DbLink dbLink, int cur_id)
{
string query =
string.Format(@"select * from curve_list t
where t.cur_id = {0}", cur_id);
var result = dbLink.GetConnection().Query <curve_list>(query);
return(result.ToList());
}
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);
}
//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);
}
//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);
}
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 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 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);
}
}
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 static cashflow FindId(DbLink dbLink, int cf_id, DateTime dateTime, DateTime validDatetime)
{
cashflow cf = new cashflow();
cf.cf_id = cf_id;
cf.dat = dateTime;
cf.valid_dat = validDatetime;
string query =
string.Format(@"select * from cashflow t
where t.cf_id = @cf_id and t.dat = @dat and t.valid_dat <= @valid_dat");
var result = dbLink.GetConnection().QueryFirstOrDefault <cashflow>(query, cf);
return(result);
}