Use C # And SQLXML technology to retrieve and display all records in northwind (including employee images)

Some people often ask this question. How do I retrieve and display the image of the photo field in the Employees table in northwind?
The following is a common problem in the image display in this table: because the image data contained in the northwind database has a header of 78 bytes at the beginning, you need to manually remove it. This is why most people cannot show pictures of the nine members with hard work.
Next I will first use Windows Forms to retrieve and display the image, and then use Asp.net + XML technology to display the data of the entire table on the website.

1. Use winform

First, create a winform project and add:
....
Using system. Data;
Using system. IO;
Using system. Data. sqlclient;

Add a button in the form, such as button1. Double-click it and add the following code to the event:
// Here, only the employee image with ID = 2 is taken. You can modify your code as needed.
Byte [] imgbytearray = getimage ("2 ");
If (imgbyte = NULL) return;
If (imgbytearray. Length <= 78) return;
Memorystream stream = new memorystream ();
Int32 offset = 78; // remove the 78-byte header
Stream. Write (imgbytearray, offset, imgbytearray. Length-offset );
// Display the image
Image m_bitmap = image. fromstream (Stream );
This. creategraphics (). drawimage (m_bitmap, 0, 0 );
Stream. Close ();

The above getimage (...) is used to retrieve the byte array of images from the database. The Code is as follows:
Private byte [] getimage (string employeeid)
{
String connstring;
// Database connection string, which can be modified as needed
Connstring = "Server =.; database = northwind; uid = ASPnet; Pwd = ASPnet ;";
Using (sqlconnection conn = new sqlconnection (connstring ))
{
String sqlselect = "select photo from employees where employeeid =" + employeeid;
 
Sqlcommand cmd = conn. createcommand ();
Cmd. commandtext = sqlselect;
Conn. open ();
Using (sqldatareader DR = cmd. executereader (commandbehavior. singlerow ))
{
If (dr. Read ())
{
If (Dr [0]! = NULL)
{
Byte [] byteimg = (byte []) Dr [0];
Return byteimg;
}
}
}
}
Return NULL;
}

Compile and run the program, and you can see the corresponding image. How is it? Does it feel like the image is not very good, It is very rough. It doesn't matter, continue ....
Note: The above code can be optimized for batch query,

Ii. Use webform

Since this example uses SQLXML technology, if you need to run this example, You need to download sqlxml3.0 version, it fully supports XML-XSLT conversion. (Of course, you can also use the SQL Server read display technology we often use to complete this example)
After installation, continue .....

Create a webform website project,
First, add Microsoft. Data. SQLXML. dll reference;
Then, add the following namespace:
Using system. Data. sqlclient;
Using system. text;
Using system. xml;
Using system. xml. XPath;
Using system. xml. XSL;
Using Microsoft. Data. SQLXML;
Using system. IO;

Add string northwindconnstring outside page_load;
In the page_load intermediate code area, add:
Private void page_load (Object sender, system. eventargs E)
{
Northwindconnstring = system. configuration. configurationsettings. appsettings ["northwindconnstring"]; // read database connection information from web. config (The following is the "database connection statement ")
Using (sqlconnection conn = new sqlconnection (connstring ))
{
String sqlselect;
Sqlselect = "select * from employees for XML auto, elements ";
Sqlxmlcommand = new sqlxmlcommand (northwindconnstring );
Sqlxmlcommand. commandtext = sqlselect;
Sqlxmlcommand. clientsidexml = true;
Sqlxmlcommand. Export Path = server. mappath ("employeetransform. XSLT ");
Sqlxmlcommand. roottag = "newdataset ";
Sqlxmlcommand. executetostream (response. outputstream );
Response. End ();
}
}
Note: The above database connection information is configured in Web. config. The configuration is as follows:
<Deleetask>
<Add key = "northwindconnstring" value = "provider = sqloledb; server = (local); database = northwind; user id = ASPnet; Password = ASPnet"/>
</Appsettings>
If your configuration information does not use the web. config configuration file, change it in the above "database connection statement":
Northwindconnstring = "provider = sqloledb; server = (local); database = northwind; user id = ASPnet; Password = ASPnet.

The above Code uses the XML + XPath conversion technology, where:
The content of employeetransform. XSLT is (you need to add this file to your website project ):
<? XML version = "1.0" encoding = "gb2312"?>
<XSL: stylesheet version = '1. 0' xmlns: XSL = "http://www.w3.org/1999/XSL/Transform"
Xmlns: msxsl = "urn: Schemas-Microsoft-com: XSLT"
Xmlns: vcsharp = "urn: vcsharp-com"
Xmlns: Ms = "urn: Schemas-Microsoft-com: XSLT">
<XSL: output method = "html" indent = "yes" doctype-Public = "-// W3C // dtd html 3.2 final // en"/>

<XSL: template match = "newdataset">
<HTML>
<Head>
<Title>
</Title>
</Head>
<Body>
<Table Style = "border-collapse: collapse; font-size: 9pt; "bordercolor =" #000000 "cellspacing =" 0 "cellpadding =" 6 "rules =" all "align =" center "border =" 1 "frame =" box ">
<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 = "employees">
<Tr style = "background-color: #99 CCEE; 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"> & amp; nbsp; </XSL: text> <XSL: value-of select = "firstname"/> </TD>
<TD> <XSL: value-of select = "title"/> </TD>
<TD> <XSL: value-of select = "titleofcourtesy"/> </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) & gt; 0">

<XSL: attribute name = "src">
<XSL: Text> getimage. aspx? Employeeid = </XSL: Text>
<XSL: value-of select = "employeeid"/>
</XSL: attribute>
</Img>

</XSL: When>
<XSL: otherwise> <XSL: value-of select = "photo"/> </XSL: otherwise>
</XSL: Choose>
<! -- XSL: value-of select = "photo"/-->
</TD>
<TD>
<XSL: Choose>
<XSL: When test = "string-length (notes) & gt; 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>
<! -- XSL: value-of select = "Notes"/-->
</TD>
<TD>
<XSL: variable name = "photopath"> <XSL: value-of select = "photopath"/> </XSL: Variable>
<XSL: If test = "string-length ($ photopath) & gt; 0">
<A href = "{$ photopath}">
<XSL: value-of select = "$ photopath"/>
</A>
</XSL: If>
</TD>
</Tr>
</XSL: Template>
</XSL: stylesheet>

Because the XSLT file cannot directly process binary data, I use a work und here, using to read image data from another Asp.net page and display the image.
Key code in getimage. aspx. CS:
Private void page_load (Object sender, system. eventargs E)
{
String employeeid = request. querystring ["employeeid"];
If (employeeid = string. Empty | employeeid = NULL)
{
Return;
}
Byte [] imgbytearray = getimage (employeeid );
Response. contenttype = "image/JPEG ";
Int offset = 78;
// Read the image and convert the image format. The source image is a BMP image. The jpg image is displayed on the webpage, And the sensory effect of the image is optimized:
System. Io. memorystream mstream = new system. Io. memorystream ();
System. Io. memorystream stream = new system. Io. memorystream ();
Mstream. Write (imgbytearray, offset, imgbytearray. Length-offset );
System. Drawing. bitmap BMP = new system. Drawing. Bitmap (mstream );
Bitmap bmp tmp = new Bitmap (BMP. width/2, BMP. Height/2); // reduce the size to 50%
Graphics G = graphics. fromimage (bmp tmp );
G. compositingquality = compositingquality. highquality;
G. interpolationmode = interpolationmode. highqualitybicubic;
Rectangle destrect = new rectangle (0, 0, BMP. Width, BMP. Height );
Rectangle srcrect = new rectangle (0, 0, BMP. Width, BMP. Height );
G. drawimage (BMP, destrect, srcrect, graphicsunit. pixel );
Bmp tmp. Save (stream, system. Drawing. imaging. imageformat. JPEG );
G. Dispose ();
BMP. Dispose ();
Byte [] bytereturn = stream. toarray ();

Mstream. Close ();
Stream. Close ();

Response. outputstream. Write (bytereturn, 0, bytereturn. Length );
Response. End ();
}

Private byte [] getimage (string employeeid)
{
Connstring = system. configuration. configurationsettings. receivettings ["connstring"];
Using (sqlconnection conn = new sqlconnection (connstring ))
{
String sqlselect = "select photo from employees where employeeid =" + employeeid;
Sqlcommand cmd = conn. createcommand ();
Cmd. commandtext = sqlselect;
Conn. open ();
Using (sqldatareader DR = cmd. executereader (commandbehavior. singlerow ))
{
If (dr. Read ())
{
If (Dr [0]! = NULL)
{
Byte [] byteimg = (byte []) Dr [0];
Return byteimg;
}
}
}
}
Return NULL;
}
Finally: