none
C# Console App. Freezes after Filling Datatable Inside a Loop RRS feed

  • Question

  • I am running a console application on a Windows 2003 Enterpise Edition 64-bit Server, using VS 2005 and .Net Framework 2.0.

    My program is using .Net Database access components and ESRI's ArcObjects (COM) components to do GIS Spatial Analysis for modeling.  The problem occurs in that I have to do an SQL sort and join to fill a datatable with a few million records and 4 columns.  On the second pass in the loop, the app will freeze once it encounters and ArcObject being accessed or created.  By moving the fill of the datatable I can change where the application will freeze, but it is always immediately following this dataadapter.fill(). 

    When I evaluate the datatable or ArcObject after the fill I get "function evaluation timed out".  I have attempted all of the work arounds that I have found in forums, online, etc, and to no avail.  Any solutions will be greatly appreciated.  See a subset of code below.

    Thank you for you assistance.

    //Set SQL Function for Sorting and Joining Tables
    strSortSQL = "SELECT " + strIDName + "cPnts.ObjectID," + strIDName + "cPnts.PopAdd," +
    strIDName + "combine." + strBoundField + "," +
    strIDName + "combine." + strCoeffField + " FROM " + strIDName + "cPnts," + strIDName + "combine WHERE "
    + strIDName + "cpnts.GRID_CODE = " + strIDName + "combine.ObjectID AND " + strIDName +
    "combine.Pop_Calc < 0 ORDER BY " + strIDName +
    "combine." + strBoundField + "," + strIDName +
    "combine." + strCoeffField + "," +
    strDBRandFunction;

    //get datatable from function (see below)
    DataTable dtSubTable = GetDataTable(strSortSQL, strBoundField, strCoeffField);

    //send table to normalize with point function
    NormalizeWithPoints(strSortSQL, strUpdateSQL, strFieldList, "\"POP-CALC\" < 0", pBoundTable,
    dtSubTable, bndryMax);

    private DataTable GetDataTable(string strSQL,
    string strBoundField, string strCoeffFld)
    {
    IDbConnection dbConnection = null;
    DbDataAdapter adapter = null;
    IDbCommand dbCommand = null;
    DataTable dtTable = new DataTable("DogTable" + strBoundField);

    try
    {
    dbConnection = objDBConn.CreateConnection(strDBConnString, dbtSDEDatabase);
    dbConnection.Open();
    dbCommand = objDBConn.CreateCommand(strSQL, dbtSDEDatabase, dbConnection);
    dbCommand.CommandTimeout = 360;
    adapter = objDBConn.CreateAdapter(dbCommand, dbtSDEDatabase);
    adapter.SelectCommand.CommandTimeout = 240;
    Console.WriteLine("Start Fill " + DateTime.Now.ToString());
    adapter.Fill(dtTable);
    Console.WriteLine("Table Filled " + DateTime.Now.ToString());
    Console.WriteLine(dtTable.Rows.Count.ToString());
    }
    catch (Exception ex)
    {
    Console.WriteLine(ex.ToString());
    }
    finally
    {
    dbConnection.Close();
    dbConnection.Dispose();
    dbConnection = null;
    dbCommand.Dispose();
    dbCommand = null;
    adapter.Dispose();
    adapter = null;
    }

    return dtTable;
    }

    private void NormalizeWithPoints(string strSQL, string strSQL1, string strFieldList,
    string strWhereClause, ITable pTable, DataTable dtTable, int MaxValue)
    {
    //This can be easily converted to use any Database Connector (SQL, Oracle, Access, etc.)
    string[] strFields = new string[3];
    strFields = strFieldList.Split(',');
    string strPopAdd = strFields[0];
    string strBoundary = strFields[1];
    string strCoeff = strFields[2];

    IDbConnection dbConnection = null;
    DbDataAdapter adapter1 = null;
    DbCommandBuilder oCmdB = null;
    DataRow drRow = null;

    IQueryFilter pQueryFilter = new QueryFilterClass(); <FAILS HERE>
    ICursor pCursor = null;
    IRow pRow = null;

    Tuesday, October 9, 2007 7:59 PM