如何从SQL Server存储过程中读取数据表

I have this SQL Server stored procedure:

我有这个SQL Server存储过程:

    SET NOCOUNT ON
    EXECUTE sp_configure 'Show Advanced Options', 1
    RECONFIGURE
    EXECUTE sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE

    SELECT UserName, LoggedState, InteractionId, InteractionType    --<-- The columns required.
    FROM 
    OPENROWSET('SQLNCLI'
            ,'Server=USER-PC\SQLEXPRESS;Trusted_Connection=yes;database=XXX'
            ,'EXECUTE dbo.[XXX]')

When I execute it in SQL Server Management Studio, I got this result:

当我在SQL Server Management Studio中执行它时,我得到了这个结果:

My question

I need to read the data from the table.

我需要从表中读取数据。

My problem

This data is not in output parameters. That is why I couldn't read them.

此数据不在输出参数中。这就是我无法阅读它们的原因。

What I have tried

string vmpgraph = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
SqlConnection Graphsqlcon = new SqlConnection(vmpgraph);

SqlCommand GraphCmd = new SqlCommand("storedProcedureName", Graphsqlcon);

SqlParameter tdate = new SqlParameter();
GraphCmd.CommandType = CommandType.StoredProcedure; ;
SqlDataAdapter DAGraph = new SqlDataAdapter(GraphCmd);

DataSet DSGraph = new DataSet();
DSGraph.Clear();

DAGraph.Fill(DSGraph);

DataTable DTgraph = new DataTable();
DTgraph = DSGraph.Tables[0];

Is this correct? If not what should I do please?

它是否正确?如果不是我该怎么办?

I couldn't test my code on a real database because I don't have the database yet.

我无法在真实数据库上测试我的代码,因为我还没有数据库。

1 个解决方案

#1

If you need to read the data and create a data set

如果需要读取数据并创建数据集

 using (SqlCommand cmd = new SqlCommand("storedProcedureName",Connection ))
 {
    cmd.CommandType = CommandType.StoredProcedure;                    

    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
     {
           DataSet dataset = new DataSet();
           da.Fill(dataset);                       
     }
 }

to read dataset data

读取数据集数据

  foreach (var table in dataSet.Tables)
   {
       foreach (var row in table.Rows)
        {
            foreach (var column in table.Columns)
            {
                var UserName= row["UserName"];            
            }
        }
     }

or

 using (var reader = connection.ExecuteReader("storedProcedureName"))
   {
      if (reader.HasRows)
        {             
         while (reader.Read())
           {
                 //if this returns multiple row you need to add these retried values to a list. Better to create a class and hold values in list object 
                 var UserName= reader["UserName"] != DBNull.Value ? reader["UserName"].ToString() : "";
                 var LoggedState= reader["LoggedState"] != DBNull.Value ? reader["LoggedState"].ToString() : "";                      
           }
        }
   }