Question DataRelation and SQL Queries

[kfirba]

Hello!

I have a question related to DataRelation and SQL Queries.
Let's say that I'm using access database which already has relationships between the tables, and I'm using a query to result to fill a new table or something like that, why would I need the DataRelation? or maybe the DataRelation is being used in the queries?

and if we are already talking about SQL Queries, can someone give me a website or something which i will be able to learn there how to create an SQL Query and fill a table with the query result and even use the result?

and one last question, what's a DataReader? Can someone provide an example along with the explanation?

Thanks in advance!

 

[jmcilhinney]

The DataRelation in the DataSet will help maintain referential integrity, i.e. make sure that every child record is related to a valid parent record. It also allows related data to be filtered automatically when bound to a UI.

A data reader is basically a conduit between your application and the result set of a query executed on the database. It allows you to read that result set in a forward direction, one record at a time. If you want random access to the data then you would usually read the data into a DataTable and then access it from there.

You might try following the MSDN links in my signature for more information.

 

[kfirba]

Thanks for your reply

Still, I can't understand the need of DataRelation because, if I execute a select query between tables that already have relationship, I can bind a text box or even a bindingsource to the query and use the filtered information.

you also said that theDataReader is being using along with SQL queries. So that mean everytime I want to execute a query, I will need a datareader? Can't I store the result in a DataTable immediately?

can you please provide me a link to a website where I can learn about executing queries against the Database with OleDb namespace?

thanks in advance!

 

[jmcilhinney]

Still, I can't understand the need of DataRelation because, if I execute a select query between tables that already have relationship, I can bind a text box or even a bindingsource to the query and use the filtered information.

If you're only going to execute one query, and therefore populate one DataTable, a DataRelation is of no value and, in fact, cannot be created. You would only create a DataRelation if you have two related DataTables. You certainly don't need a DataRelation but it is useful.

As I said, it helps maintain referential integrity. For instance, using the popular example of Customers and Orders, if you have records in the Customer table with IDs 1, 2 and 3, without a DataRelation there is nothing to stop you adding a record to the Order table with a CustomerID of 4, thereby creating an orphan record. That is unlikely to happen but it's possible. What is more likely is deleting a parent record and leaving behind orphan children.

Also, a DataRelation will automatically propagate updates from the parent to the child. For instance, if you add a new parent record and your application generates a temporary ID for it, that ID will be used in the child table. If the ID generated by the database when the data is saved is different, a DataRelation can automatically update the child, thus preventing orphan records again.

Finally, if you want to see an example of how a DataRelation can automatically filter child data based on the parent selected, check this out:

Master/Detail (Parent/Child) Data-binding

you also said that theDataReader is being using along with SQL queries. So that mean everytime I want to execute a query, I will need a datareader? Can't I store the result in a DataTable immediately?

A data reader is what's used to read the result set of a query. You can use a data adapter to populate a DataTable directly and certainly should if you intend to edit and save the data, but that data adapter will use a data reader internally.

can you please provide me a link to a website where I can learn about executing queries against the Database with OleDb namespace?

I don't really have such links lying around because I don't need them myself. I'm quite sure that I could find some with a web search though. You can do the same.

That said, here's some ADO.NET code examples that I created myself. The code uses SqlClient for SQL Server but, as it says in the thread, the pattern is exactly the same for other data sources and you simply change the types as required, e.g. OleDbConnection instead of SqlConnection.

Retrieving and Saving Data in Databases

 

[kfirba]

Thanks a lot!

as for the DataRelation, as I recall, when you are using access and making relationships between tables, if you delete a parent record it automatically delete the child record.
so why when I will import the access database with the relationships, it won't do it by itself?
i tried to find som information about the DataRelation but I couldn't find an explanation that explain what the code does, they just show the code and expect you will understand it :s

about the queries, which keywords should I use in google to find information about it, because last time I tried I received many results that I don't need

thanks in advance!

 

[IanRyder]

Hi,

as I recall, when you are using access and making relationships between tables, if you delete a parent record it automatically delete the child record.

That is only true if you have specified the relationship to "Enforce Referential Integrity" and also set the relationship to "Cascade Delete Related Records". If you do not specify this second option then you cannot delete a parent record that has existing child records associated with it.

When you add a DataSource to your project the wizard does not carry over the relationship properties from the Access database. However, if you want to delete child records when the parent record is deleted then you can re-add the "Cascade" on delete property by editing the relationship in the XSD file in your project. To do that, right click the name of your DataSource and select "Edit DataSet with Designer".

As to keywords, just use your imagination and I am sure you will come up with something. We all do.

Hope that helps.

Cheers,

Ian

 

[kfirba]

IanRyder,
Thanks a lot!
i will try to find information about what I need
oh and, why would I need an update,insert and delete queries if I can do it with simple code?

 

[jmcilhinney]

why would I need an update,insert and delete queries if I can do it with simple code?

You seem to be under various misconceptions. You really need to do some reading on databases and data access. The "simple code" that you talk about is there to execute the UPDATE, INSERT and DELETE statements. If you want to interact with a database then you use SQL code. All the ADO.NET code is basically there to execute that SQL code.

 

[kfirba]

You seem to be under various misconceptions. You really need to do some reading on databases and data access. The "simple code" that you talk about is there to execute the UPDATE, INSERT and DELETE statements. If you want to interact with a database then you use SQL code. All the ADO.NET code is basically there to execute that SQL code.

I'm very fimiliar with databases I am a website programmer.
What you meant is, that the adapter functions and the dataset function actually executing SQL queries? So I am using SQL queries without knowing that I'm using them

Well so, if I'm using the adapters and dataset functions, I don't need to use the delete, insert and update queries because it automatically uses it for me. So all I need to use it the select queries to retrieve filtered data.

is that correct?

 

[jmcilhinney]

No that is not correct. A DbCommand is a way to execute a SQL command against a database. The whole point of a DbDataAdapter is to group four commands together. You call Fill and it executes its SelectCommand, which contains a SELECT statement. You call Update and it executes the InsertCommand, UpdateCommand and DeleteCommand, which contain INSERT, UPDATE and DELETE statements respectively.

We could keep going like this all day but instead of getting us to explain the basics of ADO.NET you should do some proper reading on the subject. This is all elementary stuff that you can easily find out for yourself.

 

[kfirba]

No that is not correct. A DbCommand is a way to execute a SQL command against a database. The whole point of a DbDataAdapter is to group four commands together. You call Fill and it executes its SelectCommand, which contains a SELECT statement. You call Update and it executes the InsertCommand, UpdateCommand and DeleteCommand, which contain INSERT, UPDATE and DELETE statements respectively.

We could keep going like this all day but instead of getting us to explain the basics of ADO.NET you should do some proper reading on the subject. This is all elementary stuff that you can easily find out for yourself.

Sorry for that but, what you just said is what I tried to explain guess I didn't explain it right :x
you said that I can find information about the basics of ado.net, where exactly? I have been lookin for good explanation for few days, and even read 2 chapters in books about it, but none of them explained what you just explained.. If you have a recommended website, I would happily read the information from there

 

[IanRyder]

Hi,

Have you actually tried searching for examples on the basics of ado.net? I just tried this using Google with the search criteria of "ado.net" and there are more examples than you can count??

Try that and then come back if you are still struggling.

Cheers,

Ian

 

[kfirba]

Alright, I will do so
thanks!