static void Main(string[] args)
{
String sc = "Data Source=.\\sqlexpress;Initial Catalog=vs2010;Integrated Security=true;";
using (SqlConnection c = new SqlConnection(sc))
{
String cmd = "SELECT * FROM PESSOA FOR XML AUTO";
using (SqlCommand k = new SqlCommand(cmd, c))
{
c.Open();
XmlReader xml = k.ExecuteXmlReader();
while (xml.Read())
{
Console.WriteLine("{0} - {1} - {2}", xml["COD_PESSOA"], xml["NOME_PESSOA"], xml["SEXO_PESSOA"]);
}
Console.WriteLine();
Console.WriteLine(xml);
c.Close();
}
}
Console.ReadKey();
}
protected string getCountryInXml()
{
dbManager = new DBConnection();
try
{
SqlCommand command = new SqlCommand();
command.Connection = dbManager.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[dbo].[get_country_list]";
XmlReader reader = command.ExecuteXmlReader();
DataSet ds = new DataSet();
ds.ReadXml(reader);
return ds.GetXml();
}
finally
{
dbManager.Close();
}
}
protected string GetDocketsForUserIdInXML(string userId)
{
dbManager = new DBConnection();
try
{
SqlCommand command = new SqlCommand();
command.Connection = dbManager.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[dbo].[get_dockets_for_user]";
command.Parameters.AddWithValue("@user_id", userId);
XmlReader reader = command.ExecuteXmlReader();
DataSet ds = new DataSet();
ds.ReadXml(reader);
return ds.GetXml();
}
finally
{
dbManager.Close();
}
}
static void Main()
{
string strConn = "Data Source=localhost;Initial Catalog=booksourcedb;Integrated Security=True";
SqlConnection conn = new SqlConnection(strConn);
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText =
"SELECT 1 AS Tag, " +
" NULL AS Parent," +
" NULL AS [booklist!1]," +
" NULL AS [book!2!bid]," +
" NULL AS [book!2!kind]," +
" NULL AS [book!2!title!element]," +
" NULL AS [book!2!publisher!element]," +
" NULL AS [book!2!price!element] " +
"UNION ALL " +
"SELECT 2, 1, NULL, bid, kind, title, publisher, price " +
"FROM book WHERE bid='b1' " +
"FOR XML EXPLICIT";
command.CommandType = CommandType.Text;
conn.Open();
XmlReader xmlReader = command.ExecuteXmlReader();
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(xmlReader);
xmlReader.Close();
conn.Close();
//--> XML ���� ó��
StringWriter stringWriter = new StringWriter();
xmlDocument.Save(stringWriter);
string strXml = stringWriter.ToString();
Console.WriteLine(strXml);
}
public void ProcessRequest(HttpContext Context)
{
using (SqlConnection Connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MYDC"].ConnectionString))
{
Connection.Open();
using (SqlCommand Command = new SqlCommand())
{
Command.Connection = Connection;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "apiWorkshopsExport";
Command.Parameters.AddWithValue("EventId", Context.Request.QueryString["EventId"]);
using (XmlReader Reader = Command.ExecuteXmlReader())
{
XmlDocument Document = new XmlDocument();
Document.Load(Reader);
Document.Save(Context.Server.MapPath(string.Format("/xml/timetable{0}.xml", Context.Request.QueryString["EventId"])));
Context.Response.ContentType = "text/xml";
//Context.Response.AddHeader("Content-Disposition", "attachment; filename=timetable.xml");
Context.Response.Write(Document.InnerXml);
Reader.Close();
}
}
Connection.Close();
}
}
public XElement LoadAnnouncements()
{
XElement output;
try
{
if (Connection.State == ConnectionState.Closed) Connection.Open();
SqlCommand readCommand = new SqlCommand("LoadAnnouncements", Connection);
readCommand.CommandType = CommandType.StoredProcedure;
XmlReader reader = readCommand.ExecuteXmlReader();
reader.Read();
string result = string.Empty; ;
while (reader.ReadState != ReadState.EndOfFile)
{
result += reader.ReadOuterXml();
}
reader.Close();
output = XElement.Parse(HttpUtility.HtmlDecode(result));
}
catch
{
throw;
}
finally
{
if (Connection.State != ConnectionState.Closed) Connection.Close();
}
return output;
}
public static SqlXml ExecQuery(SqlString Sql, SqlXml Options, SqlXml Input)
{
var XOutput = new XDocument(new XElement("root"));
try
{
using (var q = new SqlCommand())
{
q.Connection = new SqlConnection("context connection=true");
q.CommandType = CommandType.Text;
q.CommandText = Sql.Value;
q.InitOptions(Options.Value);
q.Parameters.SetInput(Input.Value);
q.Connection.Open();
var Result = q.ExecuteXmlReader();
if (Result.Read())
XOutput.Root.Element("content").Add(
XElement.Load(Result, LoadOptions.None));
q.Connection.Close();
}
}
catch (Exception ex)
{
XOutput.Root.Add(ex.ExceptionSerialize());
}
return new SqlXml(XOutput.CreateReader());
}
public void Build(SqlCommand cmd, YellowstonePathology.Business.Test.AccessionOrder accessionOrder)
{
XElement document = null;
using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
{
cn.Open();
cmd.Connection = cn;
using (XmlReader xmlReader = cmd.ExecuteXmlReader())
{
if (xmlReader.Read() == true)
{
document = XElement.Load(xmlReader, LoadOptions.PreserveWhitespace);
}
}
}
if (document != null)
{
YellowstonePathology.Business.Persistence.XmlPropertyWriter xmlPropertyWriter = new YellowstonePathology.Business.Persistence.XmlPropertyWriter(document, accessionOrder);
xmlPropertyWriter.Write();
BuildSpecimenOrder(accessionOrder, document);
BuildTaskOrder(accessionOrder, document);
BuildIcdBillingCode(accessionOrder, document);
BuildPanelSetOrder(accessionOrder, document);
}
}
public void ProcessRequest(HttpContext Context)
{
using (SqlConnection Connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MYDC"].ConnectionString))
{
Connection.Open();
//using (SqlCommand Command = new SqlCommand())
//{
// Command.Connection = Connection;
// Command.CommandType = CommandType.StoredProcedure;
// Command.CommandText = "apiWorkshopsImport";
// XmlDocument Document = new XmlDocument();
// Document.Load(Context.Server.MapPath(string.Format("/xml/timetable{0}.xml", Context.Request.QueryString["EventId"])));
// Command.Parameters.AddWithValue("Import", Document.InnerXml);
// Command.ExecuteNonQuery();
//}
using (SqlCommand Command = new SqlCommand())
{
Command.Connection = Connection;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "apiTimetable";
Command.Parameters.AddWithValue("EventId", Context.Request.QueryString["EventId"]);
using (XmlReader Reader = Command.ExecuteXmlReader())
{
XmlDocument Document = new XmlDocument();
Document.Load(Reader);
Context.Response.ContentType = "text/json";
Context.Response.Write(JsonConvert.SerializeXmlNode(Document, Newtonsoft.Json.Formatting.Indented,true));
Reader.Close();
}
}
Connection.Close();
}
}
public static XElement GetCytologySlideDisposalReport(DateTime disposalDate)
{
XElement result = null;
SqlCommand cmd = new SqlCommand("pCytologySlideDisposalReport");
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@DisposalDate", System.Data.SqlDbType.DateTime).Value = disposalDate;
StringBuilder xmlString = new StringBuilder();
using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.ProductionConnectionString))
{
cn.Open();
cmd.Connection = cn;
using (XmlReader xmlReader = cmd.ExecuteXmlReader())
{
while (xmlReader.Read())
{
xmlString.Append(xmlReader.ReadOuterXml());
}
}
}
if (xmlString.Length > 0)
{
result = XElement.Parse(xmlString.ToString());
}
return result;
}
public static XElement GetClientBillingDetailReport(DateTime postDateStart, DateTime postDateEnd, Nullable<int> clientGroupId)
{
XElement result = new XElement("Document");
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "prcGetClientBillingDetailReport";
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = postDateStart;
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = postDateEnd;
cmd.Parameters.Add("@ClientGroupId", SqlDbType.Int).Value = clientGroupId;
using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
{
cn.Open();
cmd.Connection = cn;
using (XmlReader xmlReader = cmd.ExecuteXmlReader())
{
if (xmlReader.Read() == true)
{
result = XElement.Load(xmlReader);
}
}
}
return result;
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlXmlReader"/> class.
/// </summary>
/// <param name="cmd">The CMD.</param>
public SqlXmlReader(SqlCommand cmd)
{
if (cmd == null)
throw new ArgumentNullException("cmd");
connection = cmd.Connection;
reader = cmd.ExecuteXmlReader();
}
/// <summary>
/// Get Paper Name
/// </summary>
/// <param name="oHt">Hashtable</param>
/// <returns>String</returns>
public string GetPaperName(string sPpID)
{
string sPaperName = string.Empty;
DataTable dt = new DataTable();
Hashtable oHt = new Hashtable();
oHt.Add("PpIDList", sPpID);
System.Xml.XmlReader oR = null;
System.Text.StringBuilder oS = new System.Text.StringBuilder();
DBObjectPool Pool = null;
Pool = DBObjectPool.Instance;
DBObject oDB = Pool.AcquireDBObject();
try
{
System.Data.SqlClient.SqlCommand cmd = oDB.GenerateCommand("ELGV2_Get_PpName", oHt);
oR = cmd.ExecuteXmlReader();
while (!oR.EOF)
{
if (oR.IsStartElement())
{
oS.Append(oR.ReadOuterXml());
oS.Append(Environment.NewLine);
}
}
}
finally
{
Pool.ReleaseDBObject(oDB);
}
return(oS.ToString());
}
static void Main()
{
const String cs = @"Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=true";
using (var c = new SqlConnection { ConnectionString = cs })
{
const String cmd = "SELECT TOP 3 ContactID, FirstName, LastName FROM Person.Contact FOR XML AUTO";
using (var k = new SqlCommand(cmd, c))
{
c.Open();
using (var xml = k.ExecuteXmlReader())
{
while (xml.Read())
{
Console.WriteLine("{0} -> {1} {2}", xml["ContactID"], xml["FirstName"], xml["LastName"]);
}
}
c.Close();
}
}
Console.ReadKey();
}
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection objConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BikeClearinghouse_ConnectionString"].ConnectionString);
objConn.Open();
SqlCommand objCmd = new SqlCommand("get_Location_List_XML", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
XmlReader rdrXMLLocations = null;
rdrXMLLocations = objCmd.ExecuteXmlReader();
Response.Expires = 0;
Response.ContentType = "text/xml";
XmlDocument oDocument = new XmlDocument();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using (rdrXMLLocations)
{
while (!rdrXMLLocations.EOF)
{
rdrXMLLocations.MoveToContent();
sb.Append(rdrXMLLocations.ReadOuterXml());
}
rdrXMLLocations.Close();
}
if (!string.IsNullOrEmpty(sb.ToString()))
{
oDocument.LoadXml(sb.ToString());
}
oDocument.Save(Response.Output);
Response.OutputStream.Flush();
Response.OutputStream.Close();
}
public System.Xml.Linq.XDocument GetConceptMeshInfo(RDFTriple request)
{
SessionManagement sm = new SessionManagement();
string connstr = ConfigurationManager.ConnectionStrings["ProfilesDB"].ConnectionString;
using (var db = new SqlConnection(connstr))
{
SqlCommand dbcommand = new SqlCommand("[Profile.Data].[Concept.Mesh.GetDescriptorXML]", db);
dbcommand.CommandType = CommandType.StoredProcedure;
dbcommand.CommandTimeout = base.GetCommandTimeout();
dbcommand.Parameters.Add(new SqlParameter("@NodeId", request.Subject));
db.Open();
XmlReader xreader = dbcommand.ExecuteXmlReader();
System.Xml.Linq.XDocument xDoc = null;
if (xreader.Read())
xDoc = System.Xml.Linq.XDocument.Load(xreader);
xreader.Close();
db.Close();
return xDoc;
}
}
static void Main(string[] args)
{
string source = "server=116.254.206.41;database=db_gzbdc;uid=u_gzbdc;pwd=p_gzbdc";//连接字符串
SqlConnection con = new SqlConnection(source);
con.Open();//打开数据库连接
Console.WriteLine("djsa");
//string sql = "update dbo.News set Newstitle ='历史性' where NewsID =156";
//SqlCommand cmd = new SqlCommand(sql, con);
//int cout = cmd.ExecuteNonQuery();//影响行数
//Console.WriteLine(cout);
//string sqlselect = "select NewsTitle,NewsContent from dbo.News";
//SqlCommand cmd2 = new SqlCommand(sqlselect,con);
//SqlDataReader reeder = cmd2.ExecuteReader();
//if(reeder.Read())
//{
// Console.WriteLine("NewsTitle:{0,-300} NewsContent:{1}",reeder[0],reeder[1]);
//}
string sql3 = "select * from dbo.News for xml auto";
SqlCommand cmd3 = new SqlCommand(sql3,con);
XmlReader xmldata = cmd3.ExecuteXmlReader();
xmldata.Read();
string data;
do
{
data = xmldata.ReadOuterXml();
if (!string.IsNullOrEmpty(data))
{
Console.WriteLine(data);
}
} while (!string.IsNullOrEmpty(data));
//Console.WriteLine(o);
con.Close();//关闭数据库连接
Console.ReadLine();
}
protected void Page_Load(object sender, EventArgs e)
{
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sql = "SELECT C.CategoryName, C.CategoryID, P.ProductName, P.ProductID FROM Products P INNER JOIN Categories C ON P.CategoryID = C.CategoryID FOR XML AUTO, ELEMENTS";
SqlCommand cmd = new SqlCommand(sql, con);
string xml = "";
try
{
con.Open();
XmlReader r = cmd.ExecuteXmlReader();
r.Read();
xml = "<root>" + r.ReadOuterXml() + "</root>";
}
finally
{
con.Close();
}
sourceDbXml.Data = xml;
}
protected void Page_Load(object sender, System.EventArgs e)
{
double dmlSearchLatitude = 0;
double dmlSearchLongitude = 0;
Int32 intSearchRadius = default(Int32);
if (!string.IsNullOrEmpty(Request.QueryString["lat"]))
{
dmlSearchLatitude = Convert.ToDouble(Request.QueryString["lat"]);
}
if (!string.IsNullOrEmpty(Request.QueryString["lng"]))
{
dmlSearchLongitude = Convert.ToDouble(Request.QueryString["lng"]);
}
if (!string.IsNullOrEmpty(Request.QueryString["radius"]))
{
intSearchRadius = Convert.ToInt32(Request.QueryString["radius"]);
}
// Here you make the call to your locations stored procedure
// This database call is a little messy but is just to show you the point.
// You should really use the MS Application Blocks and/or some other seperate data layer
SqlConnection connDB = new SqlConnection();
SqlCommand cmd = new SqlCommand();
connDB.ConnectionString = "Server=(local);Database=Test;Trusted_Connection=True;";
connDB.Open();
cmd.Connection = connDB;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 20;
cmd.CommandText = "dbo.proc_Location_List";
cmd.Parameters.AddWithValue("@dmlLat", dmlSearchLatitude);
cmd.Parameters.AddWithValue("@dmlLng", dmlSearchLongitude);
cmd.Parameters.AddWithValue("@intRadius", intSearchRadius);
// Execute the stored procedure and return the result as plain XML
XmlReader rdrXMLLocations = null;
rdrXMLLocations = cmd.ExecuteXmlReader();
Response.Expires = 0;
Response.ContentType = "text/xml";
XmlDocument oDocument = new XmlDocument();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using (rdrXMLLocations)
{
while (!rdrXMLLocations.EOF)
{
rdrXMLLocations.MoveToContent();
sb.Append(rdrXMLLocations.ReadOuterXml());
}
rdrXMLLocations.Close();
}
oDocument.LoadXml(sb.ToString());
oDocument.Save(Response.Output);
Response.OutputStream.Flush();
Response.OutputStream.Close();
}
private void CreateXML(string queryString, string connectionString)
{
ServiceContext.LogInfo("Starting query");
using (SqlConnection connection = new SqlConnection(connectionString)) {
connection.Open();
SqlCommand command = new SqlCommand(queryString, connection);
XmlReader reader = command.ExecuteXmlReader();
WriteXmlToQueue(reader);
}
}
/// <summary>
/// This method takes an optional list of paramters.
/// </summary>
public string getXmlString(string storedProcedureName, params SqlParameter[] spParameterList)
{
SqlConnection sqlConnection = null;
XmlReader xmlReader = null;
string xmlString = "";
try
{
sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = GetConnectionString();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.CommandText = storedProcedureName;
cmd.CommandType = CommandType.StoredProcedure;
// optional list of parameters for stored procedure
if (spParameterList.Length > 0)
{
for (int i = 0; i < spParameterList.Length; i++)
{
cmd.Parameters.Add(spParameterList[i]);
}
}
sqlConnection.Open();
xmlReader = cmd.ExecuteXmlReader();
xmlReader.Read();
// we are getting XML straight from the stored procedure, so add it to
// our XML string
while (xmlReader.ReadState != ReadState.EndOfFile)
{
xmlString = xmlReader.ReadOuterXml();
}
}
catch (Exception e)
{
Console.WriteLine("updateDB error: " + e.Message);
}
finally
{
if (sqlConnection != null)
{
sqlConnection.Close();
}
if (xmlReader != null)
{
xmlReader.Close();
}
}
return xmlString;
}
private XmlReader DoExecuteXmlReader(SqlCommand sqlCommand)
{
try
{
XmlReader reader = sqlCommand.ExecuteXmlReader();
return reader;
}
catch (Exception e)
{
throw e;
}
}
public override int Run(string[] remainingArguments)
{
try
{
Query = File.ReadAllText(Path.GetFullPath(Query));
}
catch (Exception)
{
}
Dictionary<string, StreamWriter> openFiles = new Dictionary<string, StreamWriter>();
using(var connection = new SqlConnection(SqlConnectionString))
{
connection.Open();
using(var command = new SqlCommand(Query, connection))
{
using(var reader = command.ExecuteXmlReader())
{
while (reader.Read())
{
var xmlElement = XElement.Parse(reader.ReadOuterXml());
var rowName = xmlElement.Name.LocalName;
if (!openFiles.ContainsKey(rowName))
{
openFiles[rowName] = new StreamWriter(File.Open(BaseFilepath + "." + rowName + ".json.txt", FileMode.OpenOrCreate));
openFiles[rowName].WriteLine("[");
}
Dictionary<string, object> row= new Dictionary<string, object>();
foreach (var attribute in xmlElement.Attributes())
row[attribute.Name.LocalName] = attribute.Value;
openFiles[rowName].WriteLine(JsonConvert.SerializeObject(row, Formatting.Indented) + ",");
}
}
}
}
foreach(var file in openFiles.Values)
{
file.WriteLine("]");
file.Flush();
file.Close();
}
return 0;
}
public static object ExecuteXMLScalar(string procedureName, System.Data.SqlClient.SqlParameter[] parameters)
{
string connString = ConnectionString;
object retVal = null;
SqlConnection conn = null;
TransactionOptions option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
option.Timeout = new TimeSpan(0, 2, 0);
try
{
using (TransactionScope trn = new TransactionScope(TransactionScopeOption.Required, option))
{
using (conn = new SqlConnection(connString))
{
conn.Open();
System.Data.SqlClient.SqlCommand sc = new System.Data.SqlClient.SqlCommand(procedureName, conn);
sc.CommandType = CommandType.StoredProcedure;
sc.Parameters.AddRange(parameters);
System.Xml.XmlReader xr = sc.ExecuteXmlReader();
if (xr.Read())
{
retVal = xr.ReadOuterXml();
}
sc.Parameters.Clear();
}
trn.Complete();
return(retVal);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null && conn.State != ConnectionState.Broken && conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
//return retVal;
}
static void Main(string[] args)
{
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "Server=LUISDEOL\\SQLEXPRESS;Database=Infragistics;Integrated Security=SSPI;";
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM dbo.City FOR XML AUTO XMLDATA";
System.Xml.XmlReader xml = cmd.ExecuteXmlReader();
cn.Close();
}
public XmlReader ExecuteXmlReader(SqlCommand command)
{
base.PrepareCommand(command);
try
{
XmlReader reader = command.ExecuteXmlReader();
#if DEBUG
DatabaseLog.WriteInfo(command);
#endif
return reader;
}
catch(Exception e)
{
DatabaseLog.WriteError(e.Message, command);
throw;
}
}
private System.Xml.Linq.XElement ExcuteCommand(SqlCommand cmd)
{
System.Xml.Linq.XElement result = null;
using (SqlConnection cn = new SqlConnection("Data Source=TestSQL;Initial Catalog=YPIData;Integrated Security=True"))
{
cn.Open();
cmd.Connection = cn;
using (System.Xml.XmlReader xmlReader = cmd.ExecuteXmlReader())
{
xmlReader.Read();
result = System.Xml.Linq.XElement.Load(xmlReader);
}
}
return result;
}
public static XPathDocument ExecCmd(IRenderXslt ctrl, SqlCommand cmd, string rootNode)
{
XPathDocument retXml = null;
try
{
using (XmlReader xmlRead = cmd.ExecuteXmlReader())
{
retXml = new XPathDocument(xmlRead);
}
}
catch (Exception ex)
{
ctrl.LastError = ex;
var mergeMsg = ex.Message;
if (ex.InnerException != null)
mergeMsg += Environment.NewLine + ex.InnerException.Message;
ctrl.TraceWrite(ctrl.ID, mergeMsg);
return XPathError(ctrl.ID, mergeMsg);
}
if (rootNode.Length == 0)
return retXml;
XmlDocument rootX = new XmlDocument();
XmlDeclaration dec = rootX.CreateXmlDeclaration("1.0", "utf-8", null);
XmlNode xnode = rootX.CreateElement(rootNode);
rootX.AppendChild(xnode);
// Copy XPath to XPath: msdn.microsoft.com/en-us/library/5x8bxy86(v=vs.110).aspx
XPathNavigator rootNav = rootX.CreateNavigator();
rootNav.MoveToChild(rootNode, "");
var sqlNav = retXml.CreateNavigator();
foreach (XPathNavigator nav in sqlNav.Select("*"))
{
rootNav.AppendChild(nav);
}
XPathDocument retRoot = new XPathDocument(new XmlNodeReader(rootX));
return retRoot; // debug: retRoot.CreateNavigator().OuterXml
}
protected void Page_Load(object sender, EventArgs e)
{
string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
// Define the command.
string customerQuery =
"SELECT FirstName, LastName FROM Employees FOR XML AUTO, ELEMENTS";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand(customerQuery, con);
// Execute the command.
StringBuilder str = new StringBuilder();
try
{
con.Open();
XmlReader reader = com.ExecuteXmlReader();
while (reader.Read())
{
// Process each employee.
if ((reader.Name == "Employees") && (reader.NodeType == XmlNodeType.Element))
{
reader.ReadStartElement("Employees");
str.Append(reader.ReadElementString("FirstName"));
str.Append(" ");
str.Append(reader.ReadElementString("LastName"));
str.Append("<br>");
reader.ReadEndElement();
}
}
reader.Close();
}
finally
{
con.Close();
}
XmlText.Text = str.ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "database=Northwind;Data Source=.\\SQLEXPRESS;User id=wrox;pwd=wrox1*c";
XmlDocument x = new XmlDocument();
XPathNavigator xpathnav = x.CreateNavigator();
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(
"select * from Customers as Customer for XML AUTO, ELEMENTS", conn);
using (XmlWriter xw = xpathnav.PrependChild())
{
xw.WriteStartElement("Customers");
using (XmlReader xr = command.ExecuteXmlReader())
{
xw.WriteNode(xr, true);
}
xw.WriteEndElement();
}
}
Response.ContentType = "text/xml";
x.Save(Response.Output);
}
public string GetPaymentStatus()
{
ICacheManager cacheManager = CacheFactory.GetCacheManager("Cache Manager");
string paymentStatus = (string)cacheManager.GetData("PaymentStatusXML");
if (null == paymentStatus)
{
paymentStatus = string.Empty;
// connect to the database
ConnectionStringSettingsCollection connections = ConfigurationManager.ConnectionStrings;
string connectionString = connections["JobTrackerConnection"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetPaymentStatusXML";
XmlReader reader = cmd.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
reader.Read();
while (!reader.EOF) sb.AppendLine(reader.ReadOuterXml());
paymentStatus = sb.ToString();
reader.Close();
cacheManager.Add("PaymentStatusXML", paymentStatus);
}
}
return paymentStatus;
}
/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
XmlReader retval = cmd.ExecuteXmlReader();
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}