Creating Stored Procedures with the .NET Framework : CLR Database Objects « ADO.net Database « ASP.NET Tutorial

1. ASP.NET Tutorial
2. ADO.net Database
3. CLR Database Objects

You must complete the following steps:

1. Create an assembly that contains the stored procedure method.

2. Register the assembly with SQL Server.

3. Create a stored procedure based on the assembly.

Creating the Stored Procedure Assembly

File: RandomRows.cs

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class RandomRows
{
    [SqlProcedure]
    public static void GetRandomRow()
    {
        SqlDataAdapter dad = new SqlDataAdapter("SELECT Id,Title FROM Products", "context connection=true");
        DataTable dtblProducts = new DataTable();
        dad.Fill(dtblProducts);

        Random rnd = new Random();
        DataRow ranRow = dtblProducts.Rows[rnd.Next(dtblProducts.Rows.Count)];

        SqlDataRecord result = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int), new SqlMetaData("Title", SqlDbType.NVarChar, 100));
        result.SetSqlInt32(0, (int)ranRow["Id"]);
        result.SetSqlString(1, (string)ranRow["Title"]);

        SqlContext.Pipe.Send(result);
    }

    [SqlProcedure]
    public static void GetRandomRows(int rowsToReturn)
    {
        SqlDataAdapter dad = new SqlDataAdapter("SELECT Id,Title FROM Products", "context connection=true");
        DataTable dtblProducts = new DataTable();
        dad.Fill(dtblProducts);

        SqlDataRecord result = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int), new SqlMetaData("Title", SqlDbType.NVarChar, 100));
        SqlContext.Pipe.SendResultsStart(result);

        Random rnd = new Random();
        for (int i = 0; i < rowsToReturn; i++)
        {
            DataRow ranRow = dtblProducts.Rows[rnd.Next(dtblProducts.Rows.Count)];
            result.SetSqlInt32(0, (int)ranRow["Id"]);
            result.SetSqlString(1, (string)ranRow["Title"]);
            SqlContext.Pipe.SendResultsRow(result);
        }
        SqlContext.Pipe.SendResultsEnd();
    }
}

csc /t:library RandomRows.cs

Registering the Stored Procedure Assembly with SQL Server

CREATE ASSEMBLY RandomRows
FROM 'C:\RandomRows.dll'

To remove the assembly:

DROP Assembly RandomRows

Creating the Stored Procedures

CREATE PROCEDURE GetRandomRow AS
EXTERNAL NAME RandomRows.RandomRows.GetRandomRow

CREATE PROCEDURE GetRandomRows(@rowsToReturn Int) AS
EXTERNAL NAME RandomRows.RandomRows.GetRandomRows


Executing the following command displays three random products from the Products database:

GetRandomRows 3


If you need to delete these stored procedures, you can execute the following two commands:

DROP PROCEDURE GetRandomRow
DROP PROCEDURE GetRandomRows