CODEWITHSUNDEEP
c#asp.netsqlperformance
Mathematics
Mathematics

Reputation: 7618

Querying data from SQL

I need to query a table from database which has 400 rows and 24 columns. I need to query this table so that on each row and then on each column of row I can perform some C# code ( I can use column information to execute some code).

Now at the moment I am querying each row again and again from table using select statement and storing to a custom list and performing custom operations on it.

Is it best and fastest way of doing it ? or should I just query the whole table one time and store somewhere ? not sure where in a dataset and then run throw custom code to do some operation using information in each row ?

Upvotes: 0

Views: 124

Answers (6)

Billy Samuel
Billy Samuel

Reputation: 672

If the number of records will always be below thoussand, just query all the records and keep it in a List.

Once the data is in the List you can query the List n number of times using LINQ without hitting the database for each request.

Upvotes: 0

Sachin
Sachin

Reputation: 40970

You can fetch the table from database once and store it in datatable and then just use linq to select the column something like this

var data = dt.AsEnumerable().Select(s => s.Field<string>("myColumnName")).ToArray<string>();

and If you don't want to use other columns anywhere in your code then you should select only useful column from the database.

You can also select multiple columns of a database using linq. The values will be stored in anonymous type of object.

var mutipleData = from row
                  in dt.AsEnumerable()
                  select new 
                  { Value1 = row["Column1"].ToString(), 
                    Value2 = row["Column2"].ToString() 
                  };

Upvotes: 2

Tarik
Tarik

Reputation: 11209

Assuming that each field is 1000 bytes, the total memory to hold your 400 rows would be 9.6MB. Peanuts! Just read the whole table in a DataTable and process it as you wish.

Upvotes: 2

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

  • Select all the records from DB table which are f your concern
  • Copy the select records into DataTable.

Pseudo Code:

--dt is datatable
foreach(datarow dr in dt.rows)
{
--perform operation
string str=dr["columnname"].tostring 
}

Upvotes: 1

Lave Loos
Lave Loos

Reputation: 1302

If it's only 400 records, I'd fetch them all at once, store them in a class and iterate over each instance.

Something like:

Class

public class MyTableClass(){
    string property1 {get; set;}
    string property2 {get; set;}
    int property3 {get; set;}
    // etc.
}

Logic:

ICollection<MyTableClass> lstMyTableClass = (
                from m in db.mytableclass
                select m
            ).ToList();
            return lstMyTableClass;

And then a loop:

 foreach(var myTableInstance in lstMyTableClass){

      myTableInstance.DoMyStuff();   
 }

Upvotes: 0

Digbyswift
Digbyswift

Reputation: 10400

400 rows isn't a massive amount, however it depends on the data in each column and how often you are likely to run the query. If all you are going to do is run the query and manipulate the output, use a DataReader instead.

Upvotes: 0

Related Questions