CODEWITHSUNDEEP
c#sqldataset
Iswar
Iswar

Reputation: 2301

How to retrieve dataset from SQL query in C#?

I am trying to retrieve Dataset from a SQL query. I am using Edmx for data access. If I use one simple stored procedure for retrieving dataTable then the function works fine and when I am trying to retrieve Dataset then I am unable to convert the SQL result to a dataset.

Here is my Sql 

--Query For Retrieving LastUpdatedDate
select top 3 
    YP.utblYPBusinessInstanceInfoKeys.BusinessID,
    YP.utblYPBusinessInstanceInfoKeys.BusinessName,
    YP.utblYPBusinessInstanceInfoKeys.LastUpdatedByDate as GeneraliseDate
from  
    YP.utblYPBusinessInstanceInfoKeys
order by  
    GeneraliseDate desc

--Query For Retrieving Listing by CreatedDate
select top 3 
    YP.utblYPBusinessInstanceInfoKeys.BusinessID,
    YP.utblYPBusinessInstanceInfoKeys.BusinessName,
    YP.utblYPBusinessInstanceInfoKeys.CreatedByDate as GeneraliseDate
from  
    YP.utblYPBusinessInstanceInfoKeys
order by  
    GeneraliseDate desc

How can I retrieve dataset in SQl result in C#?

Upvotes: 1

Views: 8769

Answers (2)

alex.b
alex.b

Reputation: 4577

Given sql query, the simplest way to populate result into dataset is the using of SqlDataAdapter class, which does all the job(you only need to provide query and opened connection, and dataset to fill).

In you case it would be: 

string queryString1 = "select top 3 YP.utblYPBusinessInstanceInfoKeys.BusinessID,
                                    YP.utblYPBusinessInstanceInfoKeys.BusinessName,
                                    YP.utblYPBusinessInstanceInfoKeys.LastUpdatedByDate as GeneraliseDate
                       from  YP.utblYPBusinessInstanceInfoKeys
                       order by  GeneraliseDate desc"

string queryString2 = "select top 3 YP.utblYPBusinessInstanceInfoKeys.BusinessID,
                                    YP.utblYPBusinessInstanceInfoKeys.BusinessName,
                                    YP.utblYPBusinessInstanceInfoKeys.CreatedByDate as GeneraliseDate
                        from  YP.utblYPBusinessInstanceInfoKeys
                        order by  GeneraliseDate desc"

string queryString = queryString1 + Environment.NewLine + queryString2;


DataSet dataSet = new DataSet();

var connection = new SqlConnection("TODO:put connection string here");
connection.Open();
using(connection)
{
    SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection); 
    adapter.Fill(dataSet);
}

// here you go
foreach(DataTable table in dataSet.Tables)
{
    Console.WriteLine(table.Name);
}

Upvotes: 2

Alaa Mohammed
Alaa Mohammed

Reputation: 392

Entity Framework returns entities and not datatable or dataset. you can use ADO.net (SqlCommand , SqlAdapter , .... ) or do it manually by using .AsEnumerable() and fill it in a new datatable or dataset !

Upvotes: 0

Related Questions