Call stored procedure : SqlConnection Stored Procedure « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. SqlConnection Stored Procedure

/*
CREATE PROCEDURE Person.GetContacts
    @RowCount int OUTPUT
AS
    SET NOCOUNT ON
    SELECT * FROM Person.Contact
    set @RowCount = @@ROWCOUNT
    RETURN @RowCount
*/

using System;
using System.Data;
using System.Data.SqlClient;

    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect ="SELECT COUNT(*) FROM Person.Contact; SELECT * FROM Person.Contact;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                SqlCommand command = new SqlCommand(sqlSelect, connection);
                connection.Open( );
                SqlDataReader dr = command.ExecuteReader( );
                dr.Read( );
                Console.WriteLine(dr.GetInt32(0));
                dr.NextResult( );
                int count = 0;
                while (dr.Read( )){
                    count++;
                }
                Console.WriteLine(count);
                dr.Close( );
                command = new SqlCommand("Person.GetContacts", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@RowCount", SqlDbType.Int).Direction =ParameterDirection.Output;
                dr = command.ExecuteReader( );
                dr.Close( );
                Console.WriteLine("Record count, using @@ROWCOUNT = {0}",command.Parameters["@RowCount"].Value);
            }
        }
    }