Corey Wirun said:
Hi All,
I want to create a Strongly Typed DataSet for a table with a LOT of columns.
Now I could write the XML Schema file by hand (to then give to xsd.exe to
generate the C#), but before I do that, is there any tool/utility I can use
to scrape the table schema out of the database and generate the XML Schema
for me?
Ultimately, I want to load this table into a DataSet via a DataAdapter
(doable, I assume?), but i don't want to traverse rows and columns by
indexes...
I am running SQLServer 2000.
Here's what I use. It only works for SQLServer, and here's how it works.
It's a console app. It will prompt you for a ADO.NET connection string, and
connect to SQL. Then it will go through all the tables and views in the
database and output a ComplexType for each one. All these go into a schema
called RowTypes.xsd. Then you include this schema in your DataSet schemas,
and simply refer to the appropriate types in RowTypes.xsd.
This lets you regenerate RowTypes.xsd whenever you want without deleting
your DataSet xsd's. You still need to right-click on each one and choose
"Run Custom Tool" to regenerate the DataSet classes, but you don't need to
rewrite each of the other xsd's.
I put RowTypes.xsd in my project folder, and then each of the DataSet xsd's
look like this:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="ds_MY_TABLE"
targetNamespace="
http://tempuri.org/ds_MY_TABLE.xsd"
elementFormDefault="qualified"
xmlns="
http://tempuri.org/ds_MY_TABLE.xsd"
xmlns:mstns="
http://tempuri.org/ds_MY_TABLE.xsd"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:xs="
http://www.w3.org/2001/XMLSchema"
xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xs:include schemaLocation="rowtypes.xsd" />
<xs:element name="ds_MY_TABLE" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element type="MY_TABLE"
name="MY_TABLE"
msprop:BaseTable.0="MY_TABLE"
codegen:typedName="MyTableRow"
codegen:typedPlural="MyTableRows" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Notice the <xs:include> and the fact that the table has a type="MY_TABLE",
which is defined in rowtypes.xsd.
You would then use the DataSet designer to add keys and relationships to
this dataset.
RowTypes.exe will also look in the current folder for a config file with a
ConnectionString appSetting. If it finds it it will use that for the
connection string. I use this to add RowTypes as an External Tool in Visual
Studio, running it in my project directory. That way I just click on
External Tools/RowTypes.exe and the rowtypes get updated from the database.
Anyway here's the source for Rowtypes.exe:
Imports System.Data.SqlClient
Imports System.Data
Module Module1
Sub Main()
Try
Call GetRowTypes()
Catch ex As Exception
System.Console.WriteLine(ex.Message & vbCrLf & ex.StackTrace)
Finally
System.Console.WriteLine("Hit Enter to exit.")
System.Console.Read()
End Try
End Sub
Public Sub GetRowTypes()
If Not System.Diagnostics.Debugger.IsAttached Then
'System.Diagnostics.Debugger.Launch()
End If
Dim ds As DataSet
Dim adapt As SqlDataAdapter
Dim tableName As String
Dim connectionString As String
Dim appConfig As New Xml.XmlDocument
If Not IO.File.Exists("rowtypes.xsd") Then
System.Console.WriteLine("rowtypes.xsd not found in " &
IO.Directory.GetCurrentDirectory)
System.Console.Write("Would you like to create it (Y/N)?")
Dim resp As String = System.Console.ReadLine
If Not (resp.Equals("Y") Or resp.Equals("y")) Then
System.Console.WriteLine("rowtypes.xsd not updated")
Return
Else
End If
End If
Try
appConfig.Load("app.config")
Dim nConnect As Xml.XmlNode =
appConfig.SelectSingleNode("//appSettings/add[@key='ConnectionString']")
connectionString = nConnect.Attributes("value").Value
Catch ex As Exception
Console.WriteLine("Please enter connection string.")
connectionString = Console.ReadLine
End Try
If Trim(connectionString) = "" Then
Throw New Exception("Can't find connectionString.")
End If
Console.WriteLine("Connection String found: " & connectionString)
Dim con As New SqlConnection(connectionString)
con.Open()
Const mspropURI As String = "urn:schemas-microsoft-com:xml-msprop"
Const codegenURI As String = "urn:schemas-microsoft-com:xml-msprop"
Const schemaURI As String = "
http://www.w3.org/2001/XMLSchema"
Dim schema As New Xml.Schema.XmlSchema
'xmlns: msprop = "urn:schemas-microsoft-com:xml-msprop"
schema.Namespaces.Add("msprop", mspropURI)
'xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
schema.Namespaces.Add("codegen", mspropURI)
'"
http://www.w3.org/2001/XMLSchema"
schema.Namespaces.Add("xs", schemaURI)
Dim schemaText As New Text.StringBuilder
schema.Write(New IO.StringWriter(schemaText))
Dim schemaDoc As New Xml.XmlDocument
schemaDoc.LoadXml(schemaText.ToString)
Dim typeRoot As Xml.XmlElement = schemaDoc.SelectSingleNode("*")
Dim cmdTablesAndViews As New SqlCommand("select name from (select
table_name name from information_schema.tables union select table_name name
from information_schema.views) dt ", con)
Dim dsTablesAndViews As New DataSet
Call New SqlDataAdapter(cmdTablesAndViews).Fill(dsTablesAndViews)
Dim TableOrView As DataRow
Call New SqlCommand("set rowcount 1", con).ExecuteNonQuery()
Dim cmdCol As New SqlCommand("select @Nullable = is_nullable, @Default
= coalesce(column_default,'') from information_schema.columns where
table_name = @TableName and column_name = @ColumnName if @@rowcount = 0
raiserror('not found',16,1)", con)
Dim pColNullable As SqlParameter = cmdCol.Parameters.Add(New
SqlParameter("@Nullable", SqlDbType.VarChar, 3))
pColNullable.Direction = ParameterDirection.Output
Dim pColDefault As SqlParameter = cmdCol.Parameters.Add(New
SqlParameter("@Default", SqlDbType.VarChar, 255))
pColDefault.Direction = ParameterDirection.Output
Dim pColTableName As SqlParameter = cmdCol.Parameters.Add(New
SqlParameter("@TableName", SqlDbType.VarChar, 255))
Dim pColColumnName As SqlParameter = cmdCol.Parameters.Add(New
SqlParameter("@ColumnName", SqlDbType.VarChar, 255))
cmdCol.Prepare()
For Each TableOrView In dsTablesAndViews.Tables(0).Rows
tableName = TableOrView.Item(0)
Dim doc As Xml.XmlDocument
Dim cmd As New SqlCommand(String.Format("select * from {0} where 1=2",
tableName), con)
ds = New DataSet
adapt = New SqlDataAdapter(cmd)
Try
adapt.Fill(ds)
Dim sb As New Text.StringBuilder
Dim xr As New Xml.XmlTextWriter(New IO.StringWriter(sb))
xr.Formatting = Xml.Formatting.Indented
ds.WriteXmlSchema(xr)
doc = New Xml.XmlDocument
doc.LoadXml(sb.ToString)
Dim typeNode As Xml.XmlElement = doc.SelectSingleNode("*/*/*/*/*/*")
typeNode.SetAttribute("id", tableName & "")
typeNode.SetAttribute("name", tableName & "")
Dim nField As Xml.XmlElement
For Each nField In typeNode.SelectNodes("*/*")
Dim nullable As Boolean
Dim column_default As String
column_default = ""
cmdCol.Parameters("@TableName").Value = tableName
Dim fname As String =
nField.GetAttribute("name").Replace("_x0020_", " ")
fname = fname.Replace("_x0024_", "$")
cmdCol.Parameters("@ColumnName").Value = fname
cmdCol.Parameters("@Nullable").Value = DBNull.Value
Try
cmdCol.ExecuteNonQuery()
Catch ex As Exception
Throw New Exception(String.Format("Error retrieving field
information for {0}.{1} error {2}", tableName, fname, ex.Message))
End Try
nullable = (cmdCol.Parameters("@Nullable").Value = "YES")
column_default = cmdCol.Parameters("@Default").Value
If column_default <> "" _
AndAlso Not column_default.EndsWith("())") Then
Dim defVal As String = column_default.Substring(1,
column_default.Length - 2)
If defVal.ToLower <> "null" Then
If IsDate(defVal) Then
defVal = Date.Parse(defVal).ToString("yyyy-MM-ddThh:mm:ss")
End If
nField.SetAttribute("default", defVal)
End If
End If
If Not nullable Then
nField.SetAttribute("minOccurs", "1")
End If
If nField.GetAttribute("minOccurs") = "0" And
nField.GetAttribute("type") = "xs:string" Then
Dim a As Xml.XmlAttribute =
nField.OwnerDocument.CreateAttribute("codegen", "nullValue", codegenURI)
a.Value = "_empty"
nField.Attributes.Append(a)
End If
Next
typeRoot.AppendChild(schemaDoc.ImportNode(typeNode, True))
Catch ex As Exception
Trace.WriteLine(String.Format("Import rowtype failed for {0} because
{1}", tableName, ex.Message))
Throw ex
End Try
Next
Dim xw As New Xml.XmlTextWriter("rowtypes.xsd",
System.Text.Encoding.UTF8)
xw.Formatting = Xml.Formatting.Indented
Try
schemaDoc.Save(xw)
Catch ex As Exception
System.Console.WriteLine(ex)
System.Console.WriteLine(schemaDoc.InnerXml)
Throw ex
Finally
xw.Close()
End Try
System.Console.WriteLine("rowtypes.xsd sucessfully updated.")
System.Console.WriteLine("You should recompile the solution.")
System.Console.WriteLine("And you will need to run custom tool on the
dataset files, or edit them to get them recompiled")
End Sub
End Module
