sptest.cs

< SQL Server2005程序设计>

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


public partial class StoredProcedures
{
    [SqlProcedure]
    public static void spContacts()
    {
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cm = new SqlCommand("Select * from Person.Contact", conn);

        conn.Open();
        SqlDataReader dr = cm.ExecuteReader();
        SqlContext.Pipe.Send("Starting data dump");
        SqlContext.Pipe.Send(dr);
        SqlContext.Pipe.Send("Data dump complete");
        dr.Close();
        conn.Close();
    }

    [SqlProcedure()]
    public static void spContactCount()
    {
        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));

        conn.Open();
        drc.SetInt32(0, (Int32)cm.ExecuteScalar());
        SqlContext.Pipe.Send(drc);
        conn.Close();
    }
};