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