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