Datasets - Totally Stumped - Am I stupid or something?

Colin Angus Mackay

David Bliss wrote: In most of my code I just use datareaders and datacommands to interact with the database. I've been told that this is "doing it the hard way". I think what these people are saying is just rubbish. DataReaders are just doing it differently. If you access the data for quick throw away results, or are going to store it internally in a format other than in a DataSet they are an excellent choice. Lots of people blindly load data into DataSets and then copy it to where they need it and end up with extra copies of data and really crappy inefficient data access. Like Scotty said in Star Trek V "How many times do I have to tell ye! The right tool for the right job!". From my experience DataReaders have been more appropriate than DataSets. (But I do a lot of data manipulation tasks and not a lot generally gets to the User Interface) If you are using DataGrids and the link DataSets can work really well - Having said that, you can use a DataReader as the source in many cases and it works just as well (if you just need display only access). If you need random access to the records once they are client side then DataSets are quite useful. Also, the DataAdapter just uses DataReaders and Commands to get the information in and out of the database anyway. So, you're still using them, you're just one layer of abstraction further away.

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

Colin Angus Mackay

Another idea. I'm not sure if all databases support this (I think it is part of the ANSI SQL92 standard - but it may just be a Microsoft thing [sorry, that's not very definite]) You can supply semi-colon delimited SQL Commands.

SELECT * FROM TableA; SELECT * FROM TableB; SELECT * FROM TableC

That might help, but I'm not sure about the update/insert/delete part of the data adapter.

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

david mindplay com

That's what I always thought and that is why I've never tried messing with them until now. Datasets always seemed over redundant to me. Why have a copy of my tables, they are already in the database!? I don't understand how they can make anything easier if 400 pages of your avarage ADO.NET book is dedicated to datasets (and their associated classes) and only about 10 pages or less on datareaders. Datareaders and Datacommands are very straight forward and easy to understand and use. Using datasets is just overcomplicating things. Also, I've never bound anything to anything and I don't see the need. I thought that maybe insted of using datamembers inside my class, I could just use a dataset and have properties that access the dataset directly. I'm giving up at this point. I'm just going to do it a different way. I've wasted almost three days on this now. I wanted to use datasets because they can keep track of changes and what has been deleted, then do the appropriate things with the data source when updating. This is all well and good, but it seems to me like there is just as much work involved with using the datasets as it is to just keep track of that stuff myself. Maybe if I was able to use stored procedures across the board It would be a more viable option. Also, I wanted to use datasets because, I guess you can store strings in the database and not have to worry about special characters like ' and ;. Is this true? Right now I'm having a hell of a time with storing strings containing special characters. Thanks.

david mindplay com

Yea, I already thought of that. Problem is, I need to use data returned from the second select statement to create the third. So that doesn't work. Or, wait a minute "SELECT *" am i just supposed to get a copy of the entire damn database? That just seems wacky. Is that how you are supposed to use datasets? Copy the entire database in its entirety!? Holy crap, what if there are thousands of records and you only need to deal with a couple? See, the whole dataset concept just dosen't make any sense to me. :confused:

Colin Angus Mackay

David Bliss wrote: Also, I wanted to use datasets because, I guess you can store strings in the database and not have to worry about special characters like ' and ;. Is this true? Right now I'm having a hell of a time with storing strings containing special characters. You should use parameterised queries. The syntax is slightly different depending on the database. SqlServer uses named parameters, while Access uses placeholders and you must supply the parameters in the same order that they appear in the query. Here is an example using Sql Server

SqlCommand cmd = new SqlCommand("SELECT * FROM TableA WHERE ColumnA = @ParameterA AND ColumnB = @ParameterB");
cmd.Parameters.Add("@ParameterA", "David's Data");
cmd.Parameters.Add("@ParameterB", "String with ; semi-colon in it");

And in Access I think it is [disclaimer - I don't use Access much so this may be wrong]

OleDbCommand cmd = new OleDbCommand("SELECT * FROM TableA WHERE ColumnA = ? AND ColumnB = ?");
cmd.Parameters.Add("David's Data");
cmd.Parameters.Add("String with ; semi-colon in it");

I hope this helps.

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

Colin Angus Mackay

David Bliss wrote: Or, wait a minute "SELECT *" am i just supposed to get a copy of the entire damn database? No, no... I'm just being lazy when I'm typing my reply. And remember your WHERE clause to filter the data. It is always best to specify a column list in a query. If you do that and you add columns to the table it won't affect pre-existing code which might expect a certain number or order of columns. David Bliss wrote: Is that how you are supposed to use datasets? Copy the entire database in its entirety!? Holy crap, what if there are thousands of records and you only need to deal with a couple? :laugh: I'm just thinking of one table in particular that I'm working on. Data is being generated at a rate of several million rows per year. In SqlServer at least SELECT TOP xxx where xxx is the number of rows you want back is very useful.

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

david mindplay com

Hmmm, I've heard of this before. OK, pain in the butt changing syntax between providers, but doable. Does this work with ODBC/MySQL? If so, we might have something here. I'll have to look into this more deeply. Thanks.

david mindplay com

I thought not. Yea, Yea I'm fairly familiar with SQL syntax and dealing with databases in general. Just not stored procedures, paramaterized querys or anything having to do with datasets or databinding.

Colin Angus Mackay

David Bliss wrote: pain in the butt changing syntax between providers It just depends on what the back end database supports. David Bliss wrote: Does this work with ODBC/MySQL? Should do. If you want another reason to implement parameterised queries then you should know that it helps prevent SQL Injection Attacks[^]

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

david mindplay com

> Should do. I'll have to check into it. Lord, I hope so. Keeping my fingers crossed. > If you want another reason to implement parameterised queries then you should know that it helps prevent SQL Injection Attacks Yea, I know about those. Although, In my case I doubt I would have to worry about that in an elementary school setting, but you never know I suppose. Also, This is not a web based product.