Web と Web サービス経由で SQL Server 2000 および Reporting Services と XML を交換する

Public Function ConnectToSQLServer(ByVal SrvName As String,
Optional ByVal DbName As String = "master", Optional ByVal UsrName As String,
Optional ByVal Passwd as String) 
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 0
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = SrvName
cn.Properties("Initial Catalog").Value = DatabaseName
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
End Function
Public Function ExecuteForXMLSQL (ByVal MySQLQuery As String,
Optional ByVal isXML As Boolean, Optional ByVal strFileSavePath As String)
Dim mystr1, mystr2 As String
Dim objStream, adostreamquery As ADODB.Stream
On Error GoTo ErrorTrap:
cn.CursorLocation = adUseServer
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn.ConnectionString
cmd.CommandType = adCmdText
Set adostreamquery = New ADODB.Stream
adostreamquery.Open 
adostreamquery.WriteText <?xml version="1.0" standalone="yes" ?>
adostreamquery.WriteText "<ROOT xmlns:sql='urn:schemas-microsoft- com:xml-sql'> ", 
adWriteChar
adostreamquery.WriteText " <sql:query> ", adWriteChar
adostreamquery.WriteText MySQL, adWriteChar
adostreamquery.WriteText " </sql:query> ", adWriteChar
adostreamquery.WriteText "</ROOT> ", adWriteChar
adostreamquery.Position = 0
Set cmd.CommandStream = adostreamquery
''' XPATH コマンドの場合は、次の Dialect が異なります。
cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
Set objStream = New ADODB.Stream
objStream.Open
cmd.Properties("Output Stream") = objStream
cmd.Execute , , adExecuteStream
objStream.Position = 0
objStream.SaveToFile strFileSavePath, adSaveCreateOverWrite
ExecuteForXMLSQL = "File Saved at Share :" & strFileSavePath
End Function 

adostreamquery.WriteText "<ROOT xmlns: sql='urn: schemas-microsoft- com: xml-sql'> ", adWriteChar 

<%@ LANGUAGE="VBScript" %>
<% Option Explicit %>
<script LANGUAGE="VBScript" runat = server>
</script><% 
Dim myobject, Mystring, MySQLQuery, strTableName, strColName, strTopNumRecords, 
strSavePath
strTableName = Trim(request.QueryString("TableName"))
strColName = Trim(request.QueryString("ColName"))
strTopNumRecords = Trim(request.QueryString("TopNumRecords"))
strSavePath = Trim(request.QueryString("SavePath"))
set myobject = server.CreateObject("adodata.server")
myobject.Connect "MySQLServer", "MyDatabaseName"
MySQLQuery= "select TOP " &  strTopNumRecords & " " & strColName & " from " & 
strTableName & "(nolock) FOR XML AUTO"
Mystring =myobject.ExecuteSQL(MySQLQuery,"true",strSavePath) 
set myobject = nothing %>

public bool GetDataFromASPXWebPage(string SourceUrl,    
string Savepath){// 新しい 'HttpWebRequest' オブジェクトを作成します
try{
  HttpWebRequest myHttpWebRequest=
  (HttpWebRequest)WebRequest.Create(SourceUrl);
  // HttpWebRequest の 'Timeout' プロパティを設定します
  myHttpWebRequest.Timeout=1000;
  myHttpWebResponse=(HttpWebResponse)myHttpWebRequest.GetResponse();
  Stream response = myHttpWebResponse.GetResponseStream();
  Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
  // ストリームをストリーム リーダーにパイプ出力します
  StreamReader readStream = new StreamReader( response, encode );
  Console.WriteLine(readStream.ReadToEnd()); 
  readStream.Close();
  return true;}
catch (Exception e)
{Console.WriteLine("{0} 例外がキャッチされました。", e);
Console.WriteLine("{0} 例外がキャッチされました。", e.Message);}
return false;}
// 次のコードを使用して、上記の c# メソッドが呼び出されます
string URL="http://WebServer/Web.asp?TableName=mytblname&ColName=myColumnname
&TopNumRecords=1000&SavePath=\\\\RemoteServer\\XML\\WebXMLData.xml";
try{NewSourceSavePath = SourceSavePath + "\\" + "WebXMLData.xml";
   T0 = System.DateTime.Now;
   if(GetDataFromASPXWebPage (URL,NewSourceSavePath))
   {T1 = System.DateTime.Now;
Console.WriteLine("\nWebClient ポストの所要時間 :{0}" ,diff1 = T1.Subtract(T0));   }}
   catch (Exception e){
   Console.WriteLine("{0} Exception caught.", e);}

   WebClient wc=new WebClient();
   wc.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
   wc.Credentials=CredentialCache.DefaultCredentials;
System.Collections.Specialized.NameValueCollection SNS = new 
System.Collections.Specialized.NameValueCollection();
   SNS.Add("TableName","tblnetstatsperds");
   SNS.Add("ColName","ns_client");            
   SNS.Add("TopNumRecords","1000000");
   SNS.Add("SavePath",@"c:\\xmltest\mytest.xml");
   wc.QueryString =(SNS);
   String postData=  "";
   // バイト配列として文字列を取得するために ASCII エンコーディングを適用します
   Byte[] byteArray =System.Text.Encoding.ASCII.GetBytes(postData);
try{ 
   Byte[] res=wc.UploadData(SourceUrl,"POST",byteArray);
   String XmlFrag=System.Text.Encoding.ASCII.GetString(res);
   XmlDocument mydoc=new XmlDocument();
   mydoc.LoadXml(XmlFrag);
   mydoc.Save(Savepath);
   return true;}

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<sql:query>
   SELECT 1 as Tag, NULL as Parent,Customers.city as [CITY!1!city],
      Customers.ContactTitle as [CITY!1!ContactTitle],   
        Customers.ContactName as [CITY!1!ContactName]
   FROM Customers
      GROUP BY city,ContactName,ContactTitle
      FOR XML EXPLICIT
  </sql:query>
</root>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<xsl:template match="/">
    <root>
     <xsl:for-each select="root/CITY" order-by="+@ContactName">
      <City>
<xsl:attribute name="CityName"><xsl:value-of select="@city"/></xsl:attribute> 
<xsl:element name="CustomerName"><xsl:value-of select="@ContactName"/></xsl:element> 
      </City>
     </xsl:for-each>
    </root>
  </xsl:template>
</xsl:stylesheet>

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
  <sql:xpath-query mapping-schema=" MyView.xdr">
    /CUSTOMER
  </sql:xpath-query>
</root>

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"         xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <ElementType name="CUSTOMER" sql:relation="MyCustomers">
   <!-- 列にマップされる XML の属性-->
    <AttributeType name="ID" />
    <AttributeType name="name" />
      <!-SQL テーブルのビュー列の定義  -->
    <attribute type="ID" sql:field="CustomerID" />
    <attribute type="name" sql:field="CompanyName" />
   </ElementType>
</Schema>

[WebMethod]
public GetXMLDataStreamDS(string SQLQuery,string SavepathWithFileName)
{ //   XmlTextReader クラスが "FOR XML" を使用するストリームを取得します
string Myconnetionstring = "Trusted_Connection=Yes;" + "initial catalog=MyDatabase;"
 + "data source=MyServerName";          
SqlConnection SQLConn = new SqlConnection(Myconnetionstring);
SqlCommand selectCMD = new SqlCommand(SQLQuery, SQLConn);
selectCMD.CommandTimeout = 0;
SQLConn.Open();
DataSet myDataSet = new DataSet();
XmlTextReader Xrdr = (XmlTextReader)selectCMD.ExecuteXmlReader();
try{
   myDataSet.ReadXml(Xrdr, XmlReadMode.Fragment);
myDataSet.WriteXml(SavepathWithFileName,XmlWriteMode.IgnoreSchema);}
catch(Exception ex){
throw new ApplicationException("エラー: " + ex.Message, ex);}
finally{SQLConn.Close();
Xrdr.Close();}

DA.Fill(myDataSet);
XmlTextWriter xmlw = new XmlTextWriter(SavepathWithFileName, System.Text.Encoding.UTF8);
xmlw.WriteStartElement("ROOT");
xmlw.WriteComment("これは MSDN のサンプルです");
XPathDocument doc = new XPathDocument(new System.IO.StringReader(myDataSet.GetXml()));
System.Xml.Xsl.XslTransform xslt = new System.Xml.Xsl.XslTransform();
xslt.Load(@"c:\xmltest\mySchema.xslt");
xslt.Transform(doc, null, xmlw,null);
xmlw.Flush();
xmlw.Close();}
catch(Exception ex)
   {throw new ApplicationException("エラー: " + ex.Message, ex);}
finally{SQLConn.Close();}   } 

[WebMethod]
public GetXMLStreamNoDataset(string SQLQuery,string SavepathWithFileName){ 
string Myconnetionstring = "Trusted_Connection=Yes;" + "initial catalog=MyDatabase;"
 + "data source=MyServerName";          
SqlConnection SQLConn = new SqlConnection(Myconnetionstring);
SqlCommand selectCMD = new SqlCommand(SQLQuery, SQLConn);
XmlTextWriter xmlw = new XmlTextWriter(SavepathWithFileName, System.Text.Encoding.UTF8);
try{selectCMD.CommandTimeout = 0;
   SQLConn.Open();
   XmlTextReader txtrdr= (XmlTextReader)selectCMD.ExecuteXmlReader( ); 
    xmlw.WriteStartElement("ROOT");
xmlw.WriteComment("これは Venkat によるサンプルです");
   XPathDocument doc = new XPathDocument(txtrdr, XmlSpace.Default);
System.Xml.Xsl.XslTransform xslt = new System.Xml.Xsl.XslTransform();
   xslt.Load(@"c:\xmltest\mySchema.xslt");
   xslt.Transform(doc, null, xmlw,null);
   xmlw.Flush();}   
catch (Exception ex){throw new ApplicationException
   ("エラー: " + ex.Message, ex);   }
finally{   SQLConn.Close(); 
xmlw.Close();}   }

public bool GetWebServiceXMLDataDS (string SQLQuery, string FilenameWithSavePath){
try{StreamingApp.venkat.MyXMLWebClass MywebObj = new MyXMLWebClass();
MywebObj.Timeout = 1000;
bool Success = MywebObj.GetXMLDataDS(SQLQuery,FilenameWithSavePath);
   if (Success){
Console.WriteLine("ファイルは次のパスに正常に書き込まれました: {0}",FilenameWithSavePath);}
   return true; }
catch (Exception e) {
   Console.WriteLine("{0} 例外がキャッチされました。", e);
   Console.WriteLine("{0} 例外がキャッチされました。", e.Message);}
   return false; }
public GetWebServiceXMLDataStream(string SQLQuery, string FilenameWithSavePath){
try{StreamingApp.venkat.MyXMLWebClass MywebObj =  new MyXMLWebClass();
   MywebObj.Timeout = 1000;
bool Success =MywebObj.GetXMLStreamNoDataset(SQLQuery,FilenameWithSavePath);
if (Success){Console.WriteLine("ファイルは次のパスに正常に書き込まれました: {0}",FilenameWithSavePath);}return true;}
catch (Exception e){
Console.WriteLine("{0} 例外がキャッチされました。", e);
   Console.WriteLine("{0} 例外がキャッチされました。", e.Message);}
return false;}   
//クライアント コード内でのメソッド呼び出し時間を計測します
try{ T0 = System.DateTime.Now;
if(sc. GetWebServiceXMLDataDS ("select TOP 1000 myColumname from myTableName(nolock)
FOR XML AUTO, XMLDATA ",SourceSavePath + "XMLWebServiceDataStream.xml")){T1 = System.DateTime.Now;
Console.WriteLine("\nWebServiceXMLDataStream の所要時間:{0}",diff1 = T1.Subtract(T0));}}
catch (Exception e)
   {Console.WriteLine("{0} 例外がキャッチされました。", e);   }

public bool GetSQLReportingServerData(string Savepath, string Filename)
{string Savepathfilename = Savepath + Filename;
try{
StreamingApp. MyReportServer.ReportingService MywebReportServer =  
new StreamingApp.MyReportServer.ReportingService();
MywebReportServer.Credentials = System.NET.CredentialCache.DefaultCredentials;
// 引数を設定します
   byte[] result = null;
   string reportPath = "/DBGrowth/USRTechDBGrowthChart";
   string format;
   //byte[] reportDefinition; //レポート定義が必要な場合
   // = MywebReportServer.GetReportDefinition(reportName);
   System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
   //Base 64 エンコードのバイト配列としてのレポート定義 
   ParameterValue[] parameters = new ParameterValue[1];
   parameters[0] = new ParameterValue();
   parameters[0].Name = "propertyname";
   parameters[0].Value = "変数"; 
   DataSourceCredentials[] credentials = null;
   Warning[] warnings = null;
   ParameterValue[] reportHistoryParameters = null;
   string[] streamIDs = null;
   SessionHeader sh = new SessionHeader();
   MywebReportServer.SessionHeaderValue = sh;
try{   // バイナリを XML ドキュメントに読み込みます
   format = "XML";
result = MywebReportServer.Render(reportPath, format, historyID, devInfo, parameters, credentials, 
showHideToggle, out encoding, out mimeType, out reportHistoryParameters, out warnings,   out streamIDs);
   doc.Load(new System.IO.MemoryStream(result));
   doc.Save(Savepath + "report.xml");
    Console.WriteLine( "結果が XML ファイルに保存されました" );
   //必要な場合は、他の形式を指定できます
//たとえば、format = "CSV" または format = "MHTML";
   return true;
catch (Exception e){
   Console.WriteLine("{0} 例外がキャッチされました。", e);
   Console.WriteLine("{0} 例外がキャッチされました。", e.Message); }
return false;   }

byte[] result = MywebReportServer.Render(...);
MemoryStream stream = new MemoryStream(renderResult);
DataSet MyDs = new DataSet();
MyDs.ReadXml(stream);
System.IO.StringReader(MyDs.GetXml()));
System.Xml.Xsl.XslTransform xslt = new System.Xml.Xsl.XslTransform();
xslt.Load(@"c:\xmltest\mySchema.xslt");

public bool BulkLoad(string SourceSavePath, string ConnectionStr,
string   MyXsdFile, string MyXMLFile )   {
bool test;       
try{
Type Mytype;
   SQLXMLBulkLoad objBL = new SQLXMLBulkLoad();
   objBL.ConnectionString=ConnectionStr;
   objBL.ErrorLogFile = SourceSavePath + "\\" + "error.log";
   objBL.CheckConstraints = true; 
   test = objBL.Transaction;
   objBL.TempFilePath=SourceSavePath;
   objBL.XMLFragment = true;
   objBL.SchemaGen = true;
   objBL.SGDropTables = true;
objBL.Execute(SourceSavePath + "\\" + MyXsdFile, SourceSavePath + "\\" + MyXMLFile);
   return true;   }
catch (Exception e)   {
   Console.WriteLine("{0} 例外がキャッチされました。", e);   }
   return false;}

objBL.SchemaGen = true;
objBL.SGDropTables = true;

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name=" ROOT " sql:is-constant="true" >
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element ref="Customer" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
<xsd:element name="Customer" sql:relation="SQLCustomerTable" >
<xsd:complexType>
<xsd:attribute name="FirstName" sql:field="SQLColumnFirstName" sql:datatype="varchar(100)" /> 
<xsd:attribute name="LastName" sql:field="SQLColumnLastName" sql:datatype="varchar(100)" /> 
</xsd:complexType>
  </xsd:element>
</xsd:schema>