|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Fill a strong typed dataset from 2 tablesi am searching at this forum, but i don't found a solution. I try to fill two tables of a strong typed dataset by ONE call of fill-Method of the dataadapter. Is there any possibily to do this? Table Customer contents name, titel ... primary key is IDCustomer Table Adresses contents street, city ... primary key is IDAdress I have a join table with foreign keys into Table Adress2Customer content CustomerID and AdressID I generate a dataadapter with the following select statement select * from Adress2Customer inner join Adresses on Adresses.IDAdress = Adress2Customer.AdressID where Adress2Customer.CustomerID = 1234 to get all adresses from Customer 1234 At the moment I use 2 statements with different select parts select Adresses.* and select Adress2Customer.* and two dataadapters to fill my dataset. OleDbDataAdapter1.Fill(myDataSet, "Adresses") OleDbDataAdapter1.Fill(myDataSet, "Adress2Customer") I expect this style of reading dta force two querys running at the database. Because of the size of the Tables it is impossible to read all data into memory to etablish the relationship there. Is there any possibily to avoid the second database reading? Is there a way to fill two tables of a dataset from one sql-Statement? Thanks in advance for any help. Niels Nieurig:
If the database supports batch queries, then yes. You simply fire both select statements as one, then use the tableMappings (if the table name in the select statements are different from the DataSet table names) and possibly column mappings (again, only if the names in teh query are different from the DataSet's table names). When you call Fill on the adapter, it will fill both. However this is only if your db supports batch updates. Oracle and Sql Server will, Access won't (at least I don't believe it does). Just for the record, if you use a DataReader, you can use NextResult() to get to the next resultset if you ever batch queries and use a reader. The adapter will handle this for you automatically provided that the table names are the same and/or you have TableMappings in place. HTH, Bill Show quote "nieurig" <nieu***@discussions.microsoft.com> wrote in message news:6A7F2878-B827-4497-8239-158211EB4370@microsoft.com... > Hello folks, > i am searching at this forum, but i don't found a solution. > I try to fill two tables of a strong typed dataset by > ONE call of fill-Method of the dataadapter. Is there > any possibily to do this? > > Table Customer contents name, titel ... primary key is IDCustomer > Table Adresses contents street, city ... primary key is IDAdress > > I have a join table with foreign keys into > Table Adress2Customer content CustomerID and AdressID > > I generate a dataadapter with the following select statement > > select * from Adress2Customer inner join Adresses > on Adresses.IDAdress = Adress2Customer.AdressID > where Adress2Customer.CustomerID = 1234 > > to get all adresses from Customer 1234 > > At the moment I use 2 statements with different select parts > select Adresses.* and select Adress2Customer.* > > and two dataadapters to fill my dataset. > > OleDbDataAdapter1.Fill(myDataSet, "Adresses") > OleDbDataAdapter1.Fill(myDataSet, "Adress2Customer") > > I expect this style of reading dta force two querys running at > the database. > Because of the size of the Tables it is impossible to read all data into > memory to etablish the relationship there. > > Is there any possibily to avoid the second database reading? > Is there a way to fill two tables of a dataset from one sql-Statement? > > Thanks in advance for any help. > Niels Hi W.G. Ryan,
thanks for your assistance. i use firebrid and it will support batch querys ... but i need to run the same query two times only at a batch. That look suboptimal. Any ideas? Niels Show quote "W.G. Ryan - MVP" wrote: > Nieurig: > > If the database supports batch queries, then yes. You simply fire both > select statements as one, then use the tableMappings (if the table name in > the select statements are different from the DataSet table names) and > possibly column mappings (again, only if the names in teh query are > different from the DataSet's table names). > > When you call Fill on the adapter, it will fill both. However this is only > if your db supports batch updates. Oracle and Sql Server will, Access won't > (at least I don't believe it does). > > Just for the record, if you use a DataReader, you can use NextResult() to > get to the next resultset if you ever batch queries and use a reader. The > adapter will handle this for you automatically provided that the table names > are the same and/or you have TableMappings in place. > > HTH, > > Bill > "nieurig" <nieu***@discussions.microsoft.com> wrote in message > news:6A7F2878-B827-4497-8239-158211EB4370@microsoft.com... > > Hello folks, > > i am searching at this forum, but i don't found a solution. > > I try to fill two tables of a strong typed dataset by > > ONE call of fill-Method of the dataadapter. Is there > > any possibily to do this? > > > > Table Customer contents name, titel ... primary key is IDCustomer > > Table Adresses contents street, city ... primary key is IDAdress > > > > I have a join table with foreign keys into > > Table Adress2Customer content CustomerID and AdressID > > > > I generate a dataadapter with the following select statement > > > > select * from Adress2Customer inner join Adresses > > on Adresses.IDAdress = Adress2Customer.AdressID > > where Adress2Customer.CustomerID = 1234 > > > > to get all adresses from Customer 1234 > > > > At the moment I use 2 statements with different select parts > > select Adresses.* and select Adress2Customer.* > > > > and two dataadapters to fill my dataset. > > > > OleDbDataAdapter1.Fill(myDataSet, "Adresses") > > OleDbDataAdapter1.Fill(myDataSet, "Adress2Customer") > > > > I expect this style of reading dta force two querys running at > > the database. > > Because of the size of the Tables it is impossible to read all data into > > memory to etablish the relationship there. > > > > Is there any possibily to avoid the second database reading? > > Is there a way to fill two tables of a dataset from one sql-Statement? > > > > Thanks in advance for any help. > > Niels > > > If you have batch query ability, then a Sql Statement of this ...
Select * from MyFirstTable; Select * From MySecondtable. call fill on the adapter once with this statement (or whatever separator Firebird uses). If the table names in the dataSet are MyFirstTable and MySecondTable then all is good (although if columns don't match, you need to specify column mappings). Otherwise just specify a TableMapping too - that should do it for you. Show quote "nieurig" <nieu***@discussions.microsoft.com> wrote in message news:8787973D-91C8-40A2-A5ED-D7C8A37FC40D@microsoft.com... > Hi W.G. Ryan, > thanks for your assistance. > i use firebrid and it will support batch querys ... > but i need to run the same query two times only at a batch. > > That look suboptimal. > Any ideas? > > Niels > > > > > "W.G. Ryan - MVP" wrote: > >> Nieurig: >> >> If the database supports batch queries, then yes. You simply fire both >> select statements as one, then use the tableMappings (if the table name >> in >> the select statements are different from the DataSet table names) and >> possibly column mappings (again, only if the names in teh query are >> different from the DataSet's table names). >> >> When you call Fill on the adapter, it will fill both. However this is >> only >> if your db supports batch updates. Oracle and Sql Server will, Access >> won't >> (at least I don't believe it does). >> >> Just for the record, if you use a DataReader, you can use NextResult() to >> get to the next resultset if you ever batch queries and use a reader. >> The >> adapter will handle this for you automatically provided that the table >> names >> are the same and/or you have TableMappings in place. >> >> HTH, >> >> Bill >> "nieurig" <nieu***@discussions.microsoft.com> wrote in message >> news:6A7F2878-B827-4497-8239-158211EB4370@microsoft.com... >> > Hello folks, >> > i am searching at this forum, but i don't found a solution. >> > I try to fill two tables of a strong typed dataset by >> > ONE call of fill-Method of the dataadapter. Is there >> > any possibily to do this? >> > >> > Table Customer contents name, titel ... primary key is IDCustomer >> > Table Adresses contents street, city ... primary key is IDAdress >> > >> > I have a join table with foreign keys into >> > Table Adress2Customer content CustomerID and AdressID >> > >> > I generate a dataadapter with the following select statement >> > >> > select * from Adress2Customer inner join Adresses >> > on Adresses.IDAdress = Adress2Customer.AdressID >> > where Adress2Customer.CustomerID = 1234 >> > >> > to get all adresses from Customer 1234 >> > >> > At the moment I use 2 statements with different select parts >> > select Adresses.* and select Adress2Customer.* >> > >> > and two dataadapters to fill my dataset. >> > >> > OleDbDataAdapter1.Fill(myDataSet, "Adresses") >> > OleDbDataAdapter1.Fill(myDataSet, "Adress2Customer") >> > >> > I expect this style of reading dta force two querys running at >> > the database. >> > Because of the size of the Tables it is impossible to read all data >> > into >> > memory to etablish the relationship there. >> > >> > Is there any possibily to avoid the second database reading? >> > Is there a way to fill two tables of a dataset from one sql-Statement? >> > >> > Thanks in advance for any help. >> > Niels >> >> >> Nieurig,
What is your goal with this. Two seperate fills to fetch the data with two select clauses including the same where clause will certainly needs less effort on both sides than a Join statement that has to be processed. The tables are read row by row using the datareader. Just my thought, Cor Show quote "nieurig" <nieu***@discussions.microsoft.com> schreef in bericht news:6A7F2878-B827-4497-8239-158211EB4370@microsoft.com... > Hello folks, > i am searching at this forum, but i don't found a solution. > I try to fill two tables of a strong typed dataset by > ONE call of fill-Method of the dataadapter. Is there > any possibily to do this? > > Table Customer contents name, titel ... primary key is IDCustomer > Table Adresses contents street, city ... primary key is IDAdress > > I have a join table with foreign keys into > Table Adress2Customer content CustomerID and AdressID > > I generate a dataadapter with the following select statement > > select * from Adress2Customer inner join Adresses > on Adresses.IDAdress = Adress2Customer.AdressID > where Adress2Customer.CustomerID = 1234 > > to get all adresses from Customer 1234 > > At the moment I use 2 statements with different select parts > select Adresses.* and select Adress2Customer.* > > and two dataadapters to fill my dataset. > > OleDbDataAdapter1.Fill(myDataSet, "Adresses") > OleDbDataAdapter1.Fill(myDataSet, "Adress2Customer") > > I expect this style of reading dta force two querys running at > the database. > Because of the size of the Tables it is impossible to read all data into > memory to etablish the relationship there. > > Is there any possibily to avoid the second database reading? > Is there a way to fill two tables of a dataset from one sql-Statement? > > Thanks in advance for any help. > Niels Hi Cor and the others,
thanks for your comments. > What is your goal with this. Two seperate fills to fetch the data with two No i don't think so.> select clauses including the same where clause will certainly needs less > effort on both sides than a Join statement that has to be processed. The > tables are read row by row using the datareader. I need to join the tables at the database because I only know the IDCustomer. To get the adresses from one Customer i need to join the tabels two times, first for reading the adresses, second for reading the linktable with the foreign keys. Sending two select was only a workaround . Instead of calling the query two times i like to call it only once --- but need to fill the two tables of my dataset. OK. Perhaps i need to write my own code for doing that from a datareader read the query result in a "normal" dataset an copy the data from it. Is there any better idea?. Thanks Niels Show quote > > Just my thought, > > Cor > > "nieurig" <nieu***@discussions.microsoft.com> schreef in bericht > news:6A7F2878-B827-4497-8239-158211EB4370@microsoft.com... > > Hello folks, > > i am searching at this forum, but i don't found a solution. > > I try to fill two tables of a strong typed dataset by > > ONE call of fill-Method of the dataadapter. Is there > > any possibily to do this? > > > > Table Customer contents name, titel ... primary key is IDCustomer > > Table Adresses contents street, city ... primary key is IDAdress > > > > I have a join table with foreign keys into > > Table Adress2Customer content CustomerID and AdressID > > > > I generate a dataadapter with the following select statement > > > > select * from Adress2Customer inner join Adresses > > on Adresses.IDAdress = Adress2Customer.AdressID > > where Adress2Customer.CustomerID = 1234 > > > > to get all adresses from Customer 1234 > > > > At the moment I use 2 statements with different select parts > > select Adresses.* and select Adress2Customer.* > > > > and two dataadapters to fill my dataset. > > > > OleDbDataAdapter1.Fill(myDataSet, "Adresses") > > OleDbDataAdapter1.Fill(myDataSet, "Adress2Customer") > > > > I expect this style of reading dta force two querys running at > > the database. > > Because of the size of the Tables it is impossible to read all data into > > memory to etablish the relationship there. > > > > Is there any possibily to avoid the second database reading? > > Is there a way to fill two tables of a dataset from one sql-Statement? > > > > Thanks in advance for any help. > > Niels > > > Niels,
You are right, I misunderstood you (and misread). I see still see nothing wrong in sending two seperated selects instead of 1, however. I assume that you make it than only more complex on your database server. You needs at your client side a dataset containing 2 tables. One with client information and one with its address information. What is wrong with it to tell the server to give that in two steps. The datareader is as well processing row by row from the fetched resultset, I assume that the transport from Server to Client will consume the most time. Just my thought, (I am curious, is nieurig a shortcut for nieuwsgierig?) Cor Show quote "nieurig" <nieu***@discussions.microsoft.com> schreef in bericht news:637DBC64-25E2-4681-8ABB-21B84F6A39CD@microsoft.com... > Hi Cor and the others, > thanks for your comments. > >> What is your goal with this. Two seperate fills to fetch the data with >> two >> select clauses including the same where clause will certainly needs less >> effort on both sides than a Join statement that has to be processed. The >> tables are read row by row using the datareader. > > No i don't think so. > I need to join the tables at the database because I only know the > IDCustomer. > To get the adresses from one Customer i need to join the tabels two times, > first for reading the adresses, second for reading the linktable with the > foreign keys. > > Sending two select was only a workaround . Instead of calling the query > two > times i like to call it only once --- but need to fill the two tables of > my > dataset. > > OK. Perhaps i need to write my own code for doing that from a datareader > read the query result in a "normal" dataset an copy the data from it. > Is there any better idea?. > > Thanks > Niels > >> >> Just my thought, >> >> Cor >> >> "nieurig" <nieu***@discussions.microsoft.com> schreef in bericht >> news:6A7F2878-B827-4497-8239-158211EB4370@microsoft.com... >> > Hello folks, >> > i am searching at this forum, but i don't found a solution. >> > I try to fill two tables of a strong typed dataset by >> > ONE call of fill-Method of the dataadapter. Is there >> > any possibily to do this? >> > >> > Table Customer contents name, titel ... primary key is IDCustomer >> > Table Adresses contents street, city ... primary key is IDAdress >> > >> > I have a join table with foreign keys into >> > Table Adress2Customer content CustomerID and AdressID >> > >> > I generate a dataadapter with the following select statement >> > >> > select * from Adress2Customer inner join Adresses >> > on Adresses.IDAdress = Adress2Customer.AdressID >> > where Adress2Customer.CustomerID = 1234 >> > >> > to get all adresses from Customer 1234 >> > >> > At the moment I use 2 statements with different select parts >> > select Adresses.* and select Adress2Customer.* >> > >> > and two dataadapters to fill my dataset. >> > >> > OleDbDataAdapter1.Fill(myDataSet, "Adresses") >> > OleDbDataAdapter1.Fill(myDataSet, "Adress2Customer") >> > >> > I expect this style of reading dta force two querys running at >> > the database. >> > Because of the size of the Tables it is impossible to read all data >> > into >> > memory to etablish the relationship there. >> > >> > Is there any possibily to avoid the second database reading? >> > Is there a way to fill two tables of a dataset from one sql-Statement? >> > >> > Thanks in advance for any help. >> > Niels >> >> >> Hi Cor,
> You needs at your client side a dataset containing 2 tables. One with client Well I will use this approach and build my own datareader-class to support > information and one with its address information. What is wrong with it to > tell the server to give that in two steps. The datareader is as well > processing row by row from the fetched resultset, I assume that the > transport from Server to Client will consume the most time. this. > (I am curious, is nieurig a shortcut for nieuwsgierig?) No, it is build from parts of firstname an my familyname --- but is sounds like "neugierig". Good night. Niels  |
|||||||||||||||||||||||
Other interesting topics