Advertisement

Latest Post

Monday, 3 April 2017

How to Inverse Data Table using C#

How to Inverse Data Table using C#

Here i am sharing how to get Inverse Data Table from data table using C#. I implemented a method for this and called to get Inverse Data Table from data table

Following is the method that i created using c#

public static DataTable GetInversedDataTable(DataTable table, string columnX, string[] arrColumnY, string keyColumn, string columnZ, string nullValue, bool sumValues)
        {
            //Create a DataTable to Return
            DataTable returnTable = new DataTable();

            // get distinct key column values
            var distinctKeyValues = (from row in table.AsEnumerable()
                                     select row.Field<dynamic>(keyColumn)).Distinct();

            var columnXValues = (from row in table.AsEnumerable()
                                 select row.Field<dynamic>(columnX)).Distinct();

            if (columnX == "")
                columnX = table.Columns[0].ColumnName;

            #region ADD A COLUMN AT THE BEGINNING OF THE TABLE

            foreach (var columnY in arrColumnY)
            {
                returnTable.Columns.Add(columnY);
            }

            foreach (var columnY in columnXValues)
            {
                returnTable.Columns.Add(columnY, table.Columns[columnZ].DataType);
            }

            #endregion

            for (int indRow = 0; indRow < distinctKeyValues.Count(); indRow++)
            {
                returnTable.Rows.Add(returnTable.NewRow());

                DataRow[] rows = table.Select(keyColumn + "='" + distinctKeyValues.ElementAt(indRow) + "'");  

                for (int rowsCounter = 0; rowsCounter < rows.Length; rowsCounter++)
                {
                    for (int indCol = 0; indCol < arrColumnY.Length; indCol++)
                    {
                        returnTable.Rows[indRow][indCol] = rows[rowsCounter][arrColumnY[indCol]];
                    }
                }

                int i = arrColumnY.Length;
                for (int indCol = 0; indCol < columnXValues.Count(); indCol++)
                {
                    try
                    {
                        returnTable.Rows[indRow][i] = rows[indCol][columnZ];
                    }
                    catch (Exception)
                    {

                        returnTable.Rows[indRow][i] = 0;
                    }
                    
                    i++;
                }
            }

            return returnTable;
        }

Here, I am calling the "GetInversedDataTable" method to get Inversed DataTable from data table.

public ActionResult Test123()
        {
            
            DataTable dt = new DataTable();

            dt.Columns.Add("ID", Type.GetType("System.String"));
            dt.Columns.Add("Name", Type.GetType("System.String"));
            dt.Columns.Add("Amount", Type.GetType("System.Decimal"));          
            dt.Columns.Add("Date", Type.GetType("System.String"));

            dt.Rows.Add(new object[] { 1, "Alok Singh", 5000, "Jan-2017" });
            dt.Rows.Add(new object[] { 2, "Sachin", 4000, "Jan-2017" });
            dt.Rows.Add(new object[] { 1, "Alok Singh", 1500, "Feb-2017" });
            dt.Rows.Add(new object[] { 2, "Sachin", 1800, "Feb-2017" });
            dt.Rows.Add(new object[] { 1, "Alok Singh", 400, "Mar-2017" });
            dt.Rows.Add(new object[] { 2, "Sachin", 150, "Mar-2017" });
            dt.Rows.Add(new object[] { 3, "Anil Singh", 5000, "Apr-2017" });

            DataTable InversedDataTable = GetInversedDataTable(dt, "Date", new string[] { "ID", "Name"}, "ID", "amount", null, true);
           
            return View(InversedDataTable );
        }

OUTPUT:-

Government Jobs