WWW Yariv Hammer's Code Site

Tuesday, August 29, 2006

Generating a DataSet From an Xml Schema in 2005

In Visual Studio 2003 the DataSet designer and the Xml Schema designer were the same. In order to generate a Typed DataSet from an Xml Schema, you had to right-click on the Xml Schema designer, and check the Generate DataSet option. Everytime you saved the schema, the DataSet was redrawn for you.

In Visual Studio 2005 we have two seperate designer - one for Typed DataSets and one for Xml Schemas. The option to generate a Typed DataSet from an Xml Schema disappeared from the menus.

How To Generate the DataSet
In the Xml Schema designer, right-click on the designer surface and select Properties (or press F4).
At the Property Grid find the CustomTool property and type MSDataSetGenerator.
The typed dataset will be generated for you when you save the schema. You can see the files in the Solution Explorer.

I have no idea why Microsoft removed the option to generate the DataSet from the menu. The new interface for it is very uncomfortable in my opinion.

Monday, August 28, 2006

Loading the Entire Database Into a DataSet, Including Relations

Ironically, it is hard to load the entire database into a dataset. You either need to use many command objects, or TableAdapters/DataAdapters.
The ralations are even harder to get from the database. You need to manually create them on the DataSet.

In this article I give code to load the entire MS-Access database into an Untyped DataSet, with the relations. You can easily adapt this to SQL Server database. The code is in VB.NET.

The Code
Function getDataSetAndFill(ByRef connection As OleDb.OleDbConnection, Optional ByVal isExportSchema As Boolean = True) As DataSet
Dim myDataSet As New DataSet
Dim myCommand As New OleDb.OleDbCommand
Dim myAdapter As New OleDb.OleDbDataAdapter
myCommand.Connection = connection
'Get Database Tables
Dim tables As DataTable = connection.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
'iterate through all tables
Dim table As DataRow
For Each table In tables.Rows
'get current table's name
Dim tableName As String = table("TABLE_NAME")
Dim strSQL = "SELECT * FROM " & "[" & tableName & "]"
Dim adapter1 As New OleDb.OleDbDataAdapter(New OleDb.OleDbCommand(strSQL, connection))
adapter1.FillSchema(myDataSet, SchemaType.Source, tableName)
'Fill the table in the dataset
myCommand.CommandText = strSQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myDataSet, tableName)

'Add relationships to dataset
'First, get relationships names from database (as well as parent table and child table names)
Dim namesQuery As String = "SELECT DISTINCT szRelationship, szReferencedObject, szObject FROM MSysRelationships"
Dim namesCommand As New System.Data.OleDb.OleDbCommand(namesQuery, connection)
Dim namesAdapter As New System.Data.OleDb.OleDbDataAdapter(namesCommand)
Dim namesDataTable As New DataTable
'Now, get MSysRelationship from database
Dim relationsQuery As String = "SELECT * FROM MSysRelationships"
Dim command As New System.Data.OleDb.OleDbCommand(relationsQuery, connection)
Dim adapter As New System.Data.OleDb.OleDbDataAdapter(command)
Dim relationsDataTable As New DataTable
Dim relationsView As DataView = relationsDataTable.DefaultView
Dim relationName As String
Dim parentTableName As String
Dim childTablename As String
Dim row As DataRow
For Each relation As DataRow In namesDataTable.Rows
relationName = relation("szRelationship")
parentTableName = relation("szReferencedObject")
childTablename = relation("szObject")
'Keep only the record of the current relationship
relationsView.RowFilter = "szRelationship = '" & relationName & "'"
'Declare two arrays for parent and child columns arguments
Dim parentColumns(relationsView.Count - 1) As DataColumn
Dim childColumns(relationsView.Count - 1) As DataColumn
For i As Integer = 0 To relationsView.Count - 1
parentColumns(i) = myDataSet.Tables(parentTableName). Columns(relationsView.Item(i)("szReferencedColumn"))
childColumns(i) = myDataSet.Tables(childTablename). Columns(relationsView.Item(i)("szColumn"))
Dim newRelation As New DataRelation(relationName, parentColumns, childColumns, False)
If isExportSchema Then
Dim schemaName = GetXmlSchemaFileName()
If File.Exists(schemaName) Then File.SetAttributes(schemaName, FileAttributes.Normal)
End If
Return myDataSet
End Function

Loading a class dynamically

When you write software that can be extended, you have to allow othe rprogrammers to write their own classes, and then load those classes to your application and use them.
For example, let's assume you program a graphical tool, and you prvide square, circle and triangle shapes. You want other users to provide any other shapes, so you let them program the shapes in their own class library and then you load the shapes into your application.

Loading classes from different assemblies
The line of code needed to load a class that you don't know is:
object objectFromOtherDll = Activator.CreateInstance ("assemblyName", "Namespace.DllClass").Unwrap();

assemblyName - The name of the class library where the class is stored (without '.dll').
Namespace - The full name of the namespace.
DllClass - The name of the class you want to load.

Note - A file with the name assemblyName.dll must be placed in the same folder as the executable.
Activator is a class in the System namespace. CreateInstance has other overloads. You might want to look in

But how can we know which assembly did the user provide, and what class name to use? As you saw those are strings that you need to put in your code, so it's definitely an issue. The best way is to place them in the App.Config file (or other configuration file). The programmer who supply the extension should put the names of the classes, with their namespaces and assembly names in the configuration file that you provide for your application. You will need to read the configuration file (should be easy with the System.Configuration namespace), and load the classes that were provided in the configuration.

Invoking a method of the unknown class
Now we have an object that we know nothing about. We want to call a method of the object. We must know the name of the method. In the example of Shape, we have the Draw method.
We can use reflection to invoke the method. First we need a using directive to the System.Reflection namespace.
Then we need to invoke the method like this:
objectFromOtherDll.GetType().InvokeMember("methodName", BindingFlags.DeclaredOnly BindingFlags.Public BindingFlags.NonPublic BindingFlags.Instance BindingFlags.InvokeMethod, null, objectFromOtherDll, null);
More information about InvokeMember can be found here:

objectFromOtherDll is the object we created with Activator. It appears twice in the statement (once as a parameter) so pay attention.
methodName is a string containing the name of the method to invoke.

How do we know the method name? We could use a configuration file.
How do we know that there is a method called methodName? For example, the programmer might not have implemented the "Draw" method of the Shape class. We could know that by catching exception, or by using reflection to find a method called "Draw".

Using Interfaces
A better way, in my opinion, is to use interfaces instead of reflection. The idea is that you supply a class library with all the interface that you require the programmer to supply to you. The programmer must implement the interface if he wants his object to be loaded in your framework.

For example, in a class library called Interfaces, I add an interface called IShape.
public interface IShape
void Draw();

The programmer need to refernce to the Interface.dll you gave him, and each Shape must implement the IShape interface:
public class Ellipse:IShape
public void Draw()
//Do Whatever

The programmer will put in your configuration file the class Ellipse, and place his assembly in the folder of your application.

You will load his class in the following way:
IShape shape = Activator.CreateInstance("assemblyName", "CustomShapes.Ellipse").Unwrap() as IShape;

if (IShape == null) throw new Exception("Illegal class");
shape .Draw(); //No reflection needed

In this article I showed how to use Activator to load classes that you don't know about in advance.
I showed how to use reflection in order to invoke a member.
I showed how to use interfaces to help the programmer supply the right code for you.

Feel free to use everything here. Add links to my site if you wish.

Do not copy anything to other sites without adding link to here.

All the contents of the site belong to Yariv Hammer.