writing a dataset to a table

[fegdvbna22]

Sorry if this is a newbie question, but how do you write a dataset to a table?

 

[markknowsley]

Easiest way is to use the SqlDataAdapter (or OracleDataAdapter) because it will generate all your Insert / Update statements for you if used with SqlCommandBuilder.

I haven't got time to write out any code but the examples on the MSDN website are straightforward, so just Google 'sqldataadapter' and go straight to the Microsoft website.

If you're still having problems after you've read this and tried the examples come back here and I'll see if I can help you further.

 

[PGoRule]

You can do something like this...

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();

da.Fill(ds);
DataTable dtCust = new DataTable("Customer");
DataTable dtEmp = new DataTable("Employee");
            
dtCust = ds.Tables["Customer"];
dtEmp = ds.Tables["Employee"];

If you want them in DataTables...then why to go for DataSet?...Put them directly to DataTable...
This is an extra work, to put in DataSet first and again get back into DataTable.

Sharing the best from my side...

--Prashant--

 

[fegdvbna22]

The reason I'm asking this is because I have filled a dataset with data from a csv file, and I now need to transfer this data to a table.

 

[fegdvbna22]

What do you do once you have the data in a datatable?

 

[PGoRule]

It can be used to fill the controls, display on webforms, reports, graphical representation, many things are possible once you get the data successfully...

Sharing the best from my side...

--Prashant--

 

[fegdvbna22]

But what I want to do is to write the dataset to a SQL Server table.

 

[fegdvbna22]

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["XeroxConnectionString"].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?

 

[fegdvbna22]

Figured this out by created a DataTable instead of a DataSet.