14: Publishing Information Using SQLXML in VS .NET

Printer DataMatrix in VS .NET 14: Publishing Information Using SQLXML

14: Publishing Information Using SQLXML
Data Matrix Recognizer In .NET
Using Barcode Control SDK for .NET framework Control to generate, create, read, scan barcode image in .NET applications.
Make ECC200 In .NET
Using Barcode creation for VS .NET Control to generate, create Data Matrix image in Visual Studio .NET applications.
Templates You can also use an inline template as a query. You need to specify CommandType as SqlXmlCommandType.Template and provide a string with the template as a CommandText:
Scanning Data Matrix In .NET
Using Barcode decoder for .NET Control to read, scan read, scan image in .NET applications.
Barcode Printer In .NET
Using Barcode generator for .NET framework Control to generate, create barcode image in Visual Studio .NET applications.
objSqlXmlCmd.CommandType = SqlXmlCommandType.Template objSqlXmlCmd.CommandText = _ & "<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & vbCrLf _ & " <sql:query>" & vbCrLf _ & " select * from Equipment for XML auto, elements" & vbCrLf _ & " </sql:query>" & vbCrLf _ & " </root>"
Barcode Scanner In .NET Framework
Using Barcode recognizer for Visual Studio .NET Control to read, scan read, scan image in .NET applications.
Encode Data Matrix ECC200 In C#
Using Barcode creator for .NET Control to generate, create ECC200 image in .NET framework applications.
The template can also be in an external file, in which case you must use SqlXmlCommandType.TemplateFile constant and set CommandText to the location of the template file:
ECC200 Encoder In VS .NET
Using Barcode creation for ASP.NET Control to generate, create Data Matrix 2d barcode image in ASP.NET applications.
Encoding Data Matrix ECC200 In VB.NET
Using Barcode maker for .NET Control to generate, create DataMatrix image in VS .NET applications.
objSqlXmlCmd.CommandType = SqlXmlCommandType.TemplateFile objSqlXmlCmd.CommandText = "ListEquipment.xml"
Encoding Universal Product Code Version A In .NET Framework
Using Barcode printer for VS .NET Control to generate, create UPC Code image in .NET applications.
Code 128A Generation In Visual Studio .NET
Using Barcode generator for VS .NET Control to generate, create Code 128A image in Visual Studio .NET applications.
The template can also be a stream, in which case you need to assign the template to the CommandStream property (instead of CommandText). You also need to set CommandType to SqlXmlCommandType.Template, even if the origin of the stream is a file, as in this case:
Encoding Barcode In .NET Framework
Using Barcode generation for .NET framework Control to generate, create barcode image in VS .NET applications.
Printing Leitcode In .NET Framework
Using Barcode encoder for VS .NET Control to generate, create Leitcode image in Visual Studio .NET applications.
Dim objStrmTemplate As New FileStream ("ListEquipment.xml", _ FileMode.Open, FileAccess.Read) objSqlXmlCmd.CommandType = SqlXmlCommandType.Template objSqlXmlCmd.CommandStream = objStrmTemplate
Create Barcode In Objective-C
Using Barcode creator for iPad Control to generate, create bar code image in iPad applications.
Make EAN-13 In Java
Using Barcode maker for BIRT reports Control to generate, create EAN 13 image in BIRT reports applications.
XSL Transformation SqlXmlCommand calls can also transform XML documents using XSL files. In the following example, the prListEquipment2_xml stored procedure returns an XML stream that will be converted using Equipment.xsl into an HTML stream to be saved in the Inventory.htm file:
Generating Barcode In None
Using Barcode creator for Font Control to generate, create barcode image in Font applications.
Create Barcode In None
Using Barcode generation for Microsoft Word Control to generate, create barcode image in Word applications.
Imports System Imports System.IO Imports Microsoft.Data.SqlXml 'The SQLXML managed classes Module Module1 Sub Main() 'Instantiate the SqlXmlCommand object and the connection Dim objSqlXmlCmd As New SqlXmlCommand("Provider=SQLOLEDB;" _
Code 128C Drawer In Java
Using Barcode generation for Android Control to generate, create USS Code 128 image in Android applications.
Generate Barcode In Visual C#
Using Barcode printer for Visual Studio .NET Control to generate, create barcode image in VS .NET applications.
SQL Server 2000 Stored Procedure & XML Programming
Code 128 Code Set C Generation In Java
Using Barcode maker for Java Control to generate, create Code 128C image in Java applications.
Drawing Bar Code In Java
Using Barcode drawer for BIRT reports Control to generate, create bar code image in BIRT reports applications.
& "Server=(local)\SS2K;Database=Asset;" _ & "Integrated Security=SSPI") 'Set type of query objSqlXmlCmd.CommandType = SqlXmlCommandType.Sql 'Set the query objSqlXmlCmd.CommandText = "exec prListEquipment2_xml" objSqlXmlCmd.RootTag = "Root" objSqlXmlCmd.XslPath = "Equipment.xsl" 'execute into a stream reader Dim objStrmReader As New StreamReader(objSqlXmlCmd.ExecuteStream()) 'prepare destination file Dim objResult As New FileStream("Inventory.htm", FileMode.Create) Dim objStrmWriter As New StreamWriter(objResult) 'write result into destination file objStrmWriter.WriteLine(objStrmReader.ReadToEnd) objStrmWriter.Close() objResult.Close() objStrmReader.Close() End Sub End Module
XML Views and XPath Queries You can also use XPath queries against XML views (or annotated schemas) to retrieve data from the database. You need to specify CommandType as XPath, point to the schema file, and specify the XPath query, before you execute the SqlXmlCommand object in the usual manner:
'Set type of query objSqlXmlCmd.CommandType = SqlXmlCommandType.XPath 'Point to schema file objSqlXmlCmd.SchemaPath = "InvSchema.xsd" 'Set the XPath query objSqlXmlCmd.CommandText = "/ROOT/Inventory[@StatusId=5]" objSqlXmlCmd.RootTag = "DefectiveInventory"
Retrieving XML Data Using the SqlXmlParameter Class
Relational queries (including stored procedures) and templates can have parameters. The SqlXmlParameter class is used to pass values, for which the user has been prompted, to SqlXmlCommand query objects.
14: Publishing Information Using SQLXML
In the following example, a T-SQL statement (in this case, a stored procedure) is prepared for execution with as a placeholder for the parameter. A SqlXmlParameter object is declared, instantiated (in the context of a SqlXmlCommand object), and named. After the user is prompted, the value is assigned to the parameter, and finally the query is executed:
'SqlXml_Param Imports System Imports System.IO Imports Microsoft.Data.SqlXml 'The SQLXML managed classes Module Module1 Sub Main() 'Instantiate the SqlXmlCommand object and the connection Dim objSqlXmlCmd As New SqlXmlCommand("Provider=SQLOLEDB;" _ & "Server=(local)\SS2K;Database=Asset;" _ & "Integrated Security=SSPI") 'Set type of query objSqlXmlCmd.CommandType = SqlXmlCommandType.Sql 'Set the parameterized query objSqlXmlCmd.CommandText = "exec prGetEquipment_xml " 'define parameter Dim objParam As SqlXmlParameter 'instantiate parameter objParam = objSqlXmlCmd.CreateParameter() objParam.Name = "EquipmentId" 'prompt for the value Console.WriteLine("Equipment Id:") objParam.Value = Console.Read() 'Execute the query and retrieve result as a stream Dim objResult As MemoryStream = objSqlXmlCmd.ExecuteStream() 'Write result to the console Dim objStreamReader As New StreamReader(objResult) Console.Write(objStreamReader.ReadToEnd) End Sub End Module
Retrieving DataSet Using the SqlXmlAdapter Class
A DataSet object is very convenient for further processing since many other classes and methods (in other words, data-bound controls) are designed to use it. The
SQL Server 2000 Stored Procedure & XML Programming
following example is a simple Windows application that consists of a form (frmGrid) that contains a grid (gridTable) and a button (btnLoad). When the user clicks btnLoad, the application connects to the database using the SqlXmlCommand, retrieves data in the form of SqlXmlAdapter, fills the data set, and finally passes the data set content to the grid (only one line is needed):
'SqlXml2Grid.frmGrid Imports Microsoft.Data.SqlXml 'The SQLXML managed classes Public Class frmGrid Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code " ... #End Region Private Sub btnLoad_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLoad.Click Dim objSqlXmlCmd As New SqlXmlCommand("Provider=SQLOLEDB;" _ & "Server=(local)\SS2K;Database=Asset;" _ & "Integrated Security=SSPI") objSqlXmlCmd.CommandType = SqlXmlCommandType.Sql objSqlXmlCmd.CommandText = "exec prListEquipment2_xml" objSqlXmlCmd.RootTag = "Root" Dim objAdpt = New SqlXmlAdapter(objSqlXmlCmd) Dim objDs = New DataSet() objAdpt.Fill(objDs) 'load it to grid gridTable.DataSource = objDs.Tables(0).DefaultView End Sub End Class
Copyright © OnBarcode.com . All rights reserved.