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:
I need to read the data from the table.
This data is not in output parameters. That is why I couldn't read them.
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.
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() : "";
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With