Here we are going to load data from a tab delimited text file and then insert this data into SQL Server table using the .Net SQLBulkCopy class.
Here also we will use one the Microsoft sample database tables and that will be AdventureWorksDW2008R2 database and the table we selected for this demo is DimCurrency. The create script for this table is taken from the sample database as reproduce here.
/****** Object: Table [dbo].[DimCurrency] Script Date: 01/30/2012 17:04:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimCurrency](
[CurrencyKey] [int] IDENTITY(1,1) NOT NULL,
[CurrencyAlternateKey] [nchar](3) NOT NULL,
[CurrencyName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_DimCurrency_CurrencyKey] PRIMARY KEY CLUSTERED
(
[CurrencyKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK_DimCurrency_CurrencyAlternateKey] UNIQUE NONCLUSTERED
(
[CurrencyAlternateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
|
You can create this table in any database. I created this table within my TestDB database.
Now the .Net part. What we have in .Net is a Windows forms application with two forms, one main form for selecting the text file and the other to display the data within a grid, design images as shown. The second image shows the running version of the application.
 |
 |
The main form contains the following components
Ø An open file dialog, to select the text file.
Ø 3 text boxes
o TxtFile holds the complete file name.
o TxtPath holds the file folder structure from the root drive letter
o TxtFileName holds the file name with extension
Ø Two buttons
o One for selecting the file
o Other for inserting the data into SQL server.
To bulk insert data through this application, user needs to do the following
1) Select the source file
a. Application deciphers the file name and folder name for the user.
b. Application opens the text file and fills the data into a data table using Jet OLEDB provider and OLEDB connection.
2) Upload to SQL Server
a. Using SQL Connection and .Net SQL Bulk Copy utility.
Note: There are still modifications required to make this application a full-fledged bulk copy application as there are lots of hardcoding within the application, like this will insert data only to the DimCurrency table (BCP’s DestinationTableName
is hardcoded with DimCurrency), also this as of now only accepts tab delimited (hardcoded within OLEDB connection string). To make this work, you need to place schema.ini in the folder where the tab delimited text file resides. The content od schema.ini is given as follows.
[DimCurrency.csv]
Format=TabDelimited
ColNameHeader=False
CharacterSet=ANSI
|
Now the .Net code for frmMain
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.OleDb;
namespace BCPWindow
{
public partial class frmMain : Form
{
public frmMain()
{
InitializeComponent();
}
private void btnSelect_Click(object sender, EventArgs e)
{
openFile.Reset();
openFile.Multiselect = false;
openFile.ShowDialog(this);
openFile.Title = "Select";
string tmp = openFile.FileName;
txtFile.Text = tmp;
txtFolderName.Text = tmp.Substring(0, tmp.LastIndexOf("\\"));
txtFileName.Text = tmp.Substring(tmp.LastIndexOf("\\") + 1);
}
private void btnUpload_Click(object sender, EventArgs e)
{
SqlConnection sqlCn = new SqlConnection("server=(local); Initial catalog=TestDB; integrated security=SSPI");
SqlBulkCopy bcp = new SqlBulkCopy(sqlCn);
string oledbConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
txtFolderName.Text +
"; Extended Properties='text;FMT=TabDelimited;HDR=No;'";
string oledbSQL = string.Format("Select * from [{0}]", txtFileName.Text);
OleDbConnection oledbCN = new OleDbConnection(oledbConStr);
OleDbDataAdapter oledbDA = new OleDbDataAdapter(oledbSQL, oledbCN);
DataTable dt = new DataTable();
try
{
sqlCn.Open();
bcp.BatchSize = 100;
bcp.DestinationTableName = "dbo.DimCurrency";
oledbCN.Open();
oledbDA.Fill(dt);
frmGrid fg = new frmGrid(dt);
fg.Show();
for (int i = 0; i < dt.Columns.Count; i++)
bcp.ColumnMappings.Add(new SqlBulkCopyColumnMapping(i, i));
bcp.WriteToServer(dt);
MessageBox.Show(string.Format(" {0} row(s) inserted successfully.", dt.Rows.Count));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (sqlCn != null)
if (sqlCn.State != ConnectionState.Closed)
sqlCn.Close();
if (oledbCN != null)
if (oledbCN.State != ConnectionState.Closed)
oledbCN.Close();
}
}
private void frmMain_Load(object sender, EventArgs e)
{
}
}
}
|
Similarly code for frmGrid
using System;
using System.Data;
using System.Windows.Forms;
namespace BCPWindow
{
public partial class frmGrid : Form
{
public frmGrid()
{
InitializeComponent();
}
DataTable dt;
public frmGrid( DataTable dt )
{
InitializeComponent();
this.dt = dt;
}
private void frmGrid_Load(object sender, EventArgs e)
{
dg.DataSource = dt;
}
}
}
|
Hope this helps you.
Enjoy!!
No comments:
Post a Comment