public void PostgreSQL1()
{
var settings = new Dictionary <string, object>()
{
{ "Server", "10.28.119.215" },
{ "Port", "5434" },
{ "UserID", "pcornet" },
{ "Password", "HpHc082817@#" },
{ "Database", "PcorNetV3" },
{ "ConnectionTimeout", "60" },
{ "CommandTimeout", "60" },
{ "DataProvider", Model.Settings.SQLProvider.PostgreSQL.ToString() }
};
var connBuilder = new Npgsql.NpgsqlConnectionStringBuilder();
connBuilder.Host = settings["Server"].ToString();
connBuilder.Port = Convert.ToInt32(settings["Port"]);
connBuilder.Username = settings["UserID"].ToString();
connBuilder.Password = settings["Password"].ToString();
connBuilder.Database = settings["Database"].ToString();
string connectionString = connBuilder.ToString();
using (var connection = new Npgsql.NpgsqlConnection(connBuilder.ToString()))
{
/** Of the connection is opened prior to giving to the datacontext Npgsql error when trying to execute query **/
connection.Open();
//using (var db = new Lpp.Dns.DataMart.Model.PCORIQueryBuilder.DataContext(connection, "dbo"))
//{
// var patient = db.Patients.Select(p => p.ID).FirstOrDefault();
//}
}
}
public void Create(Databases database)
{
Npgsql.NpgsqlConnectionStringBuilder conStrBuilder = new Npgsql.NpgsqlConnectionStringBuilder(database.conStr);
database.Name = conStrBuilder.Database;
conStrBuilder.Database = null;
var db = EntityDB.DBContext.CreateDatabaseService(conStrBuilder.ToString(), EntityDB.DatabaseType.PostgreSql);
object flag = db.ExecSqlString("select count(*) from pg_catalog.pg_database where datname=@p0", database.Name.ToLower());
if (Convert.ToInt32(flag) == 0)
{
db.ExecSqlString("CREATE DATABASE " + database.Name.ToLower() + " ENCODING='UTF-8'");
}
conStrBuilder.Database = database.Name.ToLower();
//创建必须表
db = EntityDB.DBContext.CreateDatabaseService(conStrBuilder.ToString(), EntityDB.DatabaseType.PostgreSql);
db.DBContext.BeginTransaction();
try
{
CreateEasyJobTable(db);
db.DBContext.CommitTransaction();
}
catch (Exception ex)
{
db.DBContext.RollbackTransaction();
throw ex;
}
}
public void can_create_same_table_in_multiple_schemas_based_on_conn_string_search_path()
{
var builder = new Npgsql.NpgsqlConnectionStringBuilder(ConnectionString);
var schema1 = "schema_1";
var schema2 = "schema_2";
using (var db = OpenDbConnection())
{
CreateSchemaIfNotExists(db, schema1);
CreateSchemaIfNotExists(db, schema2);
}
builder.SearchPath = schema1;
using (var dbS1 = builder.ToString().OpenDbConnection())
{
dbS1.DropTable<CreatePostgreSQLTablesTests_dummy_table>();
dbS1.CreateTable<CreatePostgreSQLTablesTests_dummy_table>();
Assert.That(dbS1.Count<CreatePostgreSQLTablesTests_dummy_table>(), Is.EqualTo(0));
}
builder.SearchPath = schema2;
using (var dbS2 = builder.ToString().OpenDbConnection())
{
dbS2.DropTable<CreatePostgreSQLTablesTests_dummy_table>();
dbS2.CreateTable<CreatePostgreSQLTablesTests_dummy_table>();
Assert.That(dbS2.Count<CreatePostgreSQLTablesTests_dummy_table>(), Is.EqualTo(0));
}
}
public void can_create_same_table_in_multiple_schemas_based_on_conn_string_search_path()
{
var builder = new Npgsql.NpgsqlConnectionStringBuilder(ConnectionString);
var schema1 = "schema_1";
var schema2 = "schema_2";
using (var db = OpenDbConnection())
{
CreateSchemaIfNotExists(db, schema1);
CreateSchemaIfNotExists(db, schema2);
}
builder.SearchPath = schema1;
using (var dbS1 = builder.ToString().OpenDbConnection())
{
dbS1.DropTable <CreatePostgreSQLTablesTests_dummy_table>();
dbS1.CreateTable <CreatePostgreSQLTablesTests_dummy_table>();
Assert.That(dbS1.Count <CreatePostgreSQLTablesTests_dummy_table>(), Is.EqualTo(0));
}
builder.SearchPath = schema2;
using (var dbS2 = builder.ToString().OpenDbConnection())
{
dbS2.DropTable <CreatePostgreSQLTablesTests_dummy_table>();
dbS2.CreateTable <CreatePostgreSQLTablesTests_dummy_table>();
Assert.That(dbS2.Count <CreatePostgreSQLTablesTests_dummy_table>(), Is.EqualTo(0));
}
}
public void ConfigureServices(IServiceCollection services)
{
//机密文件中的密码
var connectionStringBuilder = new Npgsql.NpgsqlConnectionStringBuilder(Configuration.GetConnectionString("DefaultConnectionString"));
connectionStringBuilder.Password = Configuration["dbpassword"];
Console.WriteLine(connectionStringBuilder.ToString());
//绑定
var appsetting = new Appsetting();
Configuration.GetSection("Appsetting").Bind(appsetting);
//热更新
services.Configure <Appsetting>(Configuration.GetSection("Appsetting"));
//azure配置文件
Console.WriteLine($"Azure pgdb：{Configuration["pgpwd"]}");
//环境变量
Console.WriteLine($"环境变量Java_中的值：{Configuration["home"]}");
services.AddControllers()
.AddNewtonsoftJson();
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext <ApplicationDbContext>(options =>
{
var databaseUri = new Uri(Configuration.GetValue <string>("DATABASE_URL"));
var userInfo = databaseUri.UserInfo.Split(':');
var csb = new Npgsql.NpgsqlConnectionStringBuilder()
{
Host = databaseUri.Host,
Port = databaseUri.IsDefaultPort ? 5432 : databaseUri.Port,
Username = userInfo[0],
Password = userInfo[1],
Database = databaseUri.LocalPath.TrimStart('/')
};
options.UseNpgsql(csb.ToString());
});
services.AddDefaultIdentity <ApplicationUser>(options => options.SignIn.RequireConfirmedAccount = true)
.AddEntityFrameworkStores <ApplicationDbContext>();
services.AddIdentityServer()
.AddApiAuthorization <ApplicationUser, ApplicationDbContext>();
services.AddAuthentication()
.AddIdentityServerJwt();
services.AddControllersWithViews();
services.AddRazorPages();
// In production, the Angular files will be served from this directory
services.AddSpaStaticFiles(configuration =>
{
configuration.RootPath = "ClientApp/dist";
});
}
private static string MakeConnectionString(string host, int port, bool useWindowsAuthentication, string userName, string password, string dbName)
{
Npgsql.NpgsqlConnectionStringBuilder npgsqlConnectionStringBuilder = new Npgsql.NpgsqlConnectionStringBuilder();
npgsqlConnectionStringBuilder.Host = host;
npgsqlConnectionStringBuilder.Port = port;
npgsqlConnectionStringBuilder.IntegratedSecurity = useWindowsAuthentication;
npgsqlConnectionStringBuilder.Username = userName;
npgsqlConnectionStringBuilder.Password = password;
npgsqlConnectionStringBuilder.Database = dbName;
return(npgsqlConnectionStringBuilder.ToString());
}
public void Drop(Databases database)
{
Npgsql.NpgsqlConnectionStringBuilder conStrBuilder = new Npgsql.NpgsqlConnectionStringBuilder(database.conStr);
conStrBuilder.Database = null;
var db = EntityDB.DBContext.CreateDatabaseService(conStrBuilder.ToString(), EntityDB.DatabaseType.PostgreSql);
db.ExecSqlString("DROP DATABASE if exists " + database.Name.ToLower() + "");
db.DBContext.Dispose();
}
/// <inheritdoc/>
public override string CreateConnectionString(AuthorizedConnectionStringParameters parameters)
{
var builder = new Npgsql.NpgsqlConnectionStringBuilder()
{
Host = "127.0.0.1",
Port = 5432,
Username = parameters.Username,
Password = parameters.Password,
Database = parameters.Database,
KeepAlive = 200000,
TcpKeepAliveTime = 200000,
};
return(builder.ToString());
}
public static string GetConnectionString(BigDataPipeline.Interfaces.ISessionContext context)
{
var options = context.Options;
// Create a PostgeSQL connection string.
var csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Host = options.Get("awsRedshiftHost", "");
csb.Port = options.Get("awsRedshiftPort", 0);
csb.Database = options.Get("awsRedshiftDatabase", "");
csb.UserName = options.Get("awsRedshiftLogin", "");
csb.Password = options.Get("awsRedshiftPassword", "");
csb.CommandTimeout = (int)TimeSpan.FromHours(2).TotalSeconds;
csb.Timeout = 900; // 1024 seconds is the maximum allowed timeout for the Npgsql driver
csb.Pooling = false;
return(csb.ToString());
}
public void ConnectionString_Check()
{
System.Data.SqlClient.SqlConnectionStringBuilder conStrBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder("server=ETHAN-20171016H;uid=sa;pwd=123;Database=TestDB");
conStrBuilder.InitialCatalog = "master";
var constr = conStrBuilder.ToString();
//Server=;Port=5432;UserId=;Password=;Database=;
Npgsql.NpgsqlConnectionStringBuilder conStrBuilder2 = new Npgsql.NpgsqlConnectionStringBuilder("Server=localhost;Port=5432;UserId=sa;Password=1;Database=testDB;");
conStrBuilder2.Database = null;
constr = conStrBuilder2.ToString();
Pomelo.Data.MySql.MySqlConnectionStringBuilder conStrBuilder3 = new Pomelo.Data.MySql.MySqlConnectionStringBuilder("server=locahost;User Id=sa;password=12;Database=testDB");
conStrBuilder3.Database = null;
constr = conStrBuilder3.ToString();
//server=;User Id=;password=;Database=
}
} // End Function GetConnectionString
public static string GetPgConnectionString()
{
string str = @"CREATE ROLE stackexchangeimporter LOGIN PASSWORD '123'
SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
";
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.UserName = "******";
csb.Password = "******";
csb.Port = 5432;
csb.Database = "startups";
csb.Host = "127.0.0.1";
str = csb.ToString();
return(str);
} // End Function GetConnectionString
bool TestPostgreSQL(string server, string port, string database, string userID, string password, int connectionTimeout, int commandTimeout)
{
Npgsql.NpgsqlConnectionStringBuilder builder = new Npgsql.NpgsqlConnectionStringBuilder();
builder.Host = server;
if (!string.IsNullOrWhiteSpace(port))
{
builder.Port = Convert.ToInt32(port);
}
builder.Database = database;
builder.Username = userID;
builder.Password = password;
builder.Timeout = connectionTimeout;
builder.CommandTimeout = commandTimeout;
using (var conn = new Npgsql.NpgsqlConnection(builder.ToString()))
{
return(TestConnection(conn));
}
}
bool TestPostgreSQL(string server, string port, string database, string userID, string password, int connectionTimeout, int commandTimeout, bool encrypted)
{
Npgsql.NpgsqlConnectionStringBuilder builder = new Npgsql.NpgsqlConnectionStringBuilder();
builder.Host = server;
if (!string.IsNullOrWhiteSpace(port))
{
builder.Port = Convert.ToInt32(port);
}
builder.Database = database;
builder.Username = userID;
builder.Password = password;
builder.Timeout = connectionTimeout;
builder.CommandTimeout = commandTimeout;
builder.SslMode = encrypted ? Npgsql.SslMode.Require : Npgsql.SslMode.Prefer;
builder.TrustServerCertificate = encrypted;
using (var conn = new Npgsql.NpgsqlConnection(builder.ToString()))
{
return(TestConnection(conn));
}
}
public String GetConnectionString()
{
var config = new Npgsql.NpgsqlConnectionStringBuilder()
{
Host = Host,
Port = Port,
Username = User,
Password = Password,
PersistSecurityInfo = true,
Database = Database ?? "",
MinPoolSize = 1,
MaxPoolSize = PoolSize,
Pooling = UsePooling,
Timeout = Timeout,
ConnectionIdleLifetime = Lifetime,
CommandTimeout = Timeout,
};
return(config.ToString());
}
void InitDb()
{
if (_factory.Provider == DatabaseProvider.PostgreSql)
{
PgDbSetting ps = new PgDbSetting();
Npgsql.NpgsqlConnectionStringBuilder sb = new Npgsql.NpgsqlConnectionStringBuilder(ps.ConnectionString);
sb.Database = "postgres";
ps.ConnectionString = sb.ToString();
AnyDbFactory f = new AnyDbFactory(ps);
using (AnyDbConnection con = f.OpenConnection())
{
con.Execute("drop database if exists qb_test");
con.Execute("create database qb_test");
}
}
if (_factory.Provider == DatabaseProvider.SqLite)
{
Microsoft.Data.Sqlite.SqliteConnectionStringBuilder sb = new Microsoft.Data.Sqlite.SqliteConnectionStringBuilder(_factory.ConnectionString);
if (File.Exists(sb.DataSource))
{
File.Delete(sb.DataSource);
}
}
using (AnyDbConnection con = _factory.OpenConnection())
{
con.Execute(@"
CREATE TABLE customer (
id serial NOT NULL,
first_name varchar(50) NULL,
last_name varchar(50) NULL,
CONSTRAINT customer_pk PRIMARY KEY (id)
);");
for (int i = 0; i < 100; i++)
{
con.Execute($"insert into customer (first_name, last_name) values ('F_{i}', 'L_{i}')");
}
}
}
static void Main(string[] args)
{
Log.Logger = new LoggerConfiguration()
.WriteTo.Console()
.CreateLogger();
Log.Information("Start testing PostgreConfigurer");
var workDirectory = Path.Combine(Directory.GetParent(Directory.GetCurrentDirectory()).FullName, "postgres-oracle");
string username;
SecureString password;
Console.WriteLine("Username: "******"Enter password: "******"force");
if (isForceInstall || conf.IsInstalled)
{
if (conf.IsRunning)
{
conf.StopPostgres();
}
conf.InstallPostgreSql();
ps.InstallPostgis();
}
var sudoUser = new Npgsql.NpgsqlConnectionStringBuilder
{
Host = "127.0.0.1",
Database = "postgres",
Username = "******",
Password = "******"
};
if (!conf.IsRunning)
{
conf.RunPostgres();
}
using (var contex = new Contex())
{
bool isDbReady = !args.Any(x => x == "full_recreate");
//FIXME: Не работает
/*try {
* isDbReady &= contex.Database.CanConnect ();
* } catch {
* isDbReady = false;
* }*/
if (!isDbReady)
{
Log.Information("Need recreation of database");
using (var connection = new Npgsql.NpgsqlConnection(sudoUser.ToString()))
{
connection.Open();
connection
.DropDatabase("TestDatabase", true)
.CreateUser(new NpgsqlUtils.CreateUserOptions
{
Username = "******",
Password = "******"
})
.CreateDatabase(new NpgsqlUtils.CreateDatabaseOptions
{
Name = "TestDatabase",
Owner = "pilad",
});
contex.Database.EnsureCreated();
connection.ChangeDatabase("testdatabase");
connection.Execute(ps.ActivatePostgisSql());
//TODO: Is nesessary?
connection.Close();
}
Log.Information("Recreating full scheme contex...");
}
contex.Works.Add(new Work {
first = "123", second = "dsa"
});
contex.SaveChanges();
foreach (var work in contex.Works)
{
Log.Information("{first} {second}", work.first, work.second);
}
}
//conf.StopPostgres();
System.Threading.Thread.Sleep(1000);
}
public IActionResult CreateDefaultAsync()
{
var databaseUrl = "postgres://*****:*****@ec2-46-137-84-140.eu-west-1.compute.amazonaws.com:5432/d3cbgi217b7ap6";
var connectionString = "postgres://*****:*****@ec2-46-137-84-140.eu-west-1.compute.amazonaws.com:5432/d3cbgi217b7ap6";
var databaseUri = new Uri(databaseUrl);
var userInfo = databaseUri.UserInfo.Split(':');
if (TechnologyNewsDbContext.HerokuPostgreSqlConnectionString != null)
{
//"User ID =postgres;Password=1234;Server=localhost;Port=5432;Database=testDb; Integrated Security = true; Pooling = true;
//"DefaultConnection": "Server=localhost;Port=5432;User Id=username;Password=secret;Database=todos;"
string herokuConnectionString = $@"
Host={databaseUrl};
Port=<port>;
Username=<user>;
Password=<password>;
Database=<database>;
Pooling=true;
Use SSL Stream=True;
SSL Mode=Require;
TrustServerCertificate=True;
";
var builder = new Npgsql.NpgsqlConnectionStringBuilder
{
Host = databaseUri.Host,
Port = databaseUri.Port,
Username = userInfo[0],
Password = userInfo[1],
Database = databaseUri.LocalPath.TrimStart('/'),
SslMode = Npgsql.SslMode.Prefer,
TrustServerCertificate = true
};
string tempURL = "https://res.cloudinary.com/dfebwmqmq/image/upload/v1573550127/lmhzv9dpiq3ducxykow9.jpg";
string publicId = Regex.Replace(tempURL, $@"{(tempURL.Contains("https") ? "https" : "http")}:\/\/res\.cloudinary\.com\/.*\/image\/upload\/", "");
//publicId = Regex.Replace(tempURL, $@"{(tempURL.Contains("https") ? "https" : "http")}", "");
var locationInfoJson = _unitOfWork.GetLocationAsync().GetAwaiter().GetResult().ToString();
//var featuredPosts = _unitOfWork.PostRepository.context.Post.FromSqlRaw("SELECT * FROM {0} ORDER BY RANDOM() LIMIT {1};", "Post",2).ToList();
var tableNamesQuery = @"
SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE';
";
List <string> tableNamesQueryResult = new List <string>();
//var tableNames = _unitOfWork._context.Database.ExecuteSqlRaw(tableNamesQuery);
//var tableNames = _unitOfWork._context.Set<dynamic>().FromSqlRaw(tableNamesQuery);
using (var context = new TechnologyNewsDbContext())
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = tableNamesQuery;
context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
int count = result.FieldCount;
while (result.Read())
{
for (int i = 0; i < count; i++)
{
tableNamesQueryResult.Add(result.GetString(i));
}
}
//result.Close();
}
}
return(Ok(new
{
publicId,
tempURL,
databaseUrl,
connectionString,
builder = builder.ToString(),
tableNamesQueryResult
}));
}
return(Ok());
//var key = new byte[32];
//using (var generator = System.Security.Cryptography.RandomNumberGenerator.Create())
// generator.GetBytes(key);
//string apiSecret = Convert.ToBase64String(key);
//return Ok(apiSecret);
//await UnitOfWork.UserManager.CreateAsync(new ApplicationUser
//{
// ApiToken = "some",
// UserName = "******",
// Email = "*****@*****.**",
// EmailConfirmed = true,
// DateAdd = DateTime.Now,
// DateUpd = DateTime.Now,
//}, "admin");
//var returnObject = string.Format("{0}://{1}{2}", HttpContext.Request.Scheme, HttpContext.Request.Host, Url.Content("~"));
////return await UnitOfWork.AboutRepository.GetAllAsync();
////return Ok(await UnitOfWork.UserManager.Users.ToListAsync());
//return Ok(returnObject);
}
