This method of adding queries is powerful, but time consuming. So here is the purpose of this
article--wouldn't it be nice to have the queries generated automatically? It took a few tricks,
but here is how it happens.
First, decide which fields need filtering queries added. The automated process looks into the metadata of the database and finds all the indexed fields and adds queries to the table adapter for each. This list will include all of the foreign keys and any other fields that have indexes in the database. Currently, only single field indexes are added, but the procedure could be expanded to include multi-field indexes.
A Look at What Was Generated
When the above wizard completed, it generated a new method on the Table Adapter. Looking at the Object Browser shows the signature of the method.
The code that was generated is straight forward, but references the command array, so modifying this code directly would require making changes to generated code and would be written over the next time the dataset was generated.
<Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter"), _
Global.System.ComponentModel.DataObjectMethodAttribute(Global.System.ComponentModel.DataObjectMethodType.Fill, false)> _
Public Overloads Overridable Function FillByCustomerID(ByVal dataTable As NorthwindDataSet.OrdersDataTable, ByVal CustomerID As String) As Integer
Me.Adapter.SelectCommand = Me.CommandCollection(1)
If (CustomerID Is Nothing) Then
Me.Adapter.SelectCommand.Parameters(0).Value = Global.System.DBNull.Value
Else
Me.Adapter.SelectCommand.Parameters(0).Value = CType(CustomerID,String)
End If
If (Me.ClearBeforeFill = true) Then
dataTable.Clear
End If
Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
Return returnValue
End Function
Modifying the XML Schema for the DataSet
Another approach to automatically add the filtering queries is to modify the XML that defines the dataset and let Visual Studio generate the methods. This requires some manipulation of XML, but additions are preserved.
Datasets are stored in XML format and converted to a visual format by the Dataset Editor. Now we need to
see the raw XML behind the dataset so, right click on the .XSD file and select "Open With..." and
"XML Editor" to examine the XML and see how the file is constructed. The first part of the file
is the table adapters and the second half is the dataset. Of particular interest here is the section <Sources>
(under the table adapters).
For each additional query there is a DbSource, DbCommand, and Parameter section. To automatically build the queries in the dataset, these sections need to be created from the metadata.
What Queries Need To Be Generated?
The first step is to build a list of the fields for which to create queries. The query pulls from the system tables to list the foreign key and indexed fields. (This should be updated to use the metadata functions, but it is something I pulled out of an old app and it works.) Here is how to pull it together.
Add a new dataset to the CodeGen project and name it IndexDataSet.
Drag and drop any table on to it (it doesn't matter which one) to create the table adapter.
-
Right click configure the table and substitute the following SQL.
SELECT DISTINCT
SO.name AS TableName, SC.name AS FieldName, ST.name + CASE WHEN sc.length <> st.length THEN '(' + CONVERT(varchar, sc.length)
+ ')' ELSE '' END AS DataTypeLong, ST.name AS DataType, '' AS NetDataType
FROM sys.systypes AS ST INNER JOIN
sys.syscolumns AS SC INNER JOIN
sys.sysobjects AS SO ON SC.id = SO.id ON ST.xtype = SC.xtype LEFT OUTER JOIN
sys.sysforeignkeys AS FK ON SC.id = FK.fkeyid AND SC.colid = FK.fkey LEFT OUTER JOIN
sys.sysindexkeys AS SIK INNER JOIN
sys.sysindexes AS SI ON SIK.indid = SI.indid AND SIK.id = SI.id ON SO.id = SI.id AND SC.colid = SIK.colid AND SC.id = SIK.id
WHERE (SO.xtype = 'U') AND (SIK.keyno IS NOT NULL) AND (SC.name IS NOT NULL) AND
(SI.status / 2048 & 1 = 0) OR (SO.xtype = 'U') AND (FK.constid IS NOT NULL)
ORDER BY TableName, FieldName
(Note: For SQL Server 2000, you may need to remove the "sys." schema in this code)
Complete the wizard and then change the name of the table to sys_IndexList (in keeping with the naming scheme of the metadata tables).
-
In the code behind for the dataset (right click, view code), add a method that will accept a connection string for the database to read the metadata from:
Partial Class IndexDataSet
Shared taIndexList As New IndexDataSetTableAdapters.sys_IndexListTableAdapter
Partial Public Class sys_IndexListDataTable
Public Sub Fill(Optional ByVal ConString As String = "")
If ConString.length > 0 Then
taIndexList.Connection.ConnectionString = ConString
End If
taIndexList.Fill(Me)
End Sub
End Class
End Class
Save and close the dataset
This dataset will be used in the code to return a list of tables and indexed fields.
For the front-end to select the dataset and launch the program, the form created
in the previous articles can be used (see below). OK, it has been improved a
bit--the new version is available in the related code download.
The "Add Index Queries to Dataset" button has been added to call the code. The code behind the button is as follows:
Private Sub AddQueryButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddQueryButton.Click
If ListBox1.SelectedItem IsNot Nothing Then
'find the document file for the dataset
Dim dir As String = Me.TextBox1.Text.Substring(0, Me.TextBox1.Text.LastIndexOf("\Debug\") - 3) 'get rid of the bin or obj
Dim file As String = dir & ListBox1.SelectedItem & ".xsd"
If Not My.Computer.FileSystem.FileExists(file) Then
Me.OpenFileDialog1.FileName = ""
Me.OpenFileDialog1.InitialDirectory = file
Me.OpenFileDialog1.DefaultExt = ".xsd"
Me.OpenFileDialog1.Title = "Identify the XSD file for the dataset"
Me.OpenFileDialog1.ShowDialog()
file = Me.OpenFileDialog1.FileName
Me.OpenFileDialog1.Dispose()
End If
DataSetEdit.TableAdapterUpdate(file)
End If
End Sub
This code assumes the form has already identified the project that the dataset is in and also that the file was opened from the Debug directory. The code tries to put together the filename and if it can't, opens the file dialog to get the filename from the user.
Editing the XML
The XML namespace has many tools to help manipulate an XML file. One problem that I ran into is that the dataset .XSD file has a combination of elements, some that use a namespace prefix and some that don't. This causes a problem with the XPath function. I spent almost a day trying to make it work including defining a default namespace. I ended up skipping the XPath search and using the Document Object Model (DOM) functions. It requires more code to walk the node tree, but does work.
Let's get started putting the code together. Start by adding a new Class Library to the project named DatasetEdit.vb. Add a shared method named TableAdapterUpdate with the file name passed in as a parameter.
Public Shared Sub TableAdapterUpdate(ByVal dsFileName As String)
Dim saveFlag As Boolean = False
Dim xdoc As New System.Xml.XmlDocument
Load the XML file into the DOM. Defining the namespace is probably not important since the XPath function is not used, but the technique could be helpful to someone.
xdoc.Load(dsFileName)
'set up the namespace manager
Dim man As New XmlNamespaceManager(xdoc.NameTable)
man.AddNamespace("xs", "http://www.w3.org/2001/XMLSchema")
Dim schema As XmlNode = xdoc.SelectSingleNode("xs:schema", man)
man.AddNamespace("ns", schema.Attributes("xmlns").Value)
man.AddNamespace("msprop", schema.Attributes("xmlns:msdata").Value)
man.AddNamespace("msdata", schema.Attributes("xmlns:msprop").Value)
Load the dataset with the indexed fields from the proper database. Look at the table adapter and glean the connection name and then get the connection string from settings.
'get the indexes
Dim ds As New IndexDataSet
'get the connection ref of the first table adapter
Dim conString As String = ""
Dim conName As String = root.Item("DataSource").Item("Tables").FirstChild.Item("MainSource").Item("DbSource").Attributes("ConnectionRef").Value
'get the connection string from the settings ref
For Each conNode As XmlNode In root.Item("DataSource").Item("Connections")
If conNode.Attributes("Name").Value = conName Then
conString = System.Configuration.ConfigurationManager.ConnectionStrings("WinMetadata.My.MySettings." & conNode.Attributes("AppSettingsPropertyName").Value).ConnectionString
Exit For
End If
Next
If conString.Length > 0 Then 'if specific connection string found, use it
ds.sys_IndexList.Fill(conString)
Else
ds.sys_IndexList.Fill()
End If
In this case, the root node we will work with is the one that contains the table adapters. Looping through each node in the root will allow each table adapter to be processed.
Dim sourcesNode As XmlNode, taName As String, dsFillName As String
Dim root As XmlNode = xdoc.SelectSingleNode("xs:schema/xs:annotation/xs:appinfo", man)
For Each taNode As XmlNode In root.Item("DataSource").Item("Tables").ChildNodes
The queries are stored in a node called <Sources> under the table adapter. Each child node features a filtering query. The For loop makes a delimited list of the names of the fill methods that already exist so they are preserved and not overwritten or duplicated.
taName = taNode.Attributes("Name").Value
sourcesNode = taNode.Item("Sources")
'Get a list of the existing queries
Dim existingList As String = ""
For Each dsNode As XmlNode In sourcesNode.ChildNodes
dsFillName = dsNode.Attributes("FillMethodName").Value
existingList += dsFillName & ","
Next
'add a source for each index field
For Each fld As IndexDataSet.sys_IndexListRow In ds.sys_IndexList.Select("TableName='" & taName & "'")
'skip if the query already exist
If existingList.IndexOf("FillBy" & fld.FieldName) = -1 Then
'add the query to the adapter
Dim newNode As XmlNode = CreateIndexNode(xdoc, taNode, taName, fld.FieldName, fld.DataType, fld.NetDataType)
sourcesNode.AppendChild(newNode)
saveFlag = True
End If
Next
Next
If saveFlag Then
xdoc.Save(dsFileName)
I made an effort to automatically run the command line XSD generator to build the code, but was never successful. If anyone can get this working, let me know.
''generate DataSet designer class using XSD.exe tool
'Dim proc As New System.Diagnostics.Process()
'proc.EnableRaisingEvents = False
'proc.StartInfo.UseShellExecute = False
'proc.StartInfo.RedirectStandardOutput = True
'proc.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden
'proc.StartInfo.WorkingDirectory = dsFileName.Substring(0, dsFileName.LastIndexOf("\"))
'proc.StartInfo.FileName = "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\xsd.exe"
'proc.StartInfo.Arguments = dsFileName + " /dataset /o:" + dsFileName.Substring(0, dsFileName.LastIndexOf("\")) ' /namespace:" + Settings.Default.Namespace
'proc.Start()
'proc.WaitForExit(60000)
'proc.StartInfo.FileName = "C:\Windows\System32\xcopy.exe"
'proc.StartInfo.Arguments = dsFileName.Substring(0, dsFileName.LastIndexOf(".")) & " "
It turns out that running the command line generator is not necessary. In Visual Studio the generate can be forced by right clicking on the XSD file in Solutions Explorer and selecting "Run Custom Tool". Here is the end of the method:
MessageBox.Show("Queries Added to XSD. Open the DataSet in Visual Studio and reconfigure each table to build the added queries.")
End If
End Sub
This leaves the code for the CreateIndexNode Function called from the code above. This section of code manipulates XML nodes and creates a new set of nodes for the query.
Shared Function CreateIndexNode(ByRef xdoc As XmlDocument, ByVal taNode As XmlNode, ByVal taName As String, ByVal fldName As String, ByVal fldType As String, ByVal fldTypeNet As String)
Dim dbNode As XmlNode, selNode As XmlNode, insNode As XmlNode, parNode As XmlNode, attr As XmlAttribute
'get the dbsource node for the table
dbNode = taNode.Item("MainSource").Item("DbSource")
If dbNode IsNot Nothing Then
'use the existing DbSource node for the table as a model
Dim newNode As XmlNode = dbNode.CloneNode(True)
'get rid of the modify commands, leaving only the select command
If newNode.Item("DeleteCommand") IsNot Nothing Then newNode.RemoveChild(newNode.Item("DeleteCommand"))
If newNode.Item("UpdateCommand") IsNot Nothing Then newNode.RemoveChild(newNode.Item("UpdateCommand"))
'chang the names of the methods
newNode.Attributes("FillMethodName").Value = "FillBy" & fldName
newNode.Attributes("GeneratorSourceName").Value = "FillBy" & fldName
newNode.Attributes("UserSourceName").Value = "FillBy" & fldName
newNode.Attributes("GetMethodName").Value = "GetDataBy" & fldName
newNode.Attributes("GeneratorGetMethodName").Value = "GetDataBy" & fldName
newNode.Attributes("UserGetMethodName").Value = "GetDataBy" & fldName
parNode = Nothing
insNode = newNode.Item("InsertCommand")
If insNode Is Nothing Then
'TODO - if there is no insert command, then generate the parameter node
'get the Insert command node
If insNode.Item("DbCommand").Attributes("CommandType").Value = "StoredProcedure" Then
'TODO check for command type as stored procedure - change accordingly
End If
Else
'find the parameter for the indexed field (search through each since xpath does not work)
For Each xNode As XmlNode In insNode.Item("DbCommand").Item("Parameters").ChildNodes
If xNode.Attributes("SourceColumn").Value = fldName Then
parNode = xNode.CloneNode(True)
Exit For
End If
Next
End If
If parNode IsNot Nothing Then
parNode.Attributes("AutogeneratedName").Value = fldName
parNode.Attributes("ParameterName").Value = fldName
'add 'ColumnName' attribute
attr = xdoc.CreateAttribute("ColumnName")
attr.Value = fldName
parNode.Attributes.SetNamedItem(attr)
Else
'need to construct the parameter node
parNode = xdoc.CreateNode(XmlNodeType.Element, "", "Parameter", "")
attr = xdoc.CreateAttribute("AllowDbNull")
attr.Value = "False"
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("AutogeneratedName")
attr.Value = fldName
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("DataSourceName")
attr.Value = fldName
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("DbType")
attr.Value = fldTypeNet
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("Direction")
attr.Value = "Input"
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("ParameterName")
attr.Value = "@" + fldName
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("ProviderType")
attr.Value = fldType
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("SourceColumn")
attr.Value = fldName
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("SourceColumnNullMapping")
attr.Value = "False"
parNode.Attributes.SetNamedItem(attr)
attr = xdoc.CreateAttribute("SourceVersion")
attr.Value = "Current"
parNode.Attributes.SetNamedItem(attr)
End If
'get the select command node
selNode = newNode.Item("SelectCommand")
Dim comText As String = selNode.Item("DbCommand").Item("CommandText").InnerText
'is there already a where clause?
If comText.IndexOf(" Where ") > 0 Then
comText += " AND " 'This assumes Where is last clause -- no group by
Else 'add the where clause
comText += " Where "
End If
comText += fldName & " = @" & fldName
'replace the command text
selNode.Item("DbCommand").Item("CommandText").InnerText = comText
'add the parameter node
'TODO: if stored procedure type, need to add a ReturnValue parameter
selNode.Item("DbCommand").Item("Parameters").AppendChild(parNode)
'get rid of the insert command
newNode.RemoveChild(newNode.Item("InsertCommand"))
Return newNode
Else
Return Nothing
End If
End Function
This is a lot of code to browse through, but hopefully there are enough comments to explain what is happening.
Running the Code
Now run the code to get the Dataset Select form. Select the assembly for the project and then select the Northwind Orders dataset. Click the "Add Index Queries to Dataset" button to run the code. The code should run very quickly.
After closing the application, go back to the Northwind Orders dataset. In order to see the new queries, force Visual Studio to rebuild the tables by right clicking on each table and select "Configure". Just click "Finish" on the wizard to force rebuilding. The new queries should show in the table adapter section (Orders has a lot of indexed fields).
Note: After messing with the XML behind the dataset, you may need to manually regenerate the designer code. When viewing the dataset in the Solution explorer with "Show All Files" turned on (icon button at the top of the Solution Explorer) and you don't see the DatasetName.designer.vb (or .cs) file then right-click on the dataset main .XSD file and select "Run Custom Tool". This will force the Visual Studio code generator to run. (The DataSet Visual Editor must be closed to run this tool.)
Using the Generated Functions
The code generator from part 3 of this article series will generate wrappers for all of the new queries when you click the other "Generate" buttons on the form. In your presentation logic, when you need to fill a data table, you should have "FillBy…" functions for each indexed field in the database to choose from.
