ストアドを使ってSelect文の結果を取得するサンプル

TSql_Select

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

namespace StoredProcedureSelect
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection(@"Data Source=(local)\SQLEXPRESS; Initial Catalog=sampleDB; Integrated Security=SSPI;"))
            using (var comm = new SqlCommand("SearchEmployee", conn))
            {
                comm.CommandType = CommandType.StoredProcedure;

                var empNoFrom = new SqlParameter("@empnoFrom", SqlDbType.Int);
                empNoFrom.Value = 1;
                comm.Parameters.Add(empNoFrom);

                var empNoTo = new SqlParameter("@empnoTo", SqlDbType.Int);
                empNoTo.Value = 9;
                comm.Parameters.Add(empNoTo);

                var table = new DataTable();

                using (var adpt = new SqlDataAdapter(comm))
                {
                    try
                    {
                        adpt.Fill(table);
                    }
                    finally
                    {
                        comm.Parameters.Clear();
                        conn.Close();
                    }
                }

                if (0 < table.Rows.Count)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Console.WriteLine(row[0]);
                    }
                }

                Console.ReadKey();
            }
        }
    }
}