Datasets, Datatables and SQL commands

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I wanted to ask how i could run a stored procedure against my dataset. At present i am using a datareader to fill the values of my combobox from my database. As i have created a dataset with all my databse tables, it makes more sense to run my SQL command against the dataset. Does anyone have any examples of how i could run parameter commands against a dataset?

Also i want to filter my datatable based on what the user selects from my combobox.

If anyone has found any useful code examples it would be very much appreciated.
Thx
 
Hi Bhavna,

You're making a logical mistake here: a dataset/datatable is a memory
representation of a query. Stored Procedures run against the tables of a
database, on the server, not against the dataset/datatable. Only when and
if you make changes to the datatable and then update the database, does the
dataset affect the back end; this could be done with an sp also, with or
without a dataset.

Filtering the datatable is pretty direct: take a look at the .select method
of the datatable object.

HTH,

Bernie Yaeger

Bhavna said:
Hi,

I wanted to ask how i could run a stored procedure against my dataset. At
Click to expand...
present i am using a datareader to fill the values of my combobox from my
database. As i have created a dataset with all my databse tables, it makes
more sense to run my SQL command against the dataset. Does anyone have any
examples of how i could run parameter commands against a dataset?
 
Hello Bernie,

Can i use sql commands in the .select method of the datatable?
Also how would i use parameters in the .select method?

e.g
i want to return the rooms that are available based on a date that the user supplies on my interface using a dateTimePicker.
i.e select roomname from Rooms where RoomDate <> @RoomDate

How would i use this type of filter with the .select method??

thx
 
Hi Bhavna,
You can use sql like statements with Select method of the datatable.
You can not use SqlParameter with a data table directly because
datatable is an in memory representation of data.
Here is a way you can do it :
DataTable dttemp=new DataTable();
If datatable1 is your actual datatable with data :
if you need the rows where RoomDate is not equal to the date you are passing
DataRow[] rows = datatable1.Select(RoomDate<>pass the date from the
date picker);
Check the <> syntax to make sure it is working..
dttemp=datatable1.Clone(); // this clones the structure of the
datatable including all datatable schemas,relations and constraints.
foreach(DataRow dr in rows)
{
dttemp.ImportRow(dr);
}

Now dttemp will have the filtered data which you could use to bind to a
datagrid or whatever you want to do with it.
Hope this helps.
Regards,
Marshal Antony
http://www.dotnetmarshal.com








news:[email protected]...
 
Hi Bhavna,

Translated to Marshal the VB code is (Because I know you are using that)

dim dttemp as new DataTable
dim rows() as datarow = datatable1.Select(RoomDate & "<>" & dtPicker.text)
Check the <> syntax to make sure it is working..
dttemp=datatable1.Clone
datatable including all datatable schemas,relations and constraints.

for each dr as datarow in rows
dttemp.ImportRow(dr)
next

For the rest no additions to the text from Marshal and I did it also to show
you to converting VB to C# is very easy.

Cor
 
Back
Top