从数据库中获取数据，并以XML返回它 [英] Getting data from a database and returning it in XML

问题描述

我目前使用一个存储过程具有可变获取数据库中的数据，但我是返回结果的XML。我可以用这个存储过程从表中得到的所有数据，并将其XML返回：

 公共字符串GetAllPatients（）
        {
            康涅狄格州字符串= @数据源= SNICKERS \\ SQLEX $ P $干燥综合征;初始目录= VerveDatabase;集成安全性=真;
            数据集ODS =新的DataSet（）;
            SqlDataAdapter的oCMD =新SqlDataAdapter的（GETALL，康恩）;
            oCMD.Fill（ODS，AllPatients）;
            返回oDS.GetXml（）;
        }
 

然而，当我试图让一个idividual病人记录，并返回它的XML我不知道怎么样，我，M目前正在做这样的：

 公共无效的getUser（INT ParticipantID）
        {            SqlConnection的oConn =新的SqlConnection（）;
            oConn.ConnectionString = @数据源= SNICKERS \\ SQLEX $ P $干燥综合征;初始目录= VerveDatabase;集成安全性=真;
            oConn.Open（）;
            数据集ODS =新的DataSet（）;
            CMD的SqlCommand =新的SqlCommand（）;
            cmd.Connection = oConn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText =的getUser;
            cmd.Parameters.Add（新的SqlParameter（@ ParticipantID，SqlDbType.Int））;
            cmd.Parameters [@ ParticipantID]值= 1。
            SqlDataReader的博士= cmd.ExecuteReader（）;
        }
 

解决方案

从SQL Server获取XML，存储过程需要读取类似

  SELECT什么
 从thetable
 WHERE ID = @participantID
 FOR XML AUTO
 

这将产生一个XML结果，您可以再与阅读

  VAR xmlResult =博士[0];
 

上澄清修改

在你的第二个方法，用类似code中的最后一行替换DataReader的第一个

  SqlDataAdapter的oCMD =新SqlDataAdapter的（CMD）;
        oCMD.Fill（ODS，用户）;
        返回oDS.GetXml（）;
 

I'm currently using a store procedure with a variable to get data from a database but I was the return the result in XML. I can get all data from a table using this store procedure and it returns in XML:

public string GetAllPatients()
        {
            string conn = @"Data Source=SNICKERS\SQLEXPRESS;Initial Catalog=VerveDatabase;Integrated Security=True";
            DataSet oDS = new DataSet();
            SqlDataAdapter oCMD = new SqlDataAdapter("getAll", conn);
            oCMD.Fill(oDS, "AllPatients");
            return oDS.GetXml();
        }

However when I try to get an idividual patient record and return it in XML I'm not sure how, I;m currently doing this:

public void getUser(int ParticipantID)
        {

            SqlConnection oConn = new SqlConnection();
            oConn.ConnectionString = @"Data Source=SNICKERS\SQLEXPRESS;Initial Catalog=VerveDatabase;Integrated Security=True";
            oConn.Open();
            DataSet oDS = new DataSet();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = oConn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "getUser";
            cmd.Parameters.Add(new SqlParameter("@ParticipantID",SqlDbType.Int));
            cmd.Parameters["@ParticipantID"].Value = 1; 
            SqlDataReader dr = cmd.ExecuteReader(); 
        }

解决方案

To get XML from SQL Server, your stored procedure needs to read something like

 SELECT whatever
 FROM thetable
 WHERE ID = @participantID
 FOR XML AUTO

which will generate an XML result which you can then read with

 var xmlResult = dr[0];

EDIT on clarifications

Replace the datareader in the last line of your second procedure with code similar to your first

        SqlDataAdapter oCMD = new SqlDataAdapter(cmd); 
        oCMD.Fill(oDS, "User"); 
        return oDS.GetXml(); 

这篇关于从数据库中获取数据，并以XML返回它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！