Пример #1
0
 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();
     }
 }
Пример #2
0
 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);
			}
		}
Пример #4
0
        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);
        }
Пример #5
0
        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 });
                }
            };
        }
Пример #6
0
        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;
        }
Пример #7
0
        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);
        }
Пример #8
0
        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);
        }
Пример #9
0
        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);
        }
Пример #10
0
        /// <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);
            }
        }
Пример #11
0
        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

                }
            }
        }
Пример #12
0
        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);
        }
Пример #13
0
        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();
        }
Пример #14
0
        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);
 }
Пример #18
0
 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);
 }
Пример #19
0
 public static long Count(NpgsqlConnection conn, NpgsqlTransaction transaction)
 {
     long count = conn.Query<long>("SELECT Count(*) FROM mal_user", transaction: transaction).First();
     return count;
 }
Пример #20
0
 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;
 }
Пример #21
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;
 }
Пример #22
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;
        }
Пример #23
0
        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);
        }
Пример #24
0
 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;
 }
Пример #25
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();
                
            }
        }