Querying DataSet C#

theartha
theartha used Ask the Experts™
on
HI There,



string query = @" SELECT name,date, ROW_NUMBER() OVER (ORDER BY StartDateTime asc) AS RowNumber  from Emp  ";

SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
 DataSet empData= new DataSet();
 adapter.Fill(empData, "EmployeeData");

How can I query this dataset?
select * from EmployeeData where date > @Today;

I am using asp 2.0

Please advice.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use a SqlCommand which you assign to the SqlDataAdapter.

Those support parameters.

Microsoft has a nice description about this:
http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.71%29.aspx
var qry= 
empData.Tables("EmployeeData ").AsEnumerable().Where(rw=>(DateTime)rw["date"]>DateTime.Today)

foreach(var dr in qry)
{
//Rows where condition is met
}

Open in new window

Author

Commented:
@HugoHiasl:

There is no proper explanation about connection string.
conn.open();

In my case I got an error, Invalid object name 'Emp'. since its using the previous connection which I aready closed.

SqlConnection dataConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;");
            SqlDataAdapter custDA = new SqlDataAdapter();

            SqlCommand selectCMD = new SqlCommand(selectSQL, dataConn);
            custDA.SelectCommand = selectCMD;

            // Add parameters and set values.
            selectCMD.Parameters.Add("@Date", SqlDbType.DateTime, 15).Value = DateTime.Now.Date.ToString("d");
            DataSet custDS = new DataSet();
            custDA.Fill(custDS, "Customers");
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Did you specify the catalog where to search for the table?
You can see in the samples that they use a Initial Catalog=northwind;

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Open in new window

Author

Commented:
@HugoHiasl

What will be initial catalog in my case, as I don't have a database, all I have is the dataset name


I used

SqlConnection dataConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=empData");

I got an error:Cannot open database "empData" requested by the login. The login failed.
Login failed for user ''.

Please advise.
If you look into your database in the MSSQL Management Studio.

You see on the left side in the tree "databases" ... below this you must have added a database where your tables reside in... this is the inital catalog.

Author

Commented:
@Hugo:

1. Connected to my datase
2. Pulled the data from a table.
3. Created a DataSet
4. Using SQL Adapter, I fill the newly created dataSet.
5. Connection Closed.

------------

Now,

I have my DataSet with all the data.

1.  I need to search for rows on this dataset where date > '4/17/2012'

--------------

I can't use my databse as the initial catalog, as I am not connecting to my database.

Any advice.

Thanks.

Author

Commented:
in fact I wrote the below code, unfortunately, I am getting only one row.

            string queryDataSet1 = " (RowNumber >= '" + minRows + "'";
            string queryDataSet2 = queryDataSet1 + " and RowNumber <= '" + maxRows + "')";
            string queryDataSet3 = queryDataSet2 + " and StartDateTime >= #" + DateTime.Now.Date.ToString("d") + "#";
            DataTable eve = events.Tables[0];

            DataRow[] foundRows;

            DataTable newEvents = new DataTable();
            newEvents.Columns.Add("StartDateTime");
            newEvents.Columns.Add("EndDateTime");
            newEvents.Columns.Add("RowNumber");

            foundRows = eve.Select(queryDataSet3);

I should be getting 7 rows, but  I am gettign the last row ...7, I guess rowNumber is not working.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial