Example #1
1
        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();
        }
Example #2
0
        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();
            }
        }
Example #3
0
        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();
            }
        }
Example #4
0
        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);
        }
Example #5
0
 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;
    }
Example #7
0
        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);
            }
        }
Example #9
0
 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;
        }
Example #11
0
        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;
        }
Example #12
0
 /// <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();
 }
Example #13
0
        /// <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());
        }
Example #14
0
        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();
    }
Example #16
0
        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;
            }
        }
Example #17
0
 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();
 }
Example #18
0
    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();

    }
Example #20
0
 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);
     }
 }
Example #21
0
    /// <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;
			}
		}
Example #23
0
        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;
        }
Example #24
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;
        }
Example #25
0
        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;
     }
 }
Example #27
0
        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;
        }
Example #28
0
        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
        }
Example #29
0
    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();
    }
Example #30
0
 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;
        }
Example #32
0
        /// <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;
        }