Introduction
When you start VS.NET and create a new WinForm, then add data grid view to the form. You will walk in few wizard steps to bind this grid view to certain table in any database. The wizard instructs you to create a new connection, select the wanted items, and then you can even drag certain columns from any table and drop it in the grid view.
So how did VS.NET know what to do? In the generated XSD schema file, how did it get all tables and views from the database with relations among them?
Well, VS.NET wizard follows the steps given below to generate the strongly typed dataset schema file and class file:
- Create a new instance of the
DataSet class. - Call the
FillSchema method of all SqlDataAdapter objects that were created by dragging tables or columns from Server Explorer onto the strongly typed DataSet designer. - Call the
DataSet.WriteXmlSchema method to create new .xsd schema file that contains the DataSet. - Use the XML Schema Definition Tool (XSD.exe) to generate strongly typed
DataSet class based on the .xsd file.
This tool can be used to generate .xsd schema file and class file for the languages C#, VB, JScript, and Visual J#.
To run the project, you must have VS.NET 2008 SP1 and .NET Framework 3.5 SP1.
This tool is built for SQL Server 2005.
How to Use?
To watch a demo for using the tool, please visit Akram Mellice's Blog.
To use the tool, follow these steps:
- Start the program, Servers combo box will be bound to all SQL Server instances in the local network.
- Supply your username and password and if you use Windows authentication, then check Windows Authentication check box.
- Click Test Connection, if the supplied credentials succeeded, all the databases that exist in the selected server will be bound to Database combo box.
- Select the wanted database.
- Check the items you want to create (i.e.: Tables, Views, and Relations).
- Select the target language (for example C#).
- You can save the generated files to specific location by clicking the button Save Generated Strongly Typed DataSet As.
- Click View Selected Tables button, the Table Viewer form will open, and all item's type you chose in the first form will appear in this form, so if you only select Tables, then only tables will show in this form, if you select Tables and Views, then all tables and views will be shown in the form.
- Select the wanted items (tables or views), then click the Close button.
- Click Generate Strongly Typed
DataSet button, two successful messages should appear informing that the .xsd schema file and class file are generated successfully.
The following two images should clarify the previous steps:
Using the Code
The source code is fully documented, so I am going to show important points here:
- The method
PrepareGeneratedDataSet() method:
string selectString = String.Empty;
SqlCommand selectCommand = new SqlCommand(String.Empty, dbConnection);
SqlDataAdapter selectAdapter = new SqlDataAdapter(selectCommand);
int count = 0;
foreach (DataRow row in selectedTables.Rows)
{
if (tablesChk.Checked)
{
if (row["TABLE_TYPE"].ToString().ToLower() == "base table")
{
selectString += "SELECT * FROM [" + row["Table_Name"].ToString() + "]; ";
if (count == 0)
{
selectAdapter.TableMappings.Add("Table", row["Table_Name"].ToString());
count++;
}
else
{
selectAdapter.TableMappings.Add("Table"
+ count.ToString(), row["Table_Name"].ToString());
count++;
}
}
}
if (viewsChk.Checked)
{
if (row["TABLE_TYPE"].ToString().ToLower() == "view")
{
selectString += "SELECT * FROM [" + row["Table_Name"].ToString() + "]; ";
if (count == 0)
{
selectAdapter.TableMappings.Add("Table", row["Table_Name"].ToString());
count++;
}
else
{
selectAdapter.TableMappings.Add("Table"
+ count.ToString(), row["Table_Name"].ToString());
count++;
}
}
}
}
The foreach loop loops for all chosen items, if you chose Tables and Views then it will add all tables' name and views' name to the selectString, and fix the TableMapping in the selectAdapter based on the integer count.
AddRelationsToGeneratedDataSet() method:
The important part in this method is the SQL query I used to get all relations between all tables and views in the selected database. I have searched the web a lot, reading dozens of MSDN documents to know if there is a way to get relations using methods in ADO.NET, but could not find anything, so I get down to SQL Server itself.
It turns out that it saves this information in system tables and views, the following query is used to get this information:
SELECT OBJECT_NAME(SYS.FOREIGN_KEY_COLUMNS.CONSTRAINT_OBJECT_ID)
AS RELATIONNAME,
OBJECT_NAME(SYS.FOREIGN_KEY_COLUMNS.PARENT_OBJECT_ID) AS CHILDTABLE,
OBJECT_NAME(SYS.FOREIGN_KEY_COLUMNS.REFERENCED_OBJECT_ID)
AS PARENTTABLE,
SCHEMA_INFO_COLUMNS_1.COLUMN_NAME AS CHILDCOLUMN,
SCHEMA_INFO_COLUMNS_2.COLUMN_NAME AS PARENTCOLUMN
FROM SYS.FOREIGN_KEY_COLUMNS,
INFORMATION_SCHEMA.COLUMNS AS SCHEMA_INFO_COLUMNS_1,
INFORMATION_SCHEMA.COLUMNS AS SCHEMA_INFO_COLUMNS_2
WHERE OBJECT_NAME(SYS.FOREIGN_KEY_COLUMNS.PARENT_OBJECT_ID) =
SCHEMA_INFO_COLUMNS_1.TABLE_NAME AND
SYS.FOREIGN_KEY_COLUMNS.PARENT_COLUMN_ID =
SCHEMA_INFO_COLUMNS_1.ORDINAL_POSITION AND
OBJECT_NAME(SYS.FOREIGN_KEY_COLUMNS.REFERENCED_OBJECT_ID) =
SCHEMA_INFO_COLUMNS_2.TABLE_NAME AND
SYS.FOREIGN_KEY_COLUMNS.REFERENCED_COLUMN_ID =
SCHEMA_INFO_COLUMNS_2.ORDINAL_POSITION
This query returns the data as follows (using Northwind as example):
FK_Orders_Customers AS RELATIONNAME, Orders AS CHILDTABLE, _
Customers AS PARENTTABLE, CustomerID AS CHILDCOLUMN, CustomerID AS PARENTCOLUMN.
The rest of the method is the operation of inserting retrieved relations into the DataSet based on the selected items.
- The
GenerateBtn_Click(object sender, EventArgs e) method:
PrepareGeneratedDataSet();
AddRelationsToGeneratedDataSet();
if (xsdFilePath != String.Empty)
ds.WriteXmlSchema(xsdFilePath);
string ApplicationName =
@"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\XSD.exe";
string parameters = "\"" + xsdFilePath
+ "\""
+ " /d /o:" + "\""
+ classFilePath + "\" /l:"
+ language;
Process xsdProcess = new Process();
xsdProcess.StartInfo.FileName = ApplicationName;
xsdProcess.StartInfo.CreateNoWindow = true;
xsdProcess.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
xsdProcess.StartInfo.Arguments = parameters;
xsdProcess.Start();
xsdProcess.WaitForExit();
This method prepares the dataset to be generated and adds relations to it, and then writes the content of dataset to XSD file, then calls the XSD.exe which is part of the .NET SDK to generate the class.
History
- 20th November, 2009: Initial post
- 1st December, 2009: Updated to fix the two following issues:
- Handle Windows authentication correctly
- Handle relation based on more than one column
The first issue is straight forward, just setting the connectionBuilder.IntegratedSecurity = true.
For the second issue, I changed the code of the method AddRelationsToGeneratedDataSet as follows:
if (ds.Relations.Contains(row["RELATIONNAME"].ToString()))
{
int columnCount = ds.Relations[row["RELATIONNAME"].ToString()].ChildColumns.Length;
for (int i = 0; i < columnCount; i++)
{
if (ds.Relations[row["RELATIONNAME"].ToString()].ChildColumns[i].ColumnName.Equals
(ds.Tables[row["CHILDTABLE"].ToString()].Columns[row["CHILDCOLUMN"].ToString()]) &&
ds.Relations[row["RELATIONNAME"].ToString()].ParentColumns[i].ColumnName.Equals
(ds.Tables[row["PARENTTABLE"].ToString()].Columns[row["PARENTCOLUMN"].ToString()]))
{
string errorMessage = @"The relation "
+ ds.Relations[row["RELATIONNAME"].ToString()]
+ " between the tables " + ds.Tables[row["PARENTTABLE"].ToString()]
+ " and " + ds.Tables[row["CHILDTABLE"].ToString()] + " on the columns " +
ds.Tables[row["PARENTTABLE"].ToString()].Columns[row["PARENTCOLUMN"].ToString()]
+ " and " + ds.Tables[row["CHILDTABLE"].ToString()].Columns
[row["CHILDCOLUMN"].ToString()] + " already exist.";
MessageBox.Show(errorMessage, "Error!");
return;
}
else
{
DataColumn[] newParentColumns = new DataColumn[ds.Relations
[row["RELATIONNAME"].ToString()].ParentColumns.Length + 1];
ds.Relations[row["RELATIONNAME"].ToString()].
ParentColumns.CopyTo(newParentColumns, 0);
newParentColumns[ds.Relations[row["RELATIONNAME"].ToString()].
ParentColumns.Length] = ds.Tables[row["PARENTTABLE"].ToString()].
Columns[row["PARENTCOLUMN"].ToString()];
DataColumn[] newChildColumns = new DataColumn[ds.Relations
[row["RELATIONNAME"].ToString()].ChildColumns.Length + 1];
ds.Relations[row["RELATIONNAME"].ToString()].
ChildColumns.CopyTo(newChildColumns, 0);
newChildColumns[ds.Relations[row["RELATIONNAME"].ToString()].
ChildColumns.Length] = ds.Tables[row["CHILDTABLE"].ToString()].
Columns[row["CHILDCOLUMN"].ToString()];
ds.Relations.Remove(row["RELATIONNAME"].ToString());
ds.Relations.Add(row["RELATIONNAME"].ToString(),
newParentColumns, newChildColumns);
}
}
}
else
ds.Relations.Add(row["RELATIONNAME"].ToString(), ds.Tables
[row["PARENTTABLE"].ToString()].Columns[row["PARENTCOLUMN"].ToString()],
ds.Tables[row["CHILDTABLE"].ToString()].Columns[row["CHILDCOLUMN"].ToString()]);
I also added a button called Set xsd.exe path which can be used to set the path of xsd.exe tool if it does not exist in "C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\" which is the default.
