本文整理汇总了C#中System.Data.Common.DbProviderFactory.CreateCommandBuilder方法的典型用法代码示例。如果您正苦于以下问题:C# DbProviderFactory.CreateCommandBuilder方法的具体用法?C# DbProviderFactory.CreateCommandBuilder怎么用?C# DbProviderFactory.CreateCommandBuilder使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类System.Data.Common.DbProviderFactory
的用法示例。
在下文中一共展示了DbProviderFactory.CreateCommandBuilder方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: CreateObject
private static object CreateObject(DbProviderFactory factory, ProviderSupportedClasses kindOfObject, string providerName)
{
switch (kindOfObject)
{
case ProviderSupportedClasses.DbConnection:
return factory.CreateConnection();
case ProviderSupportedClasses.DbDataAdapter:
return factory.CreateDataAdapter();
case ProviderSupportedClasses.DbParameter:
return factory.CreateParameter();
case ProviderSupportedClasses.DbCommand:
return factory.CreateCommand();
case ProviderSupportedClasses.DbCommandBuilder:
return factory.CreateCommandBuilder();
case ProviderSupportedClasses.DbDataSourceEnumerator:
return factory.CreateDataSourceEnumerator();
case ProviderSupportedClasses.CodeAccessPermission:
return factory.CreatePermission(PermissionState.None);
}
throw new InternalException(string.Format(CultureInfo.CurrentCulture, "Cannot create object of provider class identified by enum {0} for provider {1}", new object[] { Enum.GetName(typeof(ProviderSupportedClasses), kindOfObject), providerName }));
}
示例2: Initialize
protected void Initialize (string databaseName, string querySelectRowString)
{
string providerName = GetProviderNameByDBName(databaseName);
string connectionString = GetConnectionStringByDBName(databaseName);
// Create the DbProviderFactory and DbConnection.
factory = DbProviderFactories.GetFactory(providerName);
connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
// Create the DbCommand.
DbCommand SelectTableCommand = factory.CreateCommand();
SelectTableCommand.CommandText = querySelectRowString;
SelectTableCommand.Connection = connection;
adapter = factory.CreateDataAdapter();
adapter.SelectCommand = SelectTableCommand;
// Create the DbCommandBuilder.
builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.ContinueUpdateOnError = true;
}
示例3: ReplicateToSqlIndexUpdateBatcher
public ReplicateToSqlIndexUpdateBatcher(
DbProviderFactory providerFactory,
string connectionString,
IndexReplicationDestination destination)
{
_providerFactory = providerFactory;
_commandBuilder = providerFactory.CreateCommandBuilder();
_connectionString = connectionString;
this.destination = destination;
}
示例4: RelationalDatabaseWriterSimulator
public RelationalDatabaseWriterSimulator( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
commandBuilder = providerFactory.CreateCommandBuilder();
if (SqlServerFactoryNames.Contains(cfg.FactoryName))
{
IsSqlServerFactoryType = true;
}
}
示例5: RelationalDatabaseWriter
public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
if (SqlServerFactoryNames.Contains(cfg.FactoryName))
{
IsSqlServerFactoryType = true;
}
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
stringParserList = GenerateStringParsers();
sqlReplicationMetrics = database.StartupTasks.OfType<SqlReplicationTask>().FirstOrDefault().GetSqlReplicationMetricsManager(cfg);
}
示例6: RelationalDatabaseWriter
public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
}
示例7: DbHelper
public DbHelper(int tenant, ConnectionStringSettings connectionString)
{
this.tenant = tenant;
factory = connectionString.ProviderName == "System.Data.SQLite" ? GetSQLiteFactory() : GetMySqlFactory();
builder = factory.CreateCommandBuilder();
connect = factory.CreateConnection();
connect.ConnectionString = connectionString.ConnectionString;
connect.Open();
if (factory.GetType().Name == "MySqlClientFactory")
{
CreateCommand("set @@session.sql_mode = concat(@@session.sql_mode, ',NO_AUTO_VALUE_ON_ZERO')").ExecuteNonQuery();
}
columns = connect.GetSchema("Columns");
whereExceptions["calendar_calendar_item"] = " where calendar_id in (select id from calendar_calendars where tenant = " + tenant + ") ";
whereExceptions["calendar_calendar_user"] = " where calendar_id in (select id from calendar_calendars where tenant = " + tenant + ") ";
whereExceptions["calendar_event_item"] = " where event_id in (select id from calendar_events where tenant = " + tenant + ") ";
whereExceptions["calendar_event_user"] = " where event_id in (select id from calendar_events where tenant = " + tenant + ") ";
whereExceptions["crm_entity_contact"] = " where contact_id in (select id from crm_contact where tenant_id = " + tenant + ") ";
whereExceptions["crm_entity_tag"] = " where tag_id in (select id from crm_tag where tenant_id = " + tenant + ") ";
whereExceptions["files_folder_tree"] = " where folder_id in (select id from files_folder where tenant_id = " + tenant + ") ";
whereExceptions["forum_answer_variant"] = " where answer_id in (select id from forum_answer where tenantid = " + tenant + ")";
whereExceptions["forum_topic_tag"] = " where topic_id in (select id from forum_topic where tenantid = " + tenant + ")";
whereExceptions["forum_variant"] = " where question_id in (select id from forum_question where tenantid = " + tenant + ")";
whereExceptions["projects_project_participant"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
whereExceptions["projects_following_project_participant"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
whereExceptions["projects_project_tag"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
whereExceptions["projects_project_tag_change_request"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
whereExceptions["tenants_tenants"] = " where id = " + tenant;
whereExceptions["webstudio_widgetstate"] = " where widgetcontainerid in (select id from webstudio_widgetcontainer where tenantid = " + tenant + ")";
whereExceptions["core_usersecurity"] = " where userid in (select id from core_user where tenant = " + tenant + ")";
whereExceptions["core_acl"] = " where tenant = " + tenant + " or tenant = -1";
whereExceptions["core_subscription"] = " where tenant = " + tenant + " or tenant = -1";
whereExceptions["core_subscriptionmethod"] = " where tenant = " + tenant + " or tenant = -1";
}
示例8: DataAdapter
// Utilizes the SQLiteCommandBuilder,
// which in turn utilizes SQLiteDataReader's GetSchemaTable() functionality
// This insert is slow because it must raise callbacks before and after every update.
// For a fast update, see the FastInsertMany function beneath this one
internal static void DataAdapter(DbProviderFactory fact, DbConnection cnn, bool bWithIdentity)
{
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbDataAdapter adp = fact.CreateDataAdapter())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.Transaction = dbTrans;
cmd.CommandText = "SELECT * FROM TestCase WHERE 1 = 2";
adp.SelectCommand = cmd;
using (DbCommandBuilder bld = fact.CreateCommandBuilder())
{
bld.DataAdapter = adp;
using (adp.InsertCommand = (DbCommand)((ICloneable)bld.GetInsertCommand()).Clone())
{
if (bWithIdentity)
{
adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]";
adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}
bld.DataAdapter = null;
using (DataTable tbl = new DataTable())
{
adp.Fill(tbl);
for (int n = 0; n < 10000; n++)
{
DataRow row = tbl.NewRow();
row[1] = n + (50000 * ((bWithIdentity == true) ? 2 : 1));
tbl.Rows.Add(row);
}
Console.WriteLine(String.Format(" Inserting using CommandBuilder and DataAdapter\r\n ->{0} (10,000 rows) ...", (bWithIdentity == true) ? "(with identity fetch)" : ""));
int dtStart = Environment.TickCount;
adp.Update(tbl);
int dtEnd = Environment.TickCount;
dtEnd -= dtStart;
Console.Write(String.Format(" -> Insert Ends in {0} ms ... ", (dtEnd)));
dtStart = Environment.TickCount;
dbTrans.Commit();
dtEnd = Environment.TickCount;
dtEnd -= dtStart;
Console.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
}
}
}
}
}
}
}
示例9: DbHelper
public DbHelper(ConnectionStringSettings connectionString)
{
var file = connectionString.ElementInformation.Source;
if ("web.connections.config".Equals(Path.GetFileName(file), StringComparison.InvariantCultureIgnoreCase))
{
file = Path.Combine(Path.GetDirectoryName(file), "web.config");
}
var xconfig = XDocument.Load(file);
var provider = xconfig.XPathSelectElement("/configuration/system.data/DbProviderFactories/add[@invariant='" + connectionString.ProviderName + "']");
factory = (DbProviderFactory)Activator.CreateInstance(Type.GetType(provider.Attribute("type").Value, true));
builder = factory.CreateCommandBuilder();
connect = factory.CreateConnection();
connect.ConnectionString = connectionString.ConnectionString;
connect.Open();
mysql = connectionString.ProviderName.ToLower().Contains("mysql");
if (mysql)
{
CreateCommand("set @@session.sql_mode = concat(@@session.sql_mode, ',NO_AUTO_VALUE_ON_ZERO')").ExecuteNonQuery();
}
columns = connect.GetSchema("Columns");
whereExceptions["calendar_calendar_item"] = " where calendar_id in (select id from calendar_calendars where tenant = {0}) ";
whereExceptions["calendar_calendar_user"] = " where calendar_id in (select id from calendar_calendars where tenant = {0}) ";
whereExceptions["calendar_event_item"] = " inner join calendar_events on calendar_event_item.event_id = calendar_events.id where calendar_events.tenant = {0} ";
whereExceptions["calendar_event_user"] = " inner join calendar_events on calendar_event_user.event_id = calendar_events.id where calendar_events.tenant = {0} ";
whereExceptions["crm_entity_contact"] = " inner join crm_contact on crm_entity_contact.contact_id = crm_contact.id where crm_contact.tenant_id = {0} ";
whereExceptions["crm_entity_tag"] = " inner join crm_tag on crm_entity_tag.tag_id = crm_tag.id where crm_tag.tenant_id = {0} ";
whereExceptions["files_folder_tree"] = " inner join files_folder on folder_id = id where tenant_id = {0} ";
whereExceptions["forum_answer_variant"] = " where answer_id in (select id from forum_answer where tenantid = {0})";
whereExceptions["forum_topic_tag"] = " where topic_id in (select id from forum_topic where tenantid = {0})";
whereExceptions["forum_variant"] = " where question_id in (select id from forum_question where tenantid = {0})";
whereExceptions["projects_project_participant"] = " inner join projects_projects on projects_project_participant.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["projects_following_project_participant"] = " inner join projects_projects on projects_following_project_participant.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["projects_project_tag"] = " inner join projects_projects on projects_project_tag.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["tenants_tenants"] = " where id = {0}";
whereExceptions["core_acl"] = " where tenant = {0} or tenant = -1";
whereExceptions["core_subscription"] = " where tenant = {0} or tenant = -1";
whereExceptions["core_subscriptionmethod"] = " where tenant = {0} or tenant = -1";
}
示例10: WriteToRelationalDatabase
private bool WriteToRelationalDatabase(SqlReplicationConfig cfg, DbProviderFactory providerFactory, Dictionary<string, List<ItemToReplicate>> dictionary, SqlReplicationStatistics replicationStatistics)
{
using (var commandBuilder = providerFactory.CreateCommandBuilder())
using (var connection = providerFactory.CreateConnection())
{
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
Database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
bool hadErrors = false;
using (var tx = connection.BeginTransaction())
{
const string cmdText = "DELETE FROM {0} WHERE {1} IN ({2})";
foreach (var kvp in dictionary)
{
// array of ids to delete
List<string> ids = kvp.Value.Select(x => x.DocumentId).ToList();
foreach (var partitionOfIds in ids.Partition(1000))
{
using (var deleteCmd = connection.CreateCommand())
{
// create an array of param names to store our ids (@id1, @id2 etc)
string[] paramNames = partitionOfIds.Select(
(s, i) => "@id" + i.ToString()
).ToArray();
// add a parameter for each id to our command
string inClause = string.Join(",", paramNames);
for (int i = 0; i < paramNames.Length; i++)
{
var param = deleteCmd.CreateParameter();
param.ParameterName = paramNames[i];
param.Value = partitionOfIds[i];
deleteCmd.Parameters.Add(param);
}
deleteCmd.Transaction = tx;
deleteCmd.CommandText = string.Format(cmdText,
commandBuilder.QuoteIdentifier(kvp.Key),
commandBuilder.QuoteIdentifier(
kvp.Value[0].PkName),
inClause);
try
{
deleteCmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException(
"Failure to replicate changes to relational database for: " + cfg.Name +
" (delete), will continue trying." + Environment.NewLine +
deleteCmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(kvp.Key))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
//.........这里部分代码省略.........
示例11: WriteToRelationalDatabase
private void WriteToRelationalDatabase(SqlReplicationConfig cfg, DbProviderFactory providerFactory,
Dictionary<string, List<ItemToReplicate>> dictionary)
{
using (var commandBuilder = providerFactory.CreateCommandBuilder())
using (var connection = providerFactory.CreateConnection())
{
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
connection.Open();
using (var tx = connection.BeginTransaction())
{
foreach (var kvp in dictionary)
{
// first, delete all the rows that might already exist there
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
cmd.Parameters.Add(dbParameter);
dbParameter.Value = itemToReplicate.DocumentId;
cmd.CommandText = string.Format("DELETE FROM {0} WHERE {1} = {2}",
commandBuilder.QuoteIdentifier(kvp.Key),
commandBuilder.QuoteIdentifier(itemToReplicate.PkName),
dbParameter.ParameterName
);
cmd.ExecuteNonQuery();
}
}
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(kvp.Key))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
}
tx.Commit();
}
}
}
示例12: RelationalDatabaseWriter
public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
stringParserList = new List<Func<DbParameter, string, bool>> {
(colParam, value) => {
if( char.IsDigit( value[ 0 ] ) ) {
DateTime dateTime;
if (DateTime.TryParseExact(value, Default.OnlyDateTimeFormat, CultureInfo.InvariantCulture,
DateTimeStyles.RoundtripKind, out dateTime))
{
switch( providerFactory.GetType( ).Name ) {
case "MySqlClientFactory":
colParam.Value = dateTime.ToString( "yyyy-MM-dd HH:mm:ss.ffffff" );
break;
default:
colParam.Value = dateTime;
break;
}
return true;
}
}
return false;
},
(colParam, value) => {
if( char.IsDigit( value[ 0 ] ) ) {
DateTimeOffset dateTimeOffset;
if( DateTimeOffset.TryParseExact( value, Default.DateTimeFormatsToRead, CultureInfo.InvariantCulture,
DateTimeStyles.RoundtripKind, out dateTimeOffset ) ) {
switch( providerFactory.GetType( ).Name ) {
case "MySqlClientFactory":
colParam.Value = dateTimeOffset.ToUniversalTime().ToString( "yyyy-MM-dd HH:mm:ss.ffffff" );
break;
default:
colParam.Value = dateTimeOffset;
break;
}
return true;
}
}
return false;
}
};
}
示例13: DBManager
public DBManager(Databases db)
{
switch (ConType)
{
case SqlEngine.MsSql:
dbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
break;
case SqlEngine.MySql:
dbFactory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
break;
default:
throw new ArgumentOutOfRangeException("Database type isn't within range.");
}
switch (db)
{
case Databases.Auth:
dbConString = AuthConString;
break;
case Databases.Game:
dbConString = GameConString;
break;
case Databases.User:
dbConString = UserConString;
break;
default:
ConsoleUtils.ShowError("Invalid database type.");
break;
}
targetDb = db;
if (dbFactory != null)
{
using (DbConnection dbCon = dbFactory.CreateConnection())
{
dbCon.ConnectionString = dbConString;
dbBuilder = dbFactory.CreateCommandBuilder();
try
{
dbCon.Open();
using (DataTable tbl = dbCon.GetSchema(DbMetaDataCollectionNames.DataSourceInformation))
{
dbParameterMarkerFormat = tbl.Rows[0][DbMetaDataColumnNames.ParameterMarkerFormat] as string;
}
dbCon.Close();
if (!string.IsNullOrEmpty(dbParameterMarkerFormat)) { dbParameterMarkerFormat = "{0}"; }
ConsoleUtils.ShowDebug("DBManager Instance Initialized.");
}
catch (Exception ex) { ConsoleUtils.ShowError(ex.Message); }
}
}
}
示例14: WriteToRelationalDatabase
private bool WriteToRelationalDatabase(SqlReplicationConfig cfg, DbProviderFactory providerFactory, Dictionary<string, List<ItemToReplicate>> dictionary, SqlReplicationStatistics replicationStatistics)
{
using (var commandBuilder = providerFactory.CreateCommandBuilder())
using (var connection = providerFactory.CreateConnection())
{
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
Database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
bool hadErrors = false;
using (var tx = connection.BeginTransaction())
{
foreach (var kvp in dictionary)
{
// first, delete all the rows that might already exist there
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
cmd.Parameters.Add(dbParameter);
dbParameter.Value = itemToReplicate.DocumentId;
cmd.CommandText = string.Format("DELETE FROM {0} WHERE {1} = {2}",
commandBuilder.QuoteIdentifier(kvp.Key),
commandBuilder.QuoteIdentifier(itemToReplicate.PkName),
dbParameter.ParameterName
);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name + ", will continue trying." + Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(kvp.Key))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
try
//.........这里部分代码省略.........
示例15: DbQueryTranslator
/// <summary>
/// Initializes a new instance of the <see cref="DbQueryTranslator"/> class.
/// </summary>
/// <param name="providerFactory"></param>
protected DbQueryTranslator(DbProviderFactory providerFactory)
{
_providerFactory = providerFactory;
_commandBuilder = _providerFactory.CreateCommandBuilder();
Parameters = new List<IDataParameter>();
}