Had some fun with this today as I was utterly confused why I wasn’t able to save data to my SQL2005 express DB (linked within a Windows Forms VS 2005 project), even though I wasn’t getting any errors from a SQLBulkCopy operation. The connection string was set (from the wizard) to use ‘|DataDirectory|\dbname.mdf.
It turned out of course that the reason there wasn’t an error was because the data was being written fine, but just not to the database I was looking at. By default any database linked in your project file will be copied to the output folder (bin\debug) when you build. It does this ‘every time‘. I thought I was going mad as I wasn’t getting primary key violations trying to insert the same data over and over again!
The useful info is in this MSDN blog entry. It talks about different options for working around the local databases, but I plumped for a subfolder (called data) in my project, then setting ‘Copy if newer’ on the MDF file rather than ‘do not copy’. This just means that if you make a schema change then it will blow everything away (probably what I normally want in debug-land).
The reason I was using the SQLBulkCopy class was so I could take in a dataset picked from a semicolon-delimited text file.
The first thing to do was read the text file into a dataset – and after some mucking about with the Jet OleDb provider and avoiding changing registry entries (to get it to recognise ‘;’ as the delimiter) I found that a schema.ini file is what’s required.
Here’s some quick code I then used to get a DataSet from a semicolon-delimited Text File:
/// Import from a given file to database
/// </summary>
/// <param name=”fileName”>File to import from (CSV)</param>
public void Import(string fileName)
{
if (File.Exists(fileName))
{
SqlConnection connection = new SqlConnection(Properties.Settings.Default.RBStatsConnectionString);
//Get Dataset from file
DataSet importData = GetDataSetFromFile(fileName);
//Import using SQLBulkCopy
SqlBulkCopy copyManager = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
copyManager.DestinationTableName = “TShirtStatistic”;
copyManager.BatchSize = importData.Tables[0].Rows.Count;
try
{
copyManager.WriteToServer(importData.Tables[0]);
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
copyManager.Close();
connection.Close();
}
}
public DataSet GetDataSetFromFile(string fileName)
{
//Ensure we’ve got a schema file to fudge the semicolon delimited text
string schemaFile = Path.Combine(Path.GetDirectoryName(fileName), “schema.ini”);
File.Delete(schemaFile);
string schemaContents = “[” + Path.GetFileName(fileName) + “]\nColNameHeader=True\nCharacterSet=ANSI\nFormat=Delimited(;)”;
StreamWriter writer = File.CreateText(schemaFile);
writer.Write(schemaContents);
writer.Close();
string
connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + Path.GetDirectoryName(fileName) + “\\;Extended Properties=’text;HDR=Yes;FMT=Delimited'”;string query = “SELECT * FROM ” + Path.GetFileName(fileName);
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand(query, connection);
connection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);
DataSet ds = new DataSet();
adapter.Fill(ds);
return
ds;}