How to dynamically make DataTables and with Columns and Rows being dynamically generated as well?

Question

Say I have a database with 2 tables with the following names (tbl1 and tbl2). Each of the table above have different number of columns, tbl1 has 3 while tbl2 has 4 columns.

I need to copy each of the above tables in a DataTable.

Of course I can do it manually like the following codes below:

    class 
    {  
      public Main()  
      {
        // for tbl1 with 3 columns.
        string sql = "select * from tbl1"
        MySqlCommand com = new MySqlCommand(sql, con);
        MySqlDataReader dr = com.ExecuteDataReader();
        DataTable dt = GetDataTable(3);

        while (dr.Read())
        {
          if (dr.HasRows)
          {
            dt.Rows.Add(dr[0], dr[1], dr[2]);  <---- Point of interest
          }
        }

        // for tbl2 with 4 columns.
        string sql = "select * from tbl2";
        MySqlCommand com = new MySqlCommand(sql, con);
        MySqlDataReader dr = com.ExecuteDataReader();
        DataTable dt = GetDataTable(4);

        while (dr.Read())
        {
          if (dr.HasRows)
            {
              dt.Rows.Add(dr[0], dr[1], dr[2], dr[3]);  <---- Point of interest
            }
        }
    }

      public DataTable GetDataTable(int columnCount)
      {
        DataTable dt = new DataTable();

        if (columnCount > 0)
        {
          for (int i = 0; i < length; i++)
          {
            dt.Columns.Add(i.ToString(), typeof(object));
          }
        }
        return dt;
      }
    }

But what I would like to do is to make the process above in an automated way, especially on the part where I indicated an arrow.

Is there a way I could dynamically add rows like what I did on the columns?

I was thinking I could add rows dynamically by using a function that generates the process of adding rows as string and call that string as a command but I am really really lost and don't know what to do... pls see the code below.

EX:    
String generated from a function base on number of columns:
    "dt.Rows.Add(dr[0], dr[1], dr[2], dr[3])"
Then, use the string as a command to add rows...

You should probably use an Adapter and its Fill method on the DataTable. — LarsTech
– LarsTech, Commented Apr 30, 2013 at 17:47
@Steve This is a help site, RTFM is not an acceptable response. Either help the OP answer his question, or don't respond. Also, yeah, I see you have 28484848 reputation. I really don't care, that doesn't make snotty responses acceptable. — Kyle
– Kyle, Commented Apr 30, 2013 at 17:55
You can fill both tables with the "DataAdapter" by appending both SQL seperated by a ";" with a "Fill" command into a DataSet which will create DataSet.Tables(0) and .Tables(1). Both results returned in one query. — DarrenMB
– DarrenMB, Commented Apr 30, 2013 at 17:59
@Darren I don't think it will solve my problem making rows dynamically...How am I gonna add rows to DataTable dt using DataReader dr with an arbitrary number of columns? — chad
– chad, Commented Apr 30, 2013 at 18:55

adiga · Accepted Answer · 2016-08-01 12:37:01Z

Use the GetSchemaTable method of the datareader to find out how many columns you have.

Something like this (non-tested code, meant for example):

public DataTable ReadTable(string sql, params object[] parameters) {


    using (var cmd = CreateCommand(sql, parameters))
        {
            var reader = cmd.ExecuteReader();
            if (reader == null)
            {
                return null;
            }

            var schemaTable = reader.GetSchemaTable();
                            DataTable dt = GetTable(schemaTable);

            while (reader.Read())
            {
                var values = new object[reader.FieldCount];
                reader.GetValues(values);
                                    dt.Rows.Add(values);

            }
        }
}

private DataTable GetTable(DataTable schemaTable) {
        if (schemaTable == null || schemaTable.Rows.Count == 0)
        {
            return null;
        }

        var dt = new DataTable();
        foreach (DataRow schemaRow in schemaTable.Rows)
        {
            var col = new DataColumn
            {
                ColumnName = schemaRow["ColumnName"].ToString(),
                                    DataType = schemaRow["DataType"]
                                  // use the debugger to find out the name of the type column in the schematable, 
                                  // and any other properties you need
            };
                            dt.Columns.Add(col);
        }
        return dt;

}

Note: I use code like this not to create a DataTable, but to convert the read rows into an IEnumerable object (we are using some DB2 Entity Framework libraries that don't do this automatically using a sql statement).

As many mentioned, if you just want to read a DataTable, create a DataAdapter and use the Fill() method.

// Assumes that connection is a valid SqlConnection object.
string queryString = 
 "SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");

http://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.80).aspx

Thank you so much for your help gabnaim and @Darren, exactly what I needed to push me to the right direction ^_^

DarrenMB · Accepted Answer · 2013-04-30 23:45:09Z

This is a VB example of Filling a DataSet to get your results (modified from one of my wrappers).

There is no population of rows at all (it is done for you by the data adapter)

Sub ExampleUsage()
    Dim ds as new dataset
    ExecuteDataSetFill(AnSqlConnection, "SELECT * FROM Something; SELECT * from SomethingElse", ds)
    Dim Tbl1 as datatable=ds.Tables(0)
    Dim Tbl2 as datatable=ds.Tables(1)
    ' both tables will ALREADY HAVE all the rows in them, there is no reader involved.
End Sub
    ''' <summary>
    ''' Performs a FILL operation on an adapter, populating the passed in dataset for the current "OpenConnection", returns the return value of the FILL command.
    ''' </summary>
    ''' <param name="sSQL">SQL to use for the command to issue</param>
    ''' <param name="dsToFill">a DataSet to FILL</param>
    ''' <returns>The Return Value of the FILL operation</returns>
    Public Overridable Function ExecuteDataSetFill(ByVal con As SqlClient.SqlConnection, ByVal sSQL As String, ByVal dsToFill As DataSet) As Integer
        Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        Dim com As SqlClient.SqlCommand = con.CreateCommand
        com.CommandText = sSQL
        da.SelectCommand = com
        Dim iOut As Integer
        dsToFill.EnforceConstraints = False
        Dim sw As Stopwatch = Stopwatch.StartNew
        Try
            iOut = da.Fill(dsToFill)
        Catch ex As Exception
            Throw New Exception("DataSet Error. " & vbCrLf & "SQL = " & sSQL & vbCrLf & "Error=" & ex.ToString, ex)
        End Try
        sw.Stop()
        Return iOut
    End Function

Thank you so much for your help @gabnaim and Darren, exactly what I needed to push me to the right direction ^_^

Collectives™ on Stack Overflow

How to dynamically make DataTables and with Columns and Rows being dynamically generated as well?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related