Convert dataset to arraylist in asp.net using c#


DataSet is a disconnected, cached set of records that are retrieved from the database. When a connection is established with the database, the DataAdapter creates a DataSet and stores data in it. After the data is retrieved and stored in a DataSet, the connection with the database is closed. Such a working architecture is called disconnected architecture. The DataSet acts like a virtual database containing tables, rows and columns. An application works with the database records stored in the DataSet.



Dataset stores datatables in the Tables collection and we can retrieve a datatable by passing the index of table. So, to convert DataSet to ArrayList, first we need to get the correct datatable in the DataTable collection and take each row and add to the arrayList. The below code demonstrates how to convert data from dataset to an arraylist

public partial class _Default : System.Web.UI.Page
{
    string connectionString = "Data Source=LocalHost; 
    		Initial CataLog=ERPFinAccounting; 
 			User ID=kris; Password=XXX";
     
     protected void Page_Load(object sender, EventArgs e)
     {
		ArrayList myArrayList = ConvertDataSetToArrayList();
        // Display each item of ArrayList
        foreach (Object row in myArrayList)
        {
          Response.Write(((DataRow)row)["AccountCode"].ToString() 
          		+ " " + ((DataRow)row)["AccountName"].ToString() + "<br/>");
        }
     }
}


The below code performs the following tasks.

  • Instantiate the Connection object
  • Instantiate the Command object
  • Open the connection
  • Instantiate DataAdapter object and Set DataAdapter command properties
  • Instantiate DataSet object and Populate the Dataset
  • Create an instance of ArrayList
  • To retrieve each DataRow of DataTable which is stored in the DataSet we have used the foreach loop. Inside the foreach loop we add each DataRow object to an ArrayList. We pass DataRow object to the Add method of an ArrayList as the parameter.

public ArrayList ConvertDataSetToArrayList()
{
	SqlConnection conn = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("Select * from AccountsTable",conn);
    cmd.CommandType = CommandType.Text;
    conn.Open();

    SqlDataAdapter myAdapter = new SqlDataAdapter();
    myAdapter.SelectCommand = cmd;

    DataSet myDataSet = new DataSet();
    myAdapter.Fill(myDataSet);

    ArrayList myArrayList = new ArrayList();
    foreach (DataRow dtRow in myDataSet.Tables[0].Rows)
     {
           myArrayList.Add(dtRow);
     }
     conn.Close();
     return myArrayList;
}