Dapper使用getschema獲取表信息

本文旨在介紹Dapper中使用getschema獲取表信息的方法和注意事項。

一、獲取某張表的所有列信息

使用Dapper獲取某張表信息，可以使用 `IDbConnection.GetSchema()` 方法。這個方法通過填寫不同的參數，可以獲取數據庫架構信息、表信息、視圖信息以及約束信息等。

針對獲取一個表的所有列信息，可以通過以下代碼實現：

    var connection = new SqlConnection("Data Source=(local);Initial Catalog=TestDb;Integrated Security=True");

    var columns = new List();

    connection.Open();

    var sqlCommand = connection.CreateCommand();
    sqlCommand.CommandText = "select * from TestTable where 1=0";
    var dataReader = sqlCommand.ExecuteReader();

    var schemaTable = dataReader.GetSchemaTable();
    foreach (DataRow row in schemaTable.Rows)
    {
        columns.Add(row.Field("ColumnName"));
    }
    dataReader.Close();

    connection.Close();

代碼解釋：

1. 創建SqlConnection對象，連接數據庫；

2. 創建一個List集合存儲列名；

3. 執行一條查詢語句，但是不返回任何數據，這樣就可以獲取該表的列名；

4. 使用GetSchemaTable()方法獲取數據；

5. 遍歷取到的表中的所有行，將行中的ColumnName存儲到之前創建的List集合當中；

6. 關閉數據庫連接。

通過這個方法可以得到被檢索表的所有列信息。

二、獲取某張表的主鍵信息

獲取指定表的主鍵，可以通過以下代碼實現：

    var connection = new SqlConnection("Data Source=(local);Initial Catalog=TestDb;Integrated Security=True");
    var primaryKeyColumns = new List();

    connection.Open();
    var schema = connection.GetSchema("Indexes");
    var table = schema.Rows.Cast<DataRow>().Where(p => p["TABLE_NAME"].ToString().Equals("TestTable")).FirstOrDefault();

    if(table!=null)
    {
        var indexName = (string)table["INDEX_NAME"];
        var pkColumns = schema.Rows.Cast>DataRow<>()
                            .Where(p => p["INDEX_NAME"].ToString() == indexName && ((short)p["TYPE"] == 1 || (short)p["TYPE"] == 2))
                            .OrderBy(p => (short)p["TYPE"]).ThenBy(p => (int)p["ORDINAL_POSITION"]);

        primaryKeyColumns.AddRange(pkColumns.Select(p => (string)p["COLUMN_NAME"]));
    }

    connection.Close();

代碼解釋：

1. 創建SqlConnection對象，連接數據庫；

2. 創建一個List集合存儲主鍵；

3. 執行GetSchema(“Indexes”)方法獲取索引信息；

4. 通過Linq表達式獲取指定表的索引信息，並且包含其中的所有第一順位索引、第二順位索引，主鍵需要表現為數字1或數字2，而非唯一性約束所代表的數字3；

5. 將索引的列名存入List集合，並且關閉數據庫連接。

三、獲取某張表的唯一性約束信息

一些數據庫表的列值是唯一的，為了保證這個唯一性，可以在表的列上添加唯一性約束。Dapper可以獲取這些唯一性約束信息。以下是獲取該信息的代碼：

    var connection = new SqlConnection("Data Source=(local);Initial Catalog=TestDb;Integrated Security=True");
    var uniqueColumns = new List<string>();

    connection.Open();
    var schema = connection.GetSchema("Columns");
    var table = schema.Rows.Cast<DataRow>().Where(p => p["TABLE_NAME"].ToString().Equals("TestTable")).FirstOrDefault();

    if(table!=null)
    {
        uniqueColumns.AddRange(schema.Rows.Cast<DataRow>()
                                      .Where(p => p["TABLE_NAME"].ToString().Equals("TestTable") && ((bool)p["IsUnique"] == true))
                                      .Select(p => (string)p["COLUMN_NAME"]));
    }

    connection.Close();

代碼解釋：

1. 創建SqlConnection對象，連接數據庫；

2. 創建一個List集合存儲唯一性約束列名；

3. 執行GetSchema(“Columns”)方法獲取列信息；

4. 用Linq表達式獲取特定的表信息；

5. 將具有唯一性約束的列名存入List集合，關閉數據庫連接。

四、獲取某張表的所有約束信息

獲取指定表的所有約束信息，可以使用下面的代碼：

    var connection = new SqlConnection("Data Source=(local);Initial Catalog=TestDb;Integrated Security=True");
    var constraints = new List<string>();

    connection.Open();

    var schema = connection.GetSchema("ConstraintColumns");
    var constraintsTable = schema.Rows.Count > 0 ? schema.Rows.Cast<DataRow>().Where(p => p["TABLE_NAME"].ToString().Equals("TestTable")) : null;

    if (constraintsTable != null)
    {
        foreach (var constraint in constraintsTable)
        {
            var constraintName = (string)constraint["CONSTRAINT_NAME"];
            if (!constraints.Contains(constraintName))
                constraints.Add(constraintName);
        }
    }

    connection.Close();

代碼解釋：

1. 創建SqlConnection對象，連接數據庫；

2. 創建一個List集合存儲約束的名稱；

3. 執行GetSchema(“ConstraintColumns”)方法獲取約束信息；

4. 使用Linq表達式存儲指定表的約束信息；

5. 將特定表中所有約束的名稱存入List集合，並且關閉數據庫連接。

五、獲取某張表的所有外鍵信息

獲取指定表的所有外鍵信息，可以使用下面的代碼：

    var connection = new SqlConnection("Data Source=(local);Initial Catalog=TestDb;Integrated Security=True");

    connection.Open();
    var schema = connection.GetSchema("Foreign Keys", new[] { null, null, "TestTable" });

    var foreignKeys = new List<ForeignKey>();

    foreach(DataRow item in schema.Rows)
    {
        foreignKeys.Add(new ForeignKey
        {
            Name = item["CONSTRAINT_NAME"].ToString(),
            FromTableName = item["TABLE_NAME"].ToString(),
            FromColumnName = item["COLUMN_NAME"].ToString(),
            ToTableName = item["REFERENCED_TABLE_NAME"].ToString(),
            ToColumnName = item["REFERENCED_COLUMN_NAME"].ToString()
        });
    }

    connection.Close();

代碼解釋：

1. 創建SqlConnection對象，連接數據庫；

2. 執行GetSchema(“Foreign Keys”)方法獲取外鍵信息；

3. 創建一個列表存儲ForeignKey對象；

4. 將表中的所有外鍵存儲到列表中，並且關閉數據庫連接。

六、總結

dapper 的 GetSchema 方法可以方便的獲取表和數據庫的架構信息。在這篇文章中，我們討論了如何使用它來獲取表中的列、主鍵、列的約束以及外鍵信息。這個技術可以在開發過程中方便的用來生成代碼，進行表比較和同步、校驗和更新表等等。