Filtering two datatables (urgent plz)
-
I have two datatables which are populating from two different tables of two different databases. I want to get all the records of first datatable which are not in 2nd datatable. I ‘m using the following piece of code. odr = oDs.Tables(0).Select("Sno = (SELECT SNO FROM " + Convert.ToString(oDataSetChanges.Tables(0)) + ")") or this one odr = oDs.Tables(0).Select("Sno NOT IN (SELECT " + Convert.ToString(oDataSetChanges.Tables(0).Columns("SNO")) + " FROM " + Convert.ToString(oDataSetChanges.Tables(0)) + ")") or I tried the following code as well oDv1.RowFilter = "Sno NOT IN (SELECT SNO FROM " + Convert.ToString(oDv2.Table) + ")" where SNO are the primary key columns of both tables but by using all these code I got the same syntax error that is Message "Syntax error: Missing operand after 'SNO' operator." Kindly help me in this reagard ASAP Thanx in advance
-
I have two datatables which are populating from two different tables of two different databases. I want to get all the records of first datatable which are not in 2nd datatable. I ‘m using the following piece of code. odr = oDs.Tables(0).Select("Sno = (SELECT SNO FROM " + Convert.ToString(oDataSetChanges.Tables(0)) + ")") or this one odr = oDs.Tables(0).Select("Sno NOT IN (SELECT " + Convert.ToString(oDataSetChanges.Tables(0).Columns("SNO")) + " FROM " + Convert.ToString(oDataSetChanges.Tables(0)) + ")") or I tried the following code as well oDv1.RowFilter = "Sno NOT IN (SELECT SNO FROM " + Convert.ToString(oDv2.Table) + ")" where SNO are the primary key columns of both tables but by using all these code I got the same syntax error that is Message "Syntax error: Missing operand after 'SNO' operator." Kindly help me in this reagard ASAP Thanx in advance
I don't think this is possible. The
Select()method doesn't have the full power of SQL - it can only define fairly simple filter expressions. You can set up relations between tables in aDataSetwith theRelationsproperty to set up a parent/child table relationship; however, I don't think you'll be able to do this in this case - I don't think it'll help. I'd recommend - assuming that your data is coming from at least one SQL Server - setting up a link between the two servers (so that server A can see the table on server B) and performing the difference operation in a stored procedure. You can then use the full power of SQL. -- Mike Dimmick -
I don't think this is possible. The
Select()method doesn't have the full power of SQL - it can only define fairly simple filter expressions. You can set up relations between tables in aDataSetwith theRelationsproperty to set up a parent/child table relationship; however, I don't think you'll be able to do this in this case - I don't think it'll help. I'd recommend - assuming that your data is coming from at least one SQL Server - setting up a link between the two servers (so that server A can see the table on server B) and performing the difference operation in a stored procedure. You can then use the full power of SQL. -- Mike Dimmickhi mike thanx for ur reply. i used ur opinion i.e linked both the sql servers via enterprise manager now tell me plz how much it will take effect on the execution performance with respect to time than that of disconnected datasets. regards Aamir
-
hi mike thanx for ur reply. i used ur opinion i.e linked both the sql servers via enterprise manager now tell me plz how much it will take effect on the execution performance with respect to time than that of disconnected datasets. regards Aamir
SQL Server uses OLE DB internally to access and manipulate tables of data. It's optimised for manipulating data sets. As long as the connection between the two servers is at least as fast as the connection between the client and the two servers, you shouldn't see any difference. Indeed it might even be quicker than two datasets, because SQL Server will tend to fetch rows from the linked database and its local database asynchronously, whereas you typically fill your local datasets synchronously, waiting for the first to complete before asking for the second. (I believe you could do this yourself by using
BeginInvoketo perform both operations on the thread pool, then wait for bothIAsyncResult.AsyncWaitHandles to be signalled). Your experience may vary and, as always with performance, you should measure it rather than guessing. Query Analyzer can show you the query execution plan and the time taken to execute the query. If the link between the servers is slow (perhaps they're located far apart, geographically?) you could consider replication instead. -
SQL Server uses OLE DB internally to access and manipulate tables of data. It's optimised for manipulating data sets. As long as the connection between the two servers is at least as fast as the connection between the client and the two servers, you shouldn't see any difference. Indeed it might even be quicker than two datasets, because SQL Server will tend to fetch rows from the linked database and its local database asynchronously, whereas you typically fill your local datasets synchronously, waiting for the first to complete before asking for the second. (I believe you could do this yourself by using
BeginInvoketo perform both operations on the thread pool, then wait for bothIAsyncResult.AsyncWaitHandles to be signalled). Your experience may vary and, as always with performance, you should measure it rather than guessing. Query Analyzer can show you the query execution plan and the time taken to execute the query. If the link between the servers is slow (perhaps they're located far apart, geographically?) you could consider replication instead.thnx for replying yes in my case the servers are far apart like for example one in US n other in SingaPore so can u tell me how i use replication as i have no idea of replication. i'll b very thankful to u regards aamir
-
thnx for replying yes in my case the servers are far apart like for example one in US n other in SingaPore so can u tell me how i use replication as i have no idea of replication. i'll b very thankful to u regards aamir
I could regurgitate a whole load of documentation here, but I won't: I'll just direct you to SQL Server Books Online[^] - note, this link is to a download, not online documentation. In the Contents tab, go to Replication. Snippet: When to Use Replication With organizations supporting diverse hardware and software applications in distributed environments, it becomes necessary to store data redundantly. Moreover, different applications have different needs for autonomy and data consistency. Replication is a solution for a distributed data environment when you need to:
- Copy and distribute data to one or more sites.
- Distribute copies of data on a scheduled basis.
- Distribute data changes to other servers.
- Allow multiple users and sites to make changes then merge the data modifications together, potentially identifying and resolving conflicts.
- Build data applications that need to be used in online and offline environments.
- Build Web applications where users can browse large volumes of data.
- Optionally make changes at subscribing sites that are transparently under transactional control of the Publisher.
If you always want the latest data available to your client, and cannot stand any stale data, stick with linked servers. Otherwise replication should produce less traffic between servers.