引言

经过使用一段时间后对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）的原因。

测试效果

参考

• https://www.connectionstrings.com
• https://github.com/FastReports/FastReport/blob/master/FastReport.Base/Data/DataComponentBase.cs