SQL Server: Output keyword with the Enterprise Lib

Below are some examples of using stored procedures containing the output keyword in SQL server 2005. Being a SharePoint developer, I don’t normally need to do SQL programming, hence I am blogging this so that I’ve got it for future reference.
Note that the examples use the following table:
CREATE TABLE Articles
(
    id int IDENTITY (1,1) PRIMARY KEY,
    title nvarchar (50),
    published datetime,
    article ntext,
    enabled bit
);

All C# examples reference the following:
Microsoft.Practices.EnterpriseLibrary.Common;
Microsoft.Practices.EnterpriseLibrary.Data;

Example 1: Using output keyword to return a scalar value

Stored Proc
CREATE PROCEDURE dbo.ArticleCreate
(
    @title nvarchar(MAX),
    @published datetime,
    @article ntext,
    @enabled bit
)
AS
SET NOCOUNT ON
INSERT INTO Articles
(
    title,
    published,
    article,
    enabled
)
OUTPUT inserted.id
VALUES
(
    @title,
    @published,
    @article,
    @enabled
);
RETURN

Alternatively, you could use
CREATE PROCEDURE dbo.ArticleCreate
(
    @title nvarchar(MAX),
    @published datetime,
    @article ntext,
    @enabled bit
)
AS
INSERT INTO Articles
(
    title,
    published,
    article,
    enabled
)
VALUES
(
    @title,
    @published,
    @article,
    @enabled
);
SELECT SCOPE_IDENTITY()
RETURN

C# Code
public void Create()
{
    // TODO: add checks to ensure that relevant stuff is initialised.
    // Instantiate db and begin constructing sql
    Database db = DatabaseFactory.CreateDatabase();
    string sqlCommand = "dbo.ArticleCreate";
    DbCommand command = db.GetStoredProcCommand(sqlCommand);
    // Set the parameters
    db.AddInParameter(command, "@title", DbType.String, _title);
    db.AddInParameter(command, "@published", DbType.DateTime, _published);
    db.AddInParameter(command, "@article", DbType.String, _article);
    db.AddInParameter(command, "@enabled", DbType.Boolean, true);
    // Execute scalar command
    _id = Convert.ToInt32(db.ExecuteScalar(command));
}

Example 2: Using the output keyword to return a set of fields for a record

Stored Proc
CREATE PROCEDURE dbo.ArticleRead
(
    @id int,
    @title nvarchar(MAX) OUTPUT,
    @published datetime OUTPUT,
    @article ntext OUTPUT,
    @enabled bit OUTPUT
)
AS
SET NOCOUNT ON
SELECT
    @title = [title],
    @published = [published],
    @article = [article],
    @enabled = [enabled]
FROM
    Articles
WHERE
    [id] = @id;
RETURN

C# Code
public void Read()
{
    Database db = DatabaseFactory.CreateDatabase();
    string sqlCommand = "dbo.ArticleRead";
    DbCommand command = db.GetStoredProcCommand(sqlCommand);
    // Set the parameters
    db.AddInParameter(command, "@id", DbType.Int32, _id);
    db.AddOutParameter(command, "title", DbType.String, 255);
    db.AddOutParameter(command, "published", DbType.DateTime, 8);
    db.AddOutParameter(command, "article", DbType.String, Int32.MaxValue);
    db.AddOutParameter(command, "enabled", DbType.Boolean, 1);
    // Execute scalar command
    db.ExecuteNonQuery(command);
    // Get return values
    _title = db.GetParameterValue(command, "@title").ToString();
    _published = DateTime.Parse(db.GetParameterValue(command, "@published").ToString());
    _article = db.GetParameterValue(command, "@article").ToString();
    _enabled = (bool)(db.GetParameterValue(command, "@enabled"));
}

You May Also Like

About the Author: rnowik

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.