解析数据到数据库
问题描述:
我现在有一个标准的Web服务这个方法:解析数据到数据库
[WebMethod]
public void addGame(int GamePlayID, int @ParticipantID, int @GameVersionID, string Start, string End,string success)
{
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = @"Data Source=SNICKERS\SQLEXPRESS;Initial Catalog=VerveDatabase;Integrated Security=True";
oConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = oConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "addGamePlay";
cmd.Parameters.Add(new SqlParameter("@GamePlayID", SqlDbType.Int));
cmd.Parameters["@GamePlayID"].Value = GamePlayID;
cmd.Parameters.Add(new SqlParameter("@ParticipantID", SqlDbType.Int));
cmd.Parameters["@ParticipantID"].Value = @ParticipantID;
cmd.Parameters.Add(new SqlParameter("@GameVersionID", SqlDbType.Int));
cmd.Parameters["@GameVersionID"].Value = @GameVersionID;
cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.Time));
cmd.Parameters["@Start"].Value = Start;
cmd.Parameters.Add(new SqlParameter("@End", SqlDbType.Time));
cmd.Parameters["@End"].Value = End;
cmd.Parameters.Add(new SqlParameter("@success", SqlDbType.VarChar, 10));
cmd.Parameters["@success"].Value = success;
cmd.ExecuteNonQuery();
}
这让我传递值进行的跨向其中输入manually.However我希望能够到数据库从XML文档加载数据。我如何从这个XML文档中的数据来填充变量此方法。下面是XML文档:
<?xml version="1.0" encoding="UTF-8"?>
<anyType xmlns="http://tempuri.org/" xmlns:d1p1="http://www.w3.org/2001/XMLSchema-instance" d1p1:type="q1:string" xmlns:q1="http://www.w3.org/2001/XMLSchema">
<NewDataSet>
<Game>
<GamePlayID>1</GamePlayID>
<ParticipantID>1</ParticipantID>
<GameVersionID>1</GameVersionID>
<Start-Time>PT0S</Start-Time>
<End-Time>PT5H</End-Time>
<Success>true </Success>
</Game>
</NewDataSet>
</anyType>
答
我不明白为什么我了下来投票,因为这是一个真正的问题,但是我想我应该说明我如何设法来过这个问题。正如我paticular XML文档只包含一个节点我用的XmlDocument了“的selectSingleNode”功能来接我想要的数据,并将它们分配给准备插入通过SQL PROC我的数据库变量。
[WebMethod]
public void addGame()
{
XmlDocument xd = new XmlDocument();
xd.Load(@"C:\Users\bradleya\Documents\Visual Studio 2010\Projects\Web Services\Web Services\addGame.xml");
XmlNode documentNode = xd.SelectSingleNode("/NewDataSet/Game");
int GamePlayID = Convert.ToInt32(documentNode.SelectSingleNode("GamePlayID").InnerText);
int ParticipantID = Convert.ToInt32(documentNode.SelectSingleNode("ParticipantID").InnerText);
int GameVersionID = Convert.ToInt32(documentNode.SelectSingleNode("ParticipantID").InnerText);
string Start = Convert.ToString(documentNode.SelectSingleNode("Start-Time").InnerText);
string End = Convert.ToString(documentNode.SelectSingleNode("End-Time").InnerText);
string success = Convert.ToString(documentNode.SelectSingleNode("Success").InnerText);
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = @"Data Source=SNICKERS\SQLEXPRESS;Initial Catalog=VerveDatabase;Integrated Security=True";
oConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = oConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "addGamePlay";
cmd.Parameters.Add(new SqlParameter("@GamePlayID", SqlDbType.Int));
cmd.Parameters["@GamePlayID"].Value = GamePlayID;
cmd.Parameters.Add(new SqlParameter("@ParticipantID", SqlDbType.Int));
cmd.Parameters["@ParticipantID"].Value = @ParticipantID;
cmd.Parameters.Add(new SqlParameter("@GameVersionID", SqlDbType.Int));
cmd.Parameters["@GameVersionID"].Value = @GameVersionID;
cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.Time));
cmd.Parameters["@Start"].Value = Start;
cmd.Parameters.Add(new SqlParameter("@End", SqlDbType.Time));
cmd.Parameters["@End"].Value = End;
cmd.Parameters.Add(new SqlParameter("@success", SqlDbType.VarChar, 10));
cmd.Parameters["@success"].Value = success;
cmd.ExecuteNonQuery();
}
答
你有没有想过创建具有XML类型的一个参数的存储过程,并发送整个<NewDataSet>
?您可以使用XQuery直接从XML类型插入到你的表从我的应用程序之一,与此类似INSERT
:
INSERT INTO CategoryItems (CategoryID, ItemID, SortOrderID)
SELECT @ID,
ParamValues.Item.value('./ID[1]', 'int'),
ParamValues.Item.value('./SortID[1]', 'int')
FROM @Items.nodes('/Items/Item') AS ParamValues(Item)
(@Items
是XML类型的)
你有什么已经尝试过? Linq2Xml?用XmlDocument解析?哪些问题你在阅读文件遇到过吗? – 2012-07-25 13:35:11