How to store SQL columns in array C# winforms

Question

I want to store my selected values from database in arrays so I can do computations on the values. I wrote the following code:

string command = @"select  
                      I.Dist, I.ID, D.ID, D.Temp,
                      CAST(D.Hmd as float) Hmd,
                      CAST(D.ActEvp as float) ActEvp 
                   from 
                      IFiles I 
                   inner join 
                      (select 
                          DFiles.ID, DFiles.Temp, DFiles.Hmd, DFiles.ActEvp 
                       from 
                          DFiles 
                       where 
                          FileName = '1') D on I.ID = CAST( D.ID as int) 
                   where 
                       FileName = 's1' 
                   order by 
                       CAST(I.Dist as float)";

SqlConnection con = new SqlConnection("Data Source=MyDB;Initial Catalog=Graphic;Integrated Security=True");

SqlCommand cmd = new SqlCommand(command, con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

con.Open();

DataSet ds = new DataSet();
DataTable dt = new DataTable();

da.Fill(ds, "DFiles");

List<float> Hmd = new List<float>();

foreach(DataRow row in ds.Tables["DFiles"].Rows)
{
    Hmd.Add(float.Parse(row["Hmd"].ToString()));
}

However I don't think it will be efficient if I do this for every single column. Is there a better way?

There are several ways: (1) use a Micro-ORM like Dapper to keep the "raw" SQL statement, but turn the results automagically into a List<SomeClass> - then Dapper does much of the mapping of relational data to your .NET class for you; or (2) - use a SqlDataReader rdr = cmd.ExecuteReader(), loop over all rows read by the data reader use while (rdr.Read()) { ... } and inside the while loop create objects and fill their properties with the values read from the database (that's basically what Dapper does for you - if you insist, you can code it all yourself) — marc_s
– marc_s, Commented Mar 21, 2015 at 16:40
@Sajeetharan: I want to get the selected values, which will be the columns of the select statement. — Erik
– Erik, Commented Mar 21, 2015 at 16:49

marc_s · Accepted Answer · 2015-03-21 17:18:40Z

First step: define your "DTO" (data transfer object) to hold the values you read from the database:

public class DTO
{
    public string Dist { get; set; }
    public int ID { get; set; }
    public int DID { get; set; }
    public string Temp { get; set; }
    public decimal Hmd { get; set; }
    public decimal ActEvp { get; set; }
}

(I had to guess what datatype those values might be - adapt as needed)

Second step: install "Dapper Dot Net" into your Visual Studio solution (easiest way is via NuGet - or you can get it from here on Github)

Third step: use Dapper to make your database access much simpler:

string command = "......";  // your SQL query, as before

using (SqlConnection con = new SqlConnection("Data Source=MyDB;Initial Catalog=Graphic;Integrated Security=True"))
{
    con.Open();
    IEnumerable<DTO> results = con.Query<DTO>(command);
    con.Close();
}

That's all there is! Now you have a nice proper .NET List<DTO> with the results from the database, and you can start using them.

Joel Coehoorn · Accepted Answer · 2015-03-21 16:57:47Z

1

Not an answer, but the sql query is better expressed without nesting the second table, like this:

select  
    I.Dist, I.ID, D.ID, D.Temp,
    CAST(D.Hmd as float) Hmd,
    CAST(D.ActEvp as float) ActEvp 
from IFiles I 
inner join DFiles D ON cast(D.ID as int) = I.ID AND D.FileName = '1'
where I.FileName = 's1'
order by CAST(I.Dist as float)

Additionally, it looks like you're using string types for several columns that really should be stored as a number of some kind in the first place... at least the DFiles.ID and IFiles.Dist columns stand out. That's a huge problem with the schema. Stores those values as int/float in the first place.

answered Mar 21, 2015 at 16:57

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Collectives™ on Stack Overflow

How to store SQL columns in array C# winforms

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related