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 方法可以方便的获取表和数据库的架构信息。在这篇文章中，我们讨论了如何使用它来获取表中的列、主键、列的约束以及外键信息。这个技术可以在开发过程中方便的用来生成代码，进行表比较和同步、校验和更新表等等。