Databases
StackOverflow2013
Postsdbo
POusing a local C# DataTable with SqlBulkCopy
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POusing a local C# DataTable with SqlBulkCopy
    primarykey
    data
    text
    <p>as there is so many of them, examples.. i was researching online, though they do not show use of <code>SqlBulkCopy</code>, in the folowing scenario :</p> <p>i have used a query in order to fetch existing Data from SqlServer(2008), into a <code>DataTable</code>, so i could sort the data locally, and avoid hitting database while processing.</p> <p>so now, at that stage, i already have the option to clone the source dataTable Schema using <code>localDataTable = DataTableFromOnlineSqlServer.Clone();</code></p> <p>by doing that <code>Clone()</code>, i now have all the columns, and each of the column-dataType.</p> <p>then in next stage of the program, i am filling the Cloned-From-Db, - that Local (yet Empty) new <code>DataTable</code> with some new data .</p> <p>so by now i have a populated <code>DataTable</code>, and it's ready to be stored in sql server .</p> <p>using this code below yeld no results </p> <pre><code> public string UpdateDBWithNewDtUsingSQLBulkCopy(DataTable TheLocalDtToPush, string TheOnlineSQLTableName) { // Open a connection to the AdventureWorks database. using (SqlConnection connection = new SqlConnection(RCLDBCONString)) { connection.Open(); // Perform an initial count on the destination table. SqlCommand commandRowCount = new SqlCommand("SELECT COUNT(*) FROM "+TheOnlineSQLTableName +";", connection); long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar()); var nl = "\r\n"; string retStrReport = ""; retStrReport = string.Concat(string.Format("Starting row count = {0}", countStart), nl); retStrReport += string.Concat("==================================================", nl); // Create a table with some rows. DataTable newCustomers = TheLocalDtToPush; // Create the SqlBulkCopy object. // Note that the column positions in the source DataTable // match the column positions in the destination table so // there is no need to map columns. using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = TheOnlineSQLTableName; try { // Write from the source to the destination. bulkCopy.WriteToServer(newCustomers); } catch (Exception ex) { Console.WriteLine(ex.Message); } } // Perform a final count on the destination // table to see how many rows were added. long countEnd = System.Convert.ToInt32( commandRowCount.ExecuteScalar()); retStrReport += string.Concat(string.Format("Ending row count = {0}", countEnd), nl); retStrReport += string.Concat("==================================================", nl); retStrReport += string.Concat(string.Format("{0} rows were added.", countEnd - countStart),nl); retStrReport += string.Concat("New Customers Was updated successfully", nl, "END OF PROCESS !"); Console.ReadLine(); return retStrReport; } } </code></pre> <p>now The problem is, that No data was inserted at all.</p> <p>i have made some resarch and there is no solution for me i also checked to make sure that :</p> <ol> <li><p>all the columns of source and destination are aligned (although it is a clone so no wories) </p></li> <li><p>that there is a <code>PK</code> set as <code>IDENTITY</code> column on the Sql server table</p></li> </ol> <p>what am i missing here ? </p> <p>...the "report" i have made, in order to calculate inserted rows tells</p> <p><em>" 0 rows were added "</em></p> <p>and thats it no Errors or exeptions reported /thrown .</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLoneXcoder
    1. USLoneXcoder
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POusing a local C# DataTable with SqlBulkCopy
      1. USRob Lyndon
      1. VTFavorite
    2. VO
      singulars
      1. POusing a local C# DataTable with SqlBulkCopy
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POusing a local C# DataTable with SqlBulkCopy
      1. USAvia Afer
      1. VTFavorite
    1. CODid you check SQL error log, I suspect that your localDT has same primary key as online one. I suggest not to put any value in primary field if you do not have Enable Identity Insert for auto increment column. Also if you are putting lot of fields, try with small set of data first.
      singulars
      1. POusing a local C# DataTable with SqlBulkCopy
      1. USSumit Gupta
    2. COplease let me know how do i check on that issue of the error log & if i made a clone , but i did not fill the custId(pk) - it is identity auto incremented, does the bulkCopy takes care of the issue of - source online has a record custId =1 , does it sinc' the process of insertation ?
      singulars
      1. POusing a local C# DataTable with SqlBulkCopy
      1. USLoneXcoder
    3. CO@SumitGupta what i think is this is simple and i lack the experience with SqlBulkCopy. BTW i Did Use around 230 records . is it too\ much (:
      singulars
      1. POusing a local C# DataTable with SqlBulkCopy
      1. USLoneXcoder
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload