title: Uploading csv file using C# (ASP.NET MVC)
body: <p>I have a CSV file which contain the following:</p>
  
  <pre><code>ProductName,EmployeeID,EmployeeName,ContactNo,Adddress
  iPad,1233,Tom,89897898,34 Pitt st
  iPad,1573,Jack,8978 9689,50 George st
  iPad,1893,Peter,8878 8989,32 Martin st 
  </code></pre>
  
  <p>The following code will insert into one table. What I am trying to achieve is to insert into 2 tables:</p>
  
  <pre>
  Product table (parent table)
  ProductId(Pk), ProductName
  
  Employee Table (child table)
  EmployeeId(Pk), ProductId(fk), EmployeeName, ContactNo, Address
  </pre>
  
  <p>So I need to basically insert the record first into Product table and then into Employee table from the CSV file.</p>
  
  <p><strong>Controller.cs</strong></p>
  
  <pre><code>[HttpPost]
  public ActionResult Index(HttpPostedFileBase FileUpload)
  {
      // Set up DataTable place holder 
  
      Guid ProductId= Guid.NewGuid();
      using (SqlConnection conn = new SqlConnection(connString))
      {
          conn.Open();
  
          using (SqlCommand cmd = new SqlCommand(
                 "INSERT INTO Product VALUES(" + "@ReferralListID,  @ProductName)", conn))
          {
              //Note product name need to read from csv file
              cmd.Parameters.AddWithValue("@ProductId", ProductId);
              cmd.Parameters.AddWithValue("@ProductName", ProductName); 
  
              int rows = cmd.ExecuteNonQuery();
  
              //rows number of record got inserted
          }
      }
  
      DataTable dt = new DataTable();
  
      //check we have a file 
      if (FileUpload.ContentLength &gt; 0)
      {
          //Workout our file path
          string fileName = Path.GetFileName(FileUpload.FileName);
          string path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);
  
          //Try and upload
          try
          {
              FileUpload.SaveAs(path);
              //Process the CSV file and capture the results to our DataTable place holder
              dt = ProcessCSV(path);
  
              //Process the DataTable and capture the results to our SQL Bulk copy
              ViewData["Feedback"] = ProcessBulkCopy(dt);
          }
          catch (Exception ex)
          {
              //Catch errors
              ViewData["Feedback"] = ex.Message;
          }
      }
      else
      {
          //Catch errors
          ViewData["Feedback"] = "Please select a file";
      }
  
      //Tidy up
      dt.Dispose();
  
      return View("Index", ViewData["Feedback"]);
  }
  
  /// &lt;summary&gt;
  /// Process the file supplied and process the CSV to a dynamic datatable
  /// &lt;/summary&gt;
  /// &lt;param name="fileName"&gt;String&lt;/param&gt;
  /// &lt;returns&gt;DataTable&lt;/returns&gt;
  private static DataTable ProcessCSV(string fileName)
  {
      //Set up our variables 
      string Feedback = string.Empty;
      string line = string.Empty;
      string[] strArray;  
      DataTable dt = new DataTable();
      DataRow row;
  
      // work out where we should split on comma, but not in a sentance
      Regex r = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
  
      //Set the filename in to our stream
      StreamReader sr = new StreamReader(fileName);
  
      //Read the first line and split the string at , with our regular express in to an array
      line = sr.ReadLine();
      strArray = r.Split(line);
  
      //For each item in the new split array, dynamically builds our Data columns. Save us having to worry about it.
      Array.ForEach(strArray, s =&gt; dt.Columns.Add(new DataColumn()));
  
  
      //Read each line in the CVS file until it's empty
      while ((line = sr.ReadLine()) != null)
      {
          row = dt.NewRow();
  
          //add our current value to our data row
          row.ItemArray = r.Split(line);
          dt.Rows.Add(row);
      }
  
      //Tidy Streameader up
      sr.Dispose();
  
      //return a the new DataTable
      return dt;
  
  
  }
  
  /// &lt;summary&gt;
  /// Take the DataTable and using WriteToServer(DataTable) send it all to the database table "BulkImportDetails" in one go
  /// &lt;/summary&gt;
  /// &lt;param name="dt"&gt;DataTable&lt;/param&gt;
  /// &lt;returns&gt;String&lt;/returns&gt;
  private static String ProcessBulkCopy(DataTable dt)
  {
      string Feedback = string.Empty;
      string connString = ConfigurationManager.ConnectionStrings["DataBaseConnectionString"].ConnectionString;
  
      //make our connection and dispose at the end    
      using(  SqlConnection conn = new SqlConnection(connString))
      {
          //make our command and dispose at the end
          using (var copy = new SqlBulkCopy(conn))
          {
              //Open our connection
              conn.Open();
  
              //Set target table and tell the number of rows
              copy.DestinationTableName = "Employee";
              copy.BatchSize = dt.Rows.Count;
              try
              {
                  //Send it to the server
                  copy.WriteToServer(dt);
                  Feedback = "Upload complete";
              }
              catch (Exception ex)
              {
                  Feedback = ex.Message;
              }
          }
      }
  
      return Feedback;
  }
  </code></pre>
  
  <p><strong>View.aspx</strong></p>
  
  <pre><code>&lt;asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server"&gt;
      Home Page
  &lt;/asp:Content&gt;
  
  &lt;asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"&gt;
  
      &lt;h2&gt;CSV Bulk Upload&lt;/h2&gt; 
  
      &lt;% using (Html.BeginForm("","",FormMethod.Post, new {enctype="multipart/form-data"})){ %&gt;
  
          &lt;input type="file" name="FileUpload" /&gt;
          &lt;input type="submit" name="Submit" id="Submit" value="Upload" /&gt;
      &lt;% } %&gt;
  
      &lt;p&gt;&lt;%= Html.Encode(ViewData["Feedback"]) %&gt;&lt;/p&gt; 
  &lt;/asp:Content&gt;
  </code></pre>
  
  <p><strong>Stored Procedure</strong></p>
  
  <pre><code>USE [BULkDatabase]
  GO
  
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER OFF
  GO
  
  
  
  CREATE PROCEDURE [dbo].[InsertProdutInfo] 
  (
     @ProductName varchar (50),  
     @EmployeeName varchar (50),
     @EmployeeAddress varchar (50)
  )
  
  AS
  
  
  BEGIN TRAN
  
     update [dbo.Product] 
     set    [ProductName] = @ProductName
     where  [ProductName] = @ProductName;
  
     -- get product id
     select ProductId = [ProductId] 
     from   [dbo.Product]
     where  [ProductName] = @ProductName;  
  
     if @@rowcount = 0
     BEGIN TRAN
  
        DECLARE @ProductId uniqueidentifier
        -- there's no such product, let's create it
        insert into [dbo.Product]
        values (NEWID(),@ProductName);
  
        select @ProductId = SCOPE_IDENTITY()
     end
  
     -- now that we know we have added the product and have the id, let's add the rest
     insert into [dbo.Employees]
     values (NEWID(), @EmployeeName, @EmployeeAddress, @ProductId);
  
  COMMIT TRAN
  </code></pre>