使用类DTO传递
好处:
1.如果SP需要增减参数,无需改其调用代码,易维护
2.多参数的情况不用写一堆参数
坏处:参数不直观,不知道参数里面的数据,增加了一点点调试了难度
1.创建Type
Create Type dbo.KeyValue as table
(
TName varchar(10),
TValue varchar(50)
)
2.创建SP
Create PROCEDURE dbo.[SP_Test]
@Test varchar(20)='',
@KeyValue as dbo.KeyValue readonly
as
select * from @KeyValue where TName =@Test
3.SQL 调用(Type类型可以不传)
1).传入Type
2).不传Type
4.C#调用
using (SqlConnection sqlConnection = new SqlConnection(@""))//填入连接字符串
{
sqlConnection.Open();
DataTable res = new DataTable();
DataTable dtKeyValue = new DataTable();
dtKeyValue.Columns.Add("TName", typeof(string));
dtKeyValue.Columns.Add("TValue", typeof(string));
dtKeyValue.Rows.Add("TT","Value");
string sql = "exec [SP_Test] @Test, @KeyValue";
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandText = sql;
SqlParameter sqlParameter = new SqlParameter("@Test","TT");
SqlParameter sqlParameter1 = new SqlParameter("@KeyValue", dtKeyValue);
sqlParameter1.SqlDbType = SqlDbType.Structured;
sqlParameter1.TypeName = "dbo.KeyValue";
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.Parameters.Add(sqlParameter1);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(res);
}
结果