| Groups | Blog | Home |
dotnet ado.net : Variable WHERE part of SELECT command
I need to get data from a table from a database. The data will be queried according ID column. In a fixed time interval my application will receive the list of IDs. Than all records with this IDs should be retrieved from database. Tehre is not any select SQL command which would allow to put in query a list of parameters, like SELECT ALL FROM XX WHERE ID is from MYList What would be the most efficient way to get those required data from a db? Thanks,
Thanks for your help. Your solutions sounds promissing.
Regards, Lubomir [quoted text, click to view] "OD" wrote:
> > In a fixed time interval my application will receive the list of IDs. Than > > all records with this IDs should be retrieved from database. > > First thing, you'll have to make this task in a thread because you > don't know if the task will be finished when the next group of id's > will come. > If you don't want to lose data that's the best way. > (of course, "fixed time" doesn't explicitly mean "short time", if your > fixed time is once every week, forget this idea :-) ). > > > Tehre is not any select SQL command which would allow to put in query a list > > of parameters, like SELECT ALL FROM XX WHERE ID is from MYList > > What would be the most efficient way to get those required data from a db? > > certainly a few ways. One I can tell cause I used it a few times, is > very simple, thread safe and easy to code : just use the database > possibilities... > I explain : in your database you create two tables : "IDGROUPS", with > only one column made of a server autoinc field, and "IDS" a table made > of two fields: IDGROUPS key, and ID. > tasks : > 1) when you receive a group of ID you create an entry in IDGROUPS and > get the autoinc that is generated by the database. > 2) you insert ID's in "IDS" table using preceding key and each ID you > received. > 3) You create a thread with the "IDGROUPS" key as a parameter. > 4) That's all for here. > > In each thread : > 1) you start with the key passed in parameter > 2) You can easily use a SELECT, cause the WHERE is "WHERE xx.ID IN > (SELECT ID FROM "IDS" WHERE "GROUPKEY" = TheKeyInParameter)..." > 3) you make the job. > 4) You send a DELETE to "IDS" table with the key, then you DELETE also > the key in "IDGROUPS". > 5) The thread is finished. > > > It is simple and safe. Of course there are certainly other ways. That's > one. > > -- > > > OD___ > www.e-naxos.com > >
[quoted text, click to view]
> In a fixed time interval my application will receive the list of IDs. Than > all records with this IDs should be retrieved from database. First thing, you'll have to make this task in a thread because you don't know if the task will be finished when the next group of id's will come. If you don't want to lose data that's the best way. (of course, "fixed time" doesn't explicitly mean "short time", if your fixed time is once every week, forget this idea :-) ). [quoted text, click to view] > Tehre is not any select SQL command which would allow to put in query a list > of parameters, like SELECT ALL FROM XX WHERE ID is from MYList > What would be the most efficient way to get those required data from a db? certainly a few ways. One I can tell cause I used it a few times, is very simple, thread safe and easy to code : just use the database possibilities... I explain : in your database you create two tables : "IDGROUPS", with only one column made of a server autoinc field, and "IDS" a table made of two fields: IDGROUPS key, and ID. tasks : 1) when you receive a group of ID you create an entry in IDGROUPS and get the autoinc that is generated by the database. 2) you insert ID's in "IDS" table using preceding key and each ID you received. 3) You create a thread with the "IDGROUPS" key as a parameter. 4) That's all for here. In each thread : 1) you start with the key passed in parameter 2) You can easily use a SELECT, cause the WHERE is "WHERE xx.ID IN (SELECT ID FROM "IDS" WHERE "GROUPKEY" = TheKeyInParameter)..." 3) you make the job. 4) You send a DELETE to "IDS" table with the key, then you DELETE also the key in "IDGROUPS". 5) The thread is finished. It is simple and safe. Of course there are certainly other ways. That's one. -- OD___ www.e-naxos.com
I prefer using XML personally, as it can be queried without altering the
input or placing anything in a temp table. It is a bit less efficient than a standard query, but your other options are: 1. Populate a temp table 2. Use dynamic SQL Both of these take a much larger perf hit, esp. on larger lists, than XML. -- Gregory A. Beamer MVP; MCP: +I, SE, SD, DBA http://gregorybeamer.spaces.live.com ********************************************* Think outside the box! ********************************************* [quoted text, click to view] "Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
news:803CBD5B-6A19-4B0E-AD29-752F3B9B42B9@microsoft.com... > Hi, > > I need to get data from a table from a database. The data will be queried > according ID column. > > In a fixed time interval my application will receive the list of IDs. Than > all records with this IDs should be retrieved from database. > > Tehre is not any select SQL command which would allow to put in query a > list > of parameters, like SELECT ALL FROM XX WHERE ID is from MYList > > What would be the most efficient way to get those required data from a db? > > Thanks, > > Lubomir
What is the benefit of using 2 tables IDGROUPS and IDS? Why not use IDS only
with autoinc column for a key? Thanks, Lubomir [quoted text, click to view] "OD" wrote:
> > In a fixed time interval my application will receive the list of IDs. Than > > all records with this IDs should be retrieved from database. > > First thing, you'll have to make this task in a thread because you > don't know if the task will be finished when the next group of id's > will come. > If you don't want to lose data that's the best way. > (of course, "fixed time" doesn't explicitly mean "short time", if your > fixed time is once every week, forget this idea :-) ). > > > Tehre is not any select SQL command which would allow to put in query a list > > of parameters, like SELECT ALL FROM XX WHERE ID is from MYList > > What would be the most efficient way to get those required data from a db? > > certainly a few ways. One I can tell cause I used it a few times, is > very simple, thread safe and easy to code : just use the database > possibilities... > I explain : in your database you create two tables : "IDGROUPS", with > only one column made of a server autoinc field, and "IDS" a table made > of two fields: IDGROUPS key, and ID. > tasks : > 1) when you receive a group of ID you create an entry in IDGROUPS and > get the autoinc that is generated by the database. > 2) you insert ID's in "IDS" table using preceding key and each ID you > received. > 3) You create a thread with the "IDGROUPS" key as a parameter. > 4) That's all for here. > > In each thread : > 1) you start with the key passed in parameter > 2) You can easily use a SELECT, cause the WHERE is "WHERE xx.ID IN > (SELECT ID FROM "IDS" WHERE "GROUPKEY" = TheKeyInParameter)..." > 3) you make the job. > 4) You send a DELETE to "IDS" table with the key, then you DELETE also > the key in "IDGROUPS". > 5) The thread is finished. > > > It is simple and safe. Of course there are certainly other ways. That's > one. > > -- > > > OD___ > www.e-naxos.com > >
Sure there is. Use the IN expression. For example,
SELECT Col, Col1 FROM table WHERE ID IN "15, 20, 35" The problem is that the IN expression cannot take a parameter. However, there are several techniques you can use to get around this limitation. For example, you can create a Table-returning Function that converts a delimited string to a table. Then you could code the IN expression to search for the values in the table. hth -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Lubomir" <Lubomir@discussions.microsoft.com> wrote in message news:803CBD5B-6A19-4B0E-AD29-752F3B9B42B9@microsoft.com... > Hi, > > I need to get data from a table from a database. The data will be queried > according ID column. > > In a fixed time interval my application will receive the list of IDs. Than > all records with this IDs should be retrieved from database. > > Tehre is not any select SQL command which would allow to put in query a > list > of parameters, like SELECT ALL FROM XX WHERE ID is from MYList > > What would be the most efficient way to get those required data from a db? > > Thanks, > > Lubomir
O, I see.
Thanks Lubomir [quoted text, click to view] "OD" wrote:
> > What is the benefit of using 2 tables IDGROUPS and IDS? Why not use IDS only > > with autoinc column for a key? > > because I used to be respectful with the relational nature of RDBMS. > One table is the "group of IDs", the master, the other one is the "list > of IDs for a group of IDs", the detail. > This is also allowing for a simple way to generate a "group of IDs" > key. > Of course, you can suppress "IDGROUPS" table and create a store proc > returning an autoinc value that you will use in the ID list table. But > this is more complex for the same result and less respectful with the > relational nature of the DB. Also semantics is preserved with 2 tables > (because you have 2 entities: groups and list of ids) so it is "better" > from this point of view. > > Of course, you can adapt the solution to fit your needs. For myself > I'll use two tables, DB licences are generally not based upon table > number :-) > > -- > > > OD___ > www.e-naxos.com > >
[quoted text, click to view]
> What is the benefit of using 2 tables IDGROUPS and IDS? Why not use IDS only > with autoinc column for a key? because I used to be respectful with the relational nature of RDBMS. One table is the "group of IDs", the master, the other one is the "list of IDs for a group of IDs", the detail. This is also allowing for a simple way to generate a "group of IDs" key. Of course, you can suppress "IDGROUPS" table and create a store proc returning an autoinc value that you will use in the ID list table. But this is more complex for the same result and less respectful with the relational nature of the DB. Also semantics is preserved with 2 tables (because you have 2 entities: groups and list of ids) so it is "better" from this point of view. Of course, you can adapt the solution to fit your needs. For myself I'll use two tables, DB licences are generally not based upon table number :-) -- OD___ www.e-naxos.com
[quoted text, click to view]
> O, I see. > Thanks I will add this comment : you need a Group ID (so 2 tables) because in the solution I explained, you need to work within threads. So two threads (or more) can be up at the same time. Thus, each thread must to know on which "group of IDs" it must work on. If you do not implement the thread part of the solution I presented, you just need one table storing the list of IDS. There's then no "group" entity since a "group" is processed by the main thread and can't be mixed with another one. -- OD___ www.e-naxos.com
What about just concatenating a string and using ExecuteSQL() *Greg ducks*
-- Gregory A. Beamer MVP; MCP: +I, SE, SD, DBA http://gregorybeamer.spaces.live.com ********************************************* Think outside the box! ********************************************* [quoted text, click to view] "William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message
news:%23KUHipxcHHA.4352@TK2MSFTNGP03.phx.gbl... > Sure there is. Use the IN expression. For example, > SELECT Col, Col1 FROM table > WHERE ID IN "15, 20, 35" > > The problem is that the IN expression cannot take a parameter. However, > there are several techniques you can use to get around this limitation. > For example, you can create a Table-returning Function that converts a > delimited string to a table. Then you could code the IN expression to > search for the values in the table. > > hth > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no > rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest book: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) > ----------------------------------------------------------------------------------------------------------------------- > > "Lubomir" <Lubomir@discussions.microsoft.com> wrote in message > news:803CBD5B-6A19-4B0E-AD29-752F3B9B42B9@microsoft.com... >> Hi, >> >> I need to get data from a table from a database. The data will be queried >> according ID column. >> >> In a fixed time interval my application will receive the list of IDs. >> Than >> all records with this IDs should be retrieved from database. >> >> Tehre is not any select SQL command which would allow to put in query a >> list >> of parameters, like SELECT ALL FROM XX WHERE ID is from MYList >> >> What would be the most efficient way to get those required data from a >> db? >> >> Thanks, >> >> Lubomir > >
That's ok, but can lead to SQL injection attacks.
-- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message news:E82FC1D2-CB91-420E-B01D-AA910E75117B@microsoft.com... [quoted text, click to view] > What about just concatenating a string and using ExecuteSQL() *Greg ducks* > > -- > Gregory A. Beamer > MVP; MCP: +I, SE, SD, DBA > http://gregorybeamer.spaces.live.com > > ********************************************* > Think outside the box! > ********************************************* > "William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message > news:%23KUHipxcHHA.4352@TK2MSFTNGP03.phx.gbl... >> Sure there is. Use the IN expression. For example, >> SELECT Col, Col1 FROM table >> WHERE ID IN "15, 20, 35" >> >> The problem is that the IN expression cannot take a parameter. However, >> there are several techniques you can use to get around this limitation. >> For example, you can create a Table-returning Function that converts a >> delimited string to a table. Then you could code the IN expression to >> search for the values in the table. >> >> hth >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> INETA Speaker >> www.betav.com/blog/billva >> www.betav.com >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> __________________________________ >> Visit www.hitchhikerguides.net to get more information on my latest book: >> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) >> ----------------------------------------------------------------------------------------------------------------------- >> >> "Lubomir" <Lubomir@discussions.microsoft.com> wrote in message >> news:803CBD5B-6A19-4B0E-AD29-752F3B9B42B9@microsoft.com... >>> Hi, >>> >>> I need to get data from a table from a database. The data will be >>> queried >>> according ID column. >>> >>> In a fixed time interval my application will receive the list of IDs. >>> Than >>> all records with this IDs should be retrieved from database. >>> >>> Tehre is not any select SQL command which would allow to put in query a >>> list >>> of parameters, like SELECT ALL FROM XX WHERE ID is from MYList >>> >>> What would be the most efficient way to get those required data from a >>> db? >>> >>> Thanks, >>> >>> Lubomir >> >> >
Thanks fro all answers. I implemented another table with "my list of IDs, and
with timestamp columns and used teh SQL command with "select, in, order by and group" by parts and it works so far. Yes, support for list off paramaters in SQL would be very convenient :-) Regards, Lubomir [quoted text, click to view] "Miha Markic [MVP C#]" wrote:
> > "William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message > news:%23EFVV0RdHHA.4984@TK2MSFTNGP05.phx.gbl... > > That's ok, but can lead to SQL injection attacks. > > Not if you are dealing with integers (most often scenario) and you are > careful. But yes, you are certainly correct and MS should really address > this - I mean adding support for arrays in parameters. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >
[quoted text, click to view] "William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message news:%23EFVV0RdHHA.4984@TK2MSFTNGP05.phx.gbl... > That's ok, but can lead to SQL injection attacks. Not if you are dealing with integers (most often scenario) and you are careful. But yes, you are certainly correct and MS should really address this - I mean adding support for arrays in parameters. -- Miha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/
[quoted text, click to view]
On Apr 2, 2:44 pm, "Miha Markic [MVP C#]" <miha at rthand com> wrote: > "William (Bill) Vaughn" <billvaRemoveT...@betav.com> wrote in messagenews:%23EFVV0RdHHA.4984@TK2MSFTNGP05.phx.gbl... > > > That's ok, but can lead to SQL injection attacks. > > Not if you are dealing with integers (most often scenario) and you are > careful. But yes, you are certainly correct and MS should really address > this - I mean adding support for arrays in parameters. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & developmentwww.rthand.com > Blog:http://cs.rthand.com/blogs/blog_with_righthand/ > That's ok, but can lead to SQL injection attacks. How to prevent SQl injection attacks at all? Some times we are forced to use a string from our application. What then? Is there a simple way to avoid the injections in .adonet or we should search for alternative in SQL syntax?
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |