public void Realiza_BulkInsert_Com_DataAnnotations()
{
// cria lista de itens a serem incluidos.
var listaEntidades = (from i in Enumerable.Range(1, 10000)
select new Modelo.EntidadeTesteDataAnnotation() {
Id = i + 10000,
Nome = String.Format("Nome: {0}", i),
Idade = i,
TipoPessoa = TipoPessoa.Juridica,
}).ToList();
// Para o sucesso na verificação do teste, verifica se a entidade possui TableAttributes.
Verifica_Se_A_Entidade_Possui_MappingAttributes(
listaEntidades.GetType().GetGenericArguments().First()
);
using (var uow = new MainUnitOfWork(TempDatabaseConnectionStringName))
{
// Cria BD.
if (!uow.Database.Exists()) uow.Database.Initialize(force: false);
var repo = new Repository<EntidadeTesteDataAnnotation>(uow);
var qtdOriginalDeRegistrosNaTabela = repo.Contar();
// realiza bulk insert.
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[TempDatabaseConnectionStringName].ConnectionString;
var bulkobj = new SqlBulkCopy<EntidadeTesteDataAnnotation>();
bulkobj.BulkInsert(connectionString, listaEntidades);
// verifica a quantidade de itens inseridos
var qtdEntidadesCadastradas = repo.Contar();
Assert.IsTrue(qtdEntidadesCadastradas == listaEntidades.Count() + qtdOriginalDeRegistrosNaTabela, "Não adicionou os registros na tabela.");
}
}
public static void BulkCopyTable(DataTable datatable, string table)
{
using (var scon = Connections.Connect())
{
scon.Open();
using (SqlBulkCopy copydtab = new SqlBulkCopy(scon))
{
copydtab.DestinationTableName = table;
copydtab.WriteToServer(datatable);
}
scon.Close();
scon.Dispose();
}
}
public void Realiza_BulkInsert_Sem_DataAnnotations()
{
// cria lista de itens a serem incluidos.
var listaEntidades = (from i in Enumerable.Range(1, 10000)
select new Modelo.EntidadeTeste()
{
Id = i + 10000,
Nome = String.Format("Nome: {0}", i),
Idade = i,
TipoPessoa = TipoPessoa.Juridica,
}).ToList();
using (var uow = new MainUnitOfWork(TempDatabaseConnectionStringName))
{
// Cria BD.
if (!uow.Database.Exists()) uow.Database.Initialize(force: false);
var repo = new Repository<EntidadeTeste>(uow);
var qtdOriginalDeRegistrosNaTabela = repo.Contar();
// realiza bulk insert.
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[TempDatabaseConnectionStringName].ConnectionString;
var bulkobj = new SqlBulkCopy<EntidadeTeste>();
bulkobj.NomeTabela = "TabelaEntidadesTeste";
bulkobj.MapColumn(p => p.Nome, "NomeEntidade");
bulkobj.MapColumn(p => p.TipoPessoa, "TipoDePessoa");
bulkobj.Ignore(p => p.Idade);
bulkobj.BulkInsert(connectionString, listaEntidades);
// verifica a quantidade de itens inseridos
var qtdEntidadesCadastradas = repo.Contar();
Assert.IsTrue(qtdEntidadesCadastradas == listaEntidades.Count() + qtdOriginalDeRegistrosNaTabela, "Não adicionou os registros na tabela.");
}
}
private void PerformWriteToDatabase(IEnumerable<LoggingEvent> events)
{
try
{
DataTable table = CreateTable(events);
using (SqlBulkCopy bulk = new SqlBulkCopy(connectionString, options))
{
foreach (BulkInsertMapping mapping in mappings)
{
bulk.ColumnMappings.Add(mapping.Column, mapping.Column);
}
bulk.DestinationTableName = tableName;
bulk.WriteToServer(table);
}
}
catch (Exception ex)
{
LogLog.Error("Could not write logs to database in the background", ex);
}
}
public static bool BulkCopy(DataTable sourceTable, string sDestTable)
{
// mpp mat.
using (var scon = Connections.Connect())
{
SqlBulkCopy bc = new SqlBulkCopy(scon);
bc.DestinationTableName = sDestTable;
bc.WriteToServer(sourceTable);
return true;
}
}
/// <summary>
/// 批量复制数据（把源DB中根据SQL语句查出的结果批量COPY插入到目的DB的目的表中）
/// </summary>
public static TResult BatchCopyData <TResult>(string srcSelectSql, string destConnName, string destTableName, IDictionary <string, string> colMappings, Func <IDbConnection, TResult> afterCoppyFunc)
{
using (SqlDataAdapter srcSqlDataAdapter = new SqlDataAdapter(srcSelectSql, GetConnection(true).ConnectionString))
{
DataTable srcTable = new DataTable();
TResult copyResult = default(TResult);
try
{
srcSqlDataAdapter.AcceptChangesDuringFill = true;
srcSqlDataAdapter.AcceptChangesDuringUpdate = false;
srcSqlDataAdapter.Fill(srcTable);
if (srcTable == null || srcTable.Rows.Count <= 0)
{
return(copyResult);
}
string dbProviderName2 = null;
using (var destConn = new SqlConnection(GetConnection(true).ConnectionString))
{
destConn.Open();
string tempDestTableName = "#temp_" + destTableName;
destConn.Execute(string.Format("select top 0 * into {0} from {1}", tempDestTableName, destTableName));
bool bcpResult = false;
using (var destSqlBulkCopy = new SqlBulkCopy(destConn))
{
try
{
destSqlBulkCopy.BulkCopyTimeout = 120;
destSqlBulkCopy.DestinationTableName = tempDestTableName;
foreach (var col in colMappings)
{
destSqlBulkCopy.ColumnMappings.Add(col.Key, col.Value);
}
destSqlBulkCopy.BatchSize = 1000;
destSqlBulkCopy.WriteToServer(srcTable);
bcpResult = true;
}
catch (Exception ex)
{
//LogUtil.Error("SqlDapperUtil.BatchMoveData.SqlBulkCopy:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
}
}
if (bcpResult)
{
copyResult = afterCoppyFunc(destConn);
}
destConn.Close();
}
return(copyResult);
}
catch (Exception ex)
{
//LogUtil.Error("SqlDapperUtil.BatchCopyData:" + ex.ToString(), "SqlDapperUtil.BatchCopyData");
return(copyResult);
}
}
}
/// <summary>
/// 批量转移数据(利用SqlBulkCopy实现快速大批量插入到指定的目的表及SqlDataAdapter的批量删除)
/// </summary>
public static bool BatchMoveData(string srcSelectSql, string srcTableName, List <SqlParameter> srcPrimarykeyParams, string destConnName, string destTableName)
{
using (SqlDataAdapter srcSqlDataAdapter = new SqlDataAdapter(srcSelectSql, GetConnection(true).ConnectionString))
{
DataTable srcTable = new DataTable();
SqlCommand deleteCommand = null;
try
{
srcSqlDataAdapter.AcceptChangesDuringFill = true;
srcSqlDataAdapter.AcceptChangesDuringUpdate = false;
srcSqlDataAdapter.Fill(srcTable);
if (srcTable == null || srcTable.Rows.Count <= 0)
{
return(true);
}
string notExistsDestSqlWhere = null;
string deleteSrcSqlWhere = null;
for (int i = 0; i < srcPrimarykeyParams.Count; i++)
{
string keyColName = srcPrimarykeyParams[i].ParameterName.Replace("@", "");
notExistsDestSqlWhere += string.Format(" AND told.{0}=tnew.{0}", keyColName);
deleteSrcSqlWhere += string.Format(" AND {0}=@{0}", keyColName);
}
string dbProviderName2 = null;
using (var destConn = new SqlConnection(GetConnection(true).ConnectionString))
{
destConn.Open();
string tempDestTableName = "#temp_" + destTableName;
destConn.Execute(string.Format("select top 0 * into {0} from {1}", tempDestTableName, destTableName));
string destInsertCols = null;
using (var destSqlBulkCopy = new SqlBulkCopy(destConn))
{
try
{
destSqlBulkCopy.BulkCopyTimeout = 120;
destSqlBulkCopy.DestinationTableName = tempDestTableName;
foreach (DataColumn col in srcTable.Columns)
{
destSqlBulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
destInsertCols += "," + col.ColumnName;
}
destSqlBulkCopy.BatchSize = 1000;
destSqlBulkCopy.WriteToServer(srcTable);
}
catch (Exception ex)
{
//LogUtil.Error("SqlDapperUtil.BatchMoveData.SqlBulkCopy:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
}
destInsertCols = destInsertCols.Substring(1);
destConn.Execute(string.Format("insert into {1}({0}) select {0} from {2} tnew where not exists(select 1 from {1} told where {3})",
destInsertCols, destTableName, tempDestTableName, notExistsDestSqlWhere.Trim().Substring(3)), null, null, 100);
}
destConn.Close();
}
deleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1}", srcTableName, deleteSrcSqlWhere.Trim().Substring(3)), srcSqlDataAdapter.SelectCommand.Connection);
deleteCommand.Parameters.AddRange(srcPrimarykeyParams.ToArray());
deleteCommand.UpdatedRowSource = UpdateRowSource.None;
deleteCommand.CommandTimeout = 200;
srcSqlDataAdapter.DeleteCommand = deleteCommand;
foreach (DataRow row in srcTable.Rows)
{
row.Delete();
}
srcSqlDataAdapter.UpdateBatchSize = 1000;
srcSqlDataAdapter.Update(srcTable);
srcTable.AcceptChanges();
return(true);
}
catch (Exception ex)
{
//LogUtil.Error("SqlDapperUtil.BatchMoveData:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
return(false);
}
finally
{
if (deleteCommand != null)
{
deleteCommand.Parameters.Clear();
}
}
}
}
private void btn_start_Click(object sender, EventArgs e)
{
Gerais g = new Gerais();
if (g.CampoVazio(txt_servidorSource, "Servidor Source"))
{
return;
}
if (g.CampoVazio(txt_dataBaseSource, "DataBase Source"))
{
return;
}
if (g.CampoVazio(txt_tabelaSource, "Tabela Source"))
{
return;
}
if (!(cb_autentificacaoTarget.Checked))
{
if (g.CampoVazio(txt_usuarioSource, "Usuario Source"))
{
return;
}
if (g.CampoVazio(txt_senhaSource, "Senha Source"))
{
return;
}
}
if (g.CampoVazio(txt_servidorTarget, "Servidor Target"))
{
return;
}
if (g.CampoVazio(txt_dataBaseTarget, "DataBase Target"))
{
return;
}
if (g.CampoVazio(txt_tabelaTarget, "Tabela Target"))
{
return;
}
if (!(cb_autentificacaoTarget.Checked))
{
if (g.CampoVazio(txt_usuarioTarget, "Usuario Target"))
{
return;
}
if (g.CampoVazio(txt_senhaTarget, "Senha Target"))
{
return;
}
}
Conexao conexaoTarget = new Conexao();
Conexao conexaoSource = new Conexao();
if (cb_autentificacaoSource.Checked)
{
conexaoSource.Connection(txt_servidorSource.Text, txt_dataBaseSource.Text);
}
else
{
conexaoSource.Connection(txt_servidorSource.Text, txt_dataBaseSource.Text, txt_usuarioSource.Text, txt_senhaSource.Text);
}
if (cb_autentificacaoTarget.Checked)
{
conexaoTarget.Connection(txt_servidorTarget.Text, txt_dataBaseTarget.Text);
}
else
{
conexaoTarget.Connection(txt_servidorTarget.Text, txt_dataBaseTarget.Text, txt_usuarioTarget.Text, txt_senhaTarget.Text);
}
try
{
conexaoSource.sqlConnection.Open();
conexaoTarget.sqlConnection.Open();
String consulta = "select * from " + txt_tabelaSource.Text;
SqlCommand sqlCommand = new SqlCommand(consulta, conexaoSource.sqlConnection);
SqlDataReader reader = sqlCommand.ExecuteReader();
DataTable data = new DataTable();
data.Load(reader);
SqlBulkCopy insert = new SqlBulkCopy(conexaoTarget.sqlConnection);
string create = " create table " + txt_tabelaTarget.Text + "( ";
foreach (DataColumn c in data.Columns)
{
string tipo;
if (c.DataType.ToString() == "System.String")
{
tipo = SqlHelper.GetDbType(c.DataType).ToString() + "(max)";
}
else
{
tipo = SqlHelper.GetDbType(c.DataType).ToString();
}
create += c.ColumnName + " " + tipo + ",";
insert.ColumnMappings.Add(c.ColumnName, c.ColumnName);
}
create = create.Substring(0, create.Length - 1) + ")";
SqlCommand command = new SqlCommand(create, conexaoTarget.sqlConnection);
command.ExecuteNonQuery();
insert.DestinationTableName = txt_tabelaTarget.Text;
insert.WriteToServer(data);
insert.Close();
reader.Close();
conexaoSource.sqlConnection.Close();
conexaoTarget.sqlConnection.Close();
MessageBox.Show("Tabela " + "'" + txt_tabelaTarget.Text + "'" + " salva com sucesso!!");
}
catch (SqlException erro)
{
MessageBox.Show("Ocorreu um erro!\n" + erro.Message);
}
}
private void GoButton_Click(object sender, EventArgs e)
{
DateTime start = DateTime.Now;
SetCurrentStatus("Initializing connections");
ChangeControls();
string s_Host = source_Host.Text;
string s_User = source_User.Text;
string s_Pwd = source_Password.Text;
string s_DB = source_DBName.Text;
string d_Host = dest_Host.Text;
string d_User = dest_User.Text;
string d_Pwd = dest_Password.Text;
string d_DB = dest_DBName.Text;
SqlConnection s_Conn = null, d_Conn = null;
SqlCommand s_Cmd = null, d_Cmd = null;
SqlDataAdapter s_Adapter = null, d_Adapter = null;
DataTable s_Table = new DataTable();
SqlBulkCopy d_Bulk = null;
try
{
s_Conn = source_Trusted.Checked ? new SqlConnection(String.Format(ConnectionTrustedPrototype, s_Host, s_DB)) : new SqlConnection(String.Format(ConnectionPrototype, s_Host, s_DB, s_User, s_Pwd));
d_Conn = dest_Trusted.Checked ? new SqlConnection(String.Format(ConnectionTrustedPrototype, d_Host, d_DB)) : new SqlConnection(String.Format(ConnectionPrototype, d_Host, d_DB, d_User, d_Pwd));
s_Conn.Open();
d_Conn.Open();
SetCurrentStatus("Retrieving source tables");
s_Cmd = new SqlCommand(Query_GetAllTables, s_Conn);
s_Adapter = new SqlDataAdapter(s_Cmd);
s_Adapter.Fill(s_Table);
List <string> AllTables = new List <string>();
foreach (DataRow row in s_Table.Rows)
{
SetCurrentStatus("Checking destination tables");
d_Cmd = new SqlCommand(Query_CheckForTable, d_Conn);
d_Cmd.Parameters.AddWithValue("table", row[0].ToString());
if ((int)d_Cmd.ExecuteScalar() > 0)
{
AllTables.Add(row[0].ToString());
}
}
int fetched = 0;
int error = 0;
foreach (string table in AllTables)
{
fetched++;
SetCurrentStatus("Fetching table " + fetched + "/" + AllTables.Count);
s_Cmd = new SqlCommand(String.Format(Query_FetchTable, table), s_Conn);
s_Cmd.CommandTimeout = 300;
s_Table.Dispose();
s_Table = new DataTable();
s_Adapter = new SqlDataAdapter(s_Cmd);
s_Adapter.Fill(s_Table);
SqlTransaction d_Transaction = d_Conn.BeginTransaction();
try
{
d_Cmd = new SqlCommand(String.Format(Query_EmptyTable, table), d_Conn, d_Transaction);
d_Cmd.ExecuteNonQuery();
d_Cmd.Dispose();
d_Bulk = new SqlBulkCopy(d_Conn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, d_Transaction);
d_Bulk.BulkCopyTimeout = 500;
d_Bulk.DestinationTableName = table;
d_Bulk.WriteToServer(s_Table);
d_Bulk.Close();
d_Transaction.Commit();
d_Transaction.Dispose();
}
catch (Exception ex)
{
error++;
d_Transaction.Rollback();
MessageBox.Show("TABLE: " + table + "\n" + ex.Message);
}
}
MessageBox.Show("Operation Completed! Copied " + (fetched - error) + " tables on " + AllTables.Count + ".");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
SetCurrentStatus("Welcome to \"Grand Theft Table\"");
ChangeControls();
s_Table.Dispose();
if (s_Cmd != null)
{
s_Cmd.Dispose();
}
if (d_Cmd != null)
{
d_Cmd.Dispose();
}
if (s_Adapter != null)
{
s_Adapter.Dispose();
}
if (d_Adapter != null)
{
d_Adapter.Dispose();
}
if ((s_Conn != null) && (s_Conn.State == ConnectionState.Open))
{
s_Conn.Close();
}
if ((d_Conn != null) && (d_Conn.State == ConnectionState.Open))
{
d_Conn.Close();
}
}
}
public static bool SaveBulkItemsList <T, C>(this IEnumerable <T> source, IEnumerable <C> sourceChild, string tableName, string tableNameChild)
{
bool IsSuccessSave = false;
SqlTransaction transaction = null;
try
{
#region Table Names
if (tableName.Contains("."))
{
string[] splitTableName = tableName.Split('.');
tableName = string.Format("[{0}].[{1}]", splitTableName[0], splitTableName[1]);
}
if (tableNameChild.Contains("."))
{
string[] splitTableName = tableNameChild.Split('.');
tableNameChild = string.Format("[{0}].[{1}]", splitTableName[0], splitTableName[1]);
}
#endregion
using (Qiyas.DataAccessLayer.QiyasLinqDataContext databaseContext = new DataAccessLayer.QiyasLinqDataContext())
{
var connectionString = databaseContext.Connection.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (transaction = connection.BeginTransaction())
{
var DT_tbl_FirstTable = DataTableHelper.CopyToDataTable(source);
var DT_tbl_SecondTable = DataTableHelper.CopyToDataTable(sourceChild);
using (SqlBulkCopy bulkCopy_tbl_FirstTable = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))
{
bulkCopy_tbl_FirstTable.BatchSize = 5000;
bulkCopy_tbl_FirstTable.DestinationTableName = tableName;
List <DataColumn> pkList = new List <DataColumn>();
foreach (DataColumn dc in DT_tbl_FirstTable.Columns)
{
if (dc.ColumnName == "lastException")
{
continue;
}
if (dc.ColumnName == "isNew")
{
continue;
}
if (dc.ColumnName == "context")
{
continue;
}
if (dc.ColumnName == "HasObject")
{
continue;
}
if (dc.ColumnName == "entity")
{
continue;
}
if (dc.ColumnName == "ModelandNumber")
{
continue;
}
if (dc.ColumnName == "Speciality")
{
continue;
}
if (dc.ColumnName == "PackageTypeName")
{
continue;
}
if (dc.ColumnName == "LastBookCount")
{
continue;
}
if (dc.ColumnName == "ItemModels")
{
continue;
}
if (dc.ColumnName == "BookPackItemID")
{
dc.Unique = true;
dc.AutoIncrement = true;
dc.AutoIncrementStep = 1;
pkList.Add(dc);
}
string [] columnsList = { "BookPackItemID", "BookPackingOperationID", "PackCode", "PackSerial", "Weight", "OperationStatusID"
, "ParentID", "StartBookSerial", "LastBookSerial", "BookPackItemOperationID", "ParentBookPackItemID" };
if (columnsList.Contains(dc.ColumnName))
{
bulkCopy_tbl_FirstTable.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dc.ColumnName, dc.ColumnName));
}
}
DT_tbl_FirstTable.PrimaryKey = pkList.ToArray();
//bulkCopy_tbl_FirstTable.ColumnMappings.Add("ID", "ID");
//bulkCopy_tbl_FirstTable.ColumnMappings.Add("UploadFileID", "UploadFileID");
//bulkCopy_tbl_FirstTable.ColumnMappings.Add("Active", "Active");
//bulkCopy_tbl_FirstTable.ColumnMappings.Add("CreatedUserID", "CreatedUserID");
//bulkCopy_tbl_FirstTable.ColumnMappings.Add("CreatedDate", "CreatedDate");
//bulkCopy_tbl_FirstTable.ColumnMappings.Add("UpdatedUserID", "UpdatedUserID");
//bulkCopy_tbl_FirstTable.ColumnMappings.Add("UpdatedDate", "UpdatedDate");
bulkCopy_tbl_FirstTable.WriteToServer(DT_tbl_FirstTable);
}
using (SqlBulkCopy bulkCopy_tbl_SecondTable = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))
{
bulkCopy_tbl_SecondTable.BatchSize = 5000;
bulkCopy_tbl_SecondTable.DestinationTableName = tableNameChild;
List <DataColumn> pkList = new List <DataColumn>();
foreach (DataColumn dc in DT_tbl_SecondTable.Columns)
{
if (dc.ColumnName == "lastException")
{
continue;
}
if (dc.ColumnName == "isNew")
{
continue;
}
if (dc.ColumnName == "context")
{
continue;
}
if (dc.ColumnName == "HasObject")
{
continue;
}
if (dc.ColumnName == "entity")
{
continue;
}
if (dc.ColumnName == "BookPackItemModelID")
{
continue;
}
string[] columnsList = { "BookPackItemModelID", "BookPackItemID", "ExamModelID" };
if (columnsList.Contains(dc.ColumnName))
{
bulkCopy_tbl_SecondTable.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dc.ColumnName, dc.ColumnName));
}
}
DT_tbl_SecondTable.PrimaryKey = pkList.ToArray();
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("ID", "ID");
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("UploadFileDetailID", "UploadFileDetailID");
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("CompaignFieldMasterID", "CompaignFieldMasterID");
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("Value", "Value");
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("Active", "Active");
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("CreatedUserID", "CreatedUserID");
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("CreatedDate", "CreatedDate");
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("UpdatedUserID", "UpdatedUserID");
//bulkCopy_tbl_SecondTable.ColumnMappings.Add("UpdatedDate", "UpdatedDate");
bulkCopy_tbl_SecondTable.WriteToServer(DT_tbl_SecondTable);
}
transaction.Commit();
IsSuccessSave = true;
}
connection.Close();
}
}
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
if (ex.InnerException != null)
{
}
}
return(IsSuccessSave);
}