public Recipe GetById(Guid id)
{
using ( var conn = new NpgsqlConnection( ConnectionString ) ) {
conn.Open( );
return conn.Query<Recipe>("SELECT * FROM Recipes WHERE Id=@id", new { id }).SingleOrDefault();
}
}
public IEnumerable<Recipe> List()
{
using ( var conn = new NpgsqlConnection( ConnectionString ) ) {
conn.Open( );
return conn.Query<Recipe>("SELECT * FROM Recipes");
}
}
private static void RecreateSchemaAndInstallObjects()
{
using (var connection = new NpgsqlConnection(
ConnectionUtils.GetMasterConnectionString()))
{
bool databaseExists = connection.Query<bool?>(
@"select true :: boolean from pg_database where datname = @databaseName;",
new
{
databaseName = ConnectionUtils.GetDatabaseName()
}
).SingleOrDefault() ?? false;
if (!databaseExists)
{
connection.Execute($@"CREATE DATABASE ""{ConnectionUtils.GetDatabaseName()}""");
}
}
using (var connection = new NpgsqlConnection(ConnectionUtils.GetConnectionString()))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
PostgreSqlObjectsInstaller.Install(connection);
PostgreSqlTestObjectsInitializer.CleanTables(connection);
}
}
public void NpgsqlTableChanged(ListBox listBox, UiShow uiShow)
{
IEnumerable<object> enumerable;
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
string sql =
$"SELECT column_name, is_nullable, udt_name FROM INFORMATION_SCHEMA.COLUMNS\r\nWHERE table_catalog='{connection.Database}' AND table_schema='public' AND table_name='{listBox.SelectedItem}';";
enumerable = connection.Query(sql, null, null, true, null, null);
}
StringBuilder builder = new StringBuilder();
builder.AppendFormat("public class {0}{1}{{{1}", listBox.SelectedItem, Environment.NewLine);
foreach (dynamic obj2 in enumerable)
{
string columnName = (string)obj2.column_name;
bool isNullable = (bool)(obj2.is_nullable.ToUpper() == "YES");
string dataType = (string)obj2.udt_name;
builder.AppendLine(GenerateField(columnName, isNullable, dataType));
}
string @class = builder.Append("}").ToString();
uiShow.txtClass.Text = @class;
File.AppendAllText("models.cs", @class);
}
private void Run()
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var orders = conn.Query<Order>("select * from Orders where IsMatchSearched <> true or IsMatchSearched is null").ToList();
Console.WriteLine("finding matches for " + orders.Count + " orders");
foreach(var order in orders)
{
var minAmount = 0.9 * order.Amount;
var maxAmount = 1.1 * order.Amount;
var minPrice = 0.9 * order.Price;
var maxPrice = 1.1 * order.Price;
var type = order.OrderType == "Purchase" ? "Sell" : "Purchase";
var matchedOrders = conn.Query<Order>("select * from Orders where IsMatchSearched = true and Price < @MaxPrice and Price > @MinPrice and Amount < @MaxAmount and Amount > @MinAmount and OrderType = @Type and CommodityType = @CommodityType and fkUserId <> @UserId",
new {
MinAmount = minAmount,
MaxAmount = maxAmount,
MinPrice = minPrice,
MaxPrice = maxPrice,
CommodityType = order.CommodityType,
Type = type,
UserId = order.fkUserId
}).ToList();
Console.WriteLine("order " + order.Id + " has "+matchedOrders.Count+" matches");
foreach(var matchedOrder in matchedOrders)
{
String message = "Kami menemukan order yang cocok. {0} {1} {2} sebesar {3} kg dengan harga Rp. {4}";
conn.Execute("insert into orders_matches(first_id, second_id) values (@FirstId, @SecondId)", new { FirstId = order.Id, SecondId = matchedOrder.Id });
conn.Execute("insert into orders_matches(first_id, second_id) values (@FirstId, @SecondId)", new { FirstId = matchedOrder.Id, SecondId = order.Id });
var verb = order.OrderType == "Purchase" ? "menjual" : "membeli";
var commodity = conn.Query<String>("select Name from CommodityType where Id = @Id", new { Id = order.CommodityType }).FirstOrDefault();
var user = conn.Query<User>("select Name, PhoneNumber from Users where Id = @Id", new { Id = matchedOrder.fkUserId }).FirstOrDefault();
message = String.Format(message, user.Name, verb, commodity, matchedOrder.Amount, matchedOrder.Price);
conn.Execute("insert into Outboxes(PhoneNumber, Message) values (@PhoneNumber, @Message)",
new
{
PhoneNumber = user.PhoneNumber,
Message = message
}
);
}
conn.Execute("update Orders set IsMatchSearched = true where Id = @Id", new { Id = order.Id });
}
};
}
private List<Customer> GetCustomers()
{
// Получаем данные из БД с помощью ORM Dapper
NpgsqlConnection cn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=Customers;User Id=postgres;Password=123;MaxPoolSize=100;");
cn.Open();
// Данные получаются левым соединением сразу из двух таблиц, используется маппинг сразу в два типа: Customer и BusinessType
var res = cn.Query<Customer, BusinessType, Customer>("select * from \"Customer\" as \"Customer\" left join \"BusinessType\" as \"BusinessType\" on \"Customer\".\"BusinessTypeBusinessTypeId\" = \"BusinessType\".\"BusinessTypeId\" order by \"Customer\".\"CustomerName\"", (customer, businessType)=> { customer.BusinessType = businessType; return customer; }, splitOn: "BusinessTypeId");
return (List<Customer>)res;
}
public static IEnumerable <FindBooksTakenOut> FindBooksTakenOut(string connectionString)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
var BooksTakenOutFound = connection.Query <FindBooksTakenOut>("SELECT * FROM \"Books Taken Out\"").AsList();
connection.Close();
return(BooksTakenOutFound);
}
public static IEnumerable <FindUsers> FindAllUsers(string connectionString)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
var AllUsersFound = connection.Query <FindUsers>("SELECT * FROM \"Users\"").AsList();
connection.Close();
return(AllUsersFound);
}
public static IEnumerable <Books> FindAllBooks(string connectionString)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
var FoundBooks = connection.Query <Books>("SELECT * FROM \"Books\"").AsList();
connection.Close();
return(FoundBooks);
}
/// <summary>
/// Gets all available table names from the database.
/// </summary>
/// <returns>Enumerable containing all tables in the database in schema.table format.</returns>
public IEnumerable<string> GetAvailableTables()
{
if (!TestConnection())
throw new InvalidOperationException("Failed to connect to the database.");
using (var conn = new NpgsqlConnection(ConnectionString))
{
string query = "select table_schema || '.' || table_name from information_schema.tables where table_schema = 'public' order by table_schema, table_name";
conn.Open();
return conn.Query<string>(query);
}
}
public static void ReadPostgis(string connectionString, string sql)
{
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
var res = conn.Query(sql);
foreach (var p in res)
{
// todo: create something here
}
}
}
public static bool AreThereAvailableCopies(int Bookid, string connectionString)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
var BookID = connection.Query <Books>($"SELECT \"AvailableCopies\" FROM \"Books\" WHERE \"BookID\" = \'{Bookid}\'").AsList();
connection.Close();
foreach (var element in BookID)
{
if (element.AvailableCopies > 0)
{
return(true);
}
}
return(false);
}
static void Main(string[] args)
{
ClassType ct = new ClassType();
File.WriteAllText("classtype.json", JsonConvert.SerializeObject(ct.ClassOrders));
IEnumerable<IdenRec> idenRecs;
string connStr = @"PORT=5432;KRBSRVNAME=POSTGRES;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;HOST=127.0.0.1;USER ID=postgres;PASSWORD=123456;DATABASE=irisNewAttend;";
using (NpgsqlConnection conn = new NpgsqlConnection(connStr))
{
conn.Open();
idenRecs = conn.Query<IdenRec>(@"SELECT person_id AS PersonId, recog_time AS RecTime
FROM person_recog_base WHERE recog_time>=@time ORDER BY person_id, recog_time;", new { time = ct.Origin });
File.WriteAllText("idenRecs.json", JsonConvert.SerializeObject(idenRecs));
}
NormalAttend na = new NormalAttend();
List<AttendRec> ars = new List<AttendRec>();
foreach (var recogs in idenRecs.GroupBy(r => new { r.PersonId, }).Select(r => r))
{
var attends = na.AttendCalc(recogs.Select(r => r).ToArray(), ct);
if (attends.Length > 0)
{
ars.AddRange(attends);
}
}
File.WriteAllText("attendrecs.json", JsonConvert.SerializeObject(ars));
File.WriteAllText("idenRecsNew.json", JsonConvert.SerializeObject(idenRecs));
Console.WriteLine(DateTime.Now.TimeOfDay);
//不区分设备计算考勤 => 根据班次信息和识别时间推算设备类型
//考勤：1 实时服务， 2 归档服务， 3 数值计算
//查询数据库，获得指定日期的识别记录，原数据设置为已处理
// 班次信息
//对数据进行去重（5min内重复识别不算）
Console.ReadKey();
}
public DashboardModule()
{
const string sunburstQuery = "select string_agg ( commoditytype.name || '-' || ordertype || ',' || amount , '\\n' ) from orders inner join commoditytype on orders.commoditytype = commoditytype.id";
Get["/dashboard"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
var data = datas[0];
return View["dashboard",data];
}
};
Get["/sunburst"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
var data = datas[0];
return View["sunburst", data];
};
};
Get["/verify"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<Registration>("select * from Registrations where IsVerified <> true or IsVerified is null").ToList();
return View["verifylist", datas];
};
};
Get["/verify/{id}"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var data = conn.Query<Registration>("select * from Registrations where (IsVerified <> true or IsVerified is null) and Id = @Id", new { Id = parameters.id }).FirstOrDefault();
return View["verify", data];
};
};
Post["/verify"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
dynamic form = Request.Form;
conn.Execute("insert into Users(Name, KtpNumber, PhoneNumber, Address, Longitude, Latitude) values (@Name, @KtpNumber, @PhoneNumber, @Address, @Longitude, @Latitude)",
new
{
Name = form.Name,
KtpNumber = form.KtpNumber,
PhoneNumber = form.PhoneNumber,
Address = form.Address,
Longitude = form.Longitude,
Latitude = form.Latitude,
}
);
conn.Execute("update Registrations set IsVerified = true where Id = @Id", new { Id = form.id });
return Response.AsRedirect("/verify");
};
};
Get["/orders/{start}"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var data = conn.Query<OrderUser>("select * from Orders_Users where Id > @Start", new { Start = parameters.start }).ToList();
return JsonConvert.SerializeObject(data);
};
};
Get["/sunburstdata"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
return datas[0];
};
};
}
private static bool IsEntryExpired(NpgsqlConnection connection, int entryId)
{
var count = connection.Query<long>(
@"select count(*) from """ + GetSchemaName() + @""".""counter"" where ""id"" = @id", new {id = entryId}).Single();
return count == 0;
}
private static int CreateExpirationEntry(NpgsqlConnection connection, DateTime? expireAt)
{
string insertSqlNull = @"
insert into """ + GetSchemaName() + @""".""counter""(""key"", ""value"", ""expireat"")
values ('key', 1, null) returning ""id""";
string insertSqlValue = @"
insert into """ + GetSchemaName() + @""".""counter""(""key"", ""value"", ""expireat"")
values ('key', 1, now() at time zone 'utc' - interval '{0} seconds') returning ""id""";
string insertSql = expireAt == null
? insertSqlNull
: string.Format(insertSqlValue,
((long) (DateTime.UtcNow - expireAt.Value).TotalSeconds).ToString(CultureInfo.InvariantCulture));
var id = connection.Query(insertSql).Single();
var recordId = (int) id.id;
return recordId;
}
public static IEnumerable<mal_anime_prerequisite> GetAll(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string sql = "SELECT * FROM mal_anime_prerequisite";
return conn.Query<mal_anime_prerequisite>(sql, transaction: transaction);
}
public static IEnumerable<mal_user> GetAll(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string sql = @"
SELECT mal_user_id, mal_name, time_added
FROM mal_user
";
// This will buffer all rows in memory before returning
return conn.Query<mal_user>(sql, transaction: transaction);
}
public static long Count(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
long count = conn.Query<long>("SELECT Count(*) FROM mal_user", transaction: transaction).First();
return count;
}
public static bool UserIsInDbCaseSensitive(string username, NpgsqlConnection conn, NpgsqlTransaction transaction)
{
long count = conn.Query<long>(@"SELECT Count(*) FROM mal_user WHERE mal_name = :Username", new { Username = username }, transaction).First();
return count > 0;
}
public static bool UserIsInDb(int userId, NpgsqlConnection conn, NpgsqlTransaction transaction)
{
long count = conn.Query<long>(@"SELECT Count(*) FROM mal_user WHERE mal_user_id = :UserId", new { UserId = userId }, transaction).First();
return count > 0;
}
/// <summary>
/// Get full table info for a list of tables.
/// </summary>
/// <param name="tableList">Enumerable containing the names of the tables to be retrieved.</param>
/// <returns>List of Table with full column information.</returns>
public List<Table> GetFullTableInfo(IEnumerable<string> tableList)
{
string query = @"select
cols.column_name as Name,
cols.data_type as Type,
CAST(cols.is_nullable as boolean) as IsNullable,
CAST(case when pk.COLUMN_NAME is NULL then 0 else 1 end as boolean) as IsPrimaryKey
from
information_schema.columns cols left join
(select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
and kcu.TABLE_NAME = tc.TABLE_NAME
where tc.CONSTRAINT_TYPE = 'PRIMARY KEY')
as pk on cols.TABLE_SCHEMA = pk.TABLE_SCHEMA
and cols.TABLE_NAME = pk.TABLE_NAME
and cols.COLUMN_NAME = pk.COLUMN_NAME
where
cols.TABLE_SCHEMA = @schema and
cols.TABLE_NAME = @name
order by
cols.ordinal_position";
var tables = new List<Table>(tableList.Count());
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
foreach (var value in tableList)
{
var split = value.Split('.');
var table = new Table();
table.AdoAdapterConnectionClassName = "NpgsqlConnection";
table.AdoAdapterNamespace = "Npgsql";
table.Name = split[1];
table.Schema = split[0];
table.Columns = conn.Query<Column>(query, new { schema = table.Schema, name = table.Name }).ToList<Column>();
table.Columns.ForEach(m => m.Type = GetNetDataType(m.Type, m.IsNullable));
tables.Add(table);
}
}
return tables;
}
public static IEnumerable<mal_anime> GetAll(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string sql = @"
SELECT mal_anime_id, title, mal_anime_type_id, num_episodes, mal_anime_status_id, start_year, start_month, start_day,
end_year, end_month, end_day, image_url, last_updated
FROM mal_anime
";
// This buffers all the rows in memory before returning
return conn.Query<mal_anime>(sql, transaction: transaction);
}
public static bool IsInDatabase(int animeId, NpgsqlConnection conn, NpgsqlTransaction transaction)
{
long count = conn.Query<long>("SELECT Count(*) FROM mal_anime WHERE mal_anime_id = :AnimeId",
new { AnimeId = animeId }, transaction).First();
return count > 0;
}
public void TestPostresqlArrayParameters()
{
using (var conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=dappertest;Password=dapperpass;Database=dappertest;Encoding=UNICODE"))
{
conn.Open();
IDbTransaction transaction = conn.BeginTransaction();
conn.Execute("create table tcat ( id serial not null, breed character varying(20) not null, name character varying (20) not null);");
conn.Execute("insert tcat(breed, name) values(:breed, :name) ", Cats);
var r = conn.Query<Cat>("select * from tcat where id=any(:catids)", new { catids = new[] { 1, 3, 5 } });
r.Count().IsEqualTo(3);
r.Count(c => c.Id == 1).IsEqualTo(1);
r.Count(c => c.Id == 3).IsEqualTo(1);
r.Count(c => c.Id == 5).IsEqualTo(1);
transaction.Rollback();
}
}
static void Main(string[] args)
{
var store = Wireup
.Init()
.UsingSqlPersistence("db")
.WithDialect(new PostgreSqlDialect())
.InitializeStorageEngine()
.Build();
var id = Guid.NewGuid().ToString();
//Insert some events
using (var stream = store.OpenStream(id))
{
var events = Enumerable.Range(1, 10).Select(i => new EventMessage
{
Body = new Foo {Id = i, Value = "Bar"},
Headers = new Dictionary<string, object>
{
["UserName"] = i < 8 ? "Bob" : "John"
}
});
foreach (var e in events)
{
stream.Add(e);
stream.CommitChanges(Guid.NewGuid());
}
}
//Direct sql query of json
using (var con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
{
var countOfUpdatesByJohn = con
.ExecuteScalar<int>(@"
select count(1)
from commits, jsonb_array_elements(commits.payload) as events
where
streamidoriginal = @id
and
events->'Headers'->>'UserName' = 'John'", new { id });
}
//Load events directly, bypassing neventstore
using (var con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
{
var events = con
.Query<string>(@"
select payload
from commits
where
streamidoriginal = @id
order by streamrevision", new { id })
.SelectMany(json =>
JsonConvert.DeserializeObject<List<EventMessage>>(json, new JsonSerializerSettings
{
TypeNameHandling = TypeNameHandling.Auto,
DefaultValueHandling = DefaultValueHandling.Ignore,
MetadataPropertyHandling = MetadataPropertyHandling.ReadAhead,
NullValueHandling = NullValueHandling.Ignore
}))
.ToList();
}
}
