Hi ya,
I'm trying to compare an array and the values of SqlDataReader and I can't actually figure it out. I used to work with ADO and new to .net
I want to compare them and then if the values are not matched to the SqlDataReader, I want them to store it into a recordset but there is no recordset in Ado.net plus i think my logic has some flaws as well.
Cheers
The code:
for (int i=0; i <=countme-1; i++)
{
myCommand.CommandText = "select * from tbl_empDetails";
SqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
if (myReader.GetString(abc) != SID[ i ])
{
Response.Write(SID[1] + " " + givenName[1] + "<BR>");
}
else
{
Response.Write("<BR>" + SID[0] + " " + givenName[0]);
}
}
Comparing values in an array and SqlDataReader?
IlCapo
Your code looks reasonable but you didn't post it all so I'm guessing. Nevertheless the comparison will work provided SID is an array of strings. Unfortunately the SqlDataReader.GetString method only allows ordinal values so you need to know in which column the string to compare against will be (not a good thing but it works). Assuming that abc is the correct ordinal then the comparison will work.
One issue with the comparison is that it is case sensitive. If case doesn't matter then use String.Compare rather than the equality operator. As far as recordsets go they are equivalent to DataSets in .NET. If you want to store off entire rows of data then you should use a DataSet rather than SqlDataReader. You can not modify the reader data. I would recommend a strongly typed data set if at all possible (add a new dataset to the project to generate one). You would then populate the dataset with the results of your query. You could then enumerate the rows of the data set's table and delete any that match and leave any that do not.
DataSet ds = new DataSet();
) //Matches so remove
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = myCommand;
da.Fill(ds);
DataTable dt = ds.Tables[0];
int index = 0;
while (index < dt.Rows.Count)
{
if (dt.Rows[abc] == SID
dt.Rows.RemoveAt(index);
else
++index;
};
One problem I have with your code is the for loop. What is its purpose As your code stands now it will enumerate countme times. Each time it will execute a SQL command and enumerate the rows. It will then compare each row with an element from the SID array. This presumably is not what you wanted as it'll be slow and, because you are not cleaning up the data reader, will probably eat up the system resources.
You should instead execute the command once and then, if necessary, enumerate the elements of the array each time you retrieve a new row from the data reader. The data set version would work in a similar manner.
Michael Taylor - 9/8/06
ChrisHelt
Hello Michael,
Sorry was not in on weekends so apologies for late reply.
Let me clarify it a bit because as your code stands It will do the same thing which i do not want to do.
The for loop in my code is to loop through the Array of SID[ i ]. The countme variable has the array length.
The problem is if i do not run the for loop how can i move to the next record in array. The other problem is that SID might contain more or less records then the table and I do not want to run against one array record the whole dataset or in my version dataReader. Lets just say that I have 1500 record in array and almost 1000 rows in sqlServer table. If i run my version or yours as well I will be comparing a single array record against 1000 rows in table or dataset and that is not really a great solution to this problem.
What i want to do is compare them and the records which are in the array but not in the table i should store them somewhere or give user a message and I do not have any problems with the records which are in table but not in array meaning i do not need to do anything with them.
Any ideas how to do that
Thanks
andrealisp
Ok so if I understand you correctly you have an array of values. You load some data from the DB. You want to find any values in the array that are not in the DB. The values may be anywhere in the DB.
Since your control values (if you will) are in the array what you ideally want to do is enumerate through each array element. If the element is not found in the DB then store it in a separate collection. Unfortunately readers are forward only so you can't do that. Assuming that you are avoiding DataSet and must use a data reader then the alternative is to enumerate the reader and "check off" the array values as you find them.
Create a dictionary containing the key to compare the reader rows against and a boolean value. Copy the array of values into the dictionary. Now begin reading from the reader. For each row check the dictionary (by key) to see if the value exists. If it does then remove the value from the dictionary (or set the boolean value to true). Once you have read all the records from the reader the dictionary contains the values that were not found (or, if using the boolean value, then those that are set to false were not found). You can then copy the values to a new array.
I used the dictionary above because it makes lookup constant so performance is good provided you can uniquely map each array value and reader row to a key. Some key generation code might be needed. I would also recommend that you remove the items from the dictionary as you find them. This reduces memory over time, has no impact on performance and the final step for getting the missing values degrades to simply looking at the Keys collection of the dictionary.
Michael Taylor - 9/14/06