|
I've been perplexed by this for a while now, and have found no real solution.
I cannot find a way to create a stongly typed DataRow, without first creating a DataTable, which i don't want to do.
Basicaly, i want to wrap my business object around a datarow, so that it can be used with a data table, without exposing the table and adapter to the external code, and using the datarow to enforce the validation initialy.
Can anyone give me any pointers as to how to do this? I've tried a number of ways and been blocked by various caveats.
Cheers
Tris
[Update]
I'm thinking about using a single instance of each type of data table as a row factory, and wrap them in a static class with static members. Can anyone comment on the thread safety of this approach?
|
|
|
|
|
hey there, have you tried
System.Data.DataRow myDataRow;
Riaan Booyzen
|
|
|
|
|
hello,
Can I give a list of values in a valiable and use this variable in select query where clause with in operator
for instance
declare @list nvarchar(1000)
set @list = select words from sample
select * from emp where dept in(@list)
regards,
Uma
|
|
|
|
|
yes you can give...
You can use it something like this:
declare @tt varchar(100)<br />
declare @sql varchar(100)<br />
set @tt = '''0004'','+'''0005'','+'''0006'''<br />
set @sql='select * from mst_employees where emp_no in ('+@tt+')'<br />
exec (@sql)
Best Regards,
Apurva Kaushal
|
|
|
|
|
If I want to use with in a subquery then how to use?
Regards,
Uma
|
|
|
|
|
same way you just need to create the dynamic query..
Best Regards,
Apurva Kaushal
|
|
|
|
|
It means we cannot use a single query.We have to give it in the loops i.e with cursors.Is that right
Thank u.
Regards,
Uma
|
|
|
|
|
No not at all...
What exactly is your requirement?
Best Regards,
Apurva Kaushal
|
|
|
|
|
I have two tables.
one contians a column called keywords which contains individual words.
other contains a column called list which contains a list of words separated by comma.Now i want to write a query to retrieve details from first table by searching the second table.How to do this
Regards,
Uma
|
|
|
|
|
for this no need to go for the dynamic query you can directly use the method suggested by Colin.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then it will not work.now the table data is like this.I hope this will make you clear with my requirement.
table1
col1
hyderabad
delhi
mumbai
table2
col1
hyderabad,chennai,bangalore
bangalore,kolkata,delhi
bangalore
mumbai,hyderabad
now if I give select * from table1 where col1 in (select col1 from table2)
i will not get the output
can u check it once
Regards,
Uma
|
|
|
|
|
Uma Kameswari wrote: Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then...
...it breaks the first normal form of database normalisation.
The easiest solution from this point is to go to the dynamic SQL and use cursors to navigate through the rows. However, that is a very slow solution.
If you can, it would be better if you could find some way of normalising the data so that it can be used more efficiently.
|
|
|
|
|
He wanted to use the result of another SELECT statement inside the IN() expression which is a valid thing to do.
|
|
|
|
|
Actually what she has given was some values in a variable thats why I had given that type of solution.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Apurva Kaushal wrote: Actually what she has given was some values in a variable thats why I had given that type of solution
But if you read the pseudo sql that she provided you can see what she is trying to do:
declare @list nvarchar(1000)
set @list = select words from sample
select * from emp where dept in(@list)
The second line, being an apparent result from a SELECT statement gives the indication that it isn't necessarily a variable she wants, but the result of the SELECT statement.
|
|
|
|
|
yep and there exactly I got confused and gave that solution.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Apurva Kaushal wrote: yep and there exactly I got confused and gave that solution.
Don't worry about it. It wasn't explained too well. You sometimes need to read something like that a couple of times to really understand what is wanted.
|
|
|
|
|
Colin Angus Mackay wrote: You sometimes need to read something like that a couple of times to really understand what is wanted.
Yep that is correct.. Thanks for the suggestion.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Now it looks like we were both a little bit correct and a little bit wrong.
C'est la vie!
|
|
|
|
|
You can put a select statement inside the IN expression
SELECT *
FROM Table1
WHERE SomeColumn IN (SELECT Something FROM Table2)
And no need for pesky dynamic SQL.
|
|
|
|
|
Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then it will not work.now the table data is like this
table1
col1
hyderabad
delhi
mumbai
table2
col1
hyderabad,chennai,bangalore
bangalore,kolkata,delhi
bangalore
mumbai,hyderabad
now if I give select * from table1 where col1 in (select col1 from table2)
i will not get the output
can u checkl it once
Regards,
Uma
|
|
|
|
|
You can create a table-valued function which uses a loop to split the csv list in table2 and insert them individually into a table variable.
<br />
CREATE FUNCTION [dbo].[function_string_to_table]<br />
(<br />
@string VARCHAR(MAX),<br />
@delimiter CHAR(1)<br />
)<br />
RETURNS @output TABLE(<br />
data VARCHAR(256)<br />
)<br />
BEGIN<br />
<br />
DECLARE @start INT, @end INT<br />
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)<br />
<br />
WHILE @start < LEN(@string) BEGIN<br />
IF @end = 0 <br />
SET @end = LEN(@string) + 1<br />
<br />
INSERT INTO @output (data) VALUES (SUBSTRING(@string, @start, @end - @start))<br />
SET @start = @end + 1<br />
SET @end = CHARINDEX(@delimiter, @string, @start)<br />
END<br />
<br />
RETURN<br />
<br />
END<br />
<br />
Then you can do this:
<br />
DECLARE @values VARCHAR(MAX)<br />
SELECT @values = col1 FROM table2 WHERE FOO = 'BAR'<br />
<br />
SELECT * FROM table1 <br />
WHERE col1 IN (<br />
SELECT data FROM dbo.function_string_to_table(@values, ',')<br />
)<br />
<br />
|
|
|
|
|
http://www.projectdmx.com/tsql/sqlarrays.aspx[^]
You should be able to use CharIndex as the article in the link above suggests as one solution.
select t1.*
from table1 t1
join table2 t2 on charindex(',' + t1.col1 + ',' , ',' + t2.col1 + ',') > 0
|
|
|
|
|
Hello,
I am developing a dictionary library and utility for a heavy-duty linguistic application. Because the wordlist is used very intensely, I am preloading it at startup. There are approx. 1 million records in the wordlist table, each containing a textual representation of a symbol, accompanied by a few numeric fields. There are other tables as well, but they are accessed less often and don't have to be preloaded.
What database management system would you recommend for my application?
I tried MSDE, but it required dozens of seconds for the preload operation, while the same information loaded from a custom-format binary file loads in approx. 2 seconds. I haven't tried the new SQL Server, but I suspect it'd be similar in efficiency to MSDE. Crafting a custom storage solution could be nice, but requires significantly more resources than a ready-made thing.
What do you think?
|
|
|
|
|
Bartosz Bien wrote: I tried MSDE, but it required dozens of seconds for the preload operation, while the same information loaded from a custom-format binary file loads in approx. 2 seconds.
What is more important to you: The speed of the pre-load operation, or the speed of retrieval?
To me a "pre-load" operation is something that happens in advance and is not time critical. If you need very fast search and retrieval then having a slightly slower preload operation where it properly indexes everything for you is much more advantageous.
You might also want to consider SQL Server 2005 Express Edition instead of MSDE.
|
|
|
|