获取SQL Server中存储过程返回字段和类型

最新推荐文章于 2023-08-29 14:18:33 发布

ChenXi0803

最新推荐文章于 2023-08-29 14:18:33 发布

阅读量431

点赞数

文章标签： c# sqlserver

本文链接：https://blog.csdn.net/ChenXi0803/article/details/132534291

版权

获取SQL Server中存储过程返回字段和类型

1. 首先看看我们直接在SQL Server中是怎么执行存储过程的

DECLARE @EntityGuids EntityGuidsType
INSERT @EntityGuids
(
    entity_guid
)
VALUES
('3a18c156-a347-45c7-a8cb-5744a91e29af'),
 ('e7cfe2bc-ff34-4dc9-80f0-735a776a5669'),
 ('61c022cf-4331-4fc0-be48-53a41bd628f4'),
 ('1167179b-439f-4295-b2ff-de203c879eb7'),
 ('a5851041-19be-47a0-a997-ce3b208fa2c7'),
 ('f1a3a813-f073-4930-af62-0f0d0805ffb1'),
 ('3224d8c1-06c4-4b3a-b29c-75db38f68da7'),
 ('b2f40176-c153-4a1f-b5eb-961776294ba3'),
 ('c6736bed-296e-4f0f-8f20-3546e44e1c74'),
 ('0b3f3368-8cd8-43d4-9681-9af018ec9e6e')


EXEC dbo.GetFinancialHighlights @entityGuids = @EntityGuids,   -- EntityGuidsType
                                @user_guid = 'fe88cb86-01e2-44bd-a3b0-09ed9c079877'     -- uniqueidentifier

2. 接下来我们通过ADO执行存储过程来获取返回参数和类型

1. 执行存储过程

string connectionString = "Data Source=XXXX;User ID=XXXXX;Password=XXXX;Initial Catalog=XXXX";  //更换为你的连接字符串
 using (SqlConnection connection = new SqlConnection(connectionString))
 {
  connection.Open();

  using (SqlCommand command = new SqlCommand("dbo.GetFinancialHighlights", connection))
  {
   command.CommandType = CommandType.StoredProcedure;

   DataTable dtEntityGuids = new DataTable();
   dtEntityGuids.Columns.Add("entity_guid", typeof(Guid));
   dtEntityGuids.Rows.Add(new Guid("3a18c156-a347-45c7-a8cb-5744a91e29af"));
   dtEntityGuids.Rows.Add(new Guid("e7cfe2bc-ff34-4dc9-80f0-735a776a5669"));
   dtEntityGuids.Rows.Add(new Guid("61c022cf-4331-4fc0-be48-53a41bd628f4"));
   dtEntityGuids.Rows.Add(new Guid("1167179b-439f-4295-b2ff-de203c879eb7"));
   dtEntityGuids.Rows.Add(new Guid("a5851041-19be-47a0-a997-ce3b208fa2c7"));
   dtEntityGuids.Rows.Add(new Guid("f1a3a813-f073-4930-af62-0f0d0805ffb1"));
   dtEntityGuids.Rows.Add(new Guid("3224d8c1-06c4-4b3a-b29c-75db38f68da7"));
   dtEntityGuids.Rows.Add(new Guid("b2f40176-c153-4a1f-b5eb-961776294ba3"));
   dtEntityGuids.Rows.Add(new Guid("c6736bed-296e-4f0f-8f20-3546e44e1c74"));
   dtEntityGuids.Rows.Add(new Guid("0b3f3368-8cd8-43d4-9681-9af018ec9e6e"));

   SqlParameter param1 = new SqlParameter("@entityGuids", SqlDbType.Structured);
   param1.Value = dtEntityGuids;
   param1.TypeName = "dbo.EntityGuidsType";  //这里需要设置TypeName为你的自定义表类型的名字
   command.Parameters.Add(param1);

   SqlParameter param2 = new SqlParameter("@user_guid", SqlDbType.UniqueIdentifier);
   param2.Value = Guid.Parse("fe88cb86-01e2-44bd-a3b0-09ed9c079877");
   command.Parameters.Add(param2);

   using (SqlDataReader reader = command.ExecuteReader())
   {
    do
    {
     DataTable schemaTable = reader.GetSchemaTable();
     foreach (DataRow row in schemaTable.Rows)
     {
      var providerTypeCode = row["DataTypeName"];
      Console.WriteLine("Column: {0} \t\t\t SQL DataType: {1}", row["ColumnName"], providerTypeCode);
     }
     Console.WriteLine("=====================================================");
    }
    while (reader.NextResult());

   }
  }
 }