Import values from the database to a table or something better..

Question

I have a question about using databases...

 string node = Session["node"].ToString();

 SqlConnection dataConnection = new SqlConnection();
 dataConnection.ConnectionString = ConfigurationManager.ConnectionStrings["SumooHAgentDBConnectionString"].ConnectionString;
 SqlCommand dataCommand = new SqlCommand();
 dataCommand.Connection = dataConnection;

 dataCommand.CommandText = ("Select * from xyz where @xyzID= xyzID AND PathType=0")
 dataCommand.Parameters.AddWithValue("@xyzID", node);

 dataConnection.Open();
 dataCommand.ExecuteNonQuery();
 dataConnection.Close();

Now there are 5 columns in the table with 5 rows which should return.

I then want to add these values in a table or arraylist so that i can use those values in my code later...

What is the best way to do so???? Thanks

Answer 1

Instead of dataCommand.ExecuteNonQuery(), you need:

SqlDataAdapter a = new SqlDataAdapter(dataCommand);
DataTable resultsTable = new DataTable();
a.Fill(resultsTable);

The data will then be in the resultsTable object.

Sounds like you need to read up on ADO.NET generally ... DataTables are covered here, there are lots of examples on the web showing how to loop through them and get the data out, for example, here.

Answer 2

You can add them to a DataTable - then you'll have a .Rows property with 5 rows, and each DataRow in the .Rows property has five columns which holds those values. Almost like a database in memory.

To do that, just use the SqlDataAdapter and its Fill method:

DataTable myTable = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter(dataCommand);
dap.Fill(myTable);

Or you can read the rows and values with a DataReader and then take those and turn each row into an object of type "whatever-it-is" and call a constructor on that object type with those five column values, and stick your resulting five objects into a List<WhateverItIs> and you've basically created your own little ORM (Object-relational mapper).

This would require you to use a SqlDataReader and do something like this:

SqlDataReader dr = dataCommand.ExecuteReader();
List<Entity> _list = new List<Entity>();

while(dr.Read())
{ 
   string column1 = dr.GetString(0);
   ...
   string column5 = dr.GetString(4);

   Entity newEntity = new Entity(column1, ......., column5);
   _list.Add(newEntity);
}

Whatever works best for you - the DataTable is less work in the initial loading phase, but accessing the data later on is a bit more cumbersome (MyDataTable.Rows[3].Column["ColunmName"].Value or something like that) - the "ORM" approach gives you nice objects of a .NET CLR type which you can more easily use, but it's more work initially when loading the data.

Marc

Learning resources:

• C# Station ADO.NET Tutorial
• Using ADO.NET for beginners
• ADO.NET Quickstarts on asp.net

Answer 3

You may want to use the ExecuteReader method instead of ExecuteNonQuery.

Answer 4

You can make use of Data Access Application Block released by Microsoft Patterns & Practices if you don't want to do all the low level ADO.NET plumbing work.