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 }));
}
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;
}
public ReplicateToSqlIndexUpdateBatcher(
DbProviderFactory providerFactory,
string connectionString,
IndexReplicationDestination destination)
{
_providerFactory = providerFactory;
_commandBuilder = providerFactory.CreateCommandBuilder();
_connectionString = connectionString;
this.destination = destination;
}
protected DbCommandBuilder CreateCommandBuilder()
{
if (IsSingleton)
{
if (dbCommandBuilder == null)
{
dbCommandBuilder = dbFactory.CreateCommandBuilder();
}
}
else
{
if (dbCommandBuilder != null)
{
dbCommandBuilder.Dispose();
}
dbCommandBuilder = dbFactory.CreateCommandBuilder();
}
return(dbCommandBuilder);
}
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;
}
}
private System.Data.Common.DbCommandBuilder getDbCommandBuilder(string providerName)
{
System.Data.Common.DbCommandBuilder returnValue = null;
if (!String.IsNullOrEmpty(providerName))
{
System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(providerName);
if (factory != null)
{
returnValue = factory.CreateCommandBuilder();
}
}
return(returnValue);
}
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);
}
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();
}
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;
}
};
}
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"] = "******" + tenant + ") ";
whereExceptions["calendar_event_item"] = " where event_id in (select id from calendar_events where tenant = " + tenant + ") ";
whereExceptions["calendar_event_user"] = "******" + 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";
}
// 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)));
}
}
}
}
}
}
}
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"] = "******";
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"] = "******";
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";
}
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);
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
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name + " (insert), will continue trying." + Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
}
tx.Commit();
}
return hadErrors == false;
}
}
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();
}
}
}
private static void XML2DB(DbProviderFactory dataFactory, DbConnection dbcn, DbTransaction tran, DTDatabase dtDatabase, string namespacePrefix, string namespaceUri, XPathDocument genericXMLDate, XPathDocument formData, XPathDocument resultData, string prodef_id, string project_id, DateTime? applyingDate, DateTime? finishedDate)
{
if (!string.IsNullOrEmpty(dtDatabase.BeforeProcedure))
{
if (LoggingService.IsInfoEnabled)
{
LoggingService.InfoFormatted("执行交换前存储过程：{0}", new object[] { dtDatabase.BeforeProcedure });
}
DbCommand command = dbcn.CreateCommand();
command.CommandText = dtDatabase.BeforeProcedure;
command.CommandType = CommandType.StoredProcedure;
command.Transaction = tran;
command.ExecuteNonQuery();
}
XPathDocument[] documents = new XPathDocument[] { formData, resultData, genericXMLDate };
foreach (DTTable table in dtDatabase.DTTables)
{
XPathDocument document;
if (!table.IsActive || !table.IfImport)
{
continue;
}
if (LoggingService.IsInfoEnabled)
{
LoggingService.InfoFormatted("准备交换数据至表：'{0}<{1}>'", new object[] { table.Name, table.Description });
}
if (LoggingService.IsDebugEnabled)
{
LoggingService.DebugFormatted("基础数据来自：{0}", new object[] { table.SourceType });
}
switch (table.SourceType)
{
case SourceType.FormData:
document = formData;
break;
case SourceType.ResultData:
document = resultData;
break;
case SourceType.GenericXMLData:
document = genericXMLDate;
break;
default:
throw new NotSupportedException(string.Format("尚不支持的类型:{0}！", table.SourceType));
}
if (document != null)
{
DbDataAdapter adapter = dataFactory.CreateDataAdapter();
DbCommand command2 = dbcn.CreateCommand();
command2.Transaction = tran;
command2.CommandText = string.Format("select * from {0} where 1<>1", string.IsNullOrEmpty(table.ImportToTable) ? table.Name : table.ImportToTable);
adapter.SelectCommand = command2;
DbCommandBuilder builder = dataFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
adapter.InsertCommand.Transaction = tran;
XPathNavigator navigator = document.CreateNavigator();
XPathNodeIterator iterator = null;
if (!string.IsNullOrEmpty(namespacePrefix) && !string.IsNullOrEmpty(namespaceUri))
{
table.NamespacePrefix = string.Format(table.NamespacePrefix, namespacePrefix);
table.NamespaceUri = string.Format(table.NamespaceUri, namespaceUri);
table.MapXMLElementName = string.Format(table.MapXMLElementName, namespacePrefix);
}
if (!string.IsNullOrEmpty(table.NamespacePrefix))
{
if (LoggingService.IsDebugEnabled)
{
LoggingService.DebugFormatted("搜索:'{0}',前缀:'{1}',命名空间:'{2}'", new object[] { table.MapXMLElementName, table.NamespacePrefix, table.NamespaceUri });
}
XmlNamespaceManager resolver = new XmlNamespaceManager(navigator.NameTable);
resolver.AddNamespace(table.NamespacePrefix, table.NamespaceUri);
iterator = navigator.Select(table.MapXMLElementName, resolver);
if ((iterator.Count == 0) && !string.IsNullOrEmpty(table.DataSetName))
{
if (LoggingService.IsDebugEnabled)
{
LoggingService.DebugFormatted("搜索:'/{0}:{1}/{2}'", new object[] { namespacePrefix, table.DataSetName, table.MapXMLElementName });
}
iterator = navigator.Select(string.Format("/{0}:{1}/{2}", namespacePrefix, table.DataSetName, table.MapXMLElementName), resolver);
}
}
else
{
iterator = navigator.Select(table.MapXMLElementName);
}
if (iterator.Count > 0)
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
foreach (XPathNavigator navigator2 in iterator)
{
DataRow row = dataTable.NewRow();
foreach (DTColumn column in table.DTColumns)
{
if (!column.IsActive || !column.IfImport)
{
continue;
}
if (!dataTable.Columns.Contains(column.Name))
{
goto Label_0615;
}
if (!string.IsNullOrEmpty(column.MapXMLElementName))
{
switch (column.MapXMLElementName.ToUpper())
{
case "[GUID]":
try
{
row[column.Name] = StringHelper.GetNewGuid();
}
catch (Exception exception)
{
LoggingService.Error(exception);
}
goto Label_0580;
case "[PROJECT_ID]":
try
{
row[column.Name] = project_id;
}
catch (Exception exception2)
{
LoggingService.Error(exception2);
}
goto Label_0580;
case "[PRODEF_ID]":
try
{
row[column.Name] = prodef_id;
}
catch (Exception exception3)
{
LoggingService.Error(exception3);
}
goto Label_0580;
case "[NUM]":
try
{
row[column.Name] = dataTable.Rows.Count + 1;
}
catch (Exception exception4)
{
LoggingService.Error(exception4);
}
goto Label_0580;
case "[APPLYING_DATE]":
try
{
row[column.Name] = applyingDate;
}
catch (Exception exception5)
{
LoggingService.Error(exception5);
}
goto Label_0580;
case "[FINISHED_DATE]":
try
{
row[column.Name] = finishedDate;
}
catch (Exception exception6)
{
LoggingService.Error(exception6);
}
goto Label_0580;
}
try
{
SetColumnValue(documents, namespacePrefix, namespaceUri, navigator2, row, column);
}
catch (Exception exception7)
{
LoggingService.Error(string.Format("设置'{0}<{1}>'时出错", column.Name, column.Description), exception7);
}
}
Label_0580:
if (!string.IsNullOrEmpty(column.DefaultValue) && Convert.IsDBNull(row[column.Name]))
{
row[column.Name] = column.DefaultValue;
}
if (LoggingService.IsDebugEnabled && !Convert.IsDBNull(row[column.Name]))
{
LoggingService.DebugFormatted("获取的列'{0}<{1}>'的值为:'{2}'", new object[] { column.Name, column.Description, row[column.Name] });
}
continue;
Label_0615:;
LoggingService.WarnFormatted("表‘{0}<{1}>’已经不包含列‘{2}<{3}>’", new object[] { table.Name, table.Description, column.Name, column.Description });
}
dataTable.Rows.Add(row);
}
if (dataTable.Rows.Count > 0)
{
if (LoggingService.IsDebugEnabled)
{
LoggingService.Debug("添加了新数据,更新表");
}
adapter.Update(dataTable);
}
continue;
}
LoggingService.WarnFormatted("没有找到与表'{0}'对应的'{1}'节点!", new object[] { table.Name, table.MapXMLElementName });
}
}
if (!string.IsNullOrEmpty(dtDatabase.AfterProcedure))
{
DbCommand command3 = dbcn.CreateCommand();
command3.CommandText = dtDatabase.AfterProcedure;
command3.CommandType = CommandType.StoredProcedure;
command3.Transaction = tran;
command3.ExecuteNonQuery();
if (LoggingService.IsDebugEnabled)
{
LoggingService.DebugFormatted("执行导入后存储过程:'{0}'!", new object[] { dtDatabase.AfterProcedure });
}
}
}
/// <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>();
}
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); }
}
}
}
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
{
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;
}
}
}
}
tx.Commit();
}
return hadErrors == false;
}
}