Passing a table to a stored procedure in sql server using table valued parameters

Many times we come across a situation where we need to pass a table to stored procedure from c# code.In such scenarios what we can do is either loop through table and send rows one by one or we can directly pass the full table to the procedure.Passing rows one by one may be inefficient as we have to iterate through rows and call procedure again and again. Sql Server provides us an efficient way of doing the same using 'User Defined Types' So for passing a table valued parameter to a stored procedure we need to create a user defined table type that will have same columns that we want to pass to the table. Database Node > Programmability > Types > User-Defined Table Types Now we create a table which will be filled by stored procedure.

CREATE TABLE [dbo].[Employee](
 [Emp_ID] [int] IDENTITY(1,1) NOT NULL,
 [Emp_name] [varchar](100) NULL,
 [Emp_Sal] [decimal](10, 2) NULL
) ON [PRIMARY]

GO

Once the table is being created we need to create a type same as that of a table.

CREATE TYPE Employee AS TABLE 
(
 [Emp_ID] [int] IDENTITY(1,1) NOT NULL,
 [Emp_name] [varchar](100) NULL,
 [Emp_Sal] [decimal](10, 2) NULL
)
GO

Now we are done with creating a type and a table we need to create a stored procedure that will accept a type and insert int the table using the type.

CREATE PROCEDURE sp_InsertEmployee
 @employees employee READONLY
 
  AS
 INSERT INTO Employee(Emp_name,Emp_Sal)
 
 SELECT Emp_name,Emp_Sal
 FROM @employees 

Where @employees is a table valued parameter passed to the stored procedure . Now we are done with creating a procedure ,its time to check how it works.You can pass a datatable from C# or VB Code as a parameter.What i will do here as a demonstration i will create a table variable and pass that table to the stored procedure .

DECLARE @testtable employee
  INSERT INTO @testtable(Emp_name,Emp_Sal)
 VALUES ('Anees', 1000.00),
('Rick', 1200.00),
('John', 1100.00)
select * from @testtable

Now we have our table ready we need to pass it to stored procedure and see the result.

DECLARE @testtable employee
  INSERT INTO @testtable(Emp_name,Emp_Sal)
 VALUES ('Anees', 1000.00),
('Rick', 1200.00),
('John', 1100.00)

exec  sp_InsertEmployee @testtable
select * from Employee

Read data from Excel file(xls,xlsx,csv) in Asp.net MVC

In this article, we will see how to display data from an Excel spreadsheet(xlx,xlsx,csv) using ASP.NET MVC. We will connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then display the data in a View.
First of all we will create a ImportExcel in Home Controller which returns a View.This method will return a View for Get Request. Now we will create another method ImportExcel1 and decorate it with [HttpPost] Attribute.Since in MVC 2 Methods cannot have same method name,we can call 2 actions using Action Name attribute .So we will decorate ImportExcel1 with [ActionName("Importexcel")]. Now if we make a get request then ImportExcel will be called and for post request ImportExcel1 will be called.Below is the code to read excel files.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ExcelUpload.Models;

namespace ExcelUpload.Controllers
{
    public class HomeController : Controller
    {
       
        public ActionResult ImportExcel()
        {
           

            return View();
        }
        [ActionName("Importexcel")]
        [HttpPost]
        public ActionResult Importexcel1()
        {


            if (Request.Files["FileUpload1"].ContentLength > 0)
            {
                string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
                string query = null;
                string connString = "";
                



                string[] validFileTypes = { ".xls", ".xlsx", ".csv" };
                
                string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName);
                if (!Directory.Exists(path1))
                {
                    Directory.CreateDirectory(Server.MapPath("~/Content/Uploads"));
                }
                if (validFileTypes.Contains(extension))
                {
                    if (System.IO.File.Exists(path1))
                    { System.IO.File.Delete(path1); }
                    Request.Files["FileUpload1"].SaveAs(path1);
                    if(extension==".csv")
                    {
                     DataTable dt=   Utility.ConvertCSVtoDataTable(path1);
                     ViewBag.Data = dt;
                    }
                    //Connection String to Excel Workbook
                   else if (extension.Trim() == ".xls")
                    {
                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        DataTable dt = Utility.ConvertXSLXtoDataTable(path1,connString);
                        ViewBag.Data = dt;
                    }
                    else if (extension.Trim() == ".xlsx")
                    {
                        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        DataTable dt = Utility.ConvertXSLXtoDataTable(path1, connString);
                        ViewBag.Data = dt;
                    }

                }
                else
                {
                    ViewBag.Error = "Please Upload Files in .xls, .xlsx or .csv format";

                }
                
              }

            return View();
        }

        
    }
}

Here we have created a static class Utility ,which contains 2 methods ConvertCSVtoDataTable and ConvertXSLXtoDataTable.Below is the code for Utility class.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Data.OleDb;

namespace ExcelUpload.Models
{
    public static class Utility
    {
        public static DataTable ConvertCSVtoDataTable(string strFilePath)
        {
            DataTable dt = new DataTable();
            using (StreamReader sr = new StreamReader(strFilePath))
            {
                string[] headers = sr.ReadLine().Split(',');
                foreach (string header in headers)
                {
                    dt.Columns.Add(header);
                }

                while (!sr.EndOfStream)
                {
                    string[] rows = sr.ReadLine().Split(',');
                    if (rows.Length > 1)
                    {
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            dr[i] = rows[i].Trim();
                        }
                        dt.Rows.Add(dr);
                    }
                }

            }


            return dt;
        }

        public static DataTable ConvertXSLXtoDataTable(string strFilePath,string connString)
        {
            OleDbConnection oledbConn = new OleDbConnection(connString);
            DataTable dt=new DataTable();
            try
            {
               
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                oleda.Fill(ds);

                dt= ds.Tables[0];
               
            }
            catch
            {
            }
            finally
            {
                
                oledbConn.Close();
            }

            return dt;

        }
        }
}

Now we will create a view that contains file upload control and a button.When a request for ImportExcel of Home Controller is made,we will show file upload control with button control.When we select a file and press button it will make a post request to Home Controller and ImportExcel1 method will be called.Below is the Razor View for both request.

@using System.Data;
@{
    ViewBag.Title = "ImportExcel";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>ImportExcel</h2>
    <!--[if !IE]><!-->
<style type="text/css">


  /* Generic Styling, for Desktops/Laptops */
table {
  width: 100%;
  border-collapse: collapse;
}
/* Zebra striping */
tr:nth-of-type(odd) {
  background: #eee;
}
th {
  background: #333;
  color: white;
  font-weight: bold;
}
td, th {
  padding: 6px;
  border: 1px solid #ccc;
  text-align: left;
}
/*
Max width before this PARTICULAR table gets nasty
This query will take effect for any screen smaller than 760px
and also iPads specifically.
*/
@@media only screen and (max-width: 760px),
(min-device-width: 768px) and (max-device-width: 1024px)  {

 /* Force table to not be like tables anymore */
 table, thead, tbody, th, td, tr {
  display: block;
 }

 /* Hide table headers (but not display: none;, for accessibility) */
 thead tr {
  position: absolute;
  top: -9999px;
  left: -9999px;
 }

 tr { border: 1px solid #ccc; }

 td {
  /* Behave  like a "row" */
  border: none;
  border-bottom: 1px solid #eee;
  position: relative;
  padding-left: 50%;
 }

 td:before {
  /* Now like a table header */
  position: absolute;
  /* Top/left values mimic padding */
  top: 6px;
  left: 6px;
  width: 45%;
  padding-right: 10px;
  white-space: nowrap;
 }

 /*
 Label the data
 */
        td:before {
            content: attr(data-title);
        }
 
}
</style>
           
<!--<![endif]-->
@using (Html.BeginForm("ImportExcel","Home",FormMethod.Post,new { enctype = "multipart/form-data" } ))

{
    <table>
        <tr><td>Excel file</td><td><input type="file" id="FileUpload1" name="FileUpload1" /></td></tr>
        <tr><td></td><td><input type="submit" id="Submit" name="Submit" value="Submit" /></td></tr>
    </table>
}

<div>
    <table id="">
        
            @if (ViewBag.Data != null)
            {
            <thead>

                @foreach (DataColumn column in (ViewBag.Data as System.Data.DataTable).Columns)
                {
                <th>@column.ColumnName.ToUpper()</th>


                }
       </thead>
                if ((ViewBag.Data as System.Data.DataTable).Rows.Count > 0)
                {
                    foreach (DataRow dr in (ViewBag.Data as System.Data.DataTable).Rows)
                    {

                <tr>

                   @foreach (DataColumn column in (ViewBag.Data as System.Data.DataTable).Columns)
                   {
                    <td data-title='@column.ColumnName'>
                        
                        @dr[column].ToString()&nbsp;
                    </td>
                   }




                </tr>

                    }

                }
                else
                {
                    int count = (ViewBag.Data as System.Data.DataTable).Columns.Count;
                    <tr>

                          <td colspan='@count' style="color:red;" >

                               No Data Found.
                            </td>
                       


                    </tr>

                }

            }
            else
            {
                if (ViewBag.Error != null)
                {
                    <tr>
                    <td  style = "color:red;" >

       @(ViewBag.Error != null ? ViewBag.Error.ToString() : "")
                            </td >



                    </tr >
                    }
                }
            </table>
</div>