Search code examples
c#xmlsql-server-2005stored-proceduresenterprise-library

MS Enterprise Library Data Block to pass XML into the a stored procedure with a XML parameter


In my apps I typically use the Enterprise Library Data Block to simplify my database interactions. C# code such as:

public static IDataReader AdminNavigation_Insert(int iGroupId, string sText, string sRelativeUrl, int iSortOrder)
{ 
  return DatabaseFactory.CreateDatabase("database").ExecuteReader(
        "cms_uspAdminNavigation_Insert", 
        iGroupId, 
        sText, 
        sRelativeUrl, 
        iSortOrder);
}

would insert into a stored procedure like:

ALTER PROCEDURE [dbo].[cms_uspAdminNavigation_Insert]
    @GroupId int,
    @Text nvarchar(500) = NULL,
    @RelativeUrl varchar(100) = NULL,
    @SortOrder int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.cms_tblAdminNavigation (
    GroupId, Text, RelativeUrl, SortOrder, TsCreated, TsUpdated
) VALUES (
    @GroupId, @Text, @RelativeUrl, @SortOrder, getDate(), getDate()
);
SELECT @@IDENTITY;
END

Now, if one of those stored procedure parameters were xml, what C# datatype would I use to pass to that stored procedure via the Enterprise Library?


Solution

  • String = SQLDbBtype.xml

    See C#/SQL - What’s wrong with SqlDbType.Xml in procedures

    Or another SO question

    Edit, however for Linq it may be System.Xml.Linq.XElement