How can you read XML data and store it into database ?

If you like the program or see any bug/defect/error then, hope to read it from you. Thankx

=========================================================

public void updateXmlIntoDatabase()
{
//create xml to represent collection of students with id, name, password, role
string xml = @”

0
jill
flower
admin


1
rakesh
kent
guest


2
heskar
tnek
tseug


3
bob
house
guest

”;

SqlConnection conn = null;
try
{
conn = new SqlConnection(”Data Source=localhost; Initial Catalog=cs466; Integrated security=SSPI”);
conn.Open();

////Insert users read xml from string
XmlDocument doc1 = new XmlDocument();
doc1.LoadXml(xml);
XmlNodeList nlist = doc1.SelectNodes(”/students/student”);
string n, p, r;
foreach (XmlNode node in nlist)
{
//read the value of name, password and role values from XML.
n = node.SelectSingleNode(”name”).InnerText;
p = node.SelectSingleNode(”password”).InnerText;
r = node.SelectSingleNode(”role”).InnerText;

//insert into the users table
string ins = “insert into users values(@name, @password, @role)”;
SqlCommand cmdINSERT = new SqlCommand(ins, conn);
cmdINSERT.Parameters.AddWithValue(”@name”, n);
cmdINSERT.Parameters.AddWithValue(”@password”, p);
cmdINSERT.Parameters.AddWithValue(”@role”, r);
cmdINSERT.ExecuteNonQuery();
}

//// INSERT users XML reading from file
XmlTextReader xtrxml = new XmlTextReader(”C://RTyata//Project1//XMLFile1.xml”);
XmlDocument doc2 = new XmlDocument();
doc2.Load(xtrxml);
////XmlNodeList nlist = doc2.SelectNodes(”/students/student”);
XmlNodeList nolist = doc2.DocumentElement.ChildNodes;
//string n, p, r;
foreach (XmlNode node in nolist)
{
//read the value of name, password and role values from XML File.
n = node.SelectSingleNode(”name”).InnerText;
p = node.SelectSingleNode(”password”).InnerText;
r = node.SelectSingleNode(”role”).InnerText;

//insert into the users table
string ins = “insert into users values(@name, @password, @role)”;
SqlCommand cmdINSERT = new SqlCommand(ins, conn);
cmdINSERT.Parameters.AddWithValue(”@name”, n);
cmdINSERT.Parameters.AddWithValue(”@password”, p);
cmdINSERT.Parameters.AddWithValue(”@role”, r);
cmdINSERT.ExecuteNonQuery();
}

// Select users and display
string sql = “select * from users”;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < dr.ItemArray.Length; i++)
{
Console.Write(”{0} “, dr[i]);
}
Console.WriteLine();
}

}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}

}

No comments:

Post a Comment