C# SqlCommand.ExecuteXmlReader方法代码示例

        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 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 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 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);
            }
        }

 /// <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();
 }

        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;
        }

        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);
        }

 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 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();
            }
        }

        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;
            }
        }

    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;
    }

    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 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 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());
        }