How to read Constraints (Primary key, FK,etc) over any table.

[Guest]

Hi,
Iam trying hard to read the constraints over any table using System.Data
obects in C#. Can anyone suggest the method for retreivint the constraints on
any table .

Moreover when I try reading tableschema(as XMLfile) it gives the coumn name
and type ,but it do not give me the column length. How I can get the length
of coumn?

Vinay C

 

[Mark Miller]

Below is a method I use to get the entire schema for a table. Simply call
the DataAdapter.FillSchema() method on any SelectCommand and then you'll
have the properties you're looking for. For performance reasons, once I have
the schema for the table I store it in cache as an XML schema (no data) so I
can use it again later w/o another round trip the the DB.

Also if you want to read the constraints on the table (after you have called
GetSchema) just loop thru the table's constraints property. You'll need to
check the Type of each constraint to see if it is a PrimaryKey, ForeignKey
or other. If you're using C# just use "is" or you can use the base object's
GetType() method to find out what kind it is.

private static string DiscoverXmlSchema(SqlConnection connection, string
TableName){

if( connection == null ) throw new ArgumentNullException( "connection" );
if( TableName == null || TableName.Length == 0 ) throw new
ArgumentNullException( "TableName" );

//DataSet ds = SqlDal.ExecuteDataset( connection, CommandType.Text, "SELECT
TOP 0 * FROM " + TableName );
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 0 * FROM " + TableName,
connection);
da.FillSchema(ds, SchemaType.Source);

ds.Tables[0].TableName = TableName;
string Xml = ds.GetXmlSchema();
//System.Diagnostics.Debug.WriteLine(Xml);

if( Xml == null || Xml.Length == 0 )
return null;
else
return Xml;
}

 

[Guest]

Hi Mark,
Thanks for your reply. Iam able to read the schema of table in DS usinf the
DA.FillSchema() method. Im able to get the primary key using
DS.Table("Name").PrimaryKey but I am not getting the foreign keys.
Can u suugest how can I obtain foriegn key?

Regars,
Vinay

Below is a method I use to get the entire schema for a table. Simply call
the DataAdapter.FillSchema() method on any SelectCommand and then you'll
have the properties you're looking for. For performance reasons, once I have
the schema for the table I store it in cache as an XML schema (no data) so I
can use it again later w/o another round trip the the DB.

Also if you want to read the constraints on the table (after you have called
GetSchema) just loop thru the table's constraints property. You'll need to
check the Type of each constraint to see if it is a PrimaryKey, ForeignKey
or other. If you're using C# just use "is" or you can use the base object's
GetType() method to find out what kind it is.

private static string DiscoverXmlSchema(SqlConnection connection, string
TableName){

if( connection == null ) throw new ArgumentNullException( "connection" );
if( TableName == null || TableName.Length == 0 ) throw new
ArgumentNullException( "TableName" );

//DataSet ds = SqlDal.ExecuteDataset( connection, CommandType.Text, "SELECT
TOP 0 * FROM " + TableName );
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 0 * FROM " + TableName,
connection);
da.FillSchema(ds, SchemaType.Source);

ds.Tables[0].TableName = TableName;
string Xml = ds.GetXmlSchema();
//System.Diagnostics.Debug.WriteLine(Xml);

if( Xml == null || Xml.Length == 0 )
return null;
else
return Xml;
}

Hi,
Iam trying hard to read the constraints over any table using System.Data
obects in C#. Can anyone suggest the method for retreivint the constraints on
any table .

Moreover when I try reading tableschema(as XMLfile) it gives the coumn name
and type ,but it do not give me the column length. How I can get the length
of coumn?

Vinay C

 

[Steve Willcock]

Vinay, I had to do this for a project I am working on and without posting
the whole of my code I basically had to do this manually by running the
following sql statement against the database then recursing through the
results and manually creating the relevant constraints in the dataset. (This
is running against Sql Server)

select object_name(constid) as constraint_name,
upper(substring(object_name(fkeyid),1,1)) +
substring(object_name(fkeyid),2,255) as table_name,
upper(substring(col_name(fkeyid, fkey),1,1)) + substring(col_name(fkeyid,
fkey),2,255) as column_name,
upper(substring(object_name(rkeyid),1,1)) +
substring(object_name(rkeyid),2,255) as referenced_table_name,
upper(substring(col_name(rkeyid, rkey),1,1)) + substring(col_name(rkeyid,
rkey),2,255) as referenced_column_name from sysforeignkeys
order by table_name, constraint_name,referenced_table_name, keyno

If you find an easier way to do this please let me know

--
Steve Willcock (MCSD for Microsoft.NET)
http://www.willcockconsulting.com/

Hi Mark,
Thanks for your reply. Iam able to read the schema of table in DS usinf the
DA.FillSchema() method. Im able to get the primary key using
DS.Table("Name").PrimaryKey but I am not getting the foreign keys.
Can u suugest how can I obtain foriegn key?

Regars,
Vinay

Below is a method I use to get the entire schema for a table. Simply call
the DataAdapter.FillSchema() method on any SelectCommand and then you'll
have the properties you're looking for. For performance reasons, once I have
the schema for the table I store it in cache as an XML schema (no data) so I
can use it again later w/o another round trip the the DB.

Also if you want to read the constraints on the table (after you have called
GetSchema) just loop thru the table's constraints property. You'll need to
check the Type of each constraint to see if it is a PrimaryKey, ForeignKey
or other. If you're using C# just use "is" or you can use the base object's
GetType() method to find out what kind it is.

private static string DiscoverXmlSchema(SqlConnection connection, string
TableName){

if( connection == null ) throw new ArgumentNullException( "connection" );
if( TableName == null || TableName.Length == 0 ) throw new
ArgumentNullException( "TableName" );

//DataSet ds = SqlDal.ExecuteDataset( connection, CommandType.Text, "SELECT
TOP 0 * FROM " + TableName );
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 0 * FROM " + TableName,
connection);
da.FillSchema(ds, SchemaType.Source);

ds.Tables[0].TableName = TableName;
string Xml = ds.GetXmlSchema();
//System.Diagnostics.Debug.WriteLine(Xml);

if( Xml == null || Xml.Length == 0 )
return null;
else
return Xml;
}

Hi,
Iam trying hard to read the constraints over any table using System.Data
obects in C#. Can anyone suggest the method for retreivint the

constraints
on

any table .

Moreover when I try reading tableschema(as XMLfile) it gives the coumn name
and type ,but it do not give me the column length. How I can get the length
of coumn?

Vinay C

 

[Guest]

Hi Steve,

Check whether this helps you.
SqlDataAdapter schemaDA = new SqlDataAdapter("SELECT * FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS " ,databaseName);
DataSet ds = new DataSet();
schemaDA.Fill(ds);

The above statements gives the foreign key info in the form of dataset,
which can be read using datatable.
Moreover there are some stopred procedures aslo in SQL server which that
fetch these things for you.

But I want to make it more generic that can work for any of the databases,
for example to fetch primary key I am using
DataTable["tablename"].PrimaryKey
I am looking for some approch like this, if you find any solution please let
me know.

Vinay

Vinay, I had to do this for a project I am working on and without posting
the whole of my code I basically had to do this manually by running the
following sql statement against the database then recursing through the
results and manually creating the relevant constraints in the dataset. (This
is running against Sql Server)

select object_name(constid) as constraint_name,
upper(substring(object_name(fkeyid),1,1)) +
substring(object_name(fkeyid),2,255) as table_name,
upper(substring(col_name(fkeyid, fkey),1,1)) + substring(col_name(fkeyid,
fkey),2,255) as column_name,
upper(substring(object_name(rkeyid),1,1)) +
substring(object_name(rkeyid),2,255) as referenced_table_name,
upper(substring(col_name(rkeyid, rkey),1,1)) + substring(col_name(rkeyid,
rkey),2,255) as referenced_column_name from sysforeignkeys
order by table_name, constraint_name,referenced_table_name, keyno

If you find an easier way to do this please let me know

--
Steve Willcock (MCSD for Microsoft.NET)
http://www.willcockconsulting.com/

Hi Mark,
Thanks for your reply. Iam able to read the schema of table in DS usinf the
DA.FillSchema() method. Im able to get the primary key using
DS.Table("Name").PrimaryKey but I am not getting the foreign keys.
Can u suugest how can I obtain foriegn key?

Regars,
Vinay

Below is a method I use to get the entire schema for a table. Simply call
the DataAdapter.FillSchema() method on any SelectCommand and then you'll
have the properties you're looking for. For performance reasons, once I have
the schema for the table I store it in cache as an XML schema (no data) so I
can use it again later w/o another round trip the the DB.

Also if you want to read the constraints on the table (after you have called
GetSchema) just loop thru the table's constraints property. You'll need to
check the Type of each constraint to see if it is a PrimaryKey, ForeignKey
or other. If you're using C# just use "is" or you can use the base object's
GetType() method to find out what kind it is.

private static string DiscoverXmlSchema(SqlConnection connection, string
TableName){

if( connection == null ) throw new ArgumentNullException( "connection" );
if( TableName == null || TableName.Length == 0 ) throw new
ArgumentNullException( "TableName" );

//DataSet ds = SqlDal.ExecuteDataset( connection, CommandType.Text, "SELECT
TOP 0 * FROM " + TableName );
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 0 * FROM " + TableName,
connection);
da.FillSchema(ds, SchemaType.Source);

ds.Tables[0].TableName = TableName;
string Xml = ds.GetXmlSchema();
//System.Diagnostics.Debug.WriteLine(Xml);

if( Xml == null || Xml.Length == 0 )
return null;
else
return Xml;
}

Hi,
Iam trying hard to read the constraints over any table using System.Data
obects in C#. Can anyone suggest the method for retreivint the constraints
on
any table .

Moreover when I try reading tableschema(as XMLfile) it gives the coumn
name
and type ,but it do not give me the column length. How I can get the
length
of coumn?

Vinay C

 

[Steve Willcock]

Thanks vinay, I had tried that but it doesn't give column details for the
foreign keys, hence that big sql statement...

Steve

Hi Steve,

Check whether this helps you.
SqlDataAdapter schemaDA = new SqlDataAdapter("SELECT * FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS " ,databaseName);
DataSet ds = new DataSet();
schemaDA.Fill(ds);

The above statements gives the foreign key info in the form of dataset,
which can be read using datatable.
Moreover there are some stopred procedures aslo in SQL server which that
fetch these things for you.

But I want to make it more generic that can work for any of the databases,
for example to fetch primary key I am using
DataTable["tablename"].PrimaryKey
I am looking for some approch like this, if you find any solution please let
me know.

Vinay

Vinay, I had to do this for a project I am working on and without posting
the whole of my code I basically had to do this manually by running the
following sql statement against the database then recursing through the
results and manually creating the relevant constraints in the dataset. (This
is running against Sql Server)

select object_name(constid) as constraint_name,
upper(substring(object_name(fkeyid),1,1)) +
substring(object_name(fkeyid),2,255) as table_name,
upper(substring(col_name(fkeyid, fkey),1,1)) + substring(col_name(fkeyid,
fkey),2,255) as column_name,
upper(substring(object_name(rkeyid),1,1)) +
substring(object_name(rkeyid),2,255) as referenced_table_name,
upper(substring(col_name(rkeyid, rkey),1,1)) + substring(col_name(rkeyid,
rkey),2,255) as referenced_column_name from sysforeignkeys
order by table_name, constraint_name,referenced_table_name, keyno

If you find an easier way to do this please let me know

--
Steve Willcock (MCSD for Microsoft.NET)
http://www.willcockconsulting.com/

Hi Mark,
Thanks for your reply. Iam able to read the schema of table in DS

usinf
the

DA.FillSchema() method. Im able to get the primary key using
DS.Table("Name").PrimaryKey but I am not getting the foreign keys.
Can u suugest how can I obtain foriegn key?

Regars,
Vinay

:

Below is a method I use to get the entire schema for a table. Simply call
the DataAdapter.FillSchema() method on any SelectCommand and then you'll
have the properties you're looking for. For performance reasons,

once I
have

the schema for the table I store it in cache as an XML schema (no

data)
so I

can use it again later w/o another round trip the the DB.

Also if you want to read the constraints on the table (after you

have
called

GetSchema) just loop thru the table's constraints property. You'll

need
to

check the Type of each constraint to see if it is a PrimaryKey, ForeignKey
or other. If you're using C# just use "is" or you can use the base object's
GetType() method to find out what kind it is.

private static string DiscoverXmlSchema(SqlConnection connection, string
TableName){

if( connection == null ) throw new ArgumentNullException( "connection" );
if( TableName == null || TableName.Length == 0 ) throw new
ArgumentNullException( "TableName" );

//DataSet ds = SqlDal.ExecuteDataset( connection, CommandType.Text, "SELECT
TOP 0 * FROM " + TableName );
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 0 * FROM " + TableName,
connection);
da.FillSchema(ds, SchemaType.Source);

ds.Tables[0].TableName = TableName;
string Xml = ds.GetXmlSchema();
//System.Diagnostics.Debug.WriteLine(Xml);

if( Xml == null || Xml.Length == 0 )
return null;
else
return Xml;
}

Hi,
Iam trying hard to read the constraints over any table using System.Data
obects in C#. Can anyone suggest the method for retreivint the constraints
on
any table .

Moreover when I try reading tableschema(as XMLfile) it gives the coumn
name
and type ,but it do not give me the column length. How I can get the
length
of coumn?

Vinay C

 

[Guest]

Hi steve,
you can also try stored procedure sp_fkey and check whether it works for you
or not:
conn = new SqlConnection(connStr + dbName);
SqlCommand cmd = new SqlCommand("sp_fkeys", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pktable_name", pkTable);
conn.Open();
SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
string fkTable = reader.GetSqlString(6).ToString();
string fkCol = reader.GetSqlString(7).ToString();
string pkCol = reader.GetSqlString(3).ToString();
string relName = reader.GetSqlString(11).ToString();
short updateRule = reader.GetInt16(9);
short delRule = reader.GetInt16(10);
Relation r = new
Relation(relName,pkTable,fkTable,pkCol,fkCol,updateRule,delRule);
relList.Add(r);
}
reader.Close();

_______________________
Vinay

Thanks vinay, I had tried that but it doesn't give column details for the
foreign keys, hence that big sql statement...

Steve

Hi Steve,

Check whether this helps you.
SqlDataAdapter schemaDA = new SqlDataAdapter("SELECT * FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS " ,databaseName);
DataSet ds = new DataSet();
schemaDA.Fill(ds);

The above statements gives the foreign key info in the form of dataset,
which can be read using datatable.
Moreover there are some stopred procedures aslo in SQL server which that
fetch these things for you.

But I want to make it more generic that can work for any of the databases,
for example to fetch primary key I am using
DataTable["tablename"].PrimaryKey
I am looking for some approch like this, if you find any solution please let
me know.

Vinay

Vinay, I had to do this for a project I am working on and without posting
the whole of my code I basically had to do this manually by running the
following sql statement against the database then recursing through the
results and manually creating the relevant constraints in the dataset. (This
is running against Sql Server)

select object_name(constid) as constraint_name,
upper(substring(object_name(fkeyid),1,1)) +
substring(object_name(fkeyid),2,255) as table_name,
upper(substring(col_name(fkeyid, fkey),1,1)) + substring(col_name(fkeyid,
fkey),2,255) as column_name,
upper(substring(object_name(rkeyid),1,1)) +
substring(object_name(rkeyid),2,255) as referenced_table_name,
upper(substring(col_name(rkeyid, rkey),1,1)) + substring(col_name(rkeyid,
rkey),2,255) as referenced_column_name from sysforeignkeys
order by table_name, constraint_name,referenced_table_name, keyno

If you find an easier way to do this please let me know

--
Steve Willcock (MCSD for Microsoft.NET)
http://www.willcockconsulting.com/


Hi Mark,
Thanks for your reply. Iam able to read the schema of table in DS usinf
the
DA.FillSchema() method. Im able to get the primary key using
DS.Table("Name").PrimaryKey but I am not getting the foreign keys.
Can u suugest how can I obtain foriegn key?

Regars,
Vinay

:

Below is a method I use to get the entire schema for a table. Simply
call
the DataAdapter.FillSchema() method on any SelectCommand and then you'll
have the properties you're looking for. For performance reasons, once I
have
the schema for the table I store it in cache as an XML schema (no data)
so I
can use it again later w/o another round trip the the DB.

Also if you want to read the constraints on the table (after you have
called
GetSchema) just loop thru the table's constraints property. You'll need
to
check the Type of each constraint to see if it is a PrimaryKey,
ForeignKey
or other. If you're using C# just use "is" or you can use the base
object's
GetType() method to find out what kind it is.

private static string DiscoverXmlSchema(SqlConnection connection, string
TableName){

if( connection == null ) throw new ArgumentNullException(
"connection" );
if( TableName == null || TableName.Length == 0 ) throw new
ArgumentNullException( "TableName" );

//DataSet ds = SqlDal.ExecuteDataset( connection, CommandType.Text,
"SELECT
TOP 0 * FROM " + TableName );
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 0 * FROM " +
TableName,
connection);
da.FillSchema(ds, SchemaType.Source);

ds.Tables[0].TableName = TableName;
string Xml = ds.GetXmlSchema();
//System.Diagnostics.Debug.WriteLine(Xml);

if( Xml == null || Xml.Length == 0 )
return null;
else
return Xml;
}

Hi,
Iam trying hard to read the constraints over any table using
System.Data
obects in C#. Can anyone suggest the method for retreivint the
constraints
on
any table .

Moreover when I try reading tableschema(as XMLfile) it gives the coumn
name
and type ,but it do not give me the column length. How I can get the
length
of coumn?

Vinay C