One of my functions has to insert to database multiple rows and multiple columns to SQL 2005 Express. In my C# code, I have to make a round trip call to each row that I have to insert.
Accidentally, I stumbled the new feature of SQL 2005 using native XML. Further searching and coding finally gets me to do a single call to insert my data that consist multiple columns and rows.
Here is the stored procedure:
CREATE PROCEDURE [dbo].[rproc_NewMember]
@EducationalAttainment xml
AS
BEGIN
INSERT INTO [dbo].[tblEducationAttainment]([sEASchool],[sEAYear],[sEADegree],[ixEducationalAttainmentType],[ixMember])SELECT R.Table1.value(’sEASchool[1]’,’varchar(100)’) as sEASchool,R.Table1.value(’sEAYear[1]’,’varchar(100)’) as sEAYear,R.Table1.value(’sEADegree[1]’,’varchar(100)’) as sEADegree,R.Table1.value(’ixEducationalAttainmentType[1]’,’int‘) as ixEducationalAttainmentType,@ixMember as ixMember
FROM @EducationalAttainment.nodes(’ea/Table1′) AS R(Table1)
END
Here is the XML:
<ea>
<Table1>
<ixEducationalAttainmentType>1</ixEducationalAttainmentType>
<sEducationalAttainmentType>Elementary</sEducationalAttainmentType>
<sEASchool>CWSC</sEASchool>
<sEAYear>1990</sEAYear>
<sEADegree>ELEM</sEADegree>
</Table1>
<Table1>
<ixEducationalAttainmentType>2</ixEducationalAttainmentType>
<sEducationalAttainmentType>High School</sEducationalAttainmentType>
<sEASchool>ASJ</sEASchool>
<sEAYear>1994</sEAYear>
<sEADegree>HS</sEADegree>
</Table1>
<Table1>
<ixEducationalAttainmentType>3</ixEducationalAttainmentType>
<sEducationalAttainmentType>College</sEducationalAttainmentType>
<sEASchool>SLU</sEASchool>
<sEAYear>1998</sEAYear>
<sEADegree>ECE</sEADegree>
</Table1>
<Table1>
<ixEducationalAttainmentType>4</ixEducationalAttainmentType>
<sEducationalAttainmentType>Others</sEducationalAttainmentType>
<sEASchool>STI</sEASchool>
<sEAYear>1999</sEAYear>
<sEADegree>CLIP</sEADegree>
</Table1>
</ea>
<Table1>
<ixEducationalAttainmentType>1</ixEducationalAttainmentType>
<sEducationalAttainmentType>Elementary</sEducationalAttainmentType>
<sEASchool>CWSC</sEASchool>
<sEAYear>1990</sEAYear>
<sEADegree>ELEM</sEADegree>
</Table1>
<Table1>
<ixEducationalAttainmentType>2</ixEducationalAttainmentType>
<sEducationalAttainmentType>High School</sEducationalAttainmentType>
<sEASchool>ASJ</sEASchool>
<sEAYear>1994</sEAYear>
<sEADegree>HS</sEADegree>
</Table1>
<Table1>
<ixEducationalAttainmentType>3</ixEducationalAttainmentType>
<sEducationalAttainmentType>College</sEducationalAttainmentType>
<sEASchool>SLU</sEASchool>
<sEAYear>1998</sEAYear>
<sEADegree>ECE</sEADegree>
</Table1>
<Table1>
<ixEducationalAttainmentType>4</ixEducationalAttainmentType>
<sEducationalAttainmentType>Others</sEducationalAttainmentType>
<sEASchool>STI</sEASchool>
<sEAYear>1999</sEAYear>
<sEADegree>CLIP</sEADegree>
</Table1>
</ea>
And here is the C# code:
string xml=”"; //set xml variable here
using (SqlConnection conn = new SqlConnection(@”Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=True;”))
{
using (SqlCommand cmd = new SqlCommand(“rproc_NewMember”, conn))
{
//set command type as stored procedure
cmd.CommandType = CommandType.StoredProcedure;
//add the parameter
cmd.Parameters.AddWithValue(“@EducationalAttainment”, xml);
//open connection
conn.Open();
//execute
cmd.ExecuteNonQuery();
}
}
using (SqlConnection conn = new SqlConnection(@”Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=True;”))
{
using (SqlCommand cmd = new SqlCommand(“rproc_NewMember”, conn))
{
//set command type as stored procedure
cmd.CommandType = CommandType.StoredProcedure;
//add the parameter
cmd.Parameters.AddWithValue(“@EducationalAttainment”, xml);
//open connection
conn.Open();
//execute
cmd.ExecuteNonQuery();
}
}
