Easy way to convert data table to hash table or sqldatareader to hashtable

Question

Is there an easy way to convert a DataTable to a HashTable or a SQLDataReader to a HashTable? I have to parse it through javascriptserializer. The code I am using has some problems:

try
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            dt.Load(dr);
        }
    }

    Hashtable sendData = new Hashtable();

    foreach (DataRow drIn in dt.Rows)
    {

        sendData.Add(drIn["orderNumber"].ToString(), drIn["customerName"].ToString());

    }

    sendData.Add("orderNum", order);
    JavaScriptSerializer jss = new JavaScriptSerializer();
    string output = jss.Serialize(sendData);
    return output;
}
catch (Exception ex)
{
    return ex.Message + "-" + ex.StackTrace;
}

It is giving a correct result when queried from one table in the database but from another table it's having a problem.

Is there any other way to do this?

Answer 1

You can use the following function to convert DataTable to HashTable,

public static Hashtable convertDataTableToHashTable(DataTable dtIn,string keyField,string valueField)   
{    
   Hashtable htOut = new Hashtable();    
   foreach(DataRow drIn in dtIn.Rows)    
   {    
      htOut.Add(drIn[keyField].ToString(),drIn[valueField].ToString());    
   }   
   return htOut;    
}

Then in your code just use,

Hashtable sendData = new Hashtable();
//You need to pass datatable, key field and value field
sendData = convertDataTableToHashTable(dt, "orderNumber", "customerName");

Answer 2

I think your best bet is to create your own extension methods for this, as this is kind of specific desired behavior.

public static class DataTableExtensions 
{
    public static Hashtable ToHashtable(this DataTable dt, string key, string value)
    {
        Hashtable ht = new Hashtable();
        foreach(DataRow row in dt.Rows)
        {
            ht.Add(row[key].ToString(), row[value].ToString());
        }
        return ht;
    }

    public static Hashtable ToHashtable(this DataTable dt, int keyIndex, int valueIndex)
    {
        Hashtable ht = new Hashtable();
        foreach(DataRow row in dt.Rows)
        {
            ht.Add(row[keyIndex].ToString(), row[valueIndex].ToString());
        }
        return ht;
    }
}

and here are some example use cases:

public class SomeClass{
    static void NormalWay(){
        DataTable dt = Library.RandomDataTable(); // Go get SQL Data
        Hashtable ht = dt.ToHashtable("some key column", "some value column");
        // Build a hashtable based on two specific columns
    }

    static Hashtable AnotherWay() => 
        Library
            .RandomDataTable()
            .ToHashtable(0,1); // build a hash table based on the first and second column
}

What you would need to do on your end is proper null checks, as I have omitted them here for brevity.

Answer 3

public static Hashtable Fn_ConvertDataTableToHashTable(DataTable dtTable, int iRow)
{
        Hashtable hshTable = new Hashtable();

        if (CommonUtil.Fn_CheckDatatableHasValue(dtTable))
        {
            foreach (DataColumn column in dtTable.Columns)
            {

                hshTable.Add(column.ColumnName, dtTable.Rows[iRow][column.ColumnName].ToString());
            }
        }

        return hshTable;
}