Professional ASP.NET source code

				
				
				
				
				
				
				
				
				
				
				Reading XML Direct From SQL Server With An XmlReader
				
				
				
				Reading XML Direct From SQL Server With An XmlReader
				
				
				
				
				
				Connection string: 
				SELECT command: 
				
				
				
				
				
					void Page_Load(Object sender, EventArgs e)
					{
				
						// get connection string from ..\global\connect-strings.ascx user control
						string strConnect = ctlConnectStrings.SQLConnectionString;
						outConnect.InnerText = strConnect; // and display it
				
						// specify the SELECT statement to extract the data as an
						// XML document using SQL Server 2000's "FOR XML" option
						string strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '1861003%' FOR XML AUTO";
						outSelect.InnerText = strSelect;   // and display it
				
						// create a new StringBuilder to hold the results
						StringBuilder objStrBuilder = new StringBuilder();
				
						//try
						{
				
							// create a new Connection object using the connection string
							SqlConnection objConnect = new SqlConnection(strConnect);
				
							// create a new Command using the connection object and select statement
							SqlCommand objCommand = new SqlCommand(strSelect, objConnect);
				
							// declare a variable to hold an XmlTextReader object
							XmlTextReader objXTReader;
				
							// open the connection to the database
							objConnect.Open();
				
							// execute the SQL statement against the command to fill the XmlReader
							objXTReader = (XmlTextReader)objCommand.ExecuteXmlReader();
				
							// read the first result to initialize the reader
							objXTReader.Read();
				
							// and then read remainder into the StringBuilder as well
							objStrBuilder.Append(objXTReader.GetRemainder().ReadToEnd());
				
							// close the XmlReader and Connection
							objXTReader.Close();
							objConnect.Close();
						}
						/*
						catch (Exception objError)
						{
							// display error details
							outError.InnerHtml = "* Error while accessing data."
										+ objError.Message + "" + objError.Source;
							return;  //  and stop execution
						}
						*/
						// display the results as Text to show XML elements
						outError.InnerText = objStrBuilder.ToString();
				
					}