DA.Fill Schema Retrieval

[Christopher Weaver]

It appears that the DA's Fill method does not grab the schema in situations
like this:

TasksCommandText = "SELECT * FROM \"tblTasks\" " + sTasksWhere +
sTasksOrderBy;
cmd.CommandText = TasksCommandText;
odbcDA_TaskActivities.SelectCommand = cmd;
dsTaskActivities.Clear();
odbcDA_TaskActivities.Fill(dsTaskActivities, "Tasks");

The DataSet Table "Tasks" doesn't get any of the constraints or PK
information from tblTasks. So what's the best way to go about getting the
schema and applying it to the DataTable? I've looked at FillSchema but it
creates tables of predefined names (Table1, Table2, etc.) with equally
useless column names. My code is written to reflect more meaningful names,
their actual names. Is there a way to get the schema and use the actual
identifiers, both column names and table names? What's the conventional way
of doing this?

 

[Guest]

Hi,

There are more then one ways to fetch the schema of the table. But in your
case it seems that you want to get the Primary key, column Names, Table Names
also. So the code given below best suits your requirements.

The code is written in C#.

SqlConnection sqlConnect = new SqlConnection();
sqlConnect.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings.Get("CONN_STR");
sqlConnect.Open();
DataSet dtEmployee = new DataSet();
SqlCommand cmdEmployee = new SqlCommand("select * from
Employees",sqlConnect);
SqlDataAdapter dtaNorthwind = new SqlDataAdapter(cmdEmployee);
dtaNorthwind.FillSchema(dtEmployee,System.Data.SchemaType.Mapped,"Employee");

You can see in the immediate window the following results:

?dtEmployee.Tables[0].PrimaryKey[0].ColumnName
"EmployeeID"
?dtEmployee.Tables[0].TableName
"Employee"
?dtEmployee.Tables[0].Columns[0].ColumnName
"EmployeeID"

The fillSchema Method is used in the above e.g. with SchemaType.Mapped.

There are other ways also to fetch the schema, but the above method is the
best fit for your requirements.

Thanks and Regards,