Click here to Skip to main content
15,867,568 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 
"Save Generated Strongly Typed DataSet As"

Because you're mainly setting the filename......and not doing the generating at that moment.

That was a little confusing to me.


"Set Output Filename" (or similar)

would be clearer in my opinion.
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 
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.