April 24, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0, Page 2

  • September 10, 2004
  • By Thiru Thangarathinam, Thiru Thangarathinam
  • Send Email »
  • More Articles »

The resulting page has two label controls, two textbox controls, and a button control. The Click event of the button saves the information to the database. Let's examine the code inside the Click event. To start with, the ID and the XML string values that the user enters are stored in local variables:

int ID = Convert.ToInt32(txtID.Text);
         string xmlValue = txtXmlDesc.Text;

After that, you retrieve the connection string from the web.config file by using the new ConnectionStrings collection class:

      //Get the connection string from the web.config file
      string connString =
         System.Configuration.ConfigurationSettings.
ConnectionStrings["TestDBConnectionString"].ConnectionString;

For the preceding code to work, you need to have the connection string stored in the web.config file as follows:

<connectionStrings>
<add name="TestDBConnectionString"
     connectionString="server=(local)\SqlExpress;
database=Test; Integrated Security=SSPI" />
</connectionStrings>

If the web.config file is not there, you can add one by selecting Add New Item from the Web Site menu and selecting Web Configuration File from the list of templates.

Once you have the connection string, you then can open the connection to the database by using the Open method of the SqlConnection object:

using (SqlConnection conn = new SqlConnection(connString))
      {
         conn.Open();

Then, you create a new SqlCommand object by invoking the CreateCommand method of the SqlConnection object. Next, you set the CommandText property of the SqlCommand object to an appropriate value:

SqlCommand cmd  = conn.CreateCommand();
cmd.CommandText = "Insert XmlTest(ID, XmlDesc)
                  Values(@ID, @XmlDesc)";

After that, you create the necessary parameters and then set their values:

//Set value of parameters
SqlParameter firstColParameter =
   cmd.Parameters.Add("@ID", SqlDbType.Int);
firstColParameter.Value = ID;
SqlParameter secondColParameter =
   cmd.Parameters.Add("@XmlDesc", SqlDbType.Xml);
secondColParameter.Value =
   new SqlXml(new XmlTextReader(xmlValue, XmlNodeType.Document
                                null));

To set the value for the XmlDesc parameter, create an object of type SqlXml. To the constructor of the SqlXML object, you supply an XmlReader object as an argument. The XML value that the user enters is supplied as an argument to the constructor of the XmlTextReader object.

Finally, execute the SQL query using the ExecuteNonQuery method of the SqlCommand object.

   //Execute update and close connection
   cmd.ExecuteNonQuery();
}
Response.Write("Saved values successfully");

Executing the preceding code will result in the output shown in Figure 3.



Click here for a larger image.

Figure 3: The Result from Executing Your SQL Query

In the Figure 3 screen, when you enter values for the ID and the XML columns and click the Save Values button, the entered values will be saved in the database.





Page 2 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel