/// <summary>
/// Finilizes the seeding process.
/// </summary>
public void FinishSeeding()
{
TurnOnConstraints();
TurnOnAutoIncrement();
Logger?.LogInformation("Finish seeding: " + GetTableFullName(CurrentSeedingTable));
CurrentSeedingTable = null;
}
/// <summary>
/// Finilizes the updating process.
/// </summary>
public void FinishUpdating()
{
Logger?.LogDebug("Finish updating: " + GetFormattedTableName(CurrentTable));
CurrentTable = null;
_updateCommand.Dispose();
_updateCommand = null;
_parameterColumnMap.Clear();
}
/// <summary>
/// Finilizes the seeding process.
/// </summary>
public void FinishSeeding()
{
TurnOnConstraints();
TurnOnAutoIncrement();
Logger?.LogDebug("Finish seeding: " + GetTableFullName(CurrentSeedingTable));
CurrentSeedingTable = null;
_insertCommand.Dispose();
_parameterColumnMap.Clear();
}
/// <summary>
/// Starts the seeding process for the specified table
/// </summary>
/// <param name="table">The table.</param>
/// <exception cref="Korzh.DbUtils.DbBridgeException">Seeding is not finised. Call FinishSeeding() before start another one.</exception>
public void StartSeeding(DatasetInfo table)
{
if (CurrentSeedingTable != null)
{
throw new DbBridgeException("Seeding is not finised. Call FinishSeeding() before start another one.");
}
CurrentSeedingTable = table;
Logger?.LogInformation("Start seeding: " + GetTableFullName(CurrentSeedingTable));
TurnOffConstraints();
TurnOffAutoIncrement();
}
/// <summary>
/// Gets the full name of the table (including schema and all necessary quotes).
/// </summary>
/// <param name="table">The table.</param>
/// <returns>System.String.</returns>
protected virtual string GetTableFullName(DatasetInfo table)
{
var result = "";
if (!string.IsNullOrEmpty(table.Schema))
{
result += Quote1 + table.Schema + Quote2 + ".";
}
result += Quote1 + table.Name + Quote2;
return(result);
}
/// <summary>
/// Gets the full name of the table (including schema and all necessary quotes).
/// </summary>
/// <param name="table">The table.</param>
/// <returns>System.String.</returns>
public virtual string GetFormattedTableName(DatasetInfo table)
{
var result = "";
if (!string.IsNullOrEmpty(table.Schema))
{
result += Quote1 + table.Schema + Quote2 + ".";
}
result += Quote1 + table.Name + Quote2;
return(result);
}
/// <summary>
/// Starts the updating process for the specified table
/// </summary>
/// <param name="table"></param>
public void StartUpdating(DatasetInfo table)
{
if (CurrentTable != null)
{
throw new DbBridgeException("Updating is not finised. Call FinishUpdating() before start another one.");
}
CurrentTable = table;
Logger?.LogDebug("Start updating: " + GetFormattedTableName(CurrentTable));
_updateCommand = GetConnection().CreateCommand();
_updateCommand.CommandText = GenerateUpdateStatement(CurrentTable, _updateCommand);
_updateCommand.CommandType = CommandType.Text;
}
/// <summary>
/// Starts the seeding process for the specified table
/// </summary>
/// <param name="table">The table.</param>
/// <exception cref="Korzh.DbUtils.DbBridgeException">Seeding is not finised. Call FinishSeeding() before start another one.</exception>
public void StartSeeding(DatasetInfo table)
{
if (CurrentSeedingTable != null)
{
throw new DbBridgeException("Seeding is not finised. Call FinishSeeding() before start another one.");
}
CurrentSeedingTable = table;
Logger?.LogDebug("Start seeding: " + GetTableFullName(CurrentSeedingTable));
TurnOffConstraints();
TurnOffAutoIncrement();
_insertCommand = GetConnection().CreateCommand();
_insertCommand.CommandText = GenerateInsertStatement(CurrentSeedingTable, _insertCommand);
_insertCommand.CommandType = CommandType.Text;
}
/// <summary>
/// Generates the INSERT statement.
/// </summary>
/// <param name="table">The table we would like to insert a new record.</param>
/// <param name="command">The command for which we generate our INSERT statement for</param>
/// <returns>System.String.</returns>
protected string GenerateInsertStatement(DatasetInfo table, IDbCommand command)
{
var sb = new StringBuilder(100);
sb.AppendFormat("INSERT INTO {0} ( ", GetTableFullName(table));
var columns = new List <ColumnInfo>();
ExtractColumnList(table.Schema, table.Name, columns);
for (var i = columns.Count - 1; i >= 0; i--)
{
//ignore not found columns and the columns which are auto-updated by DB (like Timestamps)
if (!table.Columns.ContainsKey(columns[i].Name) || columns[i].IsTimestamp)
{
columns.RemoveAt(i);
}
}
foreach (var column in columns)
{
sb.AppendFormat("{0}{1}{2}, ", Quote1, column.Name, Quote2);
}
sb.Remove(sb.Length - 2, 2);
sb.Append(") VALUES ( ");
foreach (var column in columns)
{
var paramName = ToParameterName(column.Name);
_parameterColumnMap[paramName] = column.Name;
sb.AppendFormat("{0}, ", paramName);
var parameter = command.CreateParameter();
parameter.DbType = column.DataType.ToDbType();
//parameter.DbType = column.DbType; ???? maybe we need to save DbType on ExtractColumnList
parameter.ParameterName = paramName;
command.Parameters.Add(parameter);
}
sb.Remove(sb.Length - 2, 2);
sb.Append(");");
return(sb.ToString());
}
/// <summary>
/// Generates the UPDATE statement.
/// </summary>
/// <param name="table"></param>
/// <param name="command"></param>
/// <returns></returns>
protected string GenerateUpdateStatement(DatasetInfo table, IDbCommand command)
{
var sb = new StringBuilder(1024);
sb.AppendFormat("UPDATE {0} SET ", GetFormattedTableName(table));
var columns = new List <ColumnInfo>();
ExtractColumnList(table.Schema, table.Name, columns);
foreach (var column in columns.Where(c => !c.IsPrimaryKey))
{
var paramName = ToParameterName(column.Name);
_parameterColumnMap[paramName] = column.Name;
var parameter = command.CreateParameter();
parameter.DbType = column.DataType.ToDbType();
//parameter.DbType = column.DbType; ???? maybe we need to save DbType on ExtractColumnList
parameter.ParameterName = paramName;
command.Parameters.Add(parameter);
sb.AppendFormat("{0}={1}, ", GetFormattedColumnName(column), paramName);
}
sb.Remove(sb.Length - 2, 2);
sb.Append(" WHERE ");
foreach (var column in columns.Where(c => c.IsPrimaryKey))
{
var paramName = ToParameterName(column.Name);
_parameterColumnMap[paramName] = column.Name;
var parameter = command.CreateParameter();
parameter.DbType = column.DataType.ToDbType();
//parameter.DbType = column.DbType; ???? maybe we need to save DbType on ExtractColumnList
parameter.ParameterName = paramName;
command.Parameters.Add(parameter);
sb.AppendFormat("{0}={1} AND ", GetFormattedColumnName(column), paramName);
}
sb.Remove(sb.Length - 5, 5);
sb.Append(";");
return(sb.ToString());
}
/// <summary>
/// Creates and returns a <see cref="IDataReader" /> object for some table.
/// This method usually just constructs a correct SQL statement to get the content of some table (like 'SELECT * FROM TableName)
/// and then calls <see cref="GetDataReaderForSql(string)" /> function.
/// </summary>
/// <param name="table">The table represented by <see cref="DatasetInfo" /> structure.</param>
/// <returns>IDataReader.</returns>
public IDataReader GetDataReaderForTable(DatasetInfo table)
{
CheckConnection();
var columns = new List <ColumnInfo>();
ExtractColumnList(table.Schema, table.Name, columns);
var sql = new StringBuilder();
sql.Append("SELECT");
foreach (var column in columns)
{
sql.AppendFormat(" {0}{1}{2},", Quote1, column.Name, Quote2);
}
sql.Remove(sql.Length - 1, 1);
sql.AppendFormat(" FROM {0}", GetTableFullName(table));
return(GetDataReaderForSql(sql.ToString()));
}
/// <summary>
/// Generates the INSERT statement.
/// </summary>
/// <param name="table">The table we would like to insert a new record.</param>
/// <param name="record">The record to insert.</param>
/// <returns>System.String.</returns>
protected string GenerateInsertStatement(DatasetInfo table, IDataRecord record)
{
var sb = new StringBuilder(100);
sb.AppendFormat("INSERT INTO {0} ( ", GetTableFullName(table));
for (var i = 0; i < record.FieldCount; i++)
{
sb.AppendFormat("{0}{1}{2}, ", Quote1, record.GetName(i), Quote2);
}
sb.Remove(sb.Length - 2, 2);
sb.Append(") VALUES ( ");
for (var i = 0; i < record.FieldCount; i++)
{
sb.AppendFormat("{0}, ", ToParameterName(record.GetName(i)));
}
sb.Remove(sb.Length - 2, 2);
sb.Append(");");
return(sb.ToString());
}
/// <summary>
/// Creates and returns a <see cref="IDataReader" /> object for some table.
/// This method usually just constructs a correct SQL statement to get the content of some table (like 'SELECT * FROM TableName)
/// and then calls <see cref="GetDataReaderForSql(string)" /> function.
/// </summary>
/// <param name="table">The table represented by <see cref="DatasetInfo" /> structure.</param>
/// <returns>IDataReader.</returns>
public IDataReader GetDataReaderForTable(DatasetInfo table)
{
return(GetDataReaderForSql("SELECT * FROM " + GetTableFullName(table)));
}
