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 static void TestPG()
{
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Host = "127.0.0.1";
csb.Database = "somedb";
csb.Port = 5432;
csb.IntegratedSecurity = false;
if (!csb.IntegratedSecurity)
{
csb.Username = "******";
csb.Password = "******";
}
csb.SslMode = Npgsql.SslMode.Prefer;
csb.Pooling = true;
csb.MinPoolSize = 1;
csb.MaxPoolSize = 5;
csb.PersistSecurityInfo = false;
csb.ApplicationName = "MyApplication";
csb.Timeout = 30;
csb.CommandTimeout = 30;
csb.Encoding = System.Text.Encoding.UTF8.WebName;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(csb.ConnectionString))
{
string foo = conn.ServerVersion;
}
}
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 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 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 static System.Data.DataTable GetDataTable(string strSQL)
{
System.Data.DataTable dt = new System.Data.DataTable();
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Host = "127.0.0.1";
if (StringComparer.OrdinalIgnoreCase.Equals(Environment.MachineName, "COR-W81-101"))
{
csb.Database = "testdb"; //"blogz";
}
else
{
csb.Database = "sqlfs";
}
csb.IntegratedSecurity = false;
if (!csb.IntegratedSecurity)
{
csb.UserName = "******";
csb.Password = "******";
} // End if(!csb.IntegratedSecurity)
csb.ApplicationName = "Web Report Designer";
using (Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter(strSQL, csb.ConnectionString))
{
da.Fill(dt);
} // End Using da
return(dt);
} // End Function GetDataTable
public static QueryComposer.Adapters.ESP.ESPModelAdapter CreateESPModelAdapterAdapter(string connectionString)
{
var connectionStringBuilder = new Npgsql.NpgsqlConnectionStringBuilder(connectionString);
var adapterSettings = new Dictionary <string, object>()
{
{ "Server", connectionStringBuilder.Host },
{ "Port", connectionStringBuilder.Port },
//{"UserID", connectionStringBuilder.UserName },
//{"Password", System.Text.Encoding.UTF8.GetString(connectionStringBuilder.PasswordAsByteArray) },
{ "UserID", connectionStringBuilder.Username },
{ "Password", connectionStringBuilder.Password },
{ "Database", connectionStringBuilder.Database },
{ "ConnectionTimeout", connectionStringBuilder.Timeout },
{ "CommandTimeout", connectionStringBuilder.CommandTimeout },
{ "DataProvider", Lpp.Dns.DataMart.Model.Settings.SQLProvider.PostgreSQL.ToString() }
};
var adapter = new QueryComposer.Adapters.ESP.ESPModelAdapter(new RequestMetadata
{
CreatedOn = DateTime.UtcNow,
MSRequestID = "Unit Test Request"
});
adapter.Initialize(adapterSettings, Guid.NewGuid().ToString("D"));
return(adapter);
}
// 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";
});
}
// AnySqlWebAdmin.Code.Trash.PgIntegratedSecurityTest.TestNpgSql();
public static void TestNpgSql()
{
var a = new { Test = 5, Result = "Success" };
var b = new { Test = 3, Result = "foo" };
var c = new { Test1 = 3, Result = "foo" };
System.Type t = a.GetType();
System.Console.WriteLine(t);
if (object.ReferenceEquals(a.GetType(), b.GetType()))
{
System.Console.WriteLine("Two anony = equal");
}
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Database = "osm_test"; // must be set
csb.Host = "localhost";
// csb.Host = "127.0.0.1"; // doesn't work
// csb.Host = System.Environment.MachineName; // doesn't work
csb.Port = 5432;
csb.IntegratedSecurity = true;
csb.Username = System.Environment.UserName; // Works when user exists
// csb.Username = "******"; // works as root
bool ret = false;
string sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'; ";
using (System.Data.Common.DbConnection conn = Npgsql.NpgsqlFactory.Instance.CreateConnection())
{
conn.ConnectionString = csb.ConnectionString;
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
using (System.Data.Common.DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
int?foo = (int?)cmd.ExecuteScalar();
if (foo.HasValue)
{
ret = foo.Value != 0;
}
} // End Using cmd
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Using conn
System.Console.WriteLine(ret);
} // End Sub TestNpgSql
public static string GetConnectionString()
{
if (fac is System.Data.SqlClient.SqlClientFactory)
{
System.Data.SqlClient.SqlConnectionStringBuilder sb = new System.Data.SqlClient.SqlConnectionStringBuilder();
sb.DataSource = Environment.MachineName;
sb.InitialCatalog = "COR_Basic_Demo";
sb.DataSource = "CORDB2014";
sb.InitialCatalog = "COR_Basic_Post";
sb.IntegratedSecurity = true;
sb.MultipleActiveResultSets = true;
sb.PersistSecurityInfo = false;
sb.Pooling = true;
sb.PacketSize = 4096;
sb.ApplicationName = "ODataTest";
return sb.ConnectionString;
}
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Host = "127.0.0.1";
csb.Port = 5432;
csb.Database = "blogz";
// csb.IntegratedSecurity = false;
// CREATE ROLE alibaba LOGIN PASSWORD 'OpenSesame' SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
csb.UserName = "******";
csb.Password = "******";
csb.CommandTimeout = 300;
csb.ApplicationName = "ODataTest";
return csb.ConnectionString;
}
/// <summary>
/// Используется для работы с историей
/// </summary>
/// <param name="table">Таблица для которой загружается история</param>
/// <param name="idObject">Идентификатор объекта для которой загружается история</param>
/// <param name="loadFromHistory">Использовать для загрузки атрибутов таблицу истории</param>
/// <param name="idHistoryTable">Идентификатор таблицы истории из которой загружаются атрибуты</param>
/// <param name="idHistoryObject">Идентификатор объекта в таблице истории</param>
/// <param name="isReadOnly">Открываются ли атрибуты только для чтения</param>
/// <param name="IsDebug">Запускается ли работа с атрибутами в режиме отладки</param>
public PgAttributesListVM(PgM.PgTableBaseM table, object idObject, bool loadFromHistory, int?idHistoryTable = null, int?idHistoryObject = null, bool isReadOnly = false, bool IsDebug = false, bool loadStyle = true)
{
LoadStyle = loadStyle;
if (loadFromHistory)
{
LoadFromHistory = loadFromHistory;
IdHistoryTable = (int)idHistoryTable;
IdHistoryObject = (int)idHistoryObject;
}
_isDebug = IsDebug;
_connect = ((PgVM.PgDataRepositoryVM)table.Source).Connect;
if (isReadOnly || table.IsReadOnly || !table.CanWrite)
{
_isReadOnly = true;
}
_table = table;
if (!String.IsNullOrEmpty(_table.GeomField))
{
_geomAttribute = new PgAtM.PgAttributeM(this, (PgM.PgFieldM)_table.Fields.First(p => p.Name == _table.GeomField));
}
_pkAttribute = new PgAtM.PgAttributeM(this, (PgM.PgFieldM)_table.PrimaryKeyField, idObject);
if (idObject == null)
{
_isNew = true;
}
_attributes = new ObservableCollection <PgAtM.PgAttributeM>();
}
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();
}
public static string GetConnectionString()
{
if (fac is System.Data.SqlClient.SqlClientFactory)
{
System.Data.SqlClient.SqlConnectionStringBuilder sb = new System.Data.SqlClient.SqlConnectionStringBuilder();
sb.DataSource = Environment.MachineName;
sb.InitialCatalog = "COR_Basic_Demo";
sb.DataSource = "CORDB2014";
sb.InitialCatalog = "COR_Basic_Post";
sb.IntegratedSecurity = true;
sb.MultipleActiveResultSets = true;
sb.PersistSecurityInfo = false;
sb.Pooling = true;
sb.PacketSize = 4096;
sb.ApplicationName = "ODataTest";
return(sb.ConnectionString);
}
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Host = "127.0.0.1";
csb.Port = 5432;
csb.Database = "blogz";
// csb.IntegratedSecurity = false;
// CREATE ROLE alibaba LOGIN PASSWORD 'OpenSesame' SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
csb.UserName = "******";
csb.Password = "******";
csb.CommandTimeout = 300;
csb.ApplicationName = "ODataTest";
return(csb.ConnectionString);
}
public PostgreSqlSimpleConnectionStringBuilder([NotNull] Npgsql.NpgsqlConnectionStringBuilder connectionStringBuilder)
{
if (connectionStringBuilder == null)
{
throw new ArgumentNullException("connectionStringBuilder");
}
_connectionStringBuilder = connectionStringBuilder;
}
/// <summary>
/// Init
/// </summary>
/// <param name="schema">Schema Name</param>
/// <param name="connection">Connection String</param>
public SchemaModel(string schema, string connection)
{
this.Connection = connection;
this.Name = schema;
var builder = new Npgsql.NpgsqlConnectionStringBuilder(connection);
this.Database = builder.Database;
}
public PgAttributesStyleVM(PgAtVM.PgAttributesVM attributeVM)
{
_attributeVM = attributeVM;
_isDebug = attributeVM.IsDebug;
_connect = attributeVM.Connect;
_isReadOnly = attributeVM.IsReadOnly;
_table = attributeVM.Table;
_pkAttribute = attributeVM.AttributesListVM.PkAttribute;
_style = new PgM.PgStyleObjectM(_table, _pkAttribute.Value);
}
/// <summary>
/// Performs an install of a database
/// </summary>
public void Install(InstallSetup setup)
{
if (setup.InstallStatus == InstallStatusConstants.Create)
{
//Connection cannot reference an existing database
if (DatabaseServer.TestConnectionString(setup.ConnectionString))
{
throw new Exception("The connection string references an existing database.");
}
//The new database name must be specified
if (string.IsNullOrEmpty(setup.NewDatabaseName))
{
throw new Exception("A new database name was not specified.");
}
//The connection string and the new database name must be the same
var builder = new Npgsql.NpgsqlConnectionStringBuilder(setup.ConnectionString);
if (builder.Database.ToLower() != setup.NewDatabaseName.ToLower())
{
throw new Exception("A new database name does not match the specified connection string.");
}
DatabaseServer.CreateDatabase(setup);
}
else if (setup.InstallStatus == InstallStatusConstants.Upgrade)
{
//The connection string must reference an existing database
if (!DatabaseServer.TestConnectionString(setup.ConnectionString))
{
throw new Exception("The connection string does not reference a valid database.");
}
}
try
{
UpgradeInstaller.UpgradeDatabase(setup);
}
catch (InvalidSQLException ex)
{
var sb = new StringBuilder();
sb.AppendLine();
sb.AppendLine("BEGIN ERROR SQL");
sb.AppendLine(ex.SQL);
sb.AppendLine("END ERROR SQL");
sb.AppendLine();
System.Diagnostics.Trace.WriteLine(sb.ToString());
UpgradeInstaller.LogError(ex, sb.ToString());
throw;
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="connectionString"></param>
public BaseModel(string connectionString)
: this()
{
var builder = new Npgsql.NpgsqlConnectionStringBuilder(connectionString);
this.Database = builder.Database;
this.Name = builder.ApplicationName;
this.Password = builder.Password;
this.Server = builder.Host;
this.User = builder.Username;
}
public cPostGreSQL(string strConnectionString)
{
//this.m_DatabaseConfiguration = dbcDBconfig;
this.m_dbtDBtype = DataBaseEngine_t.PostGreSQL;
this.m_providerFactory = this.GetFactory();
this.m_dictScriptTemplates = GetSQLtemplates();
this.m_dblDBversion = 8.5;
this.m_ConnectionString = new Npgsql.NpgsqlConnectionStringBuilder(strConnectionString);
this.m_SqlConnection = new Npgsql.NpgsqlConnection(strConnectionString);
} // End Constructor 2
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 static string ConnectionString()
{
Npgsql.NpgsqlConnectionStringBuilder connectionStringBuilder = new Npgsql.NpgsqlConnectionStringBuilder();
connectionStringBuilder.Host = MixERP.Net.Common.Conversion.TryCastString(System.Configuration.ConfigurationManager.AppSettings["Server"]);
connectionStringBuilder.Database = MixERP.Net.Common.Conversion.TryCastString(System.Configuration.ConfigurationManager.AppSettings["Database"]);
connectionStringBuilder.UserName = MixERP.Net.Common.Conversion.TryCastString(System.Configuration.ConfigurationManager.AppSettings["UserId"]);
connectionStringBuilder.Password = MixERP.Net.Common.Conversion.TryCastString(System.Configuration.ConfigurationManager.AppSettings["Password"]);
connectionStringBuilder.Timeout = 600;
return(connectionStringBuilder.ConnectionString);
}
public static string ConnectionString()
{
Npgsql.NpgsqlConnectionStringBuilder connectionStringBuilder = new Npgsql.NpgsqlConnectionStringBuilder();
connectionStringBuilder.Host = MixERP.Net.Common.Conversion.TryCastString(System.Configuration.ConfigurationManager.AppSettings["Server"]);
connectionStringBuilder.Database = MixERP.Net.Common.Conversion.TryCastString(System.Configuration.ConfigurationManager.AppSettings["Database"]);
connectionStringBuilder.UserName = MixERP.Net.Common.Conversion.TryCastString(System.Configuration.ConfigurationManager.AppSettings["UserId"]);
connectionStringBuilder.Password = MixERP.Net.Common.Conversion.TryCastString(System.Configuration.ConfigurationManager.AppSettings["Password"]);
connectionStringBuilder.Timeout = 600;
return connectionStringBuilder.ConnectionString;
}
public PostgreSQL()
{
Npgsql.NpgsqlConnectionStringBuilder conn = new Npgsql.NpgsqlConnectionStringBuilder();
conn.Database = Configuration.Postgres.DBNM;
conn.Host = Configuration.Postgres.Host;
conn.UserName = Configuration.Postgres.User;
conn.Port = Configuration.Postgres.Port;
conn.Password = Configuration.Postgres.Pass;
this.connection = new Npgsql.NpgsqlConnection(conn.ConnectionString);
//this.connection.Open();
}
public PostgreSQL()
{
Npgsql.NpgsqlConnectionStringBuilder conn = new Npgsql.NpgsqlConnectionStringBuilder();
conn.Database = Configuration.Postgres.DBNM;
conn.Host = Configuration.Postgres.Host;
conn.UserName = Configuration.Postgres.User;
conn.Port = Configuration.Postgres.Port;
conn.Password = Configuration.Postgres.Pass;
this.connection = new Npgsql.NpgsqlConnection(conn.ConnectionString);
//this.connection.Open();
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
var builder = new Npgsql.NpgsqlConnectionStringBuilder("Host=localhost;Port=5432;Database=db_travell;User Id=postgres;Password=superadmin");
services.AddDbContext <ApplicationContext>(options => options.UseNpgsql(builder.ConnectionString));
services.AddScoped <IStateRepository, StateRepository>();
services.AddScoped <IAnswerRepository, AnswerRepository>();
}
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();
}
public static Npgsql.NpgsqlConnection ConnectToPostGRES(DBConnectionDetails dbConnectionInfo)
{
Npgsql.NpgsqlConnectionStringBuilder _connString = new Npgsql.NpgsqlConnectionStringBuilder();
_connString.Username = dbConnectionInfo.DBUsername;
_connString.Password = dbConnectionInfo.DBPassword;
_connString.Database = dbConnectionInfo.DBCatalog;
_connString.Host = dbConnectionInfo.DBServer;
_connString.Port = dbConnectionInfo.DBPort;
Npgsql.NpgsqlConnection _dbConnection = new Npgsql.NpgsqlConnection(_connString.ConnectionString);
_dbConnection.Open();
return(_dbConnection);
}
public static string GetCS()
{
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Database = "blogz";
csb.Host = "localhost";
csb.Port = 5432;
csb.IntegratedSecurity = true;
csb.Username = "******";
// csb.Password = "";
return(csb.ConnectionString);
}
public static string AppendApplicationName(string connectionString)
{
var builder = new Npgsql.NpgsqlConnectionStringBuilder(connectionString);
if (string.IsNullOrEmpty(builder.ApplicationName))
{
var @namespace = typeof(ConnectionStringExtensions).Namespace;
builder.ApplicationName = @namespace.Remove(@namespace.LastIndexOf('.'));
}
return(builder.ConnectionString);
}
/// <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 PgAttributeFilesVM(PgAtVM.PgAttributesVM attributeVM)
{
_files = new MTObservableCollection <PgAtM.PgAttributeFileM>();
_filesSelected = new ObservableCollection <PgAtM.PgAttributeFileM>();
if (attributeVM.Table.FileInfo == null)
{
throw new ArgumentNullException("attributeVM.Table.FileInfo");
}
_attributeVM = attributeVM;
_isDebug = AttributeVM.IsDebug;
_connect = attributeVM.Connect;
_isReadOnly = attributeVM.IsReadOnly;
}
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 PgAttributesVM(AbsM.ITableBaseM table, object idObject, bool isReadOnly = false, bool isDebug = false, String wkt = null)
{
var pgTable = table as PgM.PgTableBaseM;
if (pgTable == null)
{
throw new ArgumentNullException("Нет ссылки на таблицу");
}
else if (pgTable.PrimaryKeyField == null)
{
throw new ArgumentNullException("В таблице нет первичного ключа");
}
_isDebug = isDebug;
_connect = ((PgVM.PgDataRepositoryVM)pgTable.Source).Connect;
_table = pgTable;
_source = pgTable.Source as PgVM.PgDataRepositoryVM;
// Проверка прав на объект с атрибутами
if (isReadOnly || pgTable.IsReadOnly || !pgTable.CanWrite)
{
_isReadOnly = true;
}
_attributesListVM = new PgAtVM.PgAttributesListVM(this, idObject);
if (_table.FileInfo != null)
{
_filesVM = new PgAtVM.PgAttributeFilesVM(this);
}
if (_table.IsMapStyle && idObject != null)
{
_styleVM = new PgAttributesStyleVM(this);
}
if (_table.Type == AbsM.ETableType.MapLayer || _table.Type == AbsM.ETableType.View)
{
//_pgGeometryVM = new PgAttributesGeomVM(this, wkt);
_pgGeometryVM = new PgAttributesGeomVM(_table.Id, wkt, false, (int?)_attributesListVM.PkAttribute.Value, (_table.Source as PgVM.PgDataRepositoryVM).Connect);
}
Title = String.Format("Объект: [id: {0}]; Таблица: \"{1}\"; Источник: \"{3}@{4}:{5} ({6})\"; Тип: \"{2}\"",
idObject ?? "null",
_table.Text,
_table.Source.Type,
_source.Connect.Database,
_source.Connect.Host,
_source.Connect.Port,
_source.CurrentUser.NameFull);
}
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=
}
private void ConnectButtonClick(object sender, RoutedEventArgs e)
{
if (!_isConnected)
{
var pg_con = new Npgsql.NpgsqlConnectionStringBuilder();
pg_con.Host = "127.0.0.1";
pg_con.UserName = "******";
pg_con.Password = "******";
pg_con.Database = "bbrother";
_pgConnection = new Npgsql.NpgsqlConnection(pg_con.ConnectionString);
_pgConnection.Open();
_isConnected = true;
ConnectButton.Content = "Diconnect";
}
else
{
_isConnected = false;
_pgConnection.Close();
ConnectButton.Content = "Connect to DB...";
}
}
/// <summary>
/// Configura a string de conexão de acordo com o banco de dados definido no arquivo de configuração RegLog.config.
/// </summary>
private static void ConfigConnection()
{
var path = Path.GetDirectoryName(Assembly.GetAssembly(typeof(LogModel)).Location);
var xmlConfig = new XmlTextReader(path + "\\RegLog.config");
while (xmlConfig.Read())
{
if (xmlConfig.NodeType != XmlNodeType.Element) continue;
var elementos = new string[] {"host","port", "database", "databasename", "connectiontimeout","connectionlifetime", "user", "password"};
if (!elementos.Contains(xmlConfig.Name)) continue;
_host = xmlConfig.Name.ToLower().Equals("host") ? xmlConfig.ReadString() : _host;
_port = xmlConfig.Name.ToLower().Equals("port") && !string.IsNullOrWhiteSpace(xmlConfig.ReadString()) ? Convert.ToInt32(xmlConfig.ReadString()) : _port;
_database = xmlConfig.Name.ToLower().Equals("database") ? Convert.ToInt32(xmlConfig.ReadString()) : _database;
_dbName = xmlConfig.Name.ToLower().Equals("databasename") ? xmlConfig.ReadString() : _dbName;
_timeOut = xmlConfig.Name.ToLower().Equals("connectiontimeout") ? Convert.ToInt32(xmlConfig.ReadString()) : _timeOut;
_lifeTime = xmlConfig.Name.ToLower().Equals("connectionlifetime") ? Convert.ToInt32(xmlConfig.ReadString()) : _lifeTime;
_user = xmlConfig.Name.ToLower().Equals("user") ? xmlConfig.ReadString() : _user;
_password = xmlConfig.Name.ToLower().Equals("password") ? xmlConfig.ReadString() : _password;
}
switch (_database)
{
case 0: //SQLITE
var sqlite = new SQLiteConnection(Path.Combine(_host, _dbName));
_connection = sqlite.ConnectionString;
break;
case 1: //MSSQL
case 11:
var mssql = new System.Data.SqlClient.SqlConnectionStringBuilder
{
ConnectTimeout = _timeOut,
DataSource = _host,
Enlist = true,
InitialCatalog = _dbName,
UserID = _user,
Password = _password,
IntegratedSecurity = false,
LoadBalanceTimeout = _lifeTime,
};
_connection = mssql.ConnectionString;
break;
case 19: //MSSQL CE
var sqlce = new SqlCeConnectionStringBuilder
{
DataSource = Path.Combine(_host, _dbName),
Enlist = true,
Password = _password,
FileAccessRetryTimeout = _timeOut,
};
_connection = sqlce.ConnectionString;
break;
case 2: //MYSQL
var mysql = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder
{
Database = _dbName,
Server = _host,
Password = _password,
UserID = _user,
ConnectionLifeTime = (uint)_lifeTime,
ConnectionTimeout = (uint)_timeOut,
IntegratedSecurity = false,
AutoEnlist = true
};
if(_port > 0)
mysql.Port = (uint)_port;
_connection = mysql.ConnectionString;
break;
case 3: //POSTGRESQL
var pgsql = new Npgsql.NpgsqlConnectionStringBuilder
{
Database = _dbName,
Host = _host,
Password = _password,
UserName = _user,
ConnectionLifeTime = _lifeTime,
Timeout = _timeOut,
IntegratedSecurity = false,
Enlist = true
};
if (_port > 0)
pgsql.Port = _port;
_connection = pgsql.ConnectionString;
break;
case 4: //DB2
//var db2 =
// DB2Configuration.Standard.ConnectionString(
// c => c.Database(_dbName).Server(_host).Username(_user).Password(_password));
break;
case 5: //FIREBIRD
var firebird = new FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder
{
Database = _dbName,
Password = _password,
ConnectionLifeTime = _lifeTime,
Enlist = true,
UserID = _user,
DataSource = _host,
ConnectionTimeout = _timeOut
};
if (_port > 0)
firebird.Port = _port;
_connection = firebird.ConnectionString;
break;
case 6: //ORACLE 9
case 61: //ORACLE 10
var oracle = new Oracle.DataAccess.Client.OracleConnectionStringBuilder
{
Password = _password,
ConnectionLifeTime = _lifeTime,
Enlist = "true",
UserID = _user,
DataSource = _dbName,
ConnectionTimeout = _timeOut
};
oracle["Server"] = _host;
_connection = oracle.ConnectionString;
break;
default:
_connection = "";
break;
}
}
