sprocsandfunc.cs

< SQL Server2005程序设计>

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


public partial class SprocsAndFunc
{
    [SqlProcedure()]
    public static void spSQLCLR()
    {
        // ADO.NET variables for SQL CLR operations:
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cm = new SqlCommand("Select Count(*) from Person.Contact", conn);
        SqlDataRecord drc = new SqlDataRecord(new SqlMetaData("ContactCount", SqlDbType.Int));

        // Execute Select Count(*) query and return result:
        conn.Open();
        drc.SetInt32(0, (Int32)cm.ExecuteScalar());
        SqlContext.Pipe.Send(drc);
        conn.Close();
    }

    [SqlProcedure()]
    public static void spSQLCLRAndTSQL()
    {
        // ADO.NET variables for SQL CLR operations:
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cm = new SqlCommand("Select Count(*) from Person.Contact", conn);
        SqlDataRecord drc = new SqlDataRecord(new SqlMetaData("ContactCount", SqlDbType.Int));

        // Call out to a T-SQL stored procedure:
        SqlDataAdapter da = new SqlDataAdapter("EXEC spTSQL 54", conn);
        DataTable dt = new DataTable();
        da.Fill(dt);

        // Execute Select Count(*) query and return result:
        conn.Open();
        drc.SetInt32(0, (Int32)cm.ExecuteScalar());
        SqlContext.Pipe.Send(drc);
        conn.Close();
    }

    [SqlFunction()]
    public static SqlString fnHelloWorldCh09()
    {
        return new SqlString("Hello World");
    }
};