当数据库返回datareader对象或DataTable对象时,可以通过对应的GetSchema方法或DataColumn取得对应的表的字段定义,包括columnName和dataType.
但这个方法默认情况下是没法取得varchar或char的length,始终返回是-1. 需要显示的在DataAdapter.Fill(DataSet)之前显示的声明一句:
DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
对于绝大多数标准的表而言,利用上面的语句,在返回数据的时候即可得到完整的DataColumn的定义. 但如果有些表的数据不规范,如not null字段中恰好为有null值,则添加了上面的语句再FILL则报错.
因此,最通用的方法是使用da.FillSchema()方法取得单独的字段定义,返回一个DataSet.当然此种方法有个不利之处就是返回数据和返回schema是两个查询, 性能上会有些问题. 完整的GetSchema的示例代码如下:
GetSchema.cs
1
using System;
2
using System.Data;
3
using System.Data.SqlClient;
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
class FillSchema
6![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
7
public static void Main()
8![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
9
SqlConnection mySqlConnection =new SqlConnection("server=(local);database=pubs;Integrated Security=SSPI;");
10![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
12
mySqlCommand.CommandText =
13
"select * from authors;";
14
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
15
mySqlDataAdapter.SelectCommand = mySqlCommand;
16
DataSet myDataSet = new DataSet();
17
mySqlConnection.Open();
18
mySqlDataAdapter.FillSchema(myDataSet, SchemaType.Mapped);
19
mySqlConnection.Close();
20
21![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
foreach (DataTable myDataTable in myDataSet.Tables)
23![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
24
Console.WriteLine("\n\nReading from the " +
25
myDataTable + "DataTable:\n");
26![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
foreach (DataColumn myPrimaryKey in myDataTable.PrimaryKey)
28![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
29
Console.WriteLine("myPrimaryKey = " + myPrimaryKey);
30
}
31![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
foreach (Constraint myConstraint in myDataTable.Constraints)
33![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
34
Console.WriteLine("myConstraint.IsPrimaryKey = " + ((UniqueConstraint) myConstraint).IsPrimaryKey);
35
foreach (DataColumn myDataColumn in ((UniqueConstraint) myConstraint).Columns)
36![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
37
Console.WriteLine("myDataColumn.ColumnName = " + myDataColumn.ColumnName);
38
}
39
}
40![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
foreach (DataColumn myDataColumn in myDataTable.Columns)
42![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
43
Console.WriteLine("\nmyDataColumn.ColumnName = " + myDataColumn.ColumnName);
44
Console.WriteLine("myDataColumn.DataType = " + myDataColumn.DataType);
45![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
Console.WriteLine("myDataColumn.AllowDBNull = " + myDataColumn.AllowDBNull);
47
Console.WriteLine("myDataColumn.AutoIncrement = " + myDataColumn.AutoIncrement);
48
Console.WriteLine("myDataColumn.AutoIncrementSeed = " + myDataColumn.AutoIncrementSeed);
49
Console.WriteLine("myDataColumn.AutoIncrementStep = " + myDataColumn.AutoIncrementStep);
50
Console.WriteLine("myDataColumn.MaxLength = " + myDataColumn.MaxLength);
51
Console.WriteLine("myDataColumn.ReadOnly = " + myDataColumn.ReadOnly);
52
Console.WriteLine("myDataColumn.Unique = " + myDataColumn.Unique);
53
}
54
}
55
56
Console.ReadKey();
57
}
58
}
59