Schema From a DataSet, Exporting SQL Data to Excel, and More

Article
10/22/2019

Web Q&A

Edited by Nancy Michell

Q How can I get a schema produced from a DataSet that is returned from a stored procedure? I know that this is possible in Visual Studio® .NET through the Generate DataSet dialog box, but I'd like to do this without using Visual Studio. How does the Visual Studio DataSet Generator do what it does? Is this functionality available from Visual Studio as a command-line tool or could I write one in C#?

A The DataReader includes a GetSchemaTable method that returns a DataTable of schema information about the resultset. Each row in the schema DataTable corresponds to a column in the resultset. You can use this schema DataTable to determine column names, data types, and so on for the resultset. If you execute a query with CommandBehavior.KeyInfo, you'll receive additional schema information such as the base table name and column name. That's probably the best way to programmatically discover this schema information (see Figure 1).

Figure 1 Get Schema

string strConn = "Data Source=(local);Initial Catalog=Northwind; Trusted_Connection=Yes;"; SqlConnection cn = new SqlConnection(strConn); cn.Open(); SqlCommand cmd = new SqlCommand("CustOrdersOrders", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@CustomerID", "ALFKI"); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo); DataTable tbl = rdr.GetSchemaTable(); rdr.Close(); cn.Close(); foreach (DataRow row in tbl.Rows) Console.WriteLine("Table: {0}\tColumn: {1}", row["BaseTableName"], row["BaseColumnName"]);

Just remember these few caveats: this won't work well if your stored procedure returns different resultsets depending on input parameters. It also won't work if your stored procedure creates and uses temporary tables. If you execute the query with CommandBehavior.KeyInfo, there's no guarantee that you'll receive additional schema information such as the base table name and column name.

Q I have some data in a SQL Server™ 2000 database and I would like to write a Windows® Forms app to allow users to export and save the data to Microsoft® Excel. How can I do this using C#? I have tried to bind SQL Server tables to an ADO.NET dataset and use WriteXML to save data into an XML file. Then I thought I could open the XML file with Excel. It seems like a simple workaround. Is there any problem doing it this way? Also, how can I export SQL Server table data to an Access database (an MDB file) using C# and/or Visual Basic® .NET?

A To export a SQL Server table into an Access database you can create a Data Transformation Services (DTS) package in SQL Server and execute it from C# or Visual Basic .NET.

See Knowledge Base article 306023, "Transfer Data to an Excel Workbook by Using Visual C# .NET," which provides sample code using ADO. You can also use XSL to transform the data into XML that fits the schema of an Excel workbook. That way, you don't have to use the Excel object model (especially if you want to do this on the server and don't have Microsoft Office installed there).

By creating an XSLT transform to convert exported XML to Excel XML Spreadsheet (XMLSS) format, you make it more Excel-friendly and allow for pre-formatting the results. Just save a simple spreadsheet as XML to get an idea of the basic schema.

However, DTS is the normal way to export data from SQL Server. Once you've created a DTS package, you can execute it programmatically with about two lines of code: one line to load the package into memory, and one line to run it. This allows you to output SQL data in native Excel and Access binary formats. Other approaches are going to be limiting or tedious to implement.

Q I have a question related to performance of XPath-based queries. Are they expensive in terms of runtime and resource utilization when compared to doing the same thing programmatically from within an application?

A In MSXML, XPath is cheap, resource-wise, and the Document Object Model (DOM) is slow. In System.Xml, the DOM is fast, and XPath may be slow (since it enforces XPath data-model rules).

Generally speaking, domain-specific languages (like XPath) beat out generic languages (like C#) for their designed task because they take advantage of special domain knowledge and internal data structures. Also, languages like XPath and SQL can throw away almost all intermediate temporary values, and choose to execute your query differently from how you specified it. C# and other generic languages are usually more constrained, and perform your program exactly the way you wrote it. For example, a descendant query could use an internal index to seek forward to all foo elements directly. You would be unable to do this if you wrote the equivalent code in C#; the common language runtime (CLR) can't do it either. But remember, when it comes to performance, you should always test, measure, and test some more.

Q I use the following piece of code in my automation scripts to start and stop tests:

DateAndTime = DatePart("m", Now) & "/" & DatePart("d", Now) & "/" & DatePart("yyyy", Now) & " " & CStr(UserDefinedStartTime) If (DateDiff("n", DateAndTime, Now) = 0)then NdisTest

When I moved these automation scripts onto machines with German locale settings they no longer worked! I think that the issue lies in the fact that English locales express the date as month/day/year, whereas German builds appear to express it as day/month/year. Originally, I simply tried to use time since that's all I'm really interested in. But when I use the time function, the event never actually fires at the specified time.

Q I use the following piece of code in my automation scripts to start and stop tests:

DateAndTime = DatePart("m", Now) & "/" & DatePart("d", Now) & "/" & DatePart("yyyy", Now) & " " & CStr(UserDefinedStartTime) If (DateDiff("n", DateAndTime, Now) = 0)then NdisTest

The bottom line is that I want a script to do something starting at 9:00 A.M. every morning. At 8:55 A.M. I want the script to stop whatever it's doing, and then start over again at 9:00 A.M. In other words, I want to create a simple loop based on the time. How can I accomplish this and remain independent of locale?

A Why use strings in the first place? Your example takes Now, which is a date, and UserDefinedStartTime (which is presumably a date, though it's difficult to tell from the code), converts them to strings, and passes them to DateDiff—which, of course, turns them right back into dates.

Also, it appears that you are trying to compare the current time to a given time. If so, then why mess around with dates when you could be using times? This should do what you want:

If DateDiff("n", UserDefinedStartTime, Time) = 0 Then

Take note of the way Visual Basic and VBScript represent dates and times. A date/time is stored in a double-precision float in which the signed integer part is the number of days since 30 December 1899 and the fractional part is the portion of the day elapsed. So 0.75 is 30 December 1899, 6 P.M.; -1.75 is 29 Dec 1899, 6 P.M.

Of course, by now you have already realized the potential problems. For example, 0.75 and -0.75 are both the same date/time. Similary, -2.999999 and -1.0 are only microseconds apart, even though there is a gap of almost two whole numbers. Hence you cannot use any simple arithmetic operation on dates whatsoever—rounding, addition, subtraction, and comparison all fail under various circumstances. This explains the necessity of helper functions like DateAdd and DateDiff.

Times are represented—of course—as the fractional part that I just discussed. So does 0.75 mean 6 P.M. or 30 December 1899, 6 P.M.? It depends on the context. That's why you're getting strange results sometimes; you are accidentally treating times as dates and the runtime is getting confused.

You'll also note that this format lacks support for both daylight savings time and time zones, has a very strange epoch date, has precision which varies enormously over its range, and has a range of a trillion trillion trillion trillion times the age of the universe.

If you want to get around these problems, here's one surefire way that works whether you give it times or dates:

Function Fire(UserTime) Fire = (Hour(UserTime) = Hour(Time)) And _ (Minute(UserTime) = Minute(Time)) End Function

This returns True if the hour and minute are equal to the hour and minute of the target; False is returned otherwise.

Q My XML code uses xs:all (see Figure 2). It validates just fine, but I get the following error when I try to add it to a schema cache:

C:\nt\admin\wmi\jobs\server\parser\schema\validate.vbs(20, 1) msxml6.dll: file://jobs.xsd#/schema/complexType[6][@name = 'intervalWeeklyType']/ complexContent[1]/extension[1] <all> is not the only particle in a <group> or being used as an extension.

What's wrong?

Q My XML code uses xs:all (see Figure 2). It validates just fine, but I get the following error when I try to add it to a schema cache:

C:\nt\admin\wmi\jobs\server\parser\schema\validate.vbs(20, 1) msxml6.dll: file://jobs.xsd#/schema/complexType[6][@name = 'intervalWeeklyType']/ complexContent[1]/extension[1] <all> is not the only particle in a <group> or being used as an extension.

What's wrong?

Figure 2 Using xs:all

<xs:complexType name="intervalWeeklyType"> <xs:complexContent> <xs:extension base="daysType"> <xs:all> <xs:element name="WeeksInterval" type="xs:unsignedByte" minOccurs="0" maxOccurs="1" /> </xs:all> </xs:extension> </xs:complexContent> </xs:complexType> <xs:complexType name="daysType"> <xs:all> <xs:element name="Monday" type="xs:boolean" minOccurs="0" maxOccurs="1" /> <xs:element name="Tuesday" type="xs:boolean" minOccurs="0" maxOccurs="1" /> <xs:element name="Wednesday" type="xs:boolean" minOccurs="0" maxOccurs="1" /> <xs:element name="Thursday" type="xs:boolean" minOccurs="0" maxOccurs="1" /> <xs:element name="Friday" type="xs:boolean" minOccurs="0" maxOccurs="1" /> <xs:element name="Saturday" type="xs:boolean" minOccurs="0" maxOccurs="1" /> <xs:element name="Sunday" type="xs:boolean" minOccurs="0" maxOccurs="1" /> </xs:all> </xs:complexType>

A An all group can only appear at the top level and (conceptually at least) you end up with the following in your derived type:

<sequence> <all> ••• </all> <all> ••• </all> </sequence>

Any complexType that uses xs:all cannot have its content model extended (you could still derive by extension and add attributes).

A An all group can only appear at the top level and (conceptually at least) you end up with the following in your derived type:

<sequence> <all> ••• </all> <all> ••• </all> </sequence>

Any complexType that uses xs:all cannot have its content model extended (you could still derive by extension and add attributes).

The MSDN® XML reference should help. In addition, see the W3C XML Schema primer at https://www.w3.org/TR/xmlschema-0.

Q What are my options for inserting binary data into SQL Server 2000 using XML? SQL Server 2000 easily generates base64-encoded binary data in response XML streams, but on insert/update operations, the conversion of a text string into the binary type is a straight character Unicode conversion. The query side is great, but I find the insert side somewhat lacking.

A If you are using SQLXML, you should be able to use Updategrams or Bulkload to upload the binary data. You will need to create an annotated mapping schema with the binary column mapped as xsd:base64Binary, and you will be able to upload the binary data which is stored as binarybase64. You can also upload binary data stored in binary format similarly using the xsd:hexBinary datatype in the mapping schema.

Unless you want to convert the binary data yourself to hex for insertion into SQL Server through OLE DB, XmlBulkload (in SQLXML 3.0) is very close to pure OLE DB calls, especially if the structure of the XML data is simple.

Q How can I use VBScript code to do a reboot on Windows NT® using Shell object, RunDll32, or something equivalent?

A You can use Windows Management Instrumentation (WMI):

Set wmi = GetObject("winmgmts:") Set objset = wmi.instancesof("win32_operatingsystem") For Each obj in objset Set os = obj Exit For Next os.reboot

A You can use Windows Management Instrumentation (WMI):

Set wmi = GetObject("winmgmts:") Set objset = wmi.instancesof("win32_operatingsystem") For Each obj in objset Set os = obj Exit For Next os.reboot

Here are two Knowledge Base articles that will help. Article 149690, "ExitWindowsEx with EWX_LOGOFF Doesn't Work Properly," offers an explanation of the problems using this API to try to programmatically reboot Windows 9x. The other, Article 168796, "ExitWindows Function Declaration Incorrect in API Viewer," offers another example of code to perform this task.

Got a question? Send questions and comments to webqa@microsoft.com.

Thanks to the following Microsoft developers for their technical expertise: Chris Beatie, Michael Brundage, Tom Cox, Derek Denny-Brown, Bruno Denuit, Roberto Di Pietro, Robert Gruen, Martin Gudgin, David He, Brian Heitt, Stephen Howard, Walter Hsueh, Chris Jensen, Douglas Laudenschlager, Eric Lippert, Taha Masood, Gray McDonald, Amar Nalla, Dare Obasanjo, Hossain Rahman, Jeffrey Richter, David Sceppa, Karol Zadora-Przylecki

Share via

Additional resources