none
How to transfer data from my local data table to sql server table (using EF 5.0) -- currently using SqlBulkCopy RRS feed

  • Question

  • I'm just checking if there is a feature of EF for transferring data from a local data table in my (C# VS2012) project to a sql server 2008 (R2) table. My project contains an edmx file and the table I want to transfer data to. Currently, I am populating a local datatable with data from a textfile (the textfile data is dirty and I clean it up in the project). The I am using SqlBulkCopy to transfer the data from the local table to the sql server table.  But is there a feature of Entity Framework that I am not taking advantage of here?

    here's how I currently transfer the data

    string connString = "Data Source=win7; Initial Catalog=EF1_DB; Integrated Security=SSPI;";
    using (SqlConnection destinationConnection = new SqlConnection(connString))
    {
       destinationConnection.Open();
    
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
       {
           bulkCopy.DestinationTableName = "dbo.tbl3";
           bulkCopy.WriteToServer(tbl3);
       }
    }


    Rich P

    Thursday, December 4, 2014 7:34 PM

Answers

All replies

  • Just rewording my question.  I can write data to my Entity Model table like this:

    EF1_DBContext db1 = new EF1_DBContext();
    
    var name = txtName.Text;
    var url = txtURL.Text;
    
    var blogTbl = new BlogsTbl { Namefld = name, Urlfld = url };
    db1.BlogsTbls.Add(blogTbl);
    db1.SaveChanges();

    Now I want to transfer bulk data to a table in my Entity model.  How do I do this the Entity framework way?  I don't think that using SqlBulkCopy is the Entity Framework way.  I have to add a separate connection string to that method.  How to do this the Entity Framework way?


    Rich P

    Friday, December 5, 2014 12:01 AM
  • I'm just checking if there is a feature of EF for transferring data from a local data table in my (C# VS2012) project to a sql server 2008 (R2) table. My project contains an edmx file and the table I want to transfer data to. Currently, I am populating a local datatable with data from a textfile (the textfile data is dirty and I clean it up in the project). The I am using SqlBulkCopy to transfer the data from the local table to the sql server table.  But is there a feature of Entity Framework that I am not taking advantage of here?

    I wouldn't even be bothered with this. :) I would use the power of MS SQL Server Service Broker, which can be implemented on the MS SQL Server Express and Server editions of the database, I would make make the SB go into communications between the two databases on two different machines over the wirer,  sending XML data representing a table record (messages) to the queue of the other database, let the other database pull the XML out of the queue and update its table.

    http://sqlblog.com/blogs/allen_white/archive/2010/01/06/service-broker-basics.aspx

    The article is just a small portion of what MS SQL Server Broker can do. The SB can use CLR-Triggers, C# or  VB .NET code hosted by SB and it can use EF too. And you can do it in real time.

    http://msdn.microsoft.com/en-us/library/bb522893.aspx

    http://www.amazon.com/Server-Service-Broker-Books-Professionals/dp/1590599993

    Or you can have your program create the XML, connect to the queue in MS SQL 2008 Server on the database server, send the XML to the queue and SB do its thing to update the table.

    Friday, December 5, 2014 5:01 AM
  • Thank you for the reply.  I suspect that the files may be too large for xml -- like 50,000+ rows.  Plus, I need to do a little data processing/massaging/parsing before porting it to a sql server.  If I can get around the xml part, the broker may be a viable option.  The other part is that I have to download the data from the internet.  Currently, I'm using HttpClient, HttpResponsMessage, HttpContent to retrieve the  raw data.  While I have the data in my app, I do the processing there and then write/pass/insert the data to the sql server. 

    I will take a look at the links you provided.


    Rich P

    Friday, December 5, 2014 11:50 AM
  • I have seen SB work with hundred of thousands of records on a mirrioring action between tables in realtime throughout the day. 50,000 rows are nothing to it. But that would also be based on the power of the machine hosthing MS SQL server too.
    Friday, December 5, 2014 5:51 PM
  • Question:  in the actual routine I am using http... commands to download data.  Originally, I was using a curl.exe app to download the files as .txt files, but with the http commands I can actually query the desired data in the url that I pass to the http commands and download these records.  In my c# app I download the raw data then parse it to another local dataTable, and then from that table I use SqlBulkCopy to transfer the data to the sql server.  The question is if the sql server broker could perform an operation like that or maybe I could start a job to launch my app?  The app has to run once a day daily.  I was thinking about creating a windows service, but maybe the sql server broker would be a better way to go?

    Rich P

    Friday, December 5, 2014 7:19 PM
  • There were two ways that XML records were dumped into the SB queues. One way was to use the CLR Triggers for table insert, update and delete. The triggers built the XML record transaction and  pushed the the XML trnascations into the SB queues.

    The otherway was to use a crawler that captuered records, sent the XML into the SB queues for processing, which was hosted by a Windows Service.

    You can XML seralize a datatable and send it into the queue. Don't forget that SB works with the .NET CLR so VB or C# .NET can be used.

    http://msdn.microsoft.com/en-us/library/ms131093.aspx

    http://blog.maskalik.com/sql-server-service-broker/scalable-webservice-calls-from-database/

    http://msdn.microsoft.com/en-us/library/ms131094.aspx

    You'll just have to investigate and findout what you can do. I worked with two developers that used it where I was working, and they had nothing but good things to say about it. I have messed around a little bit with SB, but I know what it can do, becuase I have seen it in action.

      
    Friday, December 5, 2014 9:01 PM
  • I guess I will investigate further about the sql server broker manager.  My team lead wants me to keep the c# app I created (just a console exe) and wants me to find out if SB can launch it on a scheduled job type basis.  I was originally going to create a windows service but SB would appear to be less complicated.  But you have provided me with some food for thought and a starting point to get my project rolling.

    Many thanks


    Rich P

    Saturday, December 6, 2014 1:42 AM