MVC Webgrid example with paging, sorting & custom Design

Models:

public class EmailDetails
    {
        public Int32 ID { get; set; }
        public String EMPID { get; set; }
        public String EMailId { get; set; }
        public String empname { get; set; }
        public String EMAILSTATUS { get; set; }
        public String BCC { get; set; }
        public String CC { get; set; }
        public String EMAILSUBJECT { get; set; }
        public String ATTACHMENT { get; set; }
        public String BODY { get; set; }
    }

Controller: 

 public ActionResult List()
        {
            List<EmailDetails> lst = new List<EmailDetails>();
         
            DataSet objds = objBLLEmailServices.ExecuteDataset(objBLLEmailServices);
            foreach (DataRow dr in objds.Tables[0].Rows) // loop for adding add from dataset to list<modeldata>
            {
                lst.Add(new EmailDetails
                {
                    ID = Convert.ToInt32(dr["ID"]),
                    EMPID = dr["EMPID"].ToString(),
                    EMailId = dr["EMailId"].ToString(),
                    empname = dr["empname"].ToString(),
                    EMAILSTATUS = dr["EMAILSTATUS"].ToString(),
                    BCC = dr["BCC"].ToString(),
                    CC = dr["CC"].ToString(),
                    EMAILSUBJECT = dr["EMAILSUBJECT"].ToString(),
                    ATTACHMENT = dr["ATTACHMENT"].ToString(),
                    BODY = dr["BODY"].ToString()
                });
            }

            return View(lst);

        }

List.cshtml

@using ProjectName.Models;

@{
    ViewBag.Title = "List";
    Layout = "~/Views/Shared/_Layout.cshtml";
    @model IEnumerable<EmailDetails>
    WebGrid EmailDetails = new WebGrid(Model, rowsPerPage: 5);
}
<style type="text/css">
    .bg-info {
        background:#fff;
    }

</style>
<h2>List</h2>
<div class="row">
    <div class="table-responsive">
     
        @EmailDetails.GetHtml(

    tableStyle: "table table-bordered  bg - info", // applying style on grid
   rowStyle: "bg-info",
    alternatingRowStyle: "bg-info",
    fillEmptyRows: false,
    //show empty row when there is only one record on page to it will display all empty rows there.
    headerStyle: "bg-info", //applying style.

    footerStyle: "bg-info", //applying style.

    mode: WebGridPagerModes.All, //paging to grid
    firstText: "<< First",
    previousText: "< Prev",
    nextText: "Next >",
    lastText: "Last >>",

    columns: new[]  // colums in grid
    {
        /*EmailDetails.Column(columnName:"Username",header:"User Name"),*/ //the model fields to display
        EmailDetails.Column(columnName:"empname",header:"Emp name"), //the model fields to display
        EmailDetails.Column(columnName:"EMailId",header:"EMail Id", format: (item) => new HtmlString(item.EMailId)),
        EmailDetails.Column(columnName:"EMAILSUBJECT",header:"EMAIL SUBJECT"),
        EmailDetails.Column(columnName:"CC",header:"CC"),
        EmailDetails.Column(columnName:"BCC",header:"BCC"),

        EmailDetails.Column(columnName:"BODY",header:"BODY", format: (item) => new HtmlString(item.BODY)),
  })


    </div>
    </div>

How #GST May Impact Prices Of Goods

Items	Earlier Rate (%)	GST (%)
Perfume	17.5 - 27	18
Cosmetics		28
Cheese	5-14.5	12
Butter		12
Glucometers	11-20.5	12
Tableware - Metal	11	18
		Revised 12% for spoons, forks, ladles, skimmers, cake servers, fish knives, tongs
Tableware - Ceramic	17.5	18
Tableware - Wood		18 and 28
Tableware - Plastic		18
Kitchenware - Metal	11-20.5	18
		Revised 12% for Spoons, forks, ladles, skimmers, cake servers, fish knives, tongs
Kitchenware-Ceramic	17.5 - 27	18
Kitchenware-Wood		18 and 28
Kitchenware-Plastic		18
Kitchenware-Fly ash		18
X-Ray Apparatus (for medical, dental & veterinary)	17.5-27	12
X-Ray Apparatus		28
Footwear (below Rs. 500)	14.41	5
Footwear(above Rs. 500)	14.41	18
Readymade garments (below Rs. 1000)	5-6	5
Readymade garments (above Rs. 1000)	18.5	12
Biscuits( Less than Rs. 100/kg)	11.89	18
Biscuits(Above Rs. 100/kg)	16.09	18
Corn-flakes	9.86	18
Wrist watch	20.64	28
Jam	5.66	18
Baby food (sold in unit containers)	7.06	18
Small Cars (<4m <1200 cc petrol)	25-27	28+1 (cess)
Small Cars (<4m <1500 cc diesel)	25-27	28+3 (cess)
Mid-segment (<1500 cc)	36-40	28+15 (cess)
Cars with 1500 cc & larger engines	41.5-44.5	28+15 (cess)
Vehicles for transport with 10 to 13 passenger capacity (cannot be considered as bus)	39-41	28+15
Motorcycles	25-35	28
Motorcycles Engine >350	25-35	28+3 (cess)
Television	25-27	28
Stationery (Plastics)	11-27	18
Stationery (Paper)		12 and 18
		Rates revised on following items from 18% to 12% - Exercise books and note books
Stationery (Pen/Fountain Pen)
		Rates revised on following items from 18% to 12% - Eraser
Renewable energy devices	17-18	5
Iron Ore	17-18	5
Digital Cameras	25-27	28
Luxury items like yacht	25-27	28+3 (cess)
Music Instruments (Handmade)	0-12.5	0
Music Instruments (Other than Handmade)	25-27	28

Powered by EY.

Notes from EY: -These are illustration purposes only, not meant for direct implementation or reliance
-These rates are based on the best of our understanding of the applicable rates and should not be taken as an assurance of ultimate rates.
-Information as of June 20.
-EY assumes no responsibility to update for changes in after the date of the submission.

How to remove empty lines in text In Visual Studio?

Visual Studio has ability to delete empty lines in replace operation using regular expressions.

• Click Ctrl-H (quick replace)
• Tick "Use Regular Expressions"
• In Find specify ^$\n
• In Replace box delete everything.
• Click "Replace All"

All empty lines will be deleted.

Regular expression for empty line consist of

Beginning of line ^

End of line $

Line break \n







SQL Database Full Text Search

1. DECLARE @SearchStrColumnName nvarchar(100), @SearchStrColumnValue nvarchar(100), @SearchStrInXML bit  
2. SET @SearchStrColumnValue = '%OWNED%' /* use LIKE syntax */  
3. SET @SearchStrColumnName = NULL /* NULL for all, use LIKE syntax */  
4. SET @SearchStrInXML = 0 /* Searching XML data may be slow */  
5.   
6. IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results 
7.  
8. CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))  
9.   
10. SET NOCOUNT ON  
11.   
12. DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)  
13. 
    
14. SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')  
15. 
    
16. DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))  
17.   
18. WHILE @TableName IS NOT NULL  
19. BEGIN  
20. SET @TableName =  
21. (  
22.    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))  
23.    FROM INFORMATION_SCHEMA.TABLES  
24.    WHERE TABLE_TYPE = 'BASE TABLE'  
25.    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName  
26.    AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0  
27. )  
28.   
29. IF @TableName IS NOT NULL  
30. BEGIN  
31. DECLARE @sql VARCHAR(MAX)  
32. SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE  
33. FROM INFORMATION_SCHEMA.COLUMNS  
34. WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)  
35. AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)  
36. AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''bigint'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')  
37. AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'  
38. 
    
39. INSERT INTO @ColumnNameTable  
40. EXEC (@sql)  
41. WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)  
42. BEGIN  
43. SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable  
44. SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @SearchStrInXML WHEN 1 THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''  
45. FROM ' + @TableName + ' (NOLOCK) ' +  
46. ' WHERE ' + CASE @SearchStrInXML WHEN 1 THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue  
47. INSERT INTO #Results  
48. EXEC(@sql)  
49. DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName  
50. END   
51. END  
52. END  
53. 
    
54. SET NOCOUNT OFF  
55. SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results  
56. GROUP BY TableName, ColumnName, ColumnValue, ColumnType  

Output:

How to get Visitor IP Address and Location in ASP.Net

1. HttpRequest request=base.Request;
2. String VisitorIP=request.UserHostAddress;

Export DataSet To Excel in MVC

NameSpace :
using System.Web.Hosting;
using ClosedXML.Excel;

Code  :

[MTAThread]
        private void ExportDataSetToExcel(DataSet ds, String excelName)
        {

            string path = HostingEnvironment.MapPath("~/FolderName/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            path = path + excelName;
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(ds.Tables[0]);
                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;
                wb.SaveAs(path, false);
            }
        }

Example:


DataSet objds = new Dataset();
ExportDataSetToExcel(objds, "FileName.xlsx");

DataTable To JSON With StringBuilder in MVC

Namespace 

using System.Text;

code :

      /// <summary>
        /// Datatable to JSON
        /// <CreatedBy>Ashish Srivastava</CreatedBy>
        /// <Date>12 Dec 2016</Date>
        /// </summary>
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string DataTableToJSONWithStringBuilder(DataTable table)
        {
            var JSONString = new StringBuilder();      
            if (table.Rows.Count > 0)
            {
                JSONString.Append("{  \"status\":\"1\" ,\"result\": [");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    JSONString.Append("{");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        JSONString.Append("}");
                    }
                    else
                    {
                        JSONString.Append("},");
                    }
                }
            }
            else
            {
                JSONString.Append("{  \"status\":\"0\" ,\"result\": [");
                JSONString.Append("{\"value\":" + "\"No record found\"}");
            }          
           JSONString.Append("]}");
            return JSONString.ToString();
        }