Zip file & download in Asp.Net

using Ionic.Zip;
using System.IO;
 
protected void btnDownload_Click(object sender, EventArgs e)
{
    try
    {
        if (HttpContext.Current.Session != null)
            ViewState["UniqueIdentifier"] = HttpContext.Current.Session.SessionID;
        else
            ViewState["UniqueIdentifier"] = Convert.ToString(Guid.NewGuid());
 
        string fileSavePath = Server.MapPath("Reports\\" + Convert.ToString(ViewState["UniqueIdentifier"]) + "\\SalesReport\\");
 
        if (!Directory.Exists(fileSavePath))
            Directory.CreateDirectory(fileSavePath);
 
        //Save file logic goes here
 
        ZipFileAndDownload();
    }
    catch (Exception ex) { }
}
 
protected void ZipFileAndDownload()
{
    try
    {
        string path = Server.MapPath("~/Reports/" + Convert.ToString(ViewState["UniqueIdentifier"]) + "/");
        if (Directory.Exists(path))
        {
            Response.Clear();
            Response.ClearContent();
            Response.Buffer = true;
            Response.ContentType = "application/zip";
            Response.AddHeader("content-disposition", "attachment; filename=Reports.zip");
            using (ZipFile zipfile = new ZipFile())
            {
                zipfile.AddSelectedFiles("*.*", path, "", true);
                zipfile.Save(Response.OutputStream);
            }
 
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.SuppressContent = true;
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
    }
    catch (Exception ex) { }
    finally { DeleteExistingFile();}
}
 
protected void DeleteExistingFile()
{
    try
    {
        string path = Server.MapPath("Reports\\" + Convert.ToString(ViewState["UniqueIdentifier"]) + "\\");
        if (Directory.Exists(path))
        {
            string[] files = Directory.GetFiles(path);
            foreach (string item in files)
            {
                if (File.Exists(item))
                    File.Delete(item);
            }
            string[] directories = Directory.GetDirectories(path);
            foreach (string dir in directories)
                LoadSubDirectory(dir);
 
            // Delete sub folder of UniqueIdentifier folder
            foreach (string subfolders in Directory.GetDirectories(path))
                Directory.Delete(subfolders, true);
 
            // Delete main UniqueIdentifier folder
            Directory.Delete(path, true);
        }
    }
    catch (Exception ex) { }
}
 
protected void LoadSubDirectory(string directory)
{
    string[] files = Directory.GetFiles(directory);
    foreach (string item in files)
    {
        if (File.Exists(item))
            File.Delete(item);
    }
    string[] directories = Directory.GetDirectories(directory);
    foreach (string dir in directories)
    {
        LoadSubDirectory(dir);
    }

}

Note:- Add Ionic.Zip DLL in the project using NuGet Manager

Select odd and even rows in the SQL

CREATE TABLE #TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier)
 
GO
 
INSERT INTO #TEST (ROWID) VALUES (NEWID())
 
GO 10
 
--drop table #TEST
 
select * from #TEST where Id % 2 = 0 --Even

select * from #TEST where Id % 2 != 0 --Odd

User Defined Functions in SQL

 CREATE FUNCTION ScalarValueFunction()
RETURNS VARCHAR(50)
AS
BEGIN
      DECLARE @date DATETIME
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      SET @date = GETDATE()
 
      RETURN @date
END
 
SELECT dbo.ScalarValueFunction() AS CurrentDate
/*
      1. It returns only one parameter value.
      2. It supports complex logic & in the end, returns only one parameter value.
      3. Cannot access temporary tables from within a function.
      4. It starts and ends with BEGIN...END block.
      5. It is directly used in the SELECT statement.
*/
 
 
CREATE FUNCTION InlineTableValueFunction()
RETURNS TABLE
AS
RETURN
(
      --DECLARE @date DATETIME --Throw an error
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --SET @date = GETDATE()
 
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
      UNION
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
)
 
SELECT * FROM dbo.InlineTableValueFunction()
/*
      1. It returns the table type parameter value.
      2. It does not allow DECLARE & CREATE keyword.
      3. Cannot access temporary tables from within a function.
      4. It starts with the RETURN block.
      5. It allows only one SELECT statement result.
      6. It is used in the FROM statement.
      7. It is also called the inline table-valued function.
*/
 
CREATE FUNCTION MultiStatementTableValueFunction()
RETURNS @DateTable TABLE(CurrentDate VARCHAR(50), TomorrowDate VARCHAR(50))
AS
BEGIN
      DECLARE @date DATETIME
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable1 AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable1 VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      SET @date = GETDATE()
 
      INSERT INTO @DateTable
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
 
      INSERT INTO @DateTable
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
 
      RETURN
END
 
SELECT * FROM dbo.MultiStatementTableValueFunction()
/*
      1. It returns the table structure.
      2. Cannot access temporary tables from within a function.
      3. It starts and ends with BEGIN...END block.
      4. It is used in the FROM statement.
      5. It must have a RETURN keyword.
      6. The function body can have one or more than one statement.
      7. It is also called the table-valued function.

*/

It was not possible to find any compatible framework version

 

Open Project.csproj file

Existing
  <TargetFramework>netcoreapp2.2</TargetFramework>

Change it to
<TargetFramework>netcoreapp3.1</TargetFramework>
 
Note: - You will get the list of frameworks version from an error.

- The following frameworks were found:
      1.0.1 at [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
      3.1.16 at [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]

      5.0.7 at [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]

split data table by rows c#

In this demo, large data table records split into multiple tables based on a given number of records.
For Example, 1000 records split into two tables if we pass a split table with 500 records in each table. Based on the number of records last table record may vary.

Datatable does not contain a definition for AsEnumerable: - Using NuGet Package Manager install the System.Data.DataTableExtensions 

using System;
using System.Collections.Generic;
using System.Data;

using System.Linq;

class Program
{
    public static IEnumerable<IEnumerable<T>> Split<T>(IEnumerable<T> enumerable, int chunkSize)
    {
        int itemsReturned = 0;
        List<T> list = enumerable.ToList();
        int count = list.Count;
        while (itemsReturned < count)
        {
            int currentChunkSize = Math.Min(chunkSize, count - itemsReturned);
            yield return list.GetRange(itemsReturned, currentChunkSize);
            itemsReturned += currentChunkSize;
        }
    }
 
    static void Main(string[] args)
    {
        DataTable dt = new DataTable("Table1");
        dt.Columns.Add("Col1");
        dt.Columns.Add("Col2");
 
        DataRow dr = null;
 
        for (int i = 0; i < 3400; i++)
        {
            dr = dt.NewRow();
            dr["Col1"] = i + 1;
            dr["Col2"] = i + 1;
            dt.Rows.Add(dr);
        }
 
        var tables = Split(dt.AsEnumerable(), 500).Select(rows => rows.CopyToDataTable());
 
        foreach (DataTable item in tables)
        {
            //code goes here
        }
        Console.ReadLine();
    }
}
 
With Extension Method
 
public static class Helper
{
    public static IEnumerable<IEnumerable<T>> Split<T>(this IEnumerable<T> enumerable, int chunkSize)
    {
        int itemsReturned = 0;
        List<T> list = enumerable.ToList();
        int count = list.Count;
        while (itemsReturned < count)
        {
            int currentChunkSize = Math.Min(chunkSize, count - itemsReturned);
            yield return list.GetRange(itemsReturned, currentChunkSize);
            itemsReturned += currentChunkSize;
        }
    }
}
 
class Program
{
    static void Main(string[] args)
    {
        DataTable dt = new DataTable("Table1");
        dt.Columns.Add("Col1");
        dt.Columns.Add("Col2");
 
        DataRow dr = null;
 
        for (int i = 0; i < 3400; i++)
        {
            dr = dt.NewRow();
            dr["Col1"] = i + 1;
            dr["Col2"] = i + 1;
            dt.Rows.Add(dr);
        }
 
        var tables = dt.AsEnumerable().Split(500).Select(rows => rows.CopyToDataTable());
 
        foreach (DataTable item in tables)
        {
            //code goes here
        }
        Console.ReadLine();
    } 

}

yield keyword

Upload and display CSV file data in GridView in ASP.Net MVC

In this demo,

1. We will upload a CSV file, and after uploading the record display on the page.

2. Using search, we will search in the uploaded record.

Note: - In the demo, using the in-memory object to store & retrieve the record. You can use a database.

Let's Start

Step 1: - Open Visual Studio 2015 => Go to File Menu => New => Project...
 
Step 2: - In the Installed Templates list, select Visual C# => Web
 
Step 3: - Select ASP.Net Web Application (.NET Framework) from the Web list => Type MVCApplication in the Name box => Click OK
 
Step 4: - Select MVC template from ASP.NET Templates List => Click OK
 
Step 5: - Right Click on Models folder in Solution Explorer => Add => Click New Items... => Expand Visual C# from left pane => Select Code =>Select Class from middle pane => Type UploadViewModels.cs in the Name box => Click Add
 
Copy Past following code in UploadViewModels.cs
 
public class UploadViewModels
{
    [Required]
    [DisplayName("Browse File")]
    public HttpPostedFileBase BrowseFile { get; set; }
    public string Search { get; set; }
}
 
Step 6: - Right Click on Models folder in Solution Explorer => Add => Click New Items... => Expand Visual C# from left pane => Select Code =>Select Class from middle pane => Type StudentModels.cs in the Name box => Click Add
 
Copy Past following code in StudentModels.cs
 
public class StudentModels
{
    public string Name { get; set; }
    public string Age { get; set; }
    public string DOB { get; set; }
}
 
Step 7: - Right Click on Controllers folder => Add => Controller... => Select MVC 5 Controller - Empty => Click Add => Type UploadController in Controller Name box => Click Add
 
Copy Past following code in UploadController.cs
 
using MVCApplication.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web.Mvc;
 
namespace MVCApplication.Controllers
{
    public class UploadController : Controller
    {
        static List<StudentModels> listStudent = new List<StudentModels>();
 
        public ActionResult Index()
        {
            return View();
        }
 
        [HttpPost]
        public ActionResult Index(UploadViewModels model)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    byte[] buffer = new byte[model.BrowseFile.InputStream.Length];
                    model.BrowseFile.InputStream.Seek(0, SeekOrigin.Begin);
                    model.BrowseFile.InputStream.Read(buffer, 0, Convert.ToInt32(model.BrowseFile.InputStream.Length));
                    Stream stream = new MemoryStream(buffer);
                    using (TextReader textReader = new StreamReader(stream))
                    {
                        string line;
                        while ((line = textReader.ReadLine()) != null)
                        {
                            //Here you can write the database insert code
                            string[] result = line.Split(',');
 
                            listStudent.Add(new StudentModels()
                            {
                                Name = result[0],
                                Age = result[1],
                                DOB = result[2]
                            });
                        }
                        ModelState.AddModelError("Meesage", "File uploaded successfully.");
                    }
                }
            }
            catch (Exception ex)
            {
                ModelState.AddModelError("Meesage", ex);
            }
            return View();
        }
 
        [ChildActionOnly]
        public ActionResult ViewUploadedData()
        {
            //Here you can write the database select code
            return PartialView("_ViewUploadedData", listStudent);
        }
 
        [HttpPost]
        public ActionResult Search(string name)
        {
            //Here you can write the database select code
            if (!string.IsNullOrEmpty(name))
            {
                return PartialView("_ViewUploadedData", listStudent.Where(m => m.Name == name).ToList());
            }
            return PartialView("_ViewUploadedData", listStudent);
        }
    }
}
 
Step 8: - Build Project
 
Step 9: - To Add View for Action Method, Right Click inside the Index Action body => Click Add View… => Type Index in the View name box => Click Add
 

 

Copy Past following code in Index.cshtml
 
@model MVCApplication.Models.UploadViewModels
 
@{
    ViewBag.Title = "Index";
}
 
<h2>Index</h2>
 
@using (Html.BeginForm("Index", "Upload", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.AntiForgeryToken()
    @Html.ValidationSummary(true, "", new { @class = "text-danger" })
 
    <table>
        <tr>
            <td>
                @Html.LabelFor(model => model.BrowseFile, htmlAttributes: new { @class = "control-label col-md-2" })
            </td>
            <td>
                <input name="BrowseFile" type="file" class="form-control" />
                @Html.ValidationMessageFor(model => model.BrowseFile, "", new { @class = "text-danger" })
            </td>
            <td>
                <input type="submit" value="Upload" class="btn btn-default" />
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(model => model.Search, htmlAttributes: new { @class = "control-label col-md-2" })
            </td>
            <td>
                @Html.EditorFor(model => model.Search, new { htmlAttributes = new { @class = "form-control" } })
            </td>
            <td>
                <input type="button" value="Search" id="btnSearch" class="btn btn-default" />
            </td>
        </tr>
        <tr>
            <td colspan="3">
                @Html.ValidationMessage("Meesage", "", new { @class = "text-danger" })
            </td>
        </tr>
    </table>
}
<hr />
<div id="dvViewUploadData">
    @{
        Html.RenderAction("ViewUploadedData");
    }
</div>
 
@section Scripts
{
    <script>
        $(document).on("click", "#btnSearch", function () {
            $.post("/Upload/Search", { "name": $("#Search").val() }, function (data, textStatus, jqXHR) {
                $("#dvViewUploadData").empty();
                $("#dvViewUploadData").html(data);
            });
        })
    </script>
}
 
 
Step 10: - Expand View folder => Right Click on Upload folder => Add => View… => Type _ViewUploadedData in the View name box => Checked Create as a partial view checkbox => Click Add
 

 

Copy Past following code in _ViewUploadedData.cshtml
 
@model IEnumerable<MVCApplication.Models.StudentModels>
 
<h3>Using Table</h3>
<table border="1">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Age)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.DOB)
        </th>
    </tr>
 
    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Age)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.DOB)
            </td>
        </tr>
    }
</table>
<h3>Using WebGrid</h3>
@{
    var webGrid = new WebGrid(Model, canPage: false);
}
@webGrid.GetHtml();
 
ALL Done
 
RUN Project

Note:- CSS & script rendering from the main application. Use jquery CDN URL to test script.

Output

Copy sample data in a text file and save as .csv

Ram,25,01-Jun-1988,

Shyam,20,01-Jul-1988,

Ghanshyam,30,01-Aug-1988,

Gopal,28,01-Sep-1988,