ADO.NET 调用数据库存储过程

1.没有参数的存储过程

create proc proc_getAll
as
select * from tbl_test
go

exec proc_getAll

 

            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True");
            connection.Open();
            SqlCommand command = new SqlCommand("proc_getAll", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.Write(reader[0].ToString() + "\t");
                Console.Write(reader[1].ToString() + "\t");
                Console.Write(reader[2].ToString() + "\t");
                Console.WriteLine();
            }
            reader.Close();
            connection.Close();

 

2.有输入参数的存储过程

create proc proc_getPart
@age int
as
select * from tbl_test where age=@age

exec proc_getPart 10

 

            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True");
            connection.Open();
            SqlCommand command = new SqlCommand("proc_getPart", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@age", SqlDbType.Int)};
            parms[0].Value = 10;
            command.Parameters.AddRange(parms);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.Write(reader[0].ToString() + "\t");
                Console.Write(reader[1].ToString() + "\t");
                Console.Write(reader[2].ToString() + "\t");
                Console.WriteLine();
            }
            reader.Close();
            connection.Close();

 

3.有输入参数、输出参数的存储过程

create proc proc_getCount
@age int,@count int output
as
select @count=COUNT(*) from tbl_test where age=@age

declare @c int
exec proc_getCount 10,@c out
print @c

 

            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True");
            connection.Open();
            SqlCommand command = new SqlCommand("proc_getCount", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@age", SqlDbType.Int), new SqlParameter("@count", SqlDbType.Int) };
            parms[0].Value = 10;
            parms[1].Direction = ParameterDirection.Output;
            command.Parameters.AddRange(parms);
            command.ExecuteNonQuery();
            Console.WriteLine(parms[1].Value.ToString());
            connection.Close();

 

 

4.有输入参数、返回值的存储过程

create proc proc_getCount1
@age int
as
declare @count int
select @count=COUNT(*) from tbl_test where age=@age
return @count

declare @c int
exec @c=proc_getCount1 10
print @c

            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True");
            connection.Open();
            SqlCommand command = new SqlCommand("proc_getCount1", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@age", SqlDbType.Int), new SqlParameter("@count", SqlDbType.Int) };
            parms[0].Value = 10;
            parms[1].Direction = ParameterDirection.ReturnValue;
            command.Parameters.AddRange(parms);
            command.ExecuteNonQuery();
            Console.WriteLine(parms[1].Value.ToString());
            connection.Close();