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