Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

Mapping Tables, Relations Between Tables, and Columns Info of a SQL Server Database

Rate me:
Please Sign up or sign in to vote.
3.91/5 (8 votes)
11 Dec 2007CPOL3 min read 128.2K   1.4K   43   13
Shows a way to map tables, relations between tables, and columns info of a SQL Server 2000/2005 database; also generates INSERT, UPDATE, DELETE, and SELECT SQL statements at runtime using C# (TableReader).

Screenshot - ClassDiagram1.gif

Introduction

Having the need to map database tables at runtime, columns, and relations, the basic is to know four properties about tables:

  • Check if a table exists
  • Get objects that represent which tables are in the database
  • Know, for each table, which columns are Identity and/or Primary Keys, nullable, the datatype, the default value, and the size (depending on the datatype)
  • The relationship between two or more tables

This article explains how the Class Library TableReader reads a database and returns its data map.

In the first version, it was only possible to read Tables, Columns, and Relations. In the second version, some Interfaces and methods were implemented to improve the usability and integration with other classes.

How it reads tables

This Class Library uses a SQL statement to read all the columns of a table, uses some native Stored Procedures to check if a table exists in the database, and reads all the tables and all the relations between the tables.

  • Checking if a table exists in the database:
  • SQL
    --This stored procedure "sp_tables" is native in the SQL Server 
    sp_tables @table_type = "'TABLE'", @table_name='The name of the Table to be checked'

    To read more about "sp_tables", follow this link to MSDN: http://msdn2.microsoft.com/en-us/library/ms186250.aspx.

  • Reading all the columns of a table:
  • SQL
    --This Sql Satatement returns all the columns of a certain table.
    SELECT c.COLUMN_NAME, c.IS_NULLABLE, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, 
    tc.CONSTRAINT_TYPE, COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME), 
          c.COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT, COLUMN_DEFAULT 
    FROM INFORMATION_SCHEMA.COLUMNS c 
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on 
         c.COLUMN_NAME = kcu.COLUMN_NAME AND c.TABLE_NAME = kcu.TABLE_NAME 
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
         tc on kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
    WHERE c.TABLE_NAME = 'The name of the Table to be read' BY c.ORDINAL_POSITION
  • Reading all the tables:
  • SQL
    --The same stored procedure used to check if a table exists,
    --if the parameter "@table_name" is not used,
    --it returns all the tables in the database
    sp_tables @table_type = "'TABLE'"
  • Reading all the tables and all the relations between tables:
  • SQL
    sp_fkeys @pktable_name = 'Primary Table', @fktable_name = 'Foreign Table'

    To read more about "sp_fkeys", follow this link to MSDN: http://msdn2.microsoft.com/en-us/library/aa933402(SQL.80).aspx.

Improvements

The classes are now Serializable

The fTableReader object can be serialized, added to ViewState, etc.

Added method to get the SELECT, INSERT, UPDATE, and DELETE SQL clauses for a table, based on a column-value pair

The fColumnValue class is used to relate columns with a string value.

A List<fColumn> is sent to these methods in order to filter a clause or attribute values.

C#
//Creates an List of fColumnValues
List<fcolumnvalue> lstFcv = new List<fcolumnvalue>();

//Add a fColumnValue for Column 'myColumnName1' that
//is at Table 'myTableName' and with the value 'Value for Column 1'
lstFcv.Add(new fColumnValue(MyfTableReader["myTableName"]["myColumnName1"], 
           "Value for Column 1"));

//Add a fColumnValue for Column 'myColumnName' that is at Table 
//'myTableName' and with the value 'Value for Column 2'
lstFcv.Add(new fColumnValue(MyfTableReader["myTableName"]["myColumnName2"], 
           "Value for Column 2"));

//Add a fColumnValue for Column 'myColumnName3' 
//that is at Table 'myTableName' and with Empty value
lstFcv.Add(new fColumnValue(MyfTableReader["myTableName"]["myColumnName3"]));

//Add a fColumnValue for Column 'myColumnName10' 
//that is at Table 'myTableName2' and with Empty value
lstFcv.Add(new fColumnValue(MyfTableReader["myTableName2"]["myColumnName10"]));


//Get the SELECT Clause for Table 'myTableName' Filtering with all Column
//Values of List 'lstFcv' related with Columns that belongs to Table 'myTableName'
string strSelect = "SELECT: " + 
   MyfTableReader["myTableName"].GetStringSelect(lstFcv);

//Get the UPDATE Clause for Table 'myTableName' with all Column Values 
//of List 'lstFcv' related with Columns that belongs to Table 'myTableName'
//IT Automatically speare the Primary Keys and update all Columns 
//that are not Primary Keys and Put The Primary Key Colmns in the WHERE Clause
string strUpdate = "UPDATE: " + 
       MyfTableReader["myTableName"].GetStringUpdate(lstFcv);

//Get the DELETE Clause for Table 'myTableName' withhe WHERE Clause 
//with all Column Values of List 'lstFcv' related with 
//Columns that belongs to Table 'myTableName'
//The flag inicates whether the Method should 
//put only Primary Keys in the Where Clause
string strDelete = "DELETE: " + 
  MyfTableReader["myTableName"].GetStringDelete(lstFcv, true);

//Get the INSERT Clause for Table 'myTableName' with all Column Values 
//of List 'lstFcv' related with Columns that belongs to Table 'myTableName'
//The Flag indicates whether the method should insert Identity Columns
string strInsert = "INSERT: " + 
   MyfTableReader["myTableName"].GetStringInsert(lstFcv, false);

Added the IComparable<T> interface to fTable and fColumn so that they could be ordered by table name and column name, respectively

In order to alphabetically order the tables or columns by table name or column name, respectively, without having the need to use a delegate with a List of table or column names, the IComparable<T> interface was implemented to be able to sort with the Sort() method directly.

C#
// Order the Tables in 'MyfTableReader' alphabetically
MyfTableReader.Tables.Sort();

// Order the Column in 'MyfTable' alphabetically
MyfTable.TableColumns.Sort();

Added the IList<T> interface to fTableReader and fColumn so that they could be used as DataSource

In order to populate DropDownLists, RadioButtonLists, and others easily, the IList<T> interface was implemented.

C#
//The DropDownList 'ddlMyDropDownListTables' DataSorce 
//is related to the fTableReader 'MyTableReader'
ddlMyDropDownListTables.DataSource = MyTableReader;
//Binds the DropDownList 'ddlMyDropDownListTables' 
//with all tables of the fTableReader 'MyTableReader' 
ddlMyDropDownListTables.DataBind();

//The DropDownList 'ddlMyDropDownListColumns' DataSorce is related 
//to the Table 'MyTable' in the fTableReader 'MyTableReader'
ddlMyDropDownListColumns = MyTableReader["MyTable"];
//Binds the DropDownList 'ddlMyDropDownListColumns' with all Columns 
//of the Table 'MyTable' in the fTableReader 'MyTableReader'
ddlMyDropDownListColumns.DataBind();

Using the Code

In order to use the TableReader, your project must be .NET 2.0 and needs a reference to the project source or the DLL.

This project is only compatible with SQL Server 2000/2005.

Using Generics to get the objects is very simple. Here is a sample code of how to use the TableReader to read the database and its tables.

The second version requires less code to get the objects and its properties. There is an example of the use of TableReader below:

C#
//Create a new Instance of the TableReader, tr
fTableReader tr = new TableReader("server=Your SQL Server;Integrated " + 
                  "Security=false;User Id=User Name;Password=Your Password;" + 
                  "database=Your DataBase;Pooling=false;");

//Create a fTable Object
fTable newfTable1 = new fTable();

//Check if a Table Exists in the database
if (tr.CheckTableExistance("myTable"))
    newfTable1 = tr.AddTable("myTable");

//Add all tables of the database to the object tr
tr.AddAllTables();

//Get a table object by the Table Name
fTable newfTable2 = tr["myTable"];

//The Same as 'fTable newfTable2 = CommonMethod.GetTableByName(tr.Tables, "myTable");'
//Get all the relations of newfTable1 ("myTable") when it is a Foreign Table

List<ftablerelation> lstTableRelation = newfTable1.ForeignTablesRelation;
//The same as 'List<ftablerelation> lstTableRelation = 
//   tr.GetTableRelation(newfTable1, fTableReader.enfTableRelationType.ForeignTable);'

//Get a List<fcolumn> of All the Columns
//of newfTable1 ("myTable") that are Primary Keys
List<fcolumn> lstFcolumn = newfTable1.PrimaryKeys;
//The Same as 'List<fcolumn> lstFcolumn = 
//   CommonMethod.GetTableKeys(newfTable1, CommonMethod.enfTableKeyType.PrimaryKey);'

Points of Interest

To know more, follow these links to MSDN:

History

v2.0 (18/10/2007)

  • Added methods to get the SELECT, INSERT, UPDATE, and DELETE SQL clauses for a table, based on a column-value pair.
  • Added the IComparable<T> interface to fTable and fColumn so that they could be ordered by table name and column name, respectively.
  • Added the IList<T> interface to fTableReader and fColumn so that it could be used as DataSource.
  • The classes are now Serializable.
  • Added new properties and methods to classes.
  • Bug fixes.

v1.0 (08/10/2007)

  • First release.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer I.ndigo - www.i.ndigo.com.br
Brazil Brazil
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert25-Sep-12 22:48
professionalKanasz Robert25-Sep-12 22:48 
Questionsql server database Pin
rajawasim125-Dec-11 22:02
rajawasim125-Dec-11 22:02 
Generalsql doesn't allow remote access exception Pin
Talha Siddiq29-Mar-09 9:50
Talha Siddiq29-Mar-09 9:50 
GeneralHi There Pin
bidox8-Sep-08 2:58
bidox8-Sep-08 2:58 
GeneralRe: Hi There Pin
Member 324979527-Sep-08 18:11
Member 324979527-Sep-08 18:11 
QuestionEquvalent vb.net please!!! Pin
450paul02702-Sep-08 19:51
450paul02702-Sep-08 19:51 
AnswerRe: Equvalent vb.net please!!! Pin
felipesabino11-Dec-08 9:00
felipesabino11-Dec-08 9:00 
Questionadd relationships Pin
BDaniels6-Aug-08 8:45
BDaniels6-Aug-08 8:45 
QuestionHow Can I Change IsIdentity Property Pin
Javad Bayani6-Mar-08 1:16
Javad Bayani6-Mar-08 1:16 
QuestionCould you help me? Pin
georani19-Oct-07 10:23
georani19-Oct-07 10:23 
AnswerRe: Could you help me? Pin
felipesabino19-Oct-07 11:40
felipesabino19-Oct-07 11:40 
QuestionHow about a little shorter title? Pin
Paul Selormey18-Oct-07 16:54
Paul Selormey18-Oct-07 16:54 
AnswerRe: How about a little shorter title? Pin
felipesabino19-Oct-07 1:22
felipesabino19-Oct-07 1:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.