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

Strongly Typed DataSet Generator

Rate me:
Please Sign up or sign in to vote.
4.89/5 (10 votes)
2 Dec 2009CPOL4 min read 64.3K   1.3K   54   12
Generate strongly typed datasets

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:

  1. Create a new instance of the DataSet class.
  2. 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.
  3. Call the DataSet.WriteXmlSchema method to create new .xsd schema file that contains the DataSet.
  4. 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:

  1. Start the program, Servers combo box will be bound to all SQL Server instances in the local network.
  2. Supply your username and password and if you use Windows authentication, then check Windows Authentication check box.
  3. Click Test Connection, if the supplied credentials succeeded, all the databases that exist in the selected server will be bound to Database combo box.
  4. Select the wanted database.
  5. Check the items you want to create (i.e.: Tables, Views, and Relations).
  6. Select the target language (for example C#).
  7. You can save the generated files to specific location by clicking the button Save Generated Strongly Typed DataSet As.
  8. 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.
  9. Select the wanted items (tables or views), then click the Close button.
  10. 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:

stronglytypeddatasetgeneratorform.png

tablesviewerform.png

Using the Code

The source code is fully documented, so I am going to show important points here:

  1. The method PrepareGeneratedDataSet() method:
    C#
    string selectString = String.Empty;
    SqlCommand selectCommand = new SqlCommand(String.Empty, dbConnection);
    SqlDataAdapter selectAdapter = new SqlDataAdapter(selectCommand);
    int count = 0;
    //build the select statement and fix the selectAdapter's TableMapping
    foreach (DataRow row in selectedTables.Rows)
    {
      //Tables will be generated (Table_Type = Base Table)
      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++;
        }
         }
       }
       //Tables will be generated (Table_Type = View)
       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.

  2. 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:
    SQL
    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):

    SQL
    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.

  3. The GenerateBtn_Click(object sender, EventArgs e) method:
    C#
    PrepareGeneratedDataSet();
    AddRelationsToGeneratedDataSet();
    if (xsdFilePath != String.Empty)
         ds.WriteXmlSchema(xsdFilePath);
    //call the XSD.exe
    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:
    1. Handle Windows authentication correctly
    2. 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:

    C#
    //check if the relation is based on more than one column
    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.

License

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


Written By
Egypt Egypt
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionThank you Pin
BelgiumRoland16-Oct-16 9:52
BelgiumRoland16-Oct-16 9:52 
GeneralMy vote of 5 Pin
v# guy6-Mar-12 2:52
v# guy6-Mar-12 2:52 
GeneralMy vote of 5 Pin
Akram Mellice4-May-11 12:52
Akram Mellice4-May-11 12:52 
GeneralFeature Request // Save some pieces of info in .config file Pin
granadaCoder26-May-10 4:09
granadaCoder26-May-10 4:09 
GeneralSuggestion // Button Text Rename Pin
granadaCoder26-May-10 4:05
granadaCoder26-May-10 4:05 
GeneralFeature Request // Namespace(s) Pin
granadaCoder25-May-10 12:12
granadaCoder25-May-10 12:12 
GeneralSchema(s) Pin
granadaCoder25-May-10 11:54
granadaCoder25-May-10 11:54 
I am using your code to generate some strong datasets.

You have 1 core issue with your current implementation.

Schemas. You are either assuming "dbo" or the current table owner.


Example TSQL to create an alternate schema.


if not exists(select 1 from information_schema.schemata where schema_name='LookupSchema')
BEGIN
EXEC ('CREATE SCHEMA LookupSchema AUTHORIZATION dbo;')
END

GO
--and also a table which uses that schema


if exists (select * from dbo.sysobjects where id = object_id(N'[LookupSchema].[CodeCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [LookupSchema].[CodeCategory]
END
GO

CREATE TABLE [LookupSchema].[CodeCategory] (

CodeCategoryKey [smallint] not null ,
CodeCategoryName varchar(64) not null
)
GO


ALTER TABLE LookupSchema.CodeCategory ADD CONSTRAINT PK_CodeCategory_CodeCategoryKey
PRIMARY KEY CLUSTERED (CodeCategoryKey)
GO


ALTER TABLE LookupSchema.CodeCategory ADD CONSTRAINT CK_CodeCategory_CodeCategoryName_UNIQUE
UNIQUE (CodeCategoryName)
GO


GRANT SELECT , INSERT, UPDATE, DELETE ON [LookupSchema].[CodeCategory] TO public
GO


Now for some C# code.
I've fixed one area. The form where you pick which tables you want.
//shh marks my changes


//TablesViewerForm.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace CreateStronglyTypedDataSet
{
public partial class TablesViewerForm : Form
{
#region Variables

/// <summary>
/// Passed from DataSetGeneratorForm initialy
/// </summary>
private DataTable allDataTables = null;
/// <summary>
/// Selected tables or views by user
/// </summary>
private DataTable selectedDataTables = null;
/// <summary>
/// Used in allDataTables filteration
/// </summary>
private string mode = String.Empty;
/// <summary>
/// Reference to parent form to set the selected tables and views
/// </summary>
private DatasetGeneratorForm DSGForm = null;

#endregion

#region Methods

/// <summary>
/// Constructor, initializes components, and sets variables
/// </summary>
/// <param name="allDataTables">DataTable contains all tables or views according to selected check boxes in the parent form</param>
/// <param name="mode">Used in allDataTables filteration</param>
/// <param name="DSGForm">Reference to parent form</param>
public TablesViewerForm(DataTable allDataTables, string mode, DatasetGeneratorForm DSGForm)
{
InitializeComponent();
this.allDataTables = allDataTables;
this.mode = mode;
this.DSGForm = DSGForm;
}

/// <summary>
/// Build the selectedDataTables, and sets DSGForm.SelectedTables to it
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void closeBtn_Click(object sender, EventArgs e)
{
selectedDataTables = new DataTable();
DataColumn tableName = new DataColumn("TABLE_NAME", typeof(string));
DataColumn tableType = new DataColumn("TABLE_TYPE", typeof(string));

//shh
DataColumn tableSchema = new DataColumn("TABLE_SCHEMA", typeof(string));


selectedDataTables.Columns.Add(tableName);
selectedDataTables.Columns.Add(tableType);

//shh
selectedDataTables.Columns.Add(tableSchema);

foreach (DataGridViewRow gridViewRow in viewerDG.Rows)
{
DataRow row = (gridViewRow.DataBoundItem as DataRowView).Row;
if (Boolean.Parse(row["IsChoosed"].ToString()) == true)
{
DataRow selectedRow = selectedDataTables.NewRow();

selectedRow["TABLE_NAME"] = row["TableName"];
selectedRow["TABLE_TYPE"] = row["TableType"];

//shh
selectedRow["TABLE_SCHEMA"] = row["TableSchema"];

selectedDataTables.Rows.Add(selectedRow);
}
}
if (selectedDataTables.Rows.Count == 0)
MessageBox.Show("Please select at least one table to be generated", "Error!");
else
{
DSGForm.SelectedTables = selectedDataTables;
this.Close();
}
}

/// <summary>
/// Bind the DataGridView viewerDG
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void TablesViewer_Load(object sender, EventArgs e)
{
DataTable viewerTable = new DataTable();
DataColumn isChoosed = new DataColumn("IsChoosed", typeof(bool));
DataColumn tableName = new DataColumn("TableName", typeof(string));
DataColumn tableType = new DataColumn("TableType", typeof(string));

//shh
DataColumn tableSchema = new DataColumn("TableSchema", typeof(string));


viewerTable.Columns.Add(isChoosed);
viewerTable.Columns.Add(tableName);
viewerTable.Columns.Add(tableType);

//shh
viewerTable.Columns.Add(tableSchema);

DataRow[] filteredRows = allDataTables.Select(mode);
for (int i = 0; i < filteredRows.Length; i++)
{
DataRow row = viewerTable.NewRow();
row["IsChoosed"] = false;
row["TableName"] = filteredRows[i]["TABLE_NAME"];
row["TableType"] = filteredRows[i]["TABLE_TYPE"];

//shh
row["TableSchema"] = filteredRows[i]["TABLE_SCHEMA"];

viewerTable.Rows.Add(row);
}

viewerDG.DataSource = viewerTable;
viewerDG.Columns["isChoosed"].HeaderText = "Check to choose";
viewerDG.Columns["TableName"].HeaderText = "Table Name";
viewerDG.Columns["TableType"].HeaderText = "Table Type";

//shh
viewerDG.Columns["TableSchema"].HeaderText = "Table Schema";

viewerDG.Columns["isChoosed"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
viewerDG.Columns["TableName"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
viewerDG.Columns["TableType"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

//shh
viewerDG.Columns["TableSchema"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;


viewerDG.Columns["TableName"].ReadOnly = true;
viewerDG.Columns["TableType"].ReadOnly = true;

//shh
viewerDG.Columns["TableSchema"].ReadOnly = true;

}

/// <summary>
/// Select all items
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void selectAllRB_CheckedChanged(object sender, EventArgs e)
{
if (selectAllRB.Checked)
ChangeSelected(true);
}

/// <summary>
/// Select none items
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void selectNoneRB_CheckedChanged(object sender, EventArgs e)
{
if (selectNoneRB.Checked)
ChangeSelected(false);
}

/// <summary>
/// Fix the selection of items
/// </summary>
/// <param name="all"></param>
private void ChangeSelected(bool all)
{
foreach (DataGridViewRow gridViewRow in viewerDG.Rows)
{
gridViewRow.Cells["IsChoosed"].Value = all;
}
}

#endregion
}
}






//and then one place I fixed it in DatasetGeneratorForm(.cs) // PrepareGeneratedDataSet method
//however, I don't think I've fixed every situation...ex: views


if (row["TABLE_TYPE"].ToString().ToLower() == "base table")
{
selectString += "SELECT * FROM [" + row["Table_Schema"].ToString() + "].[" + row["Table_Name"].ToString() + "]; ";


//Notice the new code : row["Table_Schema"].ToString()

You got a good project. I would take my suggestions and do a once over and fix the issues dealing with schemas.

http://technet.microsoft.com/en-us/library/dd283095(SQL.100).aspx[^]

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects.

....


One annoyance, is that when I select "Test Connection", it resets the dbname I have selected.
..

My only other minor detail suggestion.
Remove all the hardcoded strings and add some constants.

ex:

private static readonly string TABLE_NAME = "TABLE_NAME";

Thanks for your hard work.
GeneralProblem with fetch relation command Pin
thejet4820-Mar-10 6:22
thejet4820-Mar-10 6:22 
QuestionWhat about TableAdapters ? Pin
Ruchit S.19-Jan-10 0:45
Ruchit S.19-Jan-10 0:45 
GeneralSome errors Pin
Huberto Kusters20-Nov-09 8:42
Huberto Kusters20-Nov-09 8:42 
GeneralRe: Some errors Pin
Akram Mellice21-Nov-09 9:24
Akram Mellice21-Nov-09 9:24 
GeneralRe: Some errors Pin
Akram Mellice2-Dec-09 3:11
Akram Mellice2-Dec-09 3:11 

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.