How to retrieve xml from SQL Server 2005 in C#?

Question

I have inserted xml into SQL Server 2005 through rich text field successfully, now what I want to do is retrieve the xml from the DB but values separately and schema seperate... how can i do that in my existing code??

public void setData()
{
   dc.ID = textBox1.Text;
   dc.Name = richTextBox1.Text;
}

private void button1_Click(object sender, EventArgs e)
{
    setData();

    int flag = db.InsertData("insert into xmlTB values('" + dc.ID + "','" + dc.Name + "')");
    if (flag > 0)
       MessageBox.Show("Record Added");
    else
       MessageBox.Show("Not Added");

    try
    {
    }
    catch (Exception ex) 
    {
        MessageBox.Show(ex.Message);
    }
}

where the remain code of insertion is in a separate class:

public SqlConnection conn = new SqlConnection("Data Source=SERVER1\\SQLEXPRESS;Initial Catalog=xml;Integrated Security=True;Pooling=False");

public int flag = 0;
public SqlDataReader sdr = null;
public DBConnection() { } // constructor

public int InsertData(string qry)
{
     try
     {
          conn.Open();
          SqlCommand cmd = new SqlCommand(qry, conn);
          flag = cmd.ExecuteNonQuery();
          conn.Close();
          return flag;
      }
      catch (Exception)
      {
          return flag;
      }
 }

thanks a lot

marc_s · Accepted Answer · 2010-11-20 12:28:54Z

Several things you should definitely start using:

use parametrized queries for inserting values into your tables
use a specific list of columns in your INSERT statement - otherwise, next time that table changes, your INSERT will fail

The way you do it today is both fragile / brittle and will break when your table changes, plus the concatenating together of your SQL command is a great opportunity for SQL injection attacks. Just don't do it that way!

So your first method should look something like this:

private void button1_Click(object sender, EventArgs e)
{
    setData();

    string query = "INSERT INTO dbo.xmlTB(ID, Name) VALUES(@ID, @Name)";

    int flag = db.InsertData(query, ...(somehow pass in the parameters!.....);
    ......    
}

Secondly, your second method should

use the using(....) { ... } constructs to protect and dispose your SqlConnection and SqlCommand object instances
do retrieve the XML from the database, use a simple SELECT query and call either ExecuteReader or ExecuteScalar on your SqlCommand object.

Something like this:

public string ReadXmlData(int ID)
{
   string query = "SELECT XmlContent FROM dbo.xmlTB WHERE ID = @ID";
   string connectionString = "Data Source=SERVER1\\SQLEXPRESS;Initial Catalog=xml;Integrated Security=True;Pooling=False";

   using(SqlConnection conn = new SqlConnection(connectionString))
   using(SqlCommand cmd = new SqlCommand(query, conn))
   {
      cmd.Parameters.Add("@ID", SqlDbType.Int);
      cmd.Parameters["@ID"].Value = ID;

      conn.Open();
      string xmlContents = cmd.ExecuteScalar().ToString();
      conn.Close();

      return xmlContents;
   }
   catch (Exception)
   {
       return flag;
   }
}

Phil Helix · Accepted Answer · 2010-11-20 11:49:17Z

1

The question appears vague but: After record added, call another method called say "GetData" (you'll need to write this). This method might use cmd.ExecuteReader() to call the db. Ensure that your select statement in your query has "FOR XML" at the end of the table name.

answered Nov 20, 2010 at 11:49

Phil Helix

3,7414 gold badges32 silver badges52 bronze badges

6 Comments

salman Over a year ago

can u write that func?? my Q is eg. xml is <a name = "john"><b>hi</b></a>....................... so it shows john hi

Phil Helix Over a year ago

Where does the "<b>hi</b>" come from? It is neither name nor ID as specified in your question.

Phil Helix Over a year ago

The following will return you the xml:Select ID, Name from {table} FOR XML

Phil Helix Over a year ago

This can be called using: public void GetData(string qry) { try { conn.Open(); SqlCommand cmd = new SqlCommand(qry, conn); IDataReader dr = cmd.ExecuteDataReader(); // add logic to walk dr here conn.Close(); } catch (Exception) { return flag; } }

salman Over a year ago

i did this step i want to display it not insert

|

Collectives™ on Stack Overflow

How to retrieve xml from SQL Server 2005 in C#?

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related