Split datatable based on row count

[Venkatzeus]

Hi.

I am using VS 2010, C# and ASP.NET Web Application.

I am trying to split a large data table into smaller data tables . Each containing 10000 rows. I have tried the below code:

// Calling function
getDataSet(con);


public DataSet getDataSet(OracleConnection con)
{
DataSet ds = new DataSet();

ds.Tables.Add(getInfo("select * from Names", con, "Names"));

ds.Tables.Add(getInfo("select * from Address", con, "Address"));

ds.Tables.Add(getInfo("select * from City", con, "City"));

ds.Tables.Add(getInfo("select * from Country", con, "Country"));

return ds;
}


private List<DataTable> getInfo(string strQuery, OracleConnection con, string tblName)
{
List<DataTable> tables = new List<DataTable>();
System.Data.DataTable dt = new System.Data.DataTable();

using (OracleCommand cmd = new OracleCommand(strQuery))
{
using (OracleDataAdapter da = new OracleDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);

}
}

dt.TableName = tblName;
int cnt = dt.Rows.Count;

if (dt.Rows.Count > 10000)
{
tables = SplitTable(dt, 10000, tblName);
}

return tables;
}


private static List<DataTable> SplitTable(DataTable originalTable, int batchSize, string tblName)
{
List<DataTable> tables = new List<DataTable>();
DataTable new_table = new DataTable();
new_table = originalTable.Clone();
int j = 0;
int k = 1;
if (originalTable.Rows.Count < batchSize)
{
new_table.TableName = tblName + "_" + j;
new_table = originalTable.Copy();
tables.Add(new_table.Copy());
}
else
{
for (int i = 0; i < originalTable.Rows.Count; i++)
{
new_table.NewRow();
new_table.ImportRow(originalTable.Rows);
if ((i + 1) == originalTable.Rows.Count)
{
new_table.TableName = tblName + "_" + j;
tables.Add(new_table.Copy());
new_table.Rows.Clear();
k++;
}
else if (++j == batchSize)
{
new_table.TableName = tblName + "_" + j;
tables.Add(new_table.Copy());
new_table.Rows.Clear();
k++;
j = 0;
}
}
}
return tables;
}


Few issues here:

1) If the first datatable contains 53140 rows, after execution of the split code I have datatables 0 to 6 with each datatable containing 10000 rows and last one containing 3139. There is one row missing. Not sure, where is the issue.

2) Each datatable is having tablename as Table_10000 and the last one as Table_3139. I want the TableName as Table_1 , Table_2.. like this

Thanks

Continue reading...