Generic Add Record method with VistaDB DDA in C#

I wrote a generic C# method to add any standard System.Data.Datarow to a VistaDB 3.2 table using the Direct Data Access (DDA) methods for optimal performance. The routine requires the following conditions:

1. A member variable _databaseName that has the full path to the database file.
   
2. A member variable _password that has the database password if applicable.
   
3. A parameter that indicates whether to opent the table in exclusive mode or not
   
4. Each table has an autoincrement field primary key as its first field.
   
5. The datarow is from a typed datatable that matches the VistaDB table in name and fields.
   
6. A null value in any incoming field indicates to refrain from placing a value in the VistaDB row for that field.

I hope you find this useful.

Joe Kunk

public int AddRecordDDA(System.Data.DataRow dro, bool Exclusive) {
    
    // Get the table name based on the schema for the row's table
    string tableName = dro.Table.TableName;

    if ((db == null) || (db.IsClosed)) 
        db = VistaDBEngine.Connections.OpenDDA().OpenDatabase(_databaseName, _openmode, _password);

    // Open the table exclusive or shared based on the method parameter
    VistaDB.DDA.IVistaDBTable tbl = db.OpenTable(tableName,Exclusive,false);

    // Insert a new row to hold the data
    tbl.Insert();
    int autoIncrement = -1;
    
    // For each column, add it to the newly created row based on its column data type
    foreach (System.Data.DataColumn dc in dro.Table.Columns) {
        string columnName = dc.ColumnName;
        string columnType = dc.DataType.Name;
        int columnIndex = dc.Table.Columns.IndexOf(columnName);
        switch (columnType.ToLower()) {
            case "binary":  // SQL Server 2005 type Image, VarBinary
                if (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutBinary(columnIndex, (byte[])dro.ItemArray[columnIndex]);
                break;
            case "boolean": // SQL Server 2005 type Bit
                if (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutBoolean(columnIndex, (Boolean)dro.ItemArray[columnIndex]);
                break;
            case "byte": // SQL Server 2005 type TinyInt
                if (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutByte(columnIndex, (Byte)dro.ItemArray[columnIndex]);
                break;
            case "datetime": // SQL Server 2005 type DateTime
                if (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutDateTime(columnIndex, (DateTime)dro.ItemArray[columnIndex]);
                break;
            case "decimal": // SQL Server 2005 type Decimal, Money, SmallMoney
                if (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutDecimal(columnIndex, (Decimal)dro.ItemArray[columnIndex]);
                break;
            case "double": // SQL Server 2005 type Float
                if (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutDouble(columnIndex, (Double)dro.ItemArray[columnIndex]);
                break;
            case "guid": // SQL Server 2005 type UniqueIdentifier
                if (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutGuid(columnIndex, (Guid)dro.ItemArray[columnIndex]);
                break;
            case "int16": // SQL Server 2005 type SmallDateTime, SmallInt
                if ((!dc.Table.Columns[columnIndex].AutoIncrement) &&
                    (dro.ItemArray[columnIndex] != DBNull.Value))
                    tbl.PutInt16(columnIndex, (Int16)dro.ItemArray[columnIndex]);
                break;
            case "int64": // SQL Server 2005 type BigInt
                if ((!dc.Table.Columns[columnIndex].AutoIncrement) &&
                    (dro.ItemArray[columnIndex] != DBNull.Value))
                    tbl.PutInt64(columnIndex, (Int64)dro.ItemArray[columnIndex]);
                break;
            case "single": // SQL Server 2005 type 
                if (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutSingle(columnIndex, (Single)dro.ItemArray[columnIndex]);
                break;
            case "int32": // SQL Server 2005 type Int
                if ((!dc.Table.Columns[columnIndex].AutoIncrement) &&
                    (dro.ItemArray[columnIndex] != DBNull.Value))
                        tbl.PutInt32(columnIndex, (Int32)dro.ItemArray[columnIndex]);
                break;
            case "string": // SQL Server 2005 type Char, Text, 
                if  (dro.ItemArray[columnIndex] != DBNull.Value)
                    tbl.PutString(columnIndex, (String)dro.ItemArray[columnIndex]);
                break;
            default:
                MessageBox.Show(string.Format("AddRecordDDA needs data type '{0}' added to its switch statement.",columnType.ToLower()));
                break;
        }

    }
    // Commit the row to the table
    tbl.Post();

    autoIncrement = (int)tbl.Get(0).Value;

    // Close the table since row was added correctly
    tbl.Close();

    // Return the identity seed primary key for the new row.
    return autoIncrement;
}