Thursday, November 22, 2007

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;
}