using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //用数据适配器填充DataSet类
            string source = "server=(local) \\SQLEXPRESS;integrated security=true;database=Student";
            SqlConnection con = new SqlConnection(source);
            con.Open();
            if (con.State == ConnectionState.Open)
                Console.WriteLine("数据库连接成功！");
            string select = "select * from customers";
            SqlDataAdapter sda = new SqlDataAdapter(select, con);
            DataSet ds = new DataSet();
            sda.Fill(ds, "customers");//将数据适配器sda的数据（可以看成一个表）填充到ds中，数据（表）名为customers
            foreach (DataRow x in ds.Tables["customers"].Rows)
            {
                Console.WriteLine("name:{0},id:{1}", x[0], x[1]);
            }
            DataSet ds2 = new DataSet();
            SqlDataAdapter sda2 = new SqlDataAdapter();
            sda2.SelectCommand = selectcommand(con);//sda2.SelectCommand:获取或设置一个 Transact-SQL 语句或存储过程，用于在数据源中选择记录
            sda2.Fill(ds2, "customers2");//将数据适配器sda2的数据（可以看成一个表）填充到ds2中，数据（表）名为customers2
            foreach (DataRow x in ds2.Tables["customers2"].Rows)
            {
                Console.WriteLine("name:{0},id:{1}", x[0], x[1]);
            }
        }

        private static SqlCommand selectcommand(SqlConnection con)//自定义方法用于返回一个数据源记录
        {
            SqlCommand sc = new SqlCommand("selectstudent", con);
            sc.CommandType = CommandType.StoredProcedure;
            sc.UpdatedRowSource = UpdateRowSource.None;
            sc.Parameters.AddWithValue("@id", 5);
            sc.ExecuteNonQuery();
            return sc;
        }
    }
}

use Student
drop procedure selectstudent
go
create procedure selectstudent(@id int)
as
select *
from Customers
where id<=@id