/// <summary> /// Salva no banco de dados as alterações em uma tabela. /// </summary> /// <param name="dt">A tabela</param> /// <param name="state"> /// O estado que estará sendo processado. Pode ser /// <see cref="DataViewRowState.Added"/>, /// <see cref="DataViewRowState.ModifiedCurrent"/> ou /// <see cref="DataViewRowState.Deleted"/> /// </param> /// <returns>O número de registros atualizados</returns> protected int CommitTable(DataTable dt, DataViewRowState state) { int c = 0; if (dt != null && dt.Rows.Count > 0) { try { using (new PhysicalTableAdapter(conn, dt)) using (AdpDataAdapter da = new AdpDataAdapter()) { DataRow[] rows = dt.Select(null, null, state); if (rows.Length > 0) { if (!conn.BuildCommands(da, dbConn, dt)) new AdpCommandBuilder(da, dbConn, dt); if (trans != null) SetTransaction(da); if (state == DataViewRowState.Added) c += CommitInserts(dt, da, rows); else c += da.Update(rows); } } } catch (Exception ex) { throw ConnectorExceptionFactory.FromDatabaseException(ex).Detail("Error while updating data table named {0}", dt.TableName); } } return c; }
protected override void CommitChanges() { try { IEnumerator en = rows.GetEnumerator(); bool hasNext = en.MoveNext(); while (hasNext) { DataRow row = (DataRow)en.Current; DataTable dt = row.Table; using (new PhysicalTableAdapter(conn, dt)) using (AdpDataAdapter da = new AdpDataAdapter()) { if (!conn.BuildCommands(da, dbConn, dt)) { new AdpCommandBuilder(da, dbConn, dt); } if (trans != null) { SetTransaction(da); } do { if (row.RowState == DataRowState.Added) { CommitInserts(dt, da, row); // HACK: otimizar para enviar todas a serem inseridas ao mesmo tempo } else { da.Update(row); } // repete enquanto houverem mais linhas da mesma tabela row = (hasNext = en.MoveNext()) ? (DataRow)en.Current : null; } while (hasNext && Object.ReferenceEquals(row.Table, dt)); } } } catch (Exception ex) { if (ex is ConnectorException) { throw; } else { throw ConnectorExceptionFactory.FromDatabaseException(ex); } } }
/// <summary> /// Preenche um <see cref="DataTable"/> com o resultado da Stored Procedure. /// </summary> public int Fill(DataTable dt) { try { using (AdpDataAdapter da = new AdpDataAdapter(cmd)) return(da.Fill(dt)); } catch (Exception ex) { throw ConnectorExceptionFactory.FromDatabaseException(ex) .Detail("Erro ao executar stored procedure {0}", cmd.CommandText); } }
/// <summary> /// Preenche apenas as tabelas de dados especificadas. /// </summary> /// <param name="sources">Os nomes das origens. Formato de cada uma: <c>dataTable:tabela_ou_view</c></param> public int FillData(params string[] sources) { try { int c = 0; using (AdpConnection conn = CreateConnection()) { foreach (string rawInfo in sources) { FillInfo fi = new FillInfo(rawInfo); using (AdpDataAdapter da = new AdpDataAdapter()) { if (fi.Source == fi.DataTable && ds.Tables.Contains(fi.DataTable)) { DataTable dt = ds.Tables[fi.DataTable]; lock (dt) { string old = dt.TableName; dt.TableName = GetPhysicalTableName(dt); new AdpCommandBuilder(da, conn, dt, AdpCommandOperation.Select); c += da.Fill(dt); dt.TableName = old; } } else { da.SelectCommand = new AdpCommand("SELECT * FROM " + fi.Source, conn); if (!ds.Tables.Contains(fi.DataTable)) { Debug.WriteLine(String.Format("WARN: DataTable {0} doesn't exist.", fi.DataTable)); } c += da.Fill(ds, fi.DataTable); } } } } return(c); } catch (Exception ex) { throw new ConnectorException("Erro ao realizar o preenchimento dos dados", ex); } }
/// <summary> /// Salva no banco de dados as alterações em uma tabela. /// </summary> /// <param name="dt">A tabela</param> /// <param name="state"> /// O estado que estará sendo processado. Pode ser /// <see cref="DataViewRowState.Added"/>, /// <see cref="DataViewRowState.ModifiedCurrent"/> ou /// <see cref="DataViewRowState.Deleted"/> /// </param> /// <returns>O número de registros atualizados</returns> protected int CommitTable(DataTable dt, DataViewRowState state) { int c = 0; if (dt != null && dt.Rows.Count > 0) { try { using (new PhysicalTableAdapter(conn, dt)) using (AdpDataAdapter da = new AdpDataAdapter()) { DataRow[] rows = dt.Select(null, null, state); if (rows.Length > 0) { if (!conn.BuildCommands(da, dbConn, dt)) { new AdpCommandBuilder(da, dbConn, dt); } if (trans != null) { SetTransaction(da); } if (state == DataViewRowState.Added) { c += CommitInserts(dt, da, rows); } else { c += da.Update(rows); } } } } catch (Exception ex) { throw ConnectorExceptionFactory.FromDatabaseException(ex).Detail("Error while updating data table named {0}", dt.TableName); } } return(c); }
protected override void CommitChanges() { try { IEnumerator en = rows.GetEnumerator(); bool hasNext = en.MoveNext(); while (hasNext) { DataRow row = (DataRow) en.Current; DataTable dt = row.Table; using (new PhysicalTableAdapter(conn, dt)) using (AdpDataAdapter da = new AdpDataAdapter()) { if (!conn.BuildCommands(da, dbConn, dt)) new AdpCommandBuilder(da, dbConn, dt); if (trans != null) SetTransaction(da); do { if (row.RowState == DataRowState.Added) CommitInserts(dt, da, row); // HACK: otimizar para enviar todas a serem inseridas ao mesmo tempo else da.Update(row); // repete enquanto houverem mais linhas da mesma tabela row = (hasNext = en.MoveNext()) ? (DataRow) en.Current : null; } while (hasNext && Object.ReferenceEquals(row.Table, dt)); } } } catch (Exception ex) { if (ex is ConnectorException) throw; else throw ConnectorExceptionFactory.FromDatabaseException(ex); } }
/// <summary> /// Atribui a transação especificada ao <see cref="AdpDataAdapter"/> especificado. /// </summary> /// <param name="da">O <see cref="AdpDataAdapter"/> onde a transação deve ser atribuída</param> /// <param name="trans">A transação a ser atribuída.</param> protected void SetTransaction(AdpDataAdapter da, AdpTransaction trans) { AdpCommand cmd; if ((cmd = da.SelectCommand) != null) { cmd.Transaction = trans; } if ((cmd = da.UpdateCommand) != null) { cmd.Transaction = trans; } if ((cmd = da.DeleteCommand) != null) { cmd.Transaction = trans; } if ((cmd = da.InsertCommand) != null) { cmd.Transaction = trans; } }
/// <summary> /// Preenche a tabela de dados especificada, com os critérios especificados. /// </summary> /// <param name="dataTable">O nome da tabela no DataSet que deverá ser preenchida.</param> /// <param name="query">A consulta de origem dos dados</param> public int FillData(string dataTable, ISqlQuery query) { string sql = null; try { #if DEBUG Debug.WriteLine("FillData"); Debug.IndentLevel++; Debug.WriteLine("Data Table: " + dataTable); Debug.WriteLine("SQL: " + Regex.Replace(query.ToString().Replace(Environment.NewLine, " "), "\\s+", " ")); Debug.IndentLevel--; #endif sql = query.ToString(); DataTable dt = ds.Tables[dataTable]; if (dt != null) { sql = Regex.Replace(sql, "FROM\\s+" + dataTable, "FROM " + GetPhysicalTableName(dt), RegexOptions.IgnoreCase); } using (AdpConnection conn = CreateConnection()) using (AdpDataAdapter da = new AdpDataAdapter()) { da.SelectCommand = new AdpCommand(sql, conn); return(da.Fill(ds, dataTable)); } } catch (Exception ex) { if (sql == null) { throw new ConnectorException("Erro ao realizar o preenchimento dos dados", ex); } else { throw ConnectorExceptionFactory.FromDatabaseException(ex, sql); } } }
protected internal virtual bool BuildCommands(AdpDataAdapter da, AdpConnection conn, DataTable dataTable) { string procINS = GetStoredProcedureForCommiting(dataTable, AdpCommandOperation.Insert), procUPD = GetStoredProcedureForCommiting(dataTable, AdpCommandOperation.Update), procDEL = GetStoredProcedureForCommiting(dataTable, AdpCommandOperation.Delete); if (procINS != null || procUPD != null || procDEL != null) { new AdpCommandBuilder(da, conn, dataTable); if (procINS != null) { new AdpCommandBuilder(da, conn, dataTable, AdpCommandOperation.Insert, true); da.InsertCommand.CommandType = CommandType.StoredProcedure; da.InsertCommand.CommandText = procINS; } if (procUPD != null) { new AdpCommandBuilder(da, conn, dataTable, AdpCommandOperation.Update, true); da.UpdateCommand.CommandType = CommandType.StoredProcedure; da.UpdateCommand.CommandText = procUPD; } if (procDEL != null) { new AdpCommandBuilder(da, conn, dataTable, AdpCommandOperation.Delete, true); da.DeleteCommand.CommandType = CommandType.StoredProcedure; da.DeleteCommand.CommandText = procDEL; } return(true); } return(false); }
protected internal virtual bool BuildCommands(AdpDataAdapter da, AdpConnection conn, DataTable dataTable) { string procINS = GetStoredProcedureForCommiting(dataTable, AdpCommandOperation.Insert), procUPD = GetStoredProcedureForCommiting(dataTable, AdpCommandOperation.Update), procDEL = GetStoredProcedureForCommiting(dataTable, AdpCommandOperation.Delete); if (procINS != null || procUPD != null || procDEL != null) { new AdpCommandBuilder(da, conn, dataTable); if (procINS != null) { new AdpCommandBuilder(da, conn, dataTable, AdpCommandOperation.Insert, true); da.InsertCommand.CommandType = CommandType.StoredProcedure; da.InsertCommand.CommandText = procINS; } if (procUPD != null) { new AdpCommandBuilder(da, conn, dataTable, AdpCommandOperation.Update, true); da.UpdateCommand.CommandType = CommandType.StoredProcedure; da.UpdateCommand.CommandText = procUPD; } if (procDEL != null) { new AdpCommandBuilder(da, conn, dataTable, AdpCommandOperation.Delete, true); da.DeleteCommand.CommandType = CommandType.StoredProcedure; da.DeleteCommand.CommandText = procDEL; } return true; } return false; }
/// <summary> /// Atribui a transação especificada ao <see cref="AdpDataAdapter"/> especificado. /// </summary> /// <param name="da">O <see cref="AdpDataAdapter"/> onde a transação deve ser atribuída</param> /// <param name="trans">A transação a ser atribuída.</param> protected void SetTransaction(AdpDataAdapter da, AdpTransaction trans) { AdpCommand cmd; if ((cmd = da.SelectCommand) != null) cmd.Transaction = trans; if ((cmd = da.UpdateCommand) != null) cmd.Transaction = trans; if ((cmd = da.DeleteCommand) != null) cmd.Transaction = trans; if ((cmd = da.InsertCommand) != null) cmd.Transaction = trans; }
/// <summary> /// Atribui a transação ativa ao <see cref="AdpDataAdapter"/> especificado. /// </summary> /// <param name="da">O <see cref="AdpDataAdapter"/> onde a transação deve ser atribuída</param> protected void SetTransaction(AdpDataAdapter da) { SetTransaction(da, trans); }
/// <summary> /// Insere as linhas na base de dados. /// </summary> /// <param name="dt">A tabela com as linhas a serem inseridas</param> /// <param name="da">O <see cref="AdpDataAdapter"/> a ser utilizado</param> /// <param name="rows">As linhas a serem inseridas</param> /// <returns>O número de linhas inseridas</returns> protected int CommitInserts(DataTable dt, AdpDataAdapter da, params DataRow[] rows) { int c = 0; AdpCommand autoIncCmd = null; DataColumn autoIncCol = null; // procura pela coluna de auto-incremento foreach (DataColumn col in dt.PrimaryKey) { if (col.AutoIncrement) { autoIncCol = col; break; } } // se não houver coluna de auto-incremento, realiza o update normal. if (autoIncCol == null) return da.Update(rows); // procura pelo comando de busca do auto-incremento if (dbConn.Provider is MsSqlProvider) { autoIncCmd = dbConn.CreateCommand(); autoIncCmd.CommandText = "SELECT IDENT_CURRENT('" + dt.TableName + "')"; autoIncCmd.Transaction = trans; } // se não encontra o comando, realiza o update normal if (autoIncCmd == null) { Debug.WriteLine("Provider not supported: " + dbConn.Provider); return da.Update(rows); } // se está tudo certo, pode começar: // prepara o comando de auto-incremento autoIncCmd.Prepare(); bool oldRO = autoIncCol.ReadOnly; try { autoIncCol.ReadOnly = false; foreach (DataRow row in rows) { // atualiza linha a linha c += da.Update(row); // busca o conteúdo do auto-incremento object id = autoIncCmd.ExecuteScalar(); if (!(id is DBNull)) { // se houver resultado, atualiza a linha do DataSet row[autoIncCol] = id; row.AcceptChanges(); } } // retorna a quantidade de linhas atualizadas return c; } finally { // devolve o estado de "read-only" à linha autoIncCol.ReadOnly = oldRO; } }
/// <summary> /// Preenche apenas as tabelas de dados especificadas. /// </summary> /// <param name="sources">Os nomes das origens. Formato de cada uma: <c>dataTable:tabela_ou_view</c></param> public int FillData(params string[] sources) { try { int c = 0; using (AdpConnection conn = CreateConnection()) { foreach (string rawInfo in sources) { FillInfo fi = new FillInfo(rawInfo); using (AdpDataAdapter da = new AdpDataAdapter()) { if (fi.Source == fi.DataTable && ds.Tables.Contains(fi.DataTable)) { DataTable dt = ds.Tables[fi.DataTable]; lock (dt) { string old = dt.TableName; dt.TableName = GetPhysicalTableName(dt); new AdpCommandBuilder(da, conn, dt, AdpCommandOperation.Select); c += da.Fill(dt); dt.TableName = old; } } else { da.SelectCommand = new AdpCommand("SELECT * FROM " + fi.Source, conn); if (!ds.Tables.Contains(fi.DataTable)) Debug.WriteLine(String.Format("WARN: DataTable {0} doesn't exist.", fi.DataTable)); c += da.Fill(ds, fi.DataTable); } } } } return c; } catch (Exception ex) { throw new ConnectorException("Erro ao realizar o preenchimento dos dados", ex); } }
/// <summary> /// Insere as linhas na base de dados. /// </summary> /// <param name="dt">A tabela com as linhas a serem inseridas</param> /// <param name="da">O <see cref="AdpDataAdapter"/> a ser utilizado</param> /// <param name="rows">As linhas a serem inseridas</param> /// <returns>O número de linhas inseridas</returns> protected int CommitInserts(DataTable dt, AdpDataAdapter da, params DataRow[] rows) { int c = 0; AdpCommand autoIncCmd = null; DataColumn autoIncCol = null; // procura pela coluna de auto-incremento foreach (DataColumn col in dt.PrimaryKey) { if (col.AutoIncrement) { autoIncCol = col; break; } } // se não houver coluna de auto-incremento, realiza o update normal. if (autoIncCol == null) { return(da.Update(rows)); } // procura pelo comando de busca do auto-incremento if (dbConn.Provider is MsSqlProvider) { autoIncCmd = dbConn.CreateCommand(); autoIncCmd.CommandText = "SELECT IDENT_CURRENT('" + dt.TableName + "')"; autoIncCmd.Transaction = trans; } // se não encontra o comando, realiza o update normal if (autoIncCmd == null) { Debug.WriteLine("Provider not supported: " + dbConn.Provider); return(da.Update(rows)); } // se está tudo certo, pode começar: // prepara o comando de auto-incremento autoIncCmd.Prepare(); bool oldRO = autoIncCol.ReadOnly; try { autoIncCol.ReadOnly = false; foreach (DataRow row in rows) { // atualiza linha a linha c += da.Update(row); // busca o conteúdo do auto-incremento object id = autoIncCmd.ExecuteScalar(); if (!(id is DBNull)) { // se houver resultado, atualiza a linha do DataSet row[autoIncCol] = id; row.AcceptChanges(); } } // retorna a quantidade de linhas atualizadas return(c); } finally { // devolve o estado de "read-only" à linha autoIncCol.ReadOnly = oldRO; } }
/// <summary> /// Preenche um <see cref="DataTable"/> com o resultado da Stored Procedure. /// </summary> public int Fill(DataTable dt) { try { using (AdpDataAdapter da = new AdpDataAdapter(cmd)) return da.Fill(dt); } catch (Exception ex) { throw ConnectorExceptionFactory.FromDatabaseException(ex) .Detail("Erro ao executar stored procedure {0}", cmd.CommandText); } }
/// <summary> /// Preenche a tabela de dados especificada, com os critérios especificados. /// </summary> /// <param name="dataTable">O nome da tabela no DataSet que deverá ser preenchida.</param> /// <param name="query">A consulta de origem dos dados</param> public int FillData(string dataTable, ISqlQuery query) { string sql = null; try { #if DEBUG Debug.WriteLine("FillData"); Debug.IndentLevel++; Debug.WriteLine("Data Table: " + dataTable); Debug.WriteLine("SQL: " + Regex.Replace(query.ToString().Replace(Environment.NewLine, " "), "\\s+", " ")); Debug.IndentLevel--; #endif sql = query.ToString(); DataTable dt = ds.Tables[dataTable]; if (dt != null) sql = Regex.Replace(sql, "FROM\\s+" + dataTable, "FROM " + GetPhysicalTableName(dt), RegexOptions.IgnoreCase); using (AdpConnection conn = CreateConnection()) using (AdpDataAdapter da = new AdpDataAdapter()) { da.SelectCommand = new AdpCommand(sql, conn); return da.Fill(ds, dataTable); } } catch (Exception ex) { if (sql == null) throw new ConnectorException("Erro ao realizar o preenchimento dos dados", ex); else throw ConnectorExceptionFactory.FromDatabaseException(ex, sql); } }