存储过程返回到 C# .Net 中的 DataSet
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12973773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored procedure return into DataSet in C# .Net
提问by cihata87
I want to return virtual table from stored procedure and I want to use it in dataset in c# .net. My procedure is a little complex and can't find how to return a table and set it in a dataset
我想从存储过程返回虚拟表，我想在 c# .net 的数据集中使用它。我的程序有点复杂，找不到如何返回表并将其设置在数据集中
Here is my procedure to modify:
下面是我修改的过程：
ALTER PROCEDURE [dbo].[Procedure1]
@Start datetime,
@Finish datetime,
@TimeRange time
AS
BEGIN
SET NOCOUNT ON;
declare @TimeRanges as TABLE (SessionStart datetime, SessionEnd datetime);
with TimeRanges as (
select @Start as StartTime, @Start + @TimeRange as EndTime
union all
select StartTime + @TimeRange, EndTime + @TimeRange
from TimeRanges
where StartTime < @Finish )
select StartTime, EndTime, Count( Test.ScenarioID ) as TotalPeaks
from TimeRanges as TR left outer join
dbo.Test as Test on TR.StartTime <= Test.SessionStartTime and Test.SessionCloseTime < TR.EndTime
group by TR.StartTime, TR.EndTime
END
采纳答案by codingbiz
Try this
尝试这个
DataSet ds = new DataSet("TimeRanges");
using(SqlConnection conn = new SqlConnection("ConnectionString"))
{
SqlCommand sqlComm = new SqlCommand("Procedure1", conn);
sqlComm.Parameters.AddWithValue("@Start", StartTime);
sqlComm.Parameters.AddWithValue("@Finish", FinishTime);
sqlComm.Parameters.AddWithValue("@TimeRange", TimeRange);
sqlComm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlComm;
da.Fill(ds);
}
回答by muhammad kashif
I should tell you the basic steps and rest depends upon your own effort. You need to perform following steps.
我应该告诉你基本步骤，剩下的就靠你自己的努力了。您需要执行以下步骤。
- Create a connection string.
- Create a SQL connection
- Create SQL command
- Create SQL data adapter
- fill your dataset.
- 创建连接字符串。
- 创建 SQL 连接
- 创建 SQL 命令
- 创建 SQL 数据适配器
- 填充你的数据集。
Do not forget to open and close connection. follow this linkfor more under standing.
不要忘记打开和关闭连接。点击此链接以了解更多信息。
回答by Sarath Avanavu
You can declare SqlConnectionand SqlCommandinstances at global level so that you can use it through out the class. Connection string is in Web.Config.
您可以在全局级别声明SqlConnection和SqlCommand实例，以便您可以在整个类中使用它。连接字符串在Web.Config.
SqlConnection sqlConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SqlConnector"].ConnectionString);
SqlCommand sqlcomm = new SqlCommand();
Now you can use the below method to pass values to Stored Procedureand get the DataSet.
现在您可以使用以下方法将值传递给存储过程并获取DataSet.
public DataSet GetDataSet(string paramValue)
{
sqlcomm.Connection = sqlConn;
using (sqlConn)
{
try
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
// This will be your input parameter and its value
sqlcomm.Parameters.AddWithValue("@ParameterName", paramValue);
// You can retrieve values of `output` variables
var returnParam = new SqlParameter
{
ParameterName = "@Error",
Direction = ParameterDirection.Output,
Size = 1000
};
sqlcomm.Parameters.Add(returnParam);
// Name of stored procedure
sqlcomm.CommandText = "StoredProcedureName";
da.SelectCommand = sqlcomm;
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
}
}
catch (SQLException ex)
{
Console.WriteLine("SQL Error: " + ex.Message);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
}
}
return new DataSet();
}
The following is the sample of connection string in config file
以下是配置文件中的连接字符串示例
<connectionStrings>
<add name="SqlConnector"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=YourDatabaseName;User id=YourUserName;Password=YourPassword"
providerName="System.Data.SqlClient" />
</connectionStrings>
