private static void SetupPg() { using (var connection = new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "postgres", "postgrespass", "postgres"))) { connection.Open(); // drop database connection.Execute("DROP DATABASE IF EXISTS testdb;"); connection.Execute("CREATE DATABASE testdb WITH OWNER = postgres ENCODING = 'UTF8' CONNECTION LIMIT = -1;"); } System.Threading.Thread.Sleep(1000); using (var connection = new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "postgres", "postgrespass", "testdb"))) { connection.Open(); connection.Execute(@" create table Users (Id SERIAL PRIMARY KEY, Name varchar not null, Age int not null, ScheduledDayOff int null, CreatedDate date not null default CURRENT_DATE) "); connection.Execute(@" create table Car (CarId SERIAL PRIMARY KEY, Id int null, Make varchar not null, Model varchar not null, Color varchar not null, AgeInYears int not null) "); connection.Execute(@" create table BigCar (CarId BIGSERIAL PRIMARY KEY, Make varchar not null, Model varchar not null) "); connection.Execute(@" alter sequence bigcar_carid_seq RESTART WITH 2147483650"); connection.Execute(@" create table City (Name varchar not null, Population int not null) "); connection.Execute(@" CREATE SCHEMA Log; "); connection.Execute(@" create table Log.CarLog (Id SERIAL PRIMARY KEY, LogNotes varchar NOT NULL) "); connection.Execute(@" CREATE TABLE GUIDTest(Id uuid PRIMARY KEY,name varchar NOT NULL)"); connection.Execute(@" create table StrangeColumnNames (ItemId Serial PRIMARY KEY, word varchar not null, colstringstrangeword varchar) "); connection.Execute(@" create table UserWithoutAutoIdentity (Id int PRIMARY KEY, Name varchar not null, Age int not null) "); } }
private static void CreateDatabase() { var cxBuilder = new NpgsqlConnectionStringBuilder(ConnectionString); var database = cxBuilder.Database; cxBuilder.Database = null; var db = new NpgsqlConnection(cxBuilder.ToString()); db.Execute($"DROP DATABASE IF EXISTS \"{database}\""); db.Execute($"CREATE DATABASE \"{database}\""); }
private void Cleanup() { using (var connection = new NpgsqlConnection(ConnectionString)) { connection.Open(); foreach (var stream in _streams) { Catch(() => connection.Execute($"drop table if exists {TableBuilder.EventsName(stream)};")); Catch(() => connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(stream)};")); } } }
public void Dispose() { using (var connection = new NpgsqlConnection(PostgresFixture.ConnectionString)) { connection.Open(); connection.Execute($"drop table if exists {TableBuilder.EventsName(DefaultStream.StreamName)};"); connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(DefaultStream.StreamName)};"); connection.Execute($"drop table if exists {TableBuilder.EventsName(SnapshotStream.StreamName)};"); connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(SnapshotStream.StreamName)};"); } }
public static int Delete(IEnumerable<int> malAnimeIds, NpgsqlConnection conn, NpgsqlTransaction transaction) { string idList = string.Join(", ", malAnimeIds.Select(id => id.ToString(CultureInfo.InvariantCulture))); string deleteSql = string.Format("DELETE FROM mal_anime_synonym WHERE mal_anime_id IN ({0})", idList); int numRowsDeleted = conn.Execute(deleteSql, transaction: transaction); return numRowsDeleted; }
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 static void Init() { StoreEvents = Wireup.Init() //.UsingInMemoryPersistence() .UsingSqlPersistence("EventStore") // Connection string is in app.config .WithDialect(new PostgreSqlDialect()) .EnlistInAmbientTransaction() // two-phase commit .InitializeStorageEngine() //.TrackPerformanceInstance("example") .UsingJsonSerialization() //.Compress() //.EncryptWith(EncryptionKey) //.HookIntoPipelineUsing(new[] { new AuthorizationPipelineHook() }) //.UsingSynchronousDispatchScheduler() //.DispatchTo(new DelegateMessageDispatcher(DispatchCommit)) .Build(); var connectionString = ConfigurationManager.ConnectionStrings["EventStore"].ConnectionString; var connection = new NpgsqlConnection(connectionString); connection.Open(); var sql = @" CREATE TABLE IF NOT EXISTS ""LigaView"" ( ""Id"" uuid NOT NULL, ""Name"" varchar NOT NULL );"; connection.Execute(sql); connection.Close(); }
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 }); } }; }
public void PostGresInsertModelFillingId() { var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgres"].ConnectionString); var scriptBuilder = conn.GetScriptBuild(); var cliente = new Cliente() { Nome = "Miranda" }; var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>(); conn.Execute(createTableScript); conn.Insert <Cliente>(cliente); Assert.AreEqual(1, cliente.Id); conn.Execute("drop table \"Cliente\""); }
public static void CleanTables(NpgsqlConnection connection) { if (connection == null) throw new ArgumentNullException("connection"); var script = GetStringResource( typeof (PostgreSqlTestObjectsInitializer).Assembly, "Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", ConnectionUtils.GetSchemaName()); connection.Execute(script); }
public void PostGresDeleteModel() { var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgres"].ConnectionString); var scriptBuilder = conn.GetScriptBuild(); var cliente = new Cliente() { Nome = "Miranda" }; var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>(); conn.Execute(createTableScript); var id = conn.InsertReturningId <Cliente>(cliente); cliente.Id = id; conn.Delete(cliente); conn.Execute("drop table \"Cliente\""); }
public void Execute(string stream) { var sql = Sql .Replace("{events-table}", stream + "_events") .Replace("{snapshots-table}", stream + "_snapshots"); using (var connection = new NpgsqlConnection(_connectionString)) { connection.Open(); connection.Execute(sql); } }
public void PostGresSelectModel() { var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgres"].ConnectionString); var scriptBuilder = conn.GetScriptBuild(); var cliente = new Cliente() { Nome = "Miranda" }; var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>(); conn.Execute(createTableScript); var id = conn.InsertReturningId <Cliente>(cliente); var clientes = conn.GetAll <Cliente>(); Assert.AreEqual(1, clientes.Count()); Assert.AreEqual("Miranda", clientes.ToList()[0].Nome); conn.Execute("drop table \"Cliente\""); }
public void Execute(string stream) { var sql = Sql .Replace("{events-table}", TableBuilder.EventsName(stream)) .Replace("{snapshots-table}", TableBuilder.SnapshotsName(stream)); using (var connection = new NpgsqlConnection(_connectionString)) { connection.Open(); connection.Execute(sql); } }
public void OnPost() { // send information to the database var connectionString = "Server = 127.0.0.1; Port = 5432; Database = bookish; User Id = Bookish; Password = password; ";; NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString); connection.Open(); connection.Execute("INSERT INTO \"Books\" (\"Title\", \"Author\", \"ISBN\",\"AvailableCopies\", \"TotalCopies\") VALUES(@Title, @Author, @ISBN, @TotalCopies, @TotalCopies)", this); }
public void CheckoutBook(int BookID, string DueDate) { var connectionString = "Server = 127.0.0.1; Port = 5432; Database = bookish; User Id = Bookish; Password = password; "; var UserID = AccessDB.FindUserIDByEmail(Email, connectionString); var anyCopies = AccessDB.AreThereAvailableCopies(BookID, connectionString); var DateTimeDue = DateTime.Parse(DueDate); if (anyCopies == true) { NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString); connection.Open(); using (var transaction = connection.BeginTransaction()) { connection.Execute("INSERT INTO \"Books Taken Out\" (\"UserID\",\"BookID\", \"DueDate\") VALUES (@UserID, @BookID, @DateTimeDue)", new { UserID, BookID, DateTimeDue }); connection.Execute("UPDATE \"Books\" SET \"AvailableCopies\" = \"AvailableCopies\" - 1 WHERE \"BookID\" = @BookID", new { BookID }); transaction.Commit(); } connection.Close(); } }
public void PostGresTestOperators() { var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgres"].ConnectionString); var scriptBuilder = conn.GetScriptBuild(); var cliente1 = new Cliente() { Nome = "Miranda" }; var cliente2 = new Cliente() { Nome = "Moshe" }; var cliente3 = new Cliente() { Nome = "Moshe" }; var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>(); conn.Execute(createTableScript); var id = conn.InsertReturningId <Cliente>(cliente1); conn.Insert(cliente2); conn.Insert(cliente3); var clientesWithIdGreaterThan1 = conn.Select <Cliente>(c => c.Id > 1); var clientesIdOtherThan3 = conn.Select <Cliente>(c => c.Id != 3); var clientesWithIdLessThan2 = conn.Select <Cliente>(c => c.Id < 2); Assert.AreEqual(2, clientesWithIdGreaterThan1.Count()); Assert.AreEqual("Miranda", clientesIdOtherThan3.ToList()[0].Nome); Assert.AreEqual(2, clientesIdOtherThan3.ToList()[1].Id); Assert.AreEqual(1, clientesWithIdLessThan2.Count()); conn.Execute("drop table \"Cliente\""); }
public static void Install(NpgsqlConnection connection, string schemaName = "hangfire") { if (connection == null) throw new ArgumentNullException("connection"); Log.Info("Start installing Hangfire SQL objects..."); int version = 3; //We started at version 3 bool scriptFound = true; do { try { var script = GetStringResource( typeof (PostgreSqlObjectsInstaller).Assembly, string.Format("Hangfire.PostgreSql.Install.v{0}.sql", version.ToString(CultureInfo.InvariantCulture))); if (schemaName != "hangfire") { script = script.Replace("'hangfire'", string.Format("'{0}'", schemaName)) .Replace(@"""hangfire""", string.Format(@"""{0}""", schemaName)); } try { connection.Execute(script, commandTimeout: 120); } catch (NpgsqlException ex) { if((ex.BaseMessage ?? "") != "version-already-applied") { throw; } } } catch { scriptFound = false; } version++; } while (scriptFound); Log.Info("Hangfire SQL objects installed."); }
public static void DeleteOldestUsers(long numUsers, NpgsqlConnection conn, NpgsqlTransaction transaction) { if (numUsers == 0) { return; } if (numUsers < 0) { throw new ArgumentOutOfRangeException("numUsers", numUsers, string.Format("Cannot delete {0} oldest users", numUsers)); } string deleteSql = @"DELETE FROM mal_user WHERE mal_user_id IN (SELECT mal_user_id FROM mal_user ORDER BY time_added LIMIT :NumToDelete)"; conn.Execute(deleteSql, new { NumToDelete = numUsers }, transaction); }
public void Insert(NpgsqlConnection conn, NpgsqlTransaction transaction) { string sql = "INSERT INTO mal_user (mal_user_id, mal_name, time_added) VALUES (:MalUserId, :MalName, :TimeAdded)"; conn.Execute(sql, new { MalUserId = mal_user_id, MalName = mal_name, TimeAdded = time_added }, transaction); }
public void CopyToRedshift(string dataPrefix, string dataFileExtension, string processingPrefix, string completedPrefix, string errorPrefix,string delimiter, string tableName, string[] columnList, string connectionStringName, int headerRowCount,Action<Exception,string> errorLog, Action<string> debugLog) { debugLog(string.Format("Getting files with extension {0} from {1}", dataFileExtension, dataPrefix)); var files = ListFiles(dataPrefix) .Where(x => x.EndsWith(dataFileExtension)) .ToList(); if (files.Count == 0) { debugLog("No Files to copy"); return; } var batchId = Guid.NewGuid().ToString("N"); var batchProcessDir = S3.Combine(processingPrefix, batchId + "/"); try { debugLog(string.Format("Moving {0} files to {1}", files.Count, batchProcessDir)); // create a batch id and store files in specific folder for processing only this batch's files foreach (var file in files) { var pFile = file.Replace(dataPrefix, batchProcessDir); MoveFile(file, _connectionInfo.BucketName, pFile, _connectionInfo.BucketName); } debugLog("Building sql query and primary key check"); var sql = EmbeddedResource.Get("copyToRedshift.sql") .Replace("$COLUMNLIST$", string.Join(",", columnList)) .Replace("$ACCESSKEY$", _connectionInfo.AccessKey) .Replace("$SECRETKEY$", _connectionInfo.SecretKey) .Replace("$BUCKET$", _connectionInfo.BucketName) .Replace("$TABLENAME$", tableName) .Replace("$DELIMETER$", delimiter) .Replace("$HEADERROWS$", headerRowCount.ToString(CultureInfo.InvariantCulture)) .Replace("$COLUMNLIST$", string.Join(",", columnList)) .Replace("$PATH$", batchProcessDir); debugLog("Copying files to redshift"); using (var connection = new NpgsqlConnection(ConfigUtilities.GetConnectionString(connectionStringName))) { debugLog("Running SQL: " + sql); connection.Execute(sql, commandTimeout: 1000*60*15); } debugLog("Moving files to completed directory"); foreach (var file in files) { var processedFile = file.Replace(dataPrefix, batchProcessDir); var completeFile = file.Replace(dataPrefix, S3.Combine(completedPrefix, batchId + "/")); MoveFile(processedFile, _connectionInfo.BucketName, completeFile, _connectionInfo.BucketName); } } catch (Exception exception) { errorLog(exception, "Error in copy to redshift"); debugLog("Moving files to error directory"); var filesInProcessing = ListFiles(batchProcessDir) .Where(x => x.EndsWith(dataFileExtension)) .ToList(); foreach (var file in filesInProcessing) { var errorFile = file.Replace(batchProcessDir, Combine(errorPrefix,batchId + "/") ); //var processedFile = Combine(processingPrefix, file); MoveFile(file, _connectionInfo.BucketName, errorFile, _connectionInfo.BucketName); } } }
/// <summary> /// Copy from s3 to redshift /// </summary> /// <param name="dataPrefix">the s3 location where the data files are located</param> /// <param name="dataFileExtension">the extension of the files to insert</param> /// <param name="processingPrefix">the s3 location where the data files are moved during processing</param> /// <param name="completedPrefix">the s3 location where the data files are moved after copy success</param> /// <param name="errorPrefix">the s3 location where the data files are moved if there is an error</param> /// <param name="delimeter"></param> /// <param name="tableName">the redshift table to copy to</param> /// <param name="columnList">the columns of the table to copy into, same order as the data files</param> /// <param name="primaryKeyColumns">the columns that are primary keys and should be used to figure out how to merge</param> /// <param name="connectionStringName">the connection string or connection string name of the redshift database</param> /// <param name="headerRowCount">how many of the rows in the text file are header rows</param> /// <param name="errorLog">function to log errors</param> /// <param name="debugLog">function for debug logging</param> public void MergeToRedshift(string dataPrefix, string dataFileExtension, string processingPrefix, string completedPrefix, string errorPrefix, string delimeter,string tableName, string[] columnList, string[] primaryKeyColumns, string connectionStringName, int headerRowCount,Action<Exception,string> errorLog, Action<string> debugLog, bool nullCheckPrimaryKeys) { debugLog(string.Format("Getting files with extension {0} from {1}", dataFileExtension, dataPrefix)); var files = ListFiles(dataPrefix) .Where(x => x.EndsWith(dataFileExtension)) .ToList(); if (files.Count == 0) { debugLog("No Files to copy"); return; } try { debugLog(string.Format("Moving {0} files to {1}", files.Count, processingPrefix)); foreach (var file in files) { var pFile = file.Replace(dataPrefix, processingPrefix); MoveFile(file, _connectionInfo.BucketName, pFile, _connectionInfo.BucketName); } debugLog("Building sql query and primary key check"); var stagingTableName = GenerateRandom.String(6).ToLower() + "_staging"; var sql = EmbeddedResource.Get("mergeToRedshift.sql") .Replace("$COLUMNLIST$", string.Join(",", columnList)) .Replace("$ACCESSKEY$", _connectionInfo.AccessKey) .Replace("$SECRETKEY$", _connectionInfo.SecretKey) .Replace("$BUCKET$", _connectionInfo.BucketName) .Replace("$TEMPTABLE$", stagingTableName) .Replace("$TABLENAME$", tableName) .Replace("$DELIMETER$", delimeter) .Replace("$HEADERROWS$", headerRowCount.ToString(CultureInfo.InvariantCulture)) .Replace("$COLUMNLIST$", string.Join(",", columnList)) .Replace("$PATH$", processingPrefix); var primaryKeyCheck = new List<string>(); foreach (var pkey in primaryKeyColumns) { var tableKey = tableName + ".[" + pkey + "]"; var stageKey = stagingTableName + ".[" + pkey + "]"; var input = nullCheckPrimaryKeys ? "{tableKey} = {stageKey} or ({tableKey} is null and {stageKey} is null)" : "{tableKey} = {stageKey}"; input = Regex.Replace(input, "{tableKey}", tableKey); input = Regex.Replace(input, "{stageKey}", stageKey); primaryKeyCheck.Add(input); } var primaryKeySql = string.Join(" and ", primaryKeyCheck); sql = sql.Replace("$PRIMARYKEYCHECK$", primaryKeySql); debugLog("Copying files to redshift"); using (var connection = new NpgsqlConnection(ConfigUtilities.GetConnectionString(connectionStringName))) { debugLog("Running SQL: " + sql); connection.Execute(sql, commandTimeout: 1000*60*15); } debugLog("Moving files to completed directory"); foreach (var file in files) { var processedFile = file.Replace(dataPrefix, processingPrefix); var completeFile = file.Replace(dataPrefix, completedPrefix); MoveFile(processedFile, _connectionInfo.BucketName, completeFile, _connectionInfo.BucketName); } } catch (Exception exception) { errorLog(exception, "Error in copy to redshift"); debugLog("Moving files to error directory"); var filesInProcessing = ListFiles(processingPrefix) .Where(x => x.EndsWith(dataFileExtension)) .ToList(); foreach (var file in filesInProcessing) { var errorFile = file.Replace(processingPrefix, errorPrefix); //var processedFile = Combine(processingPrefix, file); MoveFile(file, _connectionInfo.BucketName, errorFile, _connectionInfo.BucketName); } } }
public void Insert(NpgsqlConnection conn, NpgsqlTransaction transaction) { string sql = @"INSERT INTO mal_anime (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) VALUES (:MalAnimeId, :Title, :MalAnimeTypeId, :NumEpisodes, :MalAnimeStatusId, :StartYear, :StartMonth, :StartDay, :EndYear, :EndMonth, :EndDay, :ImageUrl, :LastUpdated)"; conn.Execute(sql, new { MalAnimeId = mal_anime_id, Title = title, MalAnimeTypeId = mal_anime_type_id, NumEpisodes = num_episodes, MalAnimeStatusId = mal_anime_status_id, StartYear = start_year, StartMonth = start_month, StartDay = start_day, EndYear = end_year, EndMonth = end_month, EndDay = end_day, ImageUrl = image_url, LastUpdated = last_updated }, transaction); }
public static void Install(NpgsqlConnection connection, string schemaName = "hangfire") { if (connection == null) throw new ArgumentNullException(nameof(connection)); Log.Info("Start installing Hangfire SQL objects..."); // starts with version 3 to keep in check with Hangfire SqlServer, but I couldn't keep up with that idea after all; int version = 3; int previousVersion = 1; do { try { string script = null; try { script = GetStringResource( typeof (PostgreSqlObjectsInstaller).Assembly, $"Hangfire.PostgreSql.Install.v{version.ToString(CultureInfo.InvariantCulture)}.sql"); } catch(MissingManifestResourceException) { break; } if (schemaName != "hangfire") { script = script.Replace("'hangfire'", $"'{schemaName}'").Replace(@"""hangfire""", $@"""{schemaName}"""); } using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable)) using (var command = new NpgsqlCommand(script, connection, transaction)) { command.CommandTimeout = 120; try { command.ExecuteNonQuery(); // Due to https://github.com/npgsql/npgsql/issues/641 , it's not possible to send // CREATE objects and use the same object in the same command // So bump the version in another command connection.Execute($@"UPDATE ""{schemaName}"".""schema"" SET ""version"" = @version WHERE ""version"" = @previousVersion", new {version, previousVersion}, transaction); transaction.Commit(); } catch (PostgresException ex) { if ((ex.MessageText ?? "") != "version-already-applied") { throw; } } } } catch (Exception ex) { if (ex.Source.Equals("Npgsql")) { Log.ErrorException("Error while executing install/upgrade", ex); } } previousVersion = version; version++; } while (true); Log.Info("Hangfire SQL objects installed."); }
public void Update(NpgsqlConnection conn, NpgsqlTransaction transaction) { string sql = @"UPDATE mal_anime SET title = :Title, mal_anime_type_id = :MalAnimeTypeID, num_episodes = :NumEpisodes, mal_anime_status_id = :MalAnimeStatusId, start_year = :StartYear, start_month = :StartMonth, start_day = :StartDay, end_year = :EndYear, end_month = :EndMonth, end_day = :EndDay, image_url = :ImageUrl, last_updated = :LastUpdated WHERE mal_anime_id = :MalAnimeId"; conn.Execute(sql, new { MalAnimeId = mal_anime_id, Title = title, MalAnimeTypeId = mal_anime_type_id, NumEpisodes = num_episodes, MalAnimeStatusId = mal_anime_status_id, StartYear = start_year, StartMonth = start_month, StartDay = start_day, EndYear = end_year, EndMonth = end_month, EndDay = end_day, ImageUrl = image_url, LastUpdated = last_updated }, transaction); }
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(); } }
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]; }; }; }