DaniWeb Forum Index > Software Development > C#

C# RSS

Updating columns in an access database using c#

Please support our C# advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!

•

Join Date: Dec 2007

Posts: 1

Reputation: arnaki is an unknown quantity at this point

Solved Threads: 0

arnaki

Offline

Newbie Poster

Updating columns in an access database using c#

Jan 7th, 2008

Dear c-sharpers,

I'm relatively new developing code using c#. I created an access database with a table using ADOX. I want to fill the table with about 2 million records and something like 20 columns of different types (int, double, string). So far I used different methods to fill this table with data:
1) all columns at once using "INSERT INTO" statements and followed by an oledbcommand.executenonquery();
2) all columns at once using ADODB recordsets.
The second way is much faster than the first method if one wants to create thousands of records. For my problem in which the columntypes and number of columns are known in advance this is sufficient.

However, I also want to generalise it in a library so Fortran-users in our company can start using it as a fortran library. The easiest way (I think) is to use a routine that fills only one column with data. For this I also use two different ways, which both fail. I select one column and add a new row if this is the first time a column is filled with data. The first method uses recordsets again, and the second method uses the OleDbDataAdapter and a dataTable (see code below). Unfortunately, in both cases the size of the Access-file explodes. Does anyone have suggestions on how to taggle this problem?

Thanks in advance!

Method 1:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
        static public void WriteDataC(string tableName, string columnName, string[] stringValues)
        {
            ADODB.RecordsetClass recordset = new ADODB.RecordsetClass();
            ADODB.RecordClass record = new ADODB.RecordClass();
            string sqlString = "select " + columnName + " from " + tableName;
            recordset.Open(sqlString, catalog.ActiveConnection, ADODB.CursorTypeEnum.adOpenForwardOnly, 
                ADODB.LockTypeEnum.adLockOptimistic, 0);
 
            bool noRecords = false;
            object nil = Type.Missing;
            if (recordset.BOF && recordset.EOF)
            {
                noRecords = true;
            }
            else
            {
                noRecords = false;
                recordset.MoveFirst();
            }
 
            for (int iRec = 0; iRec < stringValues.Length; ++iRec)
            {
                if (noRecords)
                {
                    recordset.AddNew(columnName, stringValues[iRec]);
                    recordset.Update(columnName, stringValues[iRec]);
                }
                else
                {
                    recordset.Fields[columnName].Value = stringValues[iRec];
                    recordset.Update(columnName, stringValues[iRec]);
                    recordset.MoveNext();
                }
            }
 
            recordset.Close();
        }
        static public void WriteDataC(string tableName, string columnName, string[] stringValues)
        {
            ADODB.RecordsetClass recordset = new ADODB.RecordsetClass();
            ADODB.RecordClass record = new ADODB.RecordClass();
            string sqlString = "select " + columnName + " from " + tableName;
            recordset.Open(sqlString, catalog.ActiveConnection, ADODB.CursorTypeEnum.adOpenForwardOnly, 
                ADODB.LockTypeEnum.adLockOptimistic, 0);
            
            bool noRecords = false;
            object nil = Type.Missing;
            if (recordset.BOF && recordset.EOF)
            {
                noRecords = true;
            }
            else
            {
                noRecords = false;
                recordset.MoveFirst();
            }

            for (int iRec = 0; iRec < stringValues.Length; ++iRec)
            {
                if (noRecords)
                {
                    recordset.AddNew(columnName, stringValues[iRec]);
                    recordset.Update(columnName, stringValues[iRec]);
                }
                else
                {
                    recordset.Fields[columnName].Value = stringValues[iRec];
                    recordset.Update(columnName, stringValues[iRec]);
                    recordset.MoveNext();
                }
            }
            
            recordset.Close();
        }

Method 2:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
        static public void WriteDataC2(string tableName, string columnName, int[] varValues)
        {
            connection.Open();
            string sqlString = "select " + columnName + " from " + tableName;
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlString, connection);
            OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(dataAdapter);
 
            DataTable datatable = new DataTable();
 
            dataAdapter.Fill(datatable);
 
            for (int iRec = 0; iRec < varValues.Length; ++iRec)
            {
                DataRow datarow = datatable.NewRow();
                datarow[columnName] = varValues[iRec];
                datatable.Rows.Add(datarow);
            }
            dataAdapter.Update(datatable);
            connection.Close();
        }
        static public void WriteDataC2(string tableName, string columnName, int[] varValues)
        {
            connection.Open();
            string sqlString = "select " + columnName + " from " + tableName;
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlString, connection);
            OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(dataAdapter);
            
            DataTable datatable = new DataTable();

            dataAdapter.Fill(datatable);

            for (int iRec = 0; iRec < varValues.Length; ++iRec)
            {
                DataRow datarow = datatable.NewRow();
                datarow[columnName] = varValues[iRec];
                datatable.Rows.Add(datarow);
            }
            dataAdapter.Update(datatable);
            connection.Close();
        }