Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- foreach (var line in File.ReadLines(fileLocation + @"" + filename))
- {
- var dataColumns = line.Split('t');
- var dr = dt.NewRow();
- for (var i = 0; i < dataColumns.Length; i++)
- {
- dr[i] = dataColumns[i].Trim().Length == 0 ? null : dataColumns[i];
- }
- dt.Rows.Add(dr);
- }
- using (var destinationConnection = new SqlConnection(Settings.Default.ConnectionString))
- {
- var tableName = filename.Substring(0, filename.IndexOf('.'));
- destinationConnection.Open();
- try
- {
- using (var createAndDropTableCommand = new SqlCommand("sp_DropAndCreateTable", destinationConnection))
- {
- createAndDropTableCommand.CommandType = CommandType.StoredProcedure;
- createAndDropTableCommand.Parameters.Add("@TableToCreate", SqlDbType.VarChar).Value = tableName;
- createAndDropTableCommand.ExecuteNonQuery();
- }
- using (var bulkCopy = new SqlBulkCopy(destinationConnection))
- {
- bulkCopy.DestinationTableName = "dbo." + tableName;
- bulkCopy.BatchSize = 10000;
- bulkCopy.ColumnMappings.Clear();
- foreach (DataColumn col in dt.Columns)
- {
- bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
- }
- bulkCopy.WriteToServer(dt);
- }
- }
- finally
- {
- destinationConnection.Close();
- }
- }
- System.Data.DataSet oDataSet = new System.Data.DataSet();
- oDataSet.ReadXmlSchema(strSchemaXml);
- oDataSet.ReadXml(strTableXml);
- if (oDataSet.Tables.Count > 0)
- {
- using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(oTargetDatabase.ConnectionString))
- {
- conn.Open();
- System.Data.SqlClient.SqlTransaction oTran = conn.BeginTransaction();
- System.Data.SqlClient.SqlBulkCopy oSqlBulkCopy
- = new System.Data.SqlClient.SqlBulkCopy(conn, System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity, oTran);
- oSqlBulkCopy.BulkCopyTimeout = 600;
- oSqlBulkCopy.BatchSize = 1000;
- oSqlBulkCopy.DestinationTableName = strFullyQualifiedTableName;
- oSqlBulkCopy.NotifyAfter = 10000;
- oSqlBulkCopy.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(oSqlBulkCopy_SqlRowsCopied);
- foreach (System.Data.DataColumn ocol in oDataSet.Tables[0].Columns)
- {
- oSqlBulkCopy.ColumnMappings.Add(ocol.ColumnName, ocol.ColumnName);
- }
- oSqlBulkCopy.WriteToServer(oDataSet.Tables[0]);
- oTran.Commit();
- conn.Close();
- }
- System.Console.WriteLine("Wrote : " + oDataSet.Tables[0].Rows.Count.ToString() + " records ");
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement