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

最新推荐文章于 2024-10-11 21:29:57 发布

原创最新推荐文章于 2024-10-11 21:29:57 发布 · 447 阅读

0 ·

CC 4.0 BY-SA版权

C# 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了四种不同类型的SQL存储过程：无参数、带输入参数、带输入输出参数及带返回值的过程，并提供了C#调用这些过程的示例代码。

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();