在网上找了些有关SQLXML与XSLT的文章,重新整理和调试了下。开发环境SQL2008,VS2010,SQLXML4.0可以从网上下载。测试数据库用的是NorthWind,我下的是mdb,然后导入到SQL2008中的。
 
        新建网站项目,安装完SQLXML4.0后,添加引用,找到系统盘下\Program Files\SQLXML 4.0\bin\Microsoft.Data.SqlXml.dll. 添加完后,在web.config中就生成了这样的语句:
SQLXML与XSLT_休闲<add assembly="Microsoft.Data.SqlXml, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/></assemblies>
 
下面列出主要的代码。
Default.aspx.cs
SQLXML与XSLT_休闲using System;
SQLXML与XSLT_休闲using System.Collections.Generic;
SQLXML与XSLT_休闲using System.Linq;
SQLXML与XSLT_休闲using System.Web;
SQLXML与XSLT_休闲using System.Web.UI;
SQLXML与XSLT_休闲using System.Web.UI.WebControls;
SQLXML与XSLT_休闲using System.Data.SqlClient;
SQLXML与XSLT_休闲using System.Text;
SQLXML与XSLT_休闲using System.Xml;
SQLXML与XSLT_休闲using System.Xml.XPath;
SQLXML与XSLT_休闲using System.Xml.Xsl;
SQLXML与XSLT_休闲using System.Data;
SQLXML与XSLT_休闲using System.IO;
SQLXML与XSLT_休闲using System.Data.Sql;
SQLXML与XSLT_休闲using Microsoft.Data.SqlXml;
SQLXML与XSLT_休闲
SQLXML与XSLT_休闲public partial class _Default : System.Web.UI.Page
SQLXML与XSLT_休闲{
SQLXML与XSLT_休闲        
SQLXML与XSLT_休闲
SQLXML与XSLT_休闲        protected void Page_Load(object sender, EventArgs e)
SQLXML与XSLT_休闲        {
SQLXML与XSLT_休闲
SQLXML与XSLT_休闲                string NorthwindConnstring = "Provider=SQLOLEDB;Server=(local);database=Nwind;UID=sa;PWD=password";
SQLXML与XSLT_休闲            
SQLXML与XSLT_休闲                
SQLXML与XSLT_休闲                        
SQLXML与XSLT_休闲                        string sqlSelect;
SQLXML与XSLT_休闲                        sqlSelect = "SELECT * FROM Emp for xml auto, elements";
SQLXML与XSLT_休闲                        SqlXmlCommand sqlXmlCommand = new SqlXmlCommand(NorthwindConnstring);
SQLXML与XSLT_休闲                        sqlXmlCommand.CommandText = sqlSelect;
SQLXML与XSLT_休闲                        sqlXmlCommand.ClientSideXml = true;
SQLXML与XSLT_休闲                        sqlXmlCommand.XslPath = Server.MapPath("EmployeeTransform.xslt");
SQLXML与XSLT_休闲                        sqlXmlCommand.RootTag = "NewDataSet";
SQLXML与XSLT_休闲                        sqlXmlCommand.ExecuteToStream(Response.OutputStream);
SQLXML与XSLT_休闲                        Response.End();
SQLXML与XSLT_休闲
SQLXML与XSLT_休闲                
SQLXML与XSLT_休闲
SQLXML与XSLT_休闲        }
SQLXML与XSLT_休闲}
 
EmployeeTransform.xslt:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"    
                                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                                xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
                                xmlns:ms="urn:schemas-microsoft-com:xslt"
                                xmlns:vcsharp="urn:vcsharp-com"
                                xmlns:dt="urn:schemas-microsoft-com:datatypes"
                                >
    <xsl:output method="html" indent="yes" doctype-public="-//W3C//DTD HTML 3.2 Final//EN"/>
    <xsl:template match="NewDataSet">
           <HTML>
               <HEAD>
                   <TITLE>XSLT显示</TITLE>
                 </HEAD>
               <BODY>
                   <TABLE style="BORDER-COLLAPSE: collapse;font-size:9pt;" bordercolor="#000000" cellspacing="0" cellpadding="6" align="center" border="1" >
                       <TR style="text-align:center;font-weight:bold;background-color:#336699;color:#FFFFFF;">
                           <TD>EmployeeID</TD>
                           <TD>Name</TD>
                           <TD>Title</TD>
                           <TD>TitleOfCourtesy</TD>
                           <TD>BirthDate</TD>
                           <TD>HireDate</TD>
                           <TD>Address</TD>
                           <TD>City</TD>
                           <TD>Region</TD>
                           <TD>PostalCode</TD>
                           <TD>Country</TD>
                           <TD>HomePhone</TD>
                           <TD>Extension</TD>
                           <TD>Photo</TD>
                           <TD>Notes</TD>
                           <!--<TD>PhotoPath</TD>-->
                         </TR>
<xsl:apply-templates/>
                     </TABLE>
                 </BODY>
             </HTML>
    </xsl:template>
    
    
     <xsl:template match="Emp">
          <TR style="background-color:#99CCEE;color:#000000;">
              <TD>
                <xsl:value-of select="EmployeeID"/>
                  <xsl:variable name="EmployeeID" select="EmployeeID"/></TD>
              <TD>
                <xsl:value-of select="LastName"/>
                <xsl:text disable-output-escaping="yes">&nbsp;</xsl:text>
                <xsl:value-of select="FirstName"/></TD>
              <TD>
                <xsl:value-of select="Title"/></TD>
              <TD>
                <xsl:value-of select="TitleOfCourtesy"/></TD>
                 <TD>
             <xsl:value-of select="ms:format-date(BirthDate, 'MMM dd, yyyy')"/></TD>
              <TD>
                <xsl:value-of select="ms:format-date(HireDate, 'MMM dd, yyyy')"/>
                
                </TD>
              <TD>
                <xsl:value-of select="Address"/></TD>
              <TD>
                <xsl:value-of select="City"/></TD>
              <TD>
                <xsl:value-of select="Region"/></TD>
              <TD>
                <xsl:value-of select="PostalCode"/></TD>
              <TD>
                <xsl:value-of select="Country"/></TD>
              <TD>
                <xsl:value-of select="HomePhone"/></TD>
              <TD>
                <xsl:value-of select="Extension"/></TD>
              <TD>
                   <xsl:choose><xsl:when test="string-length( Photo) > 0">
                         <!--<xsl:variable name="fname">
                             <xsl:value-of select="ImgUrl"/>
                         </xsl:variable>
-->

                         <xsl:variable name="fname">
                             <xsl:value-of select="EmployeeID"/>
                         </xsl:variable>

                           <img src="GetImage.aspx?EmployeeID={$fname}">
                            
                               <!--<xsl:attribute name="src"> 为什么用attribute name="src" 属性就不行呢-->
                                <!--   <xsl:text>GetImage.aspx?EmployeeID={$EmployeeID}</xsl:text>-->
                                 <!--<xsl:value-of select="$fname" />
                                 </xsl:attribute>
-->
                                   
                                    
                         </img>
                          
                          
                    </xsl:when><xsl:otherwise>
                        <xsl:value-of select="Photo"/></xsl:otherwise>
                     </xsl:choose>
                 
                  
            </TD>
              <TD>
                  <xsl:choose><xsl:when test="string-length( Notes) > 50">
                           <xsl:value-of select="substring(Notes, 0, 50)"/><xsl:text>...</xsl:text>
                          
                    </xsl:when><xsl:otherwise>
                        <xsl:value-of select="Notes"/></xsl:otherwise></xsl:choose>
                  
                  
            </TD>
             <!-- <TD>
                  <xsl:variable name="photoPath">
                    <xsl:value-of select="PhotoPath"/>
                </xsl:variable>
                  <xsl:if test="string-length($photoPath) > 0">
                      <a href="{$photoPath}">
                           <xsl:value-of select="PhotoPath"/>
                          
                    </a>
                      
                </xsl:if>
                  
            </TD>
-->
              
        </TR>
          
    </xsl:template></xsl:stylesheet>
 
GetImage.aspx.cs:
SQLXML与XSLT_休闲using System;
SQLXML与XSLT_休闲using System.Collections.Generic;
SQLXML与XSLT_休闲using System.Linq;
SQLXML与XSLT_休闲using System.Web;
SQLXML与XSLT_休闲using System.Web.UI;
SQLXML与XSLT_休闲using System.Web.UI.WebControls;
SQLXML与XSLT_休闲using System.Drawing;
SQLXML与XSLT_休闲using System.Drawing.Drawing2D;
SQLXML与XSLT_休闲using System.Data.Sql;
SQLXML与XSLT_休闲using System.Data.SqlClient;
SQLXML与XSLT_休闲using System.Data;
SQLXML与XSLT_休闲using Microsoft.Data.SqlXml;
SQLXML与XSLT_休闲using System.IO;
SQLXML与XSLT_休闲
SQLXML与XSLT_休闲public partial class GetImage : System.Web.UI.Page
SQLXML与XSLT_休闲{
SQLXML与XSLT_休闲        protected void Page_Load(object sender, EventArgs e)
SQLXML与XSLT_休闲        {
SQLXML与XSLT_休闲                string employeeID = Request.QueryString["EmployeeID"];
SQLXML与XSLT_休闲                if (employeeID == string.Empty || employeeID == null)
SQLXML与XSLT_休闲                {
SQLXML与XSLT_休闲                        return;
SQLXML与XSLT_休闲                }
SQLXML与XSLT_休闲                byte[] imgByteArray = GetImage2(employeeID);
SQLXML与XSLT_休闲                Response.ContentType = "p_w_picpath/jpeg";
SQLXML与XSLT_休闲                int offset = 78;
SQLXML与XSLT_休闲                //读取图片并作图片格式转换,原图是BMP图,在网页中显示使用JPG图片,同时使图片感官效果优化:
SQLXML与XSLT_休闲                System.IO.MemoryStream mstream = new System.IO.MemoryStream();
SQLXML与XSLT_休闲                System.IO.MemoryStream stream = new System.IO.MemoryStream();
SQLXML与XSLT_休闲                mstream.Write(imgByteArray, offset, imgByteArray.Length - offset);
SQLXML与XSLT_休闲                System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(mstream);
SQLXML与XSLT_休闲                Bitmap bmpTmp = new Bitmap(bmp.Width / 2, bmp.Height / 2);//尺寸缩小为50%
SQLXML与XSLT_休闲                Graphics g = Graphics.FromImage(bmpTmp);
SQLXML与XSLT_休闲                g.CompositingQuality = CompositingQuality.HighQuality;
SQLXML与XSLT_休闲                g.InterpolationMode = InterpolationMode.HighQualityBicubic;
SQLXML与XSLT_休闲                Rectangle destRect = new Rectangle(0, 0, bmpTmp.Width, bmpTmp.Height);
SQLXML与XSLT_休闲                Rectangle srcRect = new Rectangle(0, 0, bmp.Width, bmp.Height);
SQLXML与XSLT_休闲                g.DrawImage(bmp, destRect, srcRect, GraphicsUnit.Pixel);
SQLXML与XSLT_休闲                bmpTmp.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
SQLXML与XSLT_休闲                g.Dispose();
SQLXML与XSLT_休闲                bmp.Dispose();
SQLXML与XSLT_休闲                byte[] byteReturn = stream.ToArray();
SQLXML与XSLT_休闲                mstream.Close();
SQLXML与XSLT_休闲                stream.Close();
SQLXML与XSLT_休闲                Response.OutputStream.Write(byteReturn, 0, byteReturn.Length);
SQLXML与XSLT_休闲                Response.End();
SQLXML与XSLT_休闲        }
SQLXML与XSLT_休闲        private byte[] GetImage2(string employeeID)
SQLXML与XSLT_休闲        {
SQLXML与XSLT_休闲                string NorthwindConnstring;
SQLXML与XSLT_休闲                NorthwindConnstring = "data source=(local);database=Nwind;uid=SA;pwd=password";
SQLXML与XSLT_休闲                using (SqlConnection conn = new SqlConnection(NorthwindConnstring))
SQLXML与XSLT_休闲                {
SQLXML与XSLT_休闲                        string sqlSelect = "SELECT Photo FROM Emp WHERE EmployeeID = " + employeeID;
SQLXML与XSLT_休闲                        SqlCommand cmd = conn.CreateCommand();
SQLXML与XSLT_休闲                        cmd.CommandText = sqlSelect;
SQLXML与XSLT_休闲                        conn.Open();
SQLXML与XSLT_休闲                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow))
SQLXML与XSLT_休闲                        {
SQLXML与XSLT_休闲                                if (dr.Read())
SQLXML与XSLT_休闲                                {
SQLXML与XSLT_休闲                                        if (dr[0] != null)
SQLXML与XSLT_休闲                                        {
SQLXML与XSLT_休闲                                                byte[] byteImg = (byte[])dr[0];
SQLXML与XSLT_休闲                                                return byteImg;
SQLXML与XSLT_休闲                                        }
SQLXML与XSLT_休闲                                }
SQLXML与XSLT_休闲                        }
SQLXML与XSLT_休闲                }
SQLXML与XSLT_休闲                return null;
SQLXML与XSLT_休闲        }
SQLXML与XSLT_休闲
SQLXML与XSLT_休闲}