writing dataset to table

[Mike P]

How do you write a dataset that comes from an OleDb Connection to a Sql
Server database?

 

[Guest]

You need to use a:
System.Data.SqlClient.SqlBulkCopy

This is designed for what you are doing as is very fast.

Ciaran O'Donnell

 

[Mike P]

protected void btnUpload_Click(object sender, EventArgs e)
{
string strFileName;

//check file exists
string strCheckFile = @"\\devext02\Xerox_Upload\Xerox_Dataload"
+ DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") +
DateTime.Now.ToString("dd") + ".csv";

if (System.IO.File.Exists(strCheckFile))
{
throw new Exception();
}

File1.PostedFile.SaveAs(strCheckFile);

//add datetime to filename and save to archive folder
strFileName = "Xerox_DataLoad" + DateTime.Now.ToString("yyyy") +
DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd");

File1.PostedFile.SaveAs(@"\\devext02\Xerox_UpLoad\archive\" +
strFileName + ".csv");

DataSet ds = GetCSVDataSet(strCheckFile);

//write dataset to UploadDataStaging
DataTable dtUploadDataStaging = new DataTable("UploadData");
dtUploadDataStaging = ds.Tables["UploadData"];

SqlConnection objConnection = new
SqlConnection(ConfigurationManager.ConnectionStrings["XeroxConnectionStr
ing"].ConnectionString);

System.Data.SqlClient.SqlBulkCopy bc = new
System.Data.SqlClient.SqlBulkCopy(objConnection,
SqlBulkCopyOptions.TableLock, null);

bc.BatchSize = dtUploadDataStaging.Rows.Count;
objConnection.Open();
bc.DestinationTableName = "UploadDataStaging";
bc.WriteToServer(dtUploadDataStaging);
objConnection.Close();
bc.Close();
}

This is my code, which gives the error 'Object reference not set to an
instance of an object.' What am I doing wrong?

 

[Mike P]

Figured this out by creating a DataTable rather than a DataSet in the
first place.