CODEWITHSUNDEEP
c#sqlquery-optimization
Afnan Bashir
Afnan Bashir

Reputation: 7429

How to save data retrieved from a query

I previously asked the question and got answer to Best approach to write query but the problem is that if you have to save this result in a list then there duplication of records. For example the resultant table of the join given EXAMPLE

See there are duplicate rows. How can you filter them out, and yet save the data of order number? Of course there may be some ways but I am looking for some great ways

How can we store the data in list and not create duplicate rows in list?

My current code for my tables is 

  int lastUserId = 0;
  sql_cmd = new SqlCommand();
  sql_cmd.Connection = sql_con;

  sql_cmd.CommandText = "SELECT * FROM AccountsUsers LEFT JOIN Accounts ON AccountsUsers.Id = Accounts.userId ORDER BY AccountsUsers.accFirstName";
  SqlDataReader reader = sql_cmd.ExecuteReader();

  if (reader.HasRows == true)
  {
      Users userToAdd = new Users();

      while (reader.Read())
      {
           userToAdd = new Users();
           userToAdd.userId = int.Parse(reader["Id"].ToString());
           userToAdd.firstName = reader["accFirstName"].ToString();
           userToAdd.lastName = reader["accLastName"].ToString();
           lastUserId = userToAdd.userId;

           Websites domainData = new Websites();
           domainData.domainName = reader["accDomainName"].ToString();
           domainData.userName = reader["accUserName"].ToString();
           domainData.password = reader["accPass"].ToString();
           domainData.URL = reader["accDomain"].ToString();
           userToAdd.DomainData.Add(domainData);

           allUsers.Add(userToAdd);
       }
    }

For second table I have custom list that will hold the entries of all the data in second table.

The table returned is table having joins and have multiple rows for same

Upvotes: 1

Views: 909

Answers (4)

Zamboni
Zamboni

Reputation: 8043

Besides using the Dictionary idea as answered by Antonio Bakula...

If you persist the dictionary of users and call the code in your sample multiple times you should consider that a user account is either new, modifed, or deleted.

The algorithm to use is the following when executing your SQL query:

  1. If row in query result is not in dictionary create and add new user to the dictionary.
  2. If row in query result is in dictionary update the user information.
  3. If dictionary item not in query result delete the user from the dictionary.

I'd also recommend not using SELECT *
Use only the table columns your code needs, this improves the performance of your code, and prevents a potential security breach by returning private user information.

Upvotes: 1

kheya
kheya

Reputation: 7621

Seems like the root of your problem is in your database table. When you said duplicate data rows, are you saying you get duplicate entries in the list or you have duplicate data in your table?

Give 2 rows that are duplicate.

Two options: First, prevent pulling duplicate data from sql by using a distinct clause like: select distinct from where

Second option as mentioned Antonio, is to check if the list already has it.

First option is recommended unless there are other reasons.

Upvotes: 0

AjayR
AjayR

Reputation: 4179

i am not sure why are you not using distinct clause in your sql to fetch unique results. also that will be faster. did you look at using hashtables.

Upvotes: 1

Antonio Bakula
Antonio Bakula

Reputation: 20693

I would put users into Dictonary and check if allready exists, something like this :

Dictionary<int, Users> allUsers = new Dictionary<int, Users>()

and then in Reader while loop :

int userId = int.Parse(reader["Id"].ToString());
Users currUser = allUsers[userId];

if (currUser == null)
{
  currUser = new Users();
  currUser.userId = userId);
  currUser.firstName = reader["accFirstName"].ToString();
  currUser.lastName = reader["accLastName"].ToString();
  allUsers.Add(userID, currUser);
}

Websites domainData = new Websites();
domainData.domainName = reader["accDomainName"].ToString();
domainData.userName = reader["accUserName"].ToString();
domainData.password = reader["accPass"].ToString();
domainData.URL = reader["accDomain"].ToString();
currUser.DomainData.Add(domainData);

Upvotes: 0

Related Questions