存储过程返回到c#。net中的数据集

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

3 个解决方案

#1

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

#2

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
创建一个SQL连接
Create SQL command
创建SQL命令
Create SQL data adapter
创建SQL数据适配器
fill your dataset.
填满你的数据集。

Do not forget to open and close connection. follow this link for more under standing.

不要忘记打开和关闭连接。跟随这个链接更多的站下。

#3

You can declare SqlConnection and SqlCommand instances 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 Procedure and get the 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>

#1