Querying data from SQL

Question

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 ?

yup and it's one time application that i need to run on live server — Mathematics
– Mathematics, Commented Jul 8, 2013 at 10:30
It's always best to export your data to csv or some file. Then using a loop you can iterate through each row of your file then you can assign each element of a row to an arrary.. Like this you can process.. Just a suggestion.. — Mariappan Subramanian
– Mariappan Subramanian, Commented Jul 8, 2013 at 10:34

Sachin · Accepted Answer · 2013-07-08 10:42:20Z

2

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() 
                  };

edited Jul 8, 2013 at 10:42

answered Jul 8, 2013 at 10:33

Sachin

41.1k7 gold badges94 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tarik · Accepted Answer · 2013-07-08 10:40:21Z

2

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.

answered Jul 8, 2013 at 10:40

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

Comments

Prahalad Gaggar · Accepted Answer · 2013-07-08 10:38:08Z

1

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 
}

edited Jul 8, 2013 at 10:38

answered Jul 8, 2013 at 10:33

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

Comments

Digbyswift · Accepted Answer · 2013-07-08 10:34:58Z

0

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.

answered Jul 8, 2013 at 10:34

Digbyswift

10.4k4 gold badges41 silver badges67 bronze badges

Comments

Lave Loos · Accepted Answer · 2013-07-08 10:36:31Z

0

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();   
 }

answered Jul 8, 2013 at 10:36

Lave Loos

1,3121 gold badge11 silver badges15 bronze badges

Comments

Billy Samuel · Accepted Answer · 2013-07-08 10:49:21Z

0

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.

answered Jul 8, 2013 at 10:49

Billy Samuel

6722 gold badges6 silver badges21 bronze badges

2 Comments

Mathematics Over a year ago

this is what i am doing at the moment :)

Billy Samuel Over a year ago

Are you calculating something based on the values in each column?

Collectives™ on Stack Overflow

Querying data from SQL

6 Answers 6

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related