I've written about bulk insert methods for text files in sql server 2005.
.Net 2.0 brings the
SqlBulkCopy Class in
System.Data.SqlClient namespace.
Speed wise it can't even compare to native sql server methods but it's usefull
if it's the only viable solution.
SqlBulkCopy.WriteToServer has 4 overloads:
SqlBulkCopy.WriteToServer (DataRow[])
Copies all rows from the supplied DataRow array to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (DataTable)
Copies all rows in the supplied DataTable to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (IDataReader)
Copies all rows in the supplied IDataReader to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (DataTable, DataRowState)
Copies only rows that match the supplied row state in the supplied DataTable to a
destination table specified by the DestinationTableName property of the SqlBulkCopy object.
When importing text files with this method you have to create a DataTable first, import the text file
to the created DataTable and then write this DataTable to server.
With this we're acctually performing 2 tasks in .net:
1. Fill data from text file to DataTable in memory
2. Fill data from DataTable in memory to SQL server
Compared to SQL servers native bulk import methods where we just import the text file directly.
I used the same file and the same table structure as in previous bulk import methods described
here.
The time it took to complete the whole process was around 30 seconds.
This is the code i used for import:
private void StartImport()
{
Stopwatch sw = new Stopwatch();
sw.Start();
SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=ServerName;Database=test;Trusted_Connection=True;",
SqlBulkCopyOptions.TableLock);
bulkCopy.DestinationTableName = "dbo.testSQLBulkCopy";
bulkCopy.WriteToServer(CreateDataTableFromFile());
sw.Stop();
txtResult.Text = (sw.ElapsedMilliseconds/1000.00).ToString();
}
private DataTable CreateDataTableFromFile()
{
DataTable dt = new DataTable();
DataColumn dc;
DataRow dr;
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Int32");
dc.ColumnName = "c1";
dc.Unique = false;
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Int32");
dc.ColumnName = "c2";
dc.Unique = false;
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Int32");
dc.ColumnName = "c3";
dc.Unique = false;
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Int32");
dc.ColumnName = "c4";
dc.Unique = false;
dt.Columns.Add(dc);
StreamReader sr = new StreamReader(@"d:\work\test.txt");
string input;
while ((input = sr.ReadLine()) != null)
{
string[] s = input.Split(new char[] { '|' });
dr = dt.NewRow();
dr["c1"] = s[0];
dr["c2"] = s[1];
dr["c3"] = s[2];
dr["c4"] = s[3];
dt.Rows.Add(dr);
}
sr.Close();
return dt;
}
|
|