Adding array into datatable with limited columns

Question

I already know how to add array into datatable.

string[] months = {"Jan" , "Feb" , "Mar" , "Apr" , "Jun"};
DataTable dt = new DataTable();
dt.Columns.Add("M1");
dt.Columns.Add("M2");
dt.Columns.Add("M3");
dt.Columns.Add("M4");
dt.Columns.Add("M5");
DataRow row = dt.NewRow();

for (int i = 0; i < months.Length; i++)
{
   row[i] = months[i];       
}

dt.Rows.Add(row);

The above code perfectly works. But I have a different question. Lets assume I have an array of 8 values.

string[] arr = {"1","2","3","4","5","6","7","8"};

Now I have created a new datatable with 4 columns.

DataTable dt = new DataTable();

dt.Columns.Add("v1");
dt.Columns.Add("v2");
dt.Columns.Add("v3");
dt.Columns.Add("v4");

DataRow row = dt.NewRow();

Now I want to add the values of array in to columns like

arr[0] = v1
arr[1] = v2
arr[2] = v3
arr[3] = v4

arr[4] = v1
arr[5] = v2
arr[6] = v3
arr[7] = v4

Update 1

I have a function in which I want to perform all these actions

public string LoadAMIReadings(string startTS, string endTS, string batch, string divCode, string cc_code)
{

    totalRec = 0;
    processedRec = 0;
    this.BATCH = batch;
    this.DIVCODE = divCode;
    this.CCCODE = cc_code;
    this.FROMDATE = startTS;
    this.TODATE = endTS;
    HESservicesDoCommandRequest obj = new HESservicesDoCommandRequest();




DataTable dt = new DataTable();
dt.Columns.Add("Application_No", typeof(string));
dt.Columns.Add("REF_NO", typeof(string));
dt.Columns.Add("METER_SERIAL_NO", typeof(string));
dt.Columns.Add("XMETER_NO", typeof(string));

// here I want to call the modified solution

int rowCount = bill.Length / dt.Columns.Count; // array bill is declared globally with 7780 values in it
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
    DataRow row = dt.NewRow();
    for (int columnIndex = 0; columnIndex < dt.Columns.Count; columnIndex++)
    {
        // In case of 2 rows:

        // row 1: (0 * 4) + 0 = 0
        // row 1: (0 * 4) + 1 = 1
        // row 1: (0 * 4) + 2 = 2
        // row 1: (0 * 4) + 3 = 3

        // row 2: (1 * 4) + 0 = 4
        // row 2: (1 * 4) + 1 = 5
        // row 2: (1 * 4) + 2 = 6
        // row 2: (1 * 4) + 3 = 7

        row[columnIndex] = bill[(rowIndex * dt.Columns.Count) + columnIndex];
    }

    dt.Rows.Add(row);
}


if (dt != null && dt.Rows.Count > 0)
{
    totalRec = dt.Rows.Count;
    string ReqEnvPath = System.Configuration.ConfigurationManager.AppSettings["ReadEnvPath"].ToString();
    XElement SoapReqEnv = XElement.Load(ReqEnvPath);

    foreach (DataRow dr in dt.Rows)
    {

        string uniqueID = dr["APPLICATION_NO"].ToString();
        string meterNo = dr["METER_SERIAL_NO"].ToString();

        string timestamp = DateTime.UtcNow.ToString("o");
        StringBuilder sbArg0 = new StringBuilder();
        try
        {
            sbArg0.AppendFormat(@"<?xml version=""1.0"" encoding=""UTF-8"" ?>          " + SoapReqEnv.ToString(), uniqueID, startTS, endTS, timestamp, this.HEXURL, this.HEXUID, this.HEXPWD);
            Guid currentGuid = Guid.NewGuid();

            obj.getResponseAsync(sbArg0.ToString(), currentGuid + "$" + uniqueID);
            obj.getResponseCompleted += this.myHandler;
            string delayMS = System.Configuration.ConfigurationManager.AppSettings["DelayMS"].ToString();
            ushort delay = 1000;
            ushort.TryParse(delayMS, out delay);
            System.Threading.Thread.Sleep(delay);


        }
        catch (Exception ex)
        {
            error += "Error for UniqID:" + uniqueID + "Desc:" + ex.Message + "\n";
        }
        finally
        {
            //System.Threading.Thread.CurrentThread.Join();
        }
    }
}

return error;
}

How can I add them?

What I mean is that if i is your array index, then i%4 will give you an index between 0 and 3 and you could use that to index your row — dcg
– dcg, Commented Mar 28, 2019 at 18:07
No like this for (int i=0; i< arr.Length; i++){row[i%4]=arr[i];}, if you can index your row — dcg
– dcg, Commented Mar 28, 2019 at 18:10

Odrai · Accepted Answer · 2019-03-28 19:17:34Z

1

Solution 1

Code example; this is not the best or shortest implemention, but it is a clear and understandable one for novice programmers.

string[] arr = { "1", "2", "3", "4", "5", "6", "7" };

int rowCount = arr.Length / dt.Columns.Count;
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
    DataRow row = dt.NewRow();
    for (int columnIndex = 0; columnIndex < dt.Columns.Count; columnIndex++)
    {
        // In case of 2 rows:

        // row 1: (0 * 4) + 0 = 0
        // row 1: (0 * 4) + 1 = 1
        // row 1: (0 * 4) + 2 = 2
        // row 1: (0 * 4) + 3 = 3

        // row 2: (1 * 4) + 0 = 4
        // row 2: (1 * 4) + 1 = 5
        // row 2: (1 * 4) + 2 = 6
        // row 2: (1 * 4) + 3 = 7

        row[columnIndex] = arr[(rowIndex * dt.Columns.Count) + columnIndex];
    }

    dt.Rows.Add(row);
}

Solution 2

A more advanced and edge-case-safe solution which uses extension methods to split the original array to multiple sub-arrays and fill a datatable.

public static class Extensions
{
    public static IEnumerable<IEnumerable<T>> Split<T>(this T[] array, int size)
    {
        for (var i = 0; i < (float)array.Length / size; i++)
        {
            yield return array.Skip(i * size).Take(size);
        }
    }

    public static void FillDataTable<T>(this DataTable dataTable, T[] input)
    {
        IEnumerable<IEnumerable<T>> rowValues = input.Split(dataTable.Columns.Count);
        foreach (IEnumerable<T> rowValue in rowValues)
        {
            DataRow row = dataTable.NewRow();

            T[] cellValues = rowValue.ToArray();
            for (int columnIndex = 0; columnIndex < cellValues.Length; columnIndex++)
            {
                // 'Safe'-check in case the original array didn't contain enough values. The cell value will remain 'null'
                if (columnIndex < dataTable.Columns.Count)
                {
                    row[columnIndex] = cellValues[columnIndex];
                }
            }

            dataTable.Rows.Add(row);
        }
    }
}

static class Program
{
    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main()
    {
        int[] input = { 1, 2, 3, 4, 5, 6, 7, 8 };

        DataTable dataTable = new DataTable();
        dataTable.Columns.Add("v1");
        dataTable.Columns.Add("v2");
        dataTable.Columns.Add("v3");
        dataTable.Columns.Add("v4");

        dataTable.FillDataTable(input);    
    }
}

edited Mar 28, 2019 at 19:17

answered Mar 28, 2019 at 18:12

Odrai

2,4334 gold badges38 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Odrai Over a year ago

@Moeez Solution 2 will indeed work up to n-values in the orginal (in this case 'arr') array. If the sub-array doesn't contain enough values, the cell value of the row will remain empty. If you have any further questions, please feel free to ask.

Moeez Over a year ago

@Ordai.. In actual I have array of 7780 values

Odrai Over a year ago

@Moeez The number of values shouldn't matter. Are you going to add those values sequentially? (so the first row will get value 1,2,3,4 and the second one 5,6,7,8 as described in your OP?) Solution 2 should not cause any issues.

Odrai Over a year ago

@Moeez I've updated Solution 2 which is a more generic implementation and is able to handle different kinds of input (not only string arrays, but integers as well).

Odrai Over a year ago

@Moeez You're welcome, I am glad I was able to help.

|

dcg · Accepted Answer · 2019-03-28 18:40:36Z

1

What I mean is that you can do something like this:

DataRow row = null;
for (int i = 0; i < arr.Length; i++) {
    if (i % 4 == 0)
    {
        row = dt.NewRow();
        dt.Rows.Add(row);
    }
    row[i % 4] = arr[i];
}

EDIT: Take this as an example:

var arr = Enumerable.Range(1, 40).ToArray();

for (int i = 0; i < arr.Length; i++)
{
    if (i % 4 == 0) { Console.WriteLine("new row"); }
    Console.WriteLine($"row[{i % 4}] = arr[{i}];");
}

The output would be:

new row
row[0] = arr[0];
row[1] = arr[1];
row[2] = arr[2];
row[3] = arr[3];
new row
row[0] = arr[4];
row[1] = arr[5];
row[2] = arr[6];
row[3] = arr[7];
new row
row[0] = arr[8];
row[1] = arr[9];
row[2] = arr[10];
row[3] = arr[11];
new row
row[0] = arr[12];
row[1] = arr[13];
row[2] = arr[14];
row[3] = arr[15];
...

Which is what you want, right?

edited Mar 28, 2019 at 18:40

answered Mar 28, 2019 at 18:16

dcg

4,2291 gold badge26 silver badges33 bronze badges

5 Comments

Moeez Over a year ago

It works only for 8 values bigger than that it gives IndexOutOfRangeException

dcg Over a year ago

Give me an example where that happens

Moeez Over a year ago

I added 32 values and after the 8th I got the error

Moeez Over a year ago

Yes exactly that's what I want.

dcg Over a year ago

So where is that you're having IndexOutOfRangeException?

vscoder · Accepted Answer · 2019-03-28 18:19:03Z

This worked for me, need to add hooks for the edge cases but the concept is simple enough.

        string[] arr = { "1", "2", "3", "4", "5", "6", "7", "8" };
        DataTable dt = new DataTable();
        dt.Columns.Add("v1");
        dt.Columns.Add("v2");
        dt.Columns.Add("v3");
        dt.Columns.Add("v4");
        DataRow row = dt.NewRow();

        int col = 0;
        foreach (var val in arr)
        {
            if (col > 3)
            {
                col = 0;
                dt.Rows.Add(row);
                row = dt.NewRow();
                row[col] = val;
            }
            else
            {
                row[col] = val;
                col++;
            }
        }
        dt.Rows.Add(row);

Collectives™ on Stack Overflow

Adding array into datatable with limited columns

3 Answers 3

6 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related