How do I perform insertions & updates to a table in SQL server using C#?

[napstar]

I'm trying to create a leave app in asp.net,I have a leaveHistory table which would store each employee's personal details and their leave history(empLeav eHistory).

This field has an xml data type.I would like to know how to create an xml document from data retrieved from a web form using C#.For some reason the command object doesn't execute the query and I'm stumped.

This is the C# method I have:

public void InsertXML(MyObj ects.LeaveAppli cationClass LeaveClass)
{
char c=(char) 34;
// string xmlStr = "";
string updateQryStr = "UPDATE [dbo].[EmployeeLeaveHi story] ";
//updateQryStr = updateQryStr + Environment.New Line;
updateQryStr = updateQryStr + "SET empLeaveHistory .modify";
updateQryStr = updateQryStr + "(";

updateQryStr = updateQryStr + "INSERT";
updateQryStr = updateQryStr + "<LeaveHistory> <Employee id=" + c + LeaveClass.Empl oyeeNum + c + "><leave><start date>" + LeaveClass.Star tDate.ToShortDa teString() + "</startdate><endd ate>" + LeaveClass.Star tDate.ToShortDa teString() + "</enddate/><leavetype>" + LeaveClass.Leav eType.ToString( ) + "</leavetype></leave></Employee></LeaveHistory> ";//as last into()

updateQryStr = updateQryStr + ")where EmployeeLeaveHi story.swipeNum= "+LeaveClass.Em ployeeNum.ToStr ing();


SqlConnection myConn = new SqlConnection() ;
myConn = this.CreateConn ection();
if (myConn.State== ConnectionState .Closed)
{

try
{
myConn.Open();
SqlCommand cmd = new SqlCommand(upda teQryStr,myConn );
//Int32 r = cmd.ExecuteNonQ uery();
SqlDataReader rdr = cmd.ExecuteRead er();-//ERROR OCCURS HERE
}
catch (Exception)
{

throw;
}
finally
{
if (myConn.State== ConnectionState .Open)
{
myConn.Close();
}

}
}



//return xmlStr;

}

The xml document would look like this:

<LeaveHistory >
<Employee id="460414">
<leave>
<startdate>08/08/2000</startdate>
<enddate>08/08/2000 </enddate>
<leavetype>3</leavetype>
</leave>
<leave>
<startdate>08/08/2010</startdate>
<enddate>08/08/2010 </enddate>
<leavetype>1</leavetype>
</leave>
</Employee>
</LeaveHistory>

Table:
CREATE TABLE [dbo].[EmployeeLeaveHi story](
[id] [int] IDENTITY(1,1) NOT NULL,
[swipeNum] [int] NULL,
[first_name] [varchar](50) NOT NULL,
[last_name] [varchar](50) NOT NULL,
[hire_date] [datetime] NULL,
[dob] [datetime] NULL,
[termination_dat e] [datetime] NULL,
[empLeaveHistory] [xml] NULL,
CONSTRAINT [PK_EmployeeProf ile] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORE COMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCK S = ON) ON [PRIMARY]
) ON [PRIMARY]