引言
经过使用一段时间后对FastReport
的多数据源产生了兴趣,主要是想看下如何对数据库默认的对象进行获取的,以前自行做多数据库的处理都是根据自己写Sql
进行操作,突然之间发现了内部使用的GetSchema()
方法,当时有点懵,还是说明自己对Ado.Net不够深入。
UML类结构
测试代码
在测试的时候使用了roslynpad工具进行测试,其实还有另外一款工具LinqPad。
代码片长,可以针对性的进行查看,也可以查看github。
#r "nuget:Npgsql/3.2.7"
#r "nuget:MySql.Data/6.10.7"
#r "nuget:MongoDB.Driver/2.5.0"
// Framwork
#r "nuget:Oracle.ManagedDataAccess/19.11.0"
// Net Core
#r "nuget:Microsoft.Data.SqlClient/2.1.2"
#r "nuget:Oracle.ManagedDataAccess.Core/2.12.0-beta3"
// <.net core 3.1使用
using System.Data.SqlClient;
// >.net core 3.1使用
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using Npgsql;
using MongoDB.Driver;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using MongoDB.Bson;
#region 连接字符串测试
DataConnectionBase ms = new MsSqlDataConnection();
var str1 = ms.GetConnectionStringWithLoginInfo("sa", "123");
ms.ConnectionString = str1;
str1.Dump("MsSqlDataConnection");
DataConnectionBase mysql = new MySqlDataConnection();
var str2 = mysql.GetConnectionStringWithLoginInfo("root", "123456");
mysql.ConnectionString = str2;
str2.Dump("MySqlDataConnection");
DataConnectionBase oracle = new OracleDataConnection();
var str3 = oracle.GetConnectionStringWithLoginInfo("cy", "123");
oracle.ConnectionString = str3;
str3.Dump("OracleDataConnection");
DataConnectionBase pgsql = new PostgresDataConnection();
var str4 = pgsql.GetConnectionStringWithLoginInfo("sa", "123");
pgsql.ConnectionString = str4;
str4.Dump("PostgresDataConnection");
DataConnectionBase mg = new MongoDBDataConnection();
var str5 = mg.GetConnectionStringWithLoginInfo("root", "123456");
mg.ConnectionString = str5;
str5.Dump("MongoDBDataConnection");
#endregion
#region 获取数据库对象测试
ms.GetTableNames().Dump("SqlServer TableNames");
mysql.GetTableNames().Dump("MySql TableNames");
oracle.GetTableNames().Dump("Oracle TableNames");
mg.GetTableNames().Dump("MongoDb TableNames");
pgsql.GetTableNames().Dump("PostGreSql TableNames");
#endregion
public abstract partial class DataConnectionBase
{
public abstract string ConnectionString
{
get;
set;
}
public abstract string GetConnectionStringWithLoginInfo(string userName, string password);
public DbConnection GetConnection()
{
Type connectionType = GetConnectionType();
if (connectionType != null)
{
DbConnection connection = GetDefaultConnection();
if (connection != null)
return connection;
// create a new connection object
connection = Activator.CreateInstance(connectionType) as DbConnection;
connection.ConnectionString = ConnectionString;
return connection;
}
return null;
}
private DbConnection GetDefaultConnection()
{
return null;
}
public virtual Type GetConnectionType()
{
return null;
}
private void GetDBObjectNames(string name, List<string> list)
{
DataTable schema = null;
DbConnection conn = GetConnection();
try
{
OpenConnection(conn);
schema = conn.GetSchema("Tables", new string[] { null, null, null, name });
}
finally
{
DisposeConnection(conn);
}
foreach (DataRow row in schema.Rows)
{
list.Add(row["TABLE_NAME"].ToString());
}
}
public virtual string[] GetTableNames()
{
List<string> list = new List<string>();
GetDBObjectNames("TABLE", list);
GetDBObjectNames("VIEW", list);
return list.ToArray();
}
public void OpenConnection(DbConnection connection)
{
connection.ConnectionString = ConnectionString;
if (connection.State == ConnectionState.Open)
return;
connection.Open();
}
public void DisposeConnection(DbConnection connection)
{
if (ShouldNotDispose(connection))
return;
if (connection != null)
connection.Dispose();
}
private bool ShouldNotDispose(DbConnection connection)
{
return false;
}
}
// MsSqlDataConnection
public partial class MsSqlDataConnection : DataConnectionBase
{
public override string ConnectionString
{
get;
set;
} = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
public override string GetConnectionStringWithLoginInfo(string userName, string password)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.IntegratedSecurity = false;
builder.UserID = userName;
builder.Password = password;
builder.DataSource = "127.0.0.1";
builder.InitialCatalog = "VoloDocs";
return builder.ToString();
}
private void GetDBObjectNames(string name, List<string> list)
{
DataTable schema = null;
DbConnection conn = GetConnection();
try
{
OpenConnection(conn);
schema = conn.GetSchema("Tables", new string[] { null, null, null, name });
}
finally
{
DisposeConnection(conn);
}
foreach (DataRow row in schema.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
string schemaName = row["TABLE_SCHEMA"].ToString();
if (String.Compare(schemaName, "dbo") == 0)
list.Add(tableName);
else
list.Add(schemaName + ".\"" + tableName + "\"");
}
}
/// <inheritdoc/>
public override string[] GetTableNames()
{
List<string> list = new List<string>();
GetDBObjectNames("BASE TABLE", list);
GetDBObjectNames("VIEW", list);
return list.ToArray();
}
/// <inheritdoc/>
public override Type GetConnectionType()
{
return typeof(SqlConnection);
}
}
public partial class MySqlDataConnection : DataConnectionBase
{
public override string ConnectionString
{
get;
set;
} = "Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";
public override string GetConnectionStringWithLoginInfo(string userName, string password)
{
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder(ConnectionString);
builder.UserID = userName;
builder.Password = password;
builder.Server = "192.168.3.220";
builder.Port = 3306;
builder.Database = "Test";
builder.CharacterSet = "utf8";
return builder.ToString();
}
private void GetDBObjectNames(string name, List<string> list)
{
DataTable schema = null;
string databaseName = "";
DbConnection connection = GetConnection();
try
{
OpenConnection(connection);
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder(ConnectionString);
schema = connection.GetSchema(name);
databaseName = builder.Database;
}
finally
{
DisposeConnection(connection);
}
foreach (DataRow row in schema.Rows)
{
if (String.IsNullOrEmpty(databaseName) || String.Compare(row["TABLE_SCHEMA"].ToString(), databaseName) == 0)
list.Add(row["TABLE_NAME"].ToString());
}
}
public override string[] GetTableNames()
{
List<string> list = new List<string>();
GetDBObjectNames("Tables", list);
GetDBObjectNames("Views", list);
return list.ToArray();
}
public override Type GetConnectionType()
{
return typeof(MySqlConnection);
}
}
public partial class OracleDataConnection : DataConnectionBase
{
public override string ConnectionString
{
get;
set;
} = "Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;";
public override string GetConnectionStringWithLoginInfo(string userName, string password)
{
OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder(ConnectionString);
builder.UserID = userName;
builder.Password = password;
builder.TnsAdmin = @"D:\Program Files\instantclient_11_2\network\admin";
builder.DataSource = "helowin";
return builder.ToString();
}
private void GetDBObjectNames(string name, string columnName, List<string> list)
{
DataTable schema = null;
DbConnection connection = GetConnection();
try
{
OpenConnection(connection);
OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder(connection.ConnectionString);
schema = connection.GetSchema(name, new string[] { builder.UserID.ToUpper(), null });
}
finally
{
DisposeConnection(connection);
}
foreach (DataRow row in schema.Rows)
{
string tableName = row[columnName].ToString();
string schemaName = row["OWNER"].ToString();
if (String.Compare(schemaName, "SYSTEM") == 0)
list.Add(tableName);
else
list.Add(schemaName + ".\"" + tableName + "\"");
}
}
public override string[] GetTableNames()
{
List<string> list = new List<string>();
GetDBObjectNames("Tables", "TABLE_NAME", list);
GetDBObjectNames("Views", "VIEW_NAME", list);
return list.ToArray();
}
public override Type GetConnectionType()
{
return typeof(OracleConnection);
}
}
public partial class PostgresDataConnection : DataConnectionBase
{
public override string ConnectionString
{
get;
set;
} = "User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;Pooling=true;";
public static bool EnableSystemSchemas { get; set; }
public override string GetConnectionStringWithLoginInfo(string userName, string password)
{
NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder(ConnectionString);
builder.Username = userName;
builder.Password = password;
return builder.ToString();
}
private void GetDBObjectNames(string name, List<string> list)
{
DataTable schema = null;
DbConnection connection = GetConnection();
try
{
OpenConnection(connection);
schema = connection.GetSchema("Tables", new string[] { null, "", null, name }); //not only public
}
finally
{
DisposeConnection(connection);
}
foreach (DataRow row in schema.Rows)
{
string schemaName = row["TABLE_SCHEMA"].ToString();
if (!EnableSystemSchemas && (schemaName == "pg_catalog" || schemaName == "information_schema"))
continue;
list.Add(schemaName + "." + "\"" + row["TABLE_NAME"].ToString() + "\"");
}
}
public override string[] GetTableNames()
{
List<string> list = new List<string>();
GetDBObjectNames("BASE TABLE", list);
GetDBObjectNames("VIEW", list);
if (list.Count == 0)
{
string selectCommand =
"SELECT n.nspname as \"Schema\", " +
"c.relname as \"Name\", " +
"CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as \"Type\", " +
"pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\" " +
"FROM pg_catalog.pg_class c " +
"LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " +
"WHERE c.relkind IN ('r', 'v', '') " +
"AND n.nspname <> 'pg_catalog' " +
"AND n.nspname <> 'information_schema' " +
"AND n.nspname !~'^pg_toast' " +
"AND pg_catalog.pg_table_is_visible(c.oid) " +
"ORDER BY 1,2; ";
DataSet dataset = new DataSet();
DbConnection connection = GetConnection();
try
{
OpenConnection(connection);
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(selectCommand, connection as NpgsqlConnection);
adapter.Fill(dataset);
if (dataset.Tables.Count > 0)
foreach (DataRow row in dataset.Tables[0].Rows)
{
list.Add(row["Name"].ToString());
}
}
finally
{
DisposeConnection(connection);
}
}
return list.ToArray();
}
public override Type GetConnectionType()
{
return typeof(NpgsqlConnection);
}
}
public partial class MongoDBDataConnection : DataConnectionBase
{
public static string dbName = "";
public override string ConnectionString
{
get;
set;
} = "mongodb://root:123456@localhost:27017";
public override string GetConnectionStringWithLoginInfo(string userName, string password)
{
MongoUrlBuilder builder = new MongoUrlBuilder(ConnectionString);
builder.Username = userName;
builder.Password = password;
builder.DatabaseName = "Test";
builder.AuthenticationMechanism = "admin";
builder.ReadPreference = new ReadPreference(ReadPreferenceMode.Primary);
builder.ApplicationName = "MongoDB Compass";
builder.UseSsl = false;
return builder.ToString();
}
public override string[] GetTableNames()
{
List<string> list = new List<string>();
MongoClient client = new MongoClient(ConnectionString);
IMongoDatabase db = client.GetDatabase(dbName);
IAsyncCursor<BsonDocument> collections = db.ListCollections();
foreach (var item in collections.ToList<BsonDocument>())
{
list.Add(item[0].ToString());
}
return list.ToArray();
}
}
解释说明
这里要对其中需要注意的几点进行说明。
使用Oracle时采用Tns的连接方式需要设置OracleConnectionStringBuilder.TnsAdmin
属性,虽然设置了环境变量也不知道为什么不行,可能我是使用的简易客户端(instantclient_11_2)的原因。