How to store the retrieved values from a coulmn of a table in an array??

rameshbhojan

Hi All, PLease let me know if how can a save the values in a column from a database in an array using C# .NET Web Application (so that i could use each value in the array in my code one by one.) For Eg.: if i have the following query: SELECT Name FROM Details WHERE Country='India' here "Name" and "Country" are two columns in table called "Details". Suppose the query returns Three values: Ram, John and Sohail i want these names to be saved in an arraay. i could not use the "ExecuteScalar" class as it can save only one value into a variable! please send me the few lines of code necessary to do that using the above mentioned example. Thanks, Ramesh

Talal Sultan

The SELECT command will already return the values in a table format. You need a data reader to loop through the values. You have to create your own DataTable (in this case with one column) and insert the values as you loop the data reader. If you are using SQL Server, you can use the SqlDataReader class. Assuming you have: sql command: SqlCommand myCommand = new SqlCommand(); data table: DataTable myTable = new DataTable(); You would have to put something like this

SqlDataReader dr = myCommand.ExecuteReader();

while (dr.Read()) // reads as long as there are values
{
DataRow row = myTable.NewRow();
row[0] = dr.GetString(0); // to get the Name column
row[1] = ...... // if you have other columns
myTable.Rows.Add(row);
}
dr.Close();

Good luck :) Talal

-- If this is a post that has been helpful to you, please vote for it. Thank you! "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." --Rich Cook

Giorgi Dalakishvili

You can use executereader, read all the values and store them in array but in my opinion arraylist would be better as you don't know how many values you'll need to store. But why do you need it? You can store values in datatable and then access them very easily.

#region signature my articles #endregion

Talal Sultan

oops, I just noticed that you wanted the results in an Array. Well instead of a data table, you can use an ArrayList and add the elements as you read them from the data reader. Talal

-- If this is a post that has been helpful to you, please vote for it. Thank you! "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." --Rich Cook

Giorgi Dalakishvili

Instead of manually adding each row to the datatable you can use dataadapter to fetch all the values

#region signature my articles #endregion

rameshbhojan

Thanks Talal :-) Now how do i get these values from row and save in a array variable, which i need to pass as a parameter to a method???

Talal Sultan

Like I said in my second post here http://www.codeproject.com/script/comments/forums.asp?msg=2176578&forumid=1649#xx2176578xx[^], you need to replace the data table stuff with the array stuff. Since you only return one column from the database, you would only need column 0 of the data reader:

ArrayList myList = new ArrayList();
SqlDataReader dr = myCommand.ExecuteReader();
while (dr.Read()) // reads as long as there are values
myList.Add(dr.GetString(0)); // to get the Name column
dr.Close();

If your method has an Array parameter, you can convert ArrayList into Array as follows:

Array myArray = myList.ToArray();

-- If this is a post that has been helpful to you, please vote for it. Thank you! "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." --Rich Cook

rameshbhojan

Could you please give me a sample code(using my example above) to do the same as u mentioned.......

Giorgi Dalakishvili

http://www.codeproject.com/cs/database/DatabaseAcessWithAdoNet1.asp

#region signature my articles #endregion

rameshbhojan

Thanks talal :-) Now if i wanna display all these values in the array(or datareader) in a dropdown list.... how do i do that???????

Talal Sultan

You need to do a databinding. If you have your data in the datatable as I first described, you would do as follows. Supposing DataTable myTable = new DataTable(); is your table where the values are stored and DropDownList ddlFoo; is your drop down list, you would bind like this:

ddlFoo.DataSource = myTable;
ddlFoo.DataTextField = "ColumnNameForText";
ddlFoo.DataValueField = "ColumnNameForValue";
ddlFoo.DataBind();

Remember that you can only bind 2 columns of the table, one column should contain the text to be displayed in the DDL and one column should contain the corresponding value for each entry. If you are using the ArrayList as I described ArrayList myList = new ArrayList(); you need to make sure that the object that you added in the array has 2 fields where you would place the text and the value fields. I use System.Web.UI.WebControls.ListItem because this class has 2 properties Text and Value. You would then make the binding in the same way you would do for the data table:

ddlFoo.DataSource = myList;
ddlFoo.DataTextField = "Text";
ddlFoo.DataValueField = "Value";
ddlFoo.DataBind();

Here we put Text and Value because these are the names of the properties of the ListItem class, but if you used another class with other fields, you would put their name here. Hope this helps. Talal

-- If this is a post that has been helpful to you, please vote for it. Thank you! "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." --Rich Cook