c# - Load a DataTable from a SQLDataReader in batches using Powershell -

i need load datatable sqldatareader in batches. sqldatareader return millions of records, , datatable load() method exhaust available memory.

here current code:

[cmdletbinding( defaultparametersetname = 'instance',                     supportsshouldprocess = $true,                     confirmimpact = 'high' )] param (    [string] $srcserver     = "mysqlserver,12345",    [string] $srcdatabase   = "sourcedb",    [string] $srctable      = "dbo.sourcetable",    [string] $srcquery      = "select top 100 hashbytes('sha',stay_number) stay_number_h, * $srctable",    [string] $tgtserver,    [string] $tgtdatabase   = "targetdb",    [string] $tgttable      = "tmp.targettable",    [switch] $truncate      = $true )  function connectionstring([string] $servername, [string] $dbname) {    "data source=$servername;initial catalog=$dbname;integrated security=true;connection timeout=30" }  ########## main body ############ write-host "starting..."  if ($tgtserver.length –eq 0) {    $tgtserver = $srcserver }  if ($tgtdatabase.length –eq 0) {    $tgtdatabase = $srcdatabase }  if ($tgttable.length –eq 0) {    $tgttable = $srctable }  if ($truncate) {    write-host "truncating $tgttable"    $truncatesql = "truncate table " + $tgttable    sqlcmd -s $tgtserver -d $tgtdatabase -q $truncatesql }  $srcconnstr = connectionstring $srcserver $srcdatabase $srcconn  = new-object system.data.sqlclient.sqlconnection($srcconnstr) $cmdtext = $srcquery $sqlcommand = new-object system.data.sqlclient.sqlcommand($cmdtext, $srcconn) $srcconn.open() [system.data.sqlclient.sqldatareader] $sqlreader = $sqlcommand.executereader()  # can convert sqlreader datatable? $dtschema = $sqlreader.getschematable() $dt = new-object system.data.datatable  if ($dtschema -ne $null) {     foreach ($drow in $dtschema.rows)     {         $columnname = $drow["columnname"]         $column = new-object system.data.datacolumn($columnname, $drow["datatype"])         $column.unique = $drow["isunique"]         $column.allowdbnull = $drow["allowdbnull"]         $column.autoincrement = $drow["isautoincrement"]         $dt.columns.add($column)     } }  write-host "now loading datatable" ($i=0;$i -le 10; $i++) {    $i    $null = $dt.loaddatarow($sqlreader,$true) } write-host "datatable filled, how long , check memory consumption!"  sleep 30  $datatable.clear()  write-host "finished" 

related links:

csv sql server:
https://gallery.technet.microsoft.com/scriptcenter/import-large-csvs-into-sql-216223d9 https://gallery.technet.microsoft.com/scriptcenter/import-large-csvs-into-sql-fa339046

sql server sql server:
https://blogs.technet.microsoft.com/heyscriptingguy/2011/05/06/use-powershell-to-copy-a-table-between-two-sql-server-instances/ https://newsqlblog.com/2011/08/12/moving-data-between-sql-servers-with-powershell/ https://raw.githubusercontent.com/ramblingcookiemonster/powershell/master/invoke-sqlbulkcopy.ps1

ideally final solution support both ss , external files import (actually can realized datatable)

not powershell, how process batch db:

it requires class fields match table, , processes data in whatever bath size using system.reflection:

public static void sql_reader_to_type(type t, sqldatareader r)     {          list<object> ret = new list<object>();         while (r.read())         {              fieldinfo[] f = t.getfields();             object o = activator.createinstance(t);             (int = 0; < f.length; i++)             {                 string thistype = f[i].fieldtype.tostring();                 switch (thistype)                 {                     case "system.string":                          f[i].setvalue(o, convert.tostring(r[f[i].name]));                         break;                     case "system.int16":                         f[i].setvalue(o, convert.toint16(r[f[i].name]));                         break;                     case "system.int32":                         f[i].setvalue(o, convert.toint32(r[f[i].name]));                         break;                     case "system.int64":                         f[i].setvalue(o, convert.toint64(r[f[i].name]));                         break;                     case "system.double":                         // console.writeline("converting " + f[i].name + " double");                         double th;                         if (r[f[i].name] == null)                         {                             th = 0;                         }                         else                         {                             if (r[f[i].name].gettype() == typeof(dbnull))                             {                                 th = 0;                             }                             else                             {                                 th = convert.todouble(r[f[i].name]);                             }                         }                          f[i].setvalue(o, th);                          break;                     case "system.boolean":                         f[i].setvalue(o, convert.toint32(r[f[i].name]) == 1 ? true : false);                         break;                     case "system.datetime":                         f[i].setvalue(o, convert.todatetime(r[f[i].name]));                         break;                     default:                         throw new exception("missed data type in sql select in getclassmembers class line 73");                  }             }             ret.add(o);             if (ret.count == 50000)             {                 //process data                 ret.clear();             }          }         if (ret.count > 0)         {             //processdata         }       }  create table (firstname varchar(25), lastname varchar(50), birthday date)  class person{public string firstname; public string lastname; public datetime birthday}  sql_reader_to_type(typeof(person), com.executereader());