public void Returns_Original_TableName()
{
const string INITIAL_TABLENAME = "[schema].[tablename]";
Assert.AreEqual(INITIAL_TABLENAME, TableNameHelper.FormatTableName(INITIAL_TABLENAME, string.Empty, string.Empty));
}
protected override void OnInsertIdentity(DataTable dataTable, IDbCommand dbCommand, IDbTransaction dbTransaction)
{
//throw new NotSupportedException("OnInsertIdentity not supported!");
IDbTransaction sqlTransaction = dbTransaction;
//DisableTableConstraints(dataTable, dbTransaction);
try
{
IDbDataAdapter sqlDataAdapter = CreateDbDataAdapter();
sqlDataAdapter.InsertCommand = dbCommand;
sqlDataAdapter.InsertCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.InsertCommand.Transaction = sqlTransaction;
((DbDataAdapter)sqlDataAdapter).Update(dataTable);
}
finally
{
foreach (DataColumn column in dataTable.Columns)
{
if (column.AutoIncrement)
{
IDbCommand selectMaxCommand = CreateDbCommand(string.Format("SELECT MAX({0}{2}{1}) FROM {3}", QuotePrefix, QuoteSuffix, column.ColumnName, TableNameHelper.FormatTableName(dataTable.TableName, QuotePrefix, QuoteSuffix)));
selectMaxCommand.Connection = sqlTransaction.Connection;
selectMaxCommand.Transaction = sqlTransaction;
int count = (int)selectMaxCommand.ExecuteScalar();
IDbCommand sqlCommand =
CreateDbCommand(string.Format("ALTER SEQUENCE \"{0}_{1}_seq\" RESTART WITH {2}",
dataTable.TableName, column.ColumnName, count));
sqlCommand.Connection = sqlTransaction.Connection;
sqlCommand.Transaction = sqlTransaction;
sqlCommand.ExecuteNonQuery();
break;
}
}
//EnableTableConstraints(dataTable, dbTransaction);
}
}
private void enableDisableTableConstraints(String enableDisable, DataTable dataTable, DbTransaction dbTransaction)
{
DbCommand dbCommand = null;
DbParameter dbParam = null;
DbDataReader dbReader = null;
IList <String> altersList = new List <String>();
String queryEnables =
" SELECT 'ALTER TABLE '"
+ " || table_name"
+ " || ' " + enableDisable + " CONSTRAINT '"
+ " || constraint_name AS alterComm"
+ " FROM user_constraints"
+ " WHERE UPPER(table_name) = UPPER(:tabela)"
+ " AND constraint_type IN ('C', 'R')";
using (dbCommand = new OracleCommand())
{
dbCommand.CommandText = queryEnables;
dbCommand.Connection = dbTransaction.Connection;
dbCommand.Transaction = dbTransaction;
dbParam = new OracleParameter();
dbParam.ParameterName = "tabela";
dbParam.Value = dataTable.TableName;
dbParam.DbType = DbType.String;
dbCommand.Parameters.Add(dbParam);
using (dbReader = dbCommand.ExecuteReader())
{
while (dbReader.Read())
{
altersList.Add(dbReader.GetString(dbReader.GetOrdinal("alterComm")));
}
dbReader.Close();
}
}
foreach (String returnedCommand in altersList)
{
var escapedCommand = returnedCommand.Replace(" " + dataTable.TableName + " ", TableNameHelper.FormatTableName(dataTable.TableName, QuotePrefix, QuoteSuffix));
using (dbCommand = new OracleCommand())
{
dbCommand.CommandText = escapedCommand;
dbCommand.Connection = dbTransaction.Connection;
dbCommand.Transaction = dbTransaction;
dbCommand.ExecuteNonQuery();
}
}
}
protected override IDbCommand CreateDeleteAllCommand(string tableName)
{
return
(new SQLiteCommand("DELETE FROM " + TableNameHelper.FormatTableName(tableName, QuotePrefix, QuoteSuffix)));
}
protected override IDbCommand CreateUpdateCommand(IDbCommand selectCommand, string tableName)
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE " + TableNameHelper.FormatTableName(tableName, QuotePrefix, QuoteSuffix) + " SET ");
SQLiteCommand sqlUpdateCommand = CreateDbCommand() as SQLiteCommand;
int count = 1;
bool notFirstKey = false;
bool notFirstColumn = false;
DbParameter sqlParameter = null;
StringBuilder sbPrimaryKey = new StringBuilder();
bool containsAllPrimaryKeys = true;
foreach (DataRow dataRow in _dataTableSchema.Rows)
{
if (!(bool)dataRow[SchemaColumns.IsKey])
{
containsAllPrimaryKeys = false;
break;
}
}
foreach (DataRow dataRow in _dataTableSchema.Rows)
{
if (ColumnOKToInclude(dataRow))
{
// A key column.
if ((bool)dataRow[SchemaColumns.IsKey])
{
if (notFirstKey)
{
sbPrimaryKey.Append(" AND ");
}
notFirstKey = true;
sbPrimaryKey.Append(QuotePrefix + dataRow[SchemaColumns.ColumnName] + QuoteSuffix);
sbPrimaryKey.Append("=@p" + count);
sqlParameter = (SQLiteParameter)CreateNewSqlParameter(count, dataRow);
sqlUpdateCommand.Parameters.Add(sqlParameter);
++count;
}
if (containsAllPrimaryKeys || !(bool)dataRow[SchemaColumns.IsKey])
{
if (notFirstColumn)
{
sb.Append(", ");
}
notFirstColumn = true;
sb.Append(QuotePrefix + dataRow[SchemaColumns.ColumnName] + QuoteSuffix);
sb.Append("=@p" + count);
sqlParameter = (SQLiteParameter)CreateNewSqlParameter(count, dataRow);
sqlUpdateCommand.Parameters.Add(sqlParameter);
++count;
}
}
}
sb.Append(" WHERE " + sbPrimaryKey);
sqlUpdateCommand.CommandText = sb.ToString();
return(sqlUpdateCommand);
}
protected override IDbCommand CreateUpdateCommand(IDbCommand selectCommand, string tableName)
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE " + TableNameHelper.FormatTableName(tableName, QuotePrefix, QuoteSuffix) + " SET ");
OleDbCommand oleDbUpdateCommand = CreateDbCommand() as OleDbCommand;
int count = 1;
bool notFirstKey = false;
bool notFirstColumn = false;
DbParameter oleDbParameter;
StringBuilder sbPrimaryKey = new StringBuilder();
ArrayList keyParameters = new ArrayList();
bool containsAllPrimaryKeys = true;
foreach (DataRow dataRow in _dataTableSchema.Rows)
{
if (!(bool)dataRow["IsKey"])
{
containsAllPrimaryKeys = false;
break;
}
}
foreach (DataRow dataRow in _dataTableSchema.Rows)
{
if (ColumnOKToInclude(dataRow))
{
// A key column.
if ((bool)dataRow["IsKey"])
{
if (notFirstKey)
{
sbPrimaryKey.Append(" AND ");
}
notFirstKey = true;
sbPrimaryKey.Append(QuotePrefix + dataRow["ColumnName"] + QuoteSuffix);
sbPrimaryKey.Append("=?");
oleDbParameter = (OleDbParameter)CreateNewSqlParameter(count, dataRow);
keyParameters.Add(oleDbParameter);
++count;
}
if (containsAllPrimaryKeys || !(bool)dataRow["IsKey"])
{
if (notFirstColumn)
{
sb.Append(", ");
}
notFirstColumn = true;
sb.Append(QuotePrefix + dataRow["ColumnName"] + QuoteSuffix);
sb.Append("=?");
oleDbParameter = (OleDbParameter)CreateNewSqlParameter(count, dataRow);
oleDbUpdateCommand.Parameters.Add(oleDbParameter);
++count;
}
}
}
// Add key parameters last since ordering is important.
for (int i = 0; i < keyParameters.Count; ++i)
{
oleDbUpdateCommand.Parameters.Add((OleDbParameter)keyParameters[i]);
}
sb.Append(" WHERE " + sbPrimaryKey);
oleDbUpdateCommand.CommandText = sb.ToString();
return(oleDbUpdateCommand);
}