protected virtual IDbCommand CreateDeleteCommand(IDbCommand selectCommand, string tableName)
{
StringBuilder sb = new StringBuilder();
sb.Append(String.Format("DELETE FROM {0} WHERE ", TableNameHelper.FormatTableName(tableName, QuotePrefix, QuoteSuffix)));
IDbCommand sqlDeleteCommand = CreateDbCommand();
int count = 1;
foreach (DataRow dataRow in _dataTableSchema.Rows)
{
// A key column.
if ((bool)dataRow["IsKey"])
{
if (count != 1)
{
sb.Append(" AND ");
}
sb.Append(QuotePrefix + dataRow["ColumnName"] + QuoteSuffix);
sb.Append(String.Format("={0}", GetParameterDesignator(count)));
IDataParameter sqlParameter = CreateNewSqlParameter(count, dataRow);
sqlDeleteCommand.Parameters.Add(sqlParameter);
++count;
}
}
sqlDeleteCommand.CommandText = sb.ToString();
return(sqlDeleteCommand);
}
protected virtual IDbCommand CreateDeleteAllCommand(string tableName)
{
IDbCommand command = CreateDbCommand();
command.CommandText = String.Format("DELETE FROM {0}", TableNameHelper.FormatTableName(tableName, QuotePrefix, QuoteSuffix));
return(command);
}
protected virtual void OnInsertIdentity(DataTable dataTable, IDbCommand dbCommand, IDbTransaction dbTransaction)
{
IDbTransaction sqlTransaction = dbTransaction;
//DisableTableConstraints(dataTable, dbTransaction);
foreach (DataColumn column in dataTable.Columns)
{
if (column.AutoIncrement)
{
// Set identity insert on.
IDbCommand sqlCommand =
CreateDbCommand("SET IDENTITY_INSERT " +
TableNameHelper.FormatTableName(dataTable.TableName, QuotePrefix, QuoteSuffix) +
" ON");
sqlCommand.Connection = sqlTransaction.Connection;
sqlCommand.Transaction = sqlTransaction;
sqlCommand.ExecuteNonQuery();
break;
}
}
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)
{
// Set identity insert off.
IDbCommand sqlCommand =
CreateDbCommand("SET IDENTITY_INSERT " +
TableNameHelper.FormatTableName(dataTable.TableName, QuotePrefix,
QuoteSuffix) + " OFF");
sqlCommand.Connection = sqlTransaction.Connection;
sqlCommand.Transaction = sqlTransaction;
sqlCommand.ExecuteNonQuery();
break;
}
}
//EnableTableConstraints(dataTable, dbTransaction);
}
}
protected virtual DbCommand CreateSelectCommand(DbTransaction transaction, DataSet ds, string tableName)
{
DbCommand sqlSelectCommand = CreateDbCommand();
bool notFirstColumn = false;
StringBuilder sb = new StringBuilder("SELECT ");
DataTable dataTable = ds.Tables[tableName];
foreach (DataColumn dataColumn in dataTable.Columns)
{
if (notFirstColumn)
{
sb.Append(", ");
}
notFirstColumn = true;
sb.Append(QuotePrefix + dataColumn.ColumnName + QuoteSuffix);
}
sb.Append(" FROM ");
sb.Append(TableNameHelper.FormatTableName(tableName, QuotePrefix, QuoteSuffix));
sqlSelectCommand.CommandText = sb.ToString();
sqlSelectCommand.Connection = ConnectionManager.GetConnection();
if (transaction != null)
{
sqlSelectCommand.Transaction = transaction;
}
try
{
_dataTableSchema = GetSchemaTable(sqlSelectCommand);
}
catch (Exception e)
{
string message =
String.Format(
"DbCommandBuilder.CreateSelectCommand(DataSet, string) failed for tableName = '{0}'",
tableName);
throw new NDbUnitException(message, e);
}
return(sqlSelectCommand);
}
protected virtual IDbCommand CreateInsertCommand(IDbCommand selectCommand, string tableName)
{
int count = 1;
bool notFirstColumn = false;
StringBuilder sb = new StringBuilder();
sb.Append(String.Format("INSERT INTO {0}(", TableNameHelper.FormatTableName(tableName, QuotePrefix, QuoteSuffix)));
StringBuilder sbParam = new StringBuilder();
IDataParameter sqlParameter;
IDbCommand sqlInsertCommand = CreateDbCommand();
foreach (DataRow dataRow in _dataTableSchema.Rows)
{
if (ColumnOKToInclude(dataRow))
{
// Not an identity column.
if (!((bool)dataRow[GetIdentityColumnDesignator()]))
{
if (notFirstColumn)
{
sb.Append(", ");
sbParam.Append(", ");
}
notFirstColumn = true;
sb.Append(QuotePrefix + dataRow["ColumnName"] + QuoteSuffix);
sbParam.Append(GetParameterDesignator(count));
sqlParameter = CreateNewSqlParameter(count, dataRow);
sqlInsertCommand.Parameters.Add(sqlParameter);
++count;
}
}
}
sb.Append(String.Format(") VALUES({0})", sbParam));
sqlInsertCommand.CommandText = sb.ToString();
return(sqlInsertCommand);
}
public SqlServerInsertIdentity(string tableName, string quotePrefix, string quoteSuffix, Func <string, DbCommand> createDbCommand, DbTransaction transaction)
{
_identityInsertOn =
createDbCommand(
"SET IDENTITY_INSERT " +
TableNameHelper.FormatTableName(tableName, quotePrefix, quoteSuffix) +
" ON");
_identityInsertOn.Connection = transaction.Connection;
_identityInsertOn.Transaction = transaction;
_identityInsertOff =
createDbCommand(
"SET IDENTITY_INSERT " +
TableNameHelper.FormatTableName(tableName, quotePrefix, quoteSuffix) +
" OFF");
_identityInsertOff.Connection = transaction.Connection;
_identityInsertOff.Transaction = transaction;
EnableIdentityInsert();
}
protected virtual IDbCommand CreateUpdateCommand(IDbCommand selectCommand, string tableName)
{
StringBuilder sb = new StringBuilder();
sb.Append(String.Format("UPDATE {0} SET ", TableNameHelper.FormatTableName(tableName, QuotePrefix, QuoteSuffix)));
IDbCommand sqlUpdateCommand = CreateDbCommand();
int count = 1;
bool notFirstKey = false;
bool notFirstColumn = false;
StringBuilder sbPrimaryKey = new StringBuilder();
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.
IDataParameter sqlParameter;
if ((bool)dataRow["IsKey"])
{
if (notFirstKey)
{
sbPrimaryKey.Append(" AND ");
}
notFirstKey = true;
sbPrimaryKey.Append(QuotePrefix + dataRow["ColumnName"] + QuoteSuffix);
sbPrimaryKey.Append(String.Format("={0}", GetParameterDesignator(count)));
sqlParameter = CreateNewSqlParameter(count, dataRow);
sqlUpdateCommand.Parameters.Add(sqlParameter);
++count;
}
if (containsAllPrimaryKeys || !(bool)dataRow["IsKey"])
{
if (notFirstColumn)
{
sb.Append(", ");
}
notFirstColumn = true;
sb.Append(QuotePrefix + dataRow["ColumnName"] + QuoteSuffix);
sb.Append(String.Format("={0}", GetParameterDesignator(count)));
sqlParameter = CreateNewSqlParameter(count, dataRow);
sqlUpdateCommand.Parameters.Add(sqlParameter);
++count;
}
}
}
sb.Append(String.Format(" WHERE {0}", sbPrimaryKey));
sqlUpdateCommand.CommandText = sb.ToString();
return(sqlUpdateCommand);
}
