December 2009
M T W T F S S
« Nov   Mar »
 123456
78910111213
14151617181920
21222324252627
28293031  

Send SqlDbType.Xml to Store Procedure

SqlCommand command = new SqlCommand(”ReportMultiTruckGeneric”);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(”@startDate”, SqlDbType.DateTime).Value = ProcessStartDate;
command.Parameters.Add(”@endDate”, SqlDbType.DateTime).Value = ProcessEndDate;

StringBuilder sb = new StringBuilder();

sb.Append(”<Trucks>”);
foreach (DataRow row in trucks.Tables[0].Rows)
{
sb.AppendFormat(”<TruckId>{0}</TruckId>”, row["ID"].ToString());
}
sb.Append(”</Trucks>”);

command.Parameters.Add(”@trucks”, SqlDbType.Xml).Value = sb.ToString();

DataSet queryResult = DBHook.SendSQLSelectRequest(command, out ex);

==store procedure=============
ALTER procedure [dbo].[ReportMultiTruckGeneric]
@startDate datetime,
@endDate datetime,
@trucks xml
as


set @trucksCopy = @trucks

select Trucks.TruckId.value(’.', ‘int’)
from @trucksCopy.nodes(’Trucks/TruckId’) as Trucks(TruckId)

1 comment to Send SqlDbType.Xml to Store Procedure

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>