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(empLeaveHistory).
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(MyObjects.LeaveApplicationClass LeaveClass)
{
char c=(char) 34;
// string xmlStr = "";
string updateQryStr = "UPDATE [dbo].[EmployeeLeaveHistory] ";
//updateQryStr = updateQryStr + Environment.NewLine;
updateQryStr = updateQryStr + "SET empLeaveHistory.modify";
updateQryStr = updateQryStr + "(";
updateQryStr = updateQryStr + "INSERT";
updateQryStr = updateQryStr + "<LeaveHistory><Employee id=" + c + LeaveClass.EmployeeNum + c + "><leave><startdate>" + LeaveClass.StartDate.ToShortDateString() + "</startdate><enddate>" + LeaveClass.StartDate.ToShortDateString() + "</enddate/><leavetype>" + LeaveClass.LeaveType.ToString() + "</leavetype></leave></Employee></LeaveHistory> ";//as last into()
updateQryStr = updateQryStr + ")where EmployeeLeaveHistory.swipeNum="+LeaveClass.Employe eNum.ToString();
SqlConnection myConn = new SqlConnection();
myConn = this.CreateConnection();
if (myConn.State==ConnectionState.Closed)
{
try
{
myConn.Open();
SqlCommand cmd = new SqlCommand(updateQryStr,myConn);
//Int32 r = cmd.ExecuteNonQuery();
SqlDataReader rdr = cmd.ExecuteReader();-//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].[EmployeeLeaveHistory](
[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_date] [datetime] NULL,
[empLeaveHistory] [xml] NULL,
CONSTRAINT [PK_EmployeeProfile] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]