Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to read data table from SQL Server stored procedure

I have this SQL Server stored procedure:

    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:

enter image description here

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.

like image 409
user2226785 Avatar asked Apr 07 '14 14:04

user2226785


1 Answers

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() : "";                      
           }
        }
   }
like image 102
huMpty duMpty Avatar answered Oct 18 '22 20:10

huMpty duMpty