protected override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
{
this.dataTable = dataTable;
this.datasetConfig = datasetConfig;
this.fieldList = datasetConfig.DatasetConfigRows;
//Look if Table exists, when not, create it!
try
{
string sql = "SELECT * FROM " + dataTable + ";";
myCmd = new FbCommand(sql, myDBConn);
myReader = myCmd.ExecuteReader();
}
catch (FbException ex)
{
/*if (ex.ErrorCode == 0)
{
try
{
string sql = "CREATE TABLE " + dataTable + " (id INTEGER PRIMARY KEY ASC AUTOINCREMENT); ";
myCmd = new FbCommand(sql, myDBConn);
myCmd.ExecuteNonQuery();
sql = "SELECT * FROM " + dataTable + ";";
myCmd = new FbCommand(sql, myDBConn);
myReader = myCmd.ExecuteReader();
}
catch (System.Data.SQLite.SQLiteException ex_ex)
{
throw ex_ex;
}
}
else
{
throw ex;
}*/
}
//Look for the Fields, create or alter them!
List<String> existDBFelderliste = new List<string>();
for (int n = 0; n < myReader.FieldCount; n++)
{
existDBFelderliste.Add(myReader.GetName(n));
}
myReader.Close();
foreach (DatasetConfigRow myFeld in fieldList)
{
foreach (string existMyFeld in existDBFelderliste)
{
if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower())
{
goto nextFeld;
}
}
//Feld existiert nicht -> erzeugen
string sql = "ALTER TABLE " + dataTable + " ADD COLUMN " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType;
try
{
myCmd = new FbCommand(sql, myDBConn);
myCmd.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
nextFeld:
//Irgendeine anweisung, da sonst der Sprung nicht geht...
{
}
}
//Create Insert Command
string wertliste = "", felderliste = "";
foreach (DatasetConfigRow myFeld in fieldList)
{
if (wertliste != "")
{
wertliste += ",";
felderliste += ",";
}
felderliste += myFeld.DatabaseField;
wertliste += "@" + myFeld.DatabaseField;
}
insertCommand = "INSERT INTO " + dataTable + "(" + felderliste + ") values(" + wertliste + ")";
}
private static bool IsReadOnly(FbDataReader r)
{
return(IsExpression(r));
}
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
{
if (datasetConfig.DatasetTableName != "") //Add the posibility to use a specific table_name (for using the table more then ones)
{
this.dataTable = datasetConfig.DatasetTableName;
}
else
{
this.dataTable = dataTable;
}
this.datasetConfig = datasetConfig;
this.fieldList = datasetConfig.DatasetConfigRows;
//Look if Table exists, when not, create it!
try
{
string sql = "SELECT * FROM " + dataTable + ";";
myCmd = new FbCommand(sql, myDBConn);
myReader = myCmd.ExecuteReader();
}
catch (FbException ex)
{
/*if (ex.ErrorCode == 0)
* {
* try
* {
* string sql = "CREATE TABLE " + dataTable + " (id INTEGER PRIMARY KEY ASC AUTOINCREMENT); ";
*
* myCmd = new FbCommand(sql, myDBConn);
* myCmd.ExecuteNonQuery();
*
* sql = "SELECT * FROM " + dataTable + ";";
* myCmd = new FbCommand(sql, myDBConn);
* myReader = myCmd.ExecuteReader();
* }
* catch (System.Data.SQLite.SQLiteException ex_ex)
* {
* throw ex_ex;
* }
* }
* else
* {
* throw ex;
* }*/
}
//Look for the Fields, create or alter them!
List <String> existDBFelderliste = new List <string>();
for (int n = 0; n < myReader.FieldCount; n++)
{
existDBFelderliste.Add(myReader.GetName(n));
}
myReader.Close();
foreach (DatasetConfigRow myFeld in fieldList)
{
foreach (string existMyFeld in existDBFelderliste)
{
if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower())
{
goto nextFeld;
}
}
//Feld existiert nicht -> erzeugen
string sql = "ALTER TABLE " + dataTable + " ADD COLUMN " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType;
try
{
myCmd = new FbCommand(sql, myDBConn);
myCmd.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
nextFeld:
//Irgendeine anweisung, da sonst der Sprung nicht geht...
{
}
}
//Create Insert Command
string wertliste = "", felderliste = "";
foreach (DatasetConfigRow myFeld in fieldList)
{
if (wertliste != "")
{
wertliste += ",";
felderliste += ",";
}
felderliste += myFeld.DatabaseField;
wertliste += "@" + myFeld.DatabaseField;
}
insertCommand = "INSERT INTO " + this.dataTable + "(" + felderliste + ") values(" + wertliste + ")";
}
public override DataTable GetSchemaTable()
{
CheckState();
if (_schemaTable != null)
{
return(_schemaTable);
}
DataRow schemaRow = null;
int tableCount = 0;
string currentTable = string.Empty;
_schemaTable = GetSchemaTableStructure();
/* Prepare statement for schema fields information */
FbCommand schemaCmd = new FbCommand(
GetSchemaCommandText(),
_command.Connection,
_command.Connection.InnerConnection.ActiveTransaction);
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
schemaCmd.Prepare();
_schemaTable.BeginLoadData();
for (int i = 0; i < _fields.Count; i++)
{
bool isKeyColumn = false;
bool isUnique = false;
bool isReadOnly = false;
int precision = 0;
bool isExpression = false;
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = _fields[i].Relation;
schemaCmd.Parameters[1].Value = _fields[i].Name;
using (FbDataReader r = schemaCmd.ExecuteReader())
{
if (r.Read())
{
isReadOnly = (IsReadOnly(r) || IsExpression(r)) ? true : false;
isKeyColumn = (r.GetInt32(2) == 1) ? true : false;
isUnique = (r.GetInt32(3) == 1) ? true : false;
precision = r.IsDBNull(4) ? -1 : r.GetInt32(4);
isExpression = IsExpression(r);
}
}
/* Create new row for the Schema Table */
schemaRow = _schemaTable.NewRow();
schemaRow["ColumnName"] = GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = _fields[i].GetSize();
if (_fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = _fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = GetFieldType(i);
schemaRow["ProviderType"] = GetProviderType(i);
schemaRow["IsLong"] = _fields[i].IsLong();
schemaRow["AllowDBNull"] = _fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = _fields[i].IsAliased();
schemaRow["IsExpression"] = isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = _fields[i].Relation;
schemaRow["BaseColumnName"] = _fields[i].Name;
_schemaTable.Rows.Add(schemaRow);
if (!String.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation)
{
tableCount++;
currentTable = _fields[i].Relation;
}
/* Close statement */
schemaCmd.Close();
}
if (tableCount > 1)
{
foreach (DataRow row in _schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
_schemaTable.EndLoadData();
/* Dispose command */
schemaCmd.Dispose();
return(_schemaTable);
}
