[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Uploading Data from DataSet to two different tables in SQL2005 using LINQ to DataSet and LINQ to SQL.

Posted on 2009-04-24
1
Medium Priority
?
325 Views
Last Modified: 2013-11-11
Uploading Data from DataSet to two different tables in SQL2005 using LINQ to DataSet and LINQ to SQL.

Hi,
I have Excel file (Excel2007 file -C:\ExcelTest.xlsx)
And two tables in SQL Server 2005 database
I use VS2008/C#
I need to upload this file to dataset (It was done, and you can see the code sample).Everything is OK.
Finally I need to use LINQ to Dataset, make some parsing, for example use case statements, or pull same data from this dastaset and upload this data to two different existing tables in the same database.
I have some restrictions, I can not use SSIS, CLR store procs and so on for this uploading. I can use only LINQ and I can not use Linq Excel providers.
I am not familiar with LINQ,please give me a code sample, how to take data from DataSet, make some query and upload this data to two sql server tables.
I know I can use LINQ to DataSet and LINQ to SQL.
Thank You,
J_Kogan 4/24/2009

This is sample of my code It is working OK, but it is only dataset. 
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient; 
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
 
    protected void btnImportExcelToGrid_Click(object sender, EventArgs e)
    {
       
        string fileName = FileUpload1.PostedFile.FileName;
       
        string strConn = @"Data Source=" + fileName + ";Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;";
        
        //You must use the $ after the object
        //you reference in the spreadsheet
 
 
        //sheet#3 I need sheet#3 only NPS ENL ACC WITH QUALITY GOALS -name of the sheet
        DataSet ds3 = new DataSet();
        OleDbDataAdapter da3 = new OleDbDataAdapter
       ("SELECT * FROM [NPS ENL ACC WITH QUALITY GOALS $]", strConn);
        da3.Fill(ds3);
        //GreedView is only for the show
        GridView3.DataSource = ds3.Tables[0].DefaultView;
    
        GridView3.DataBind();       
             
 
        
    }
    
 
    }

Open in new window

0
Comment
Question by:J_Kogan
1 Comment
 
LVL 21

Accepted Solution

by:
naspinski earned 1500 total points
ID: 24230802
now you need to cycle through the data, pulling out what you need and pushing it into your linq objects.  FIrst I use Linq to make generic objects of each row, then I run through those rows and insert them into linq-to-sql objects:
var rows = from p in ds3.Tables[0].AsEnumerable()
  select new 
  {
    name = p["Name"],
    address = p["Address"]
    something_else = p["column name"]
  }
 
//now you have a collection of objects that each represents a row, so push it into L2S now
 
foreach(var v in rows)
{
  some_item i = new some_item();
  i.name = v.name;
  i.address = v.address;
  i.other = v.something_else;
  db.InsertOnSubmit(i);
}
db.SubmitChanges();
 
//this is assuming 'db' is your DataContext and you have an object named 'some_item'

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question