Download DataTable TO Excel By Calling Action in MVC

        [AcceptVerbs(HttpVerbs.Get)]
        public FileResult DownloadSearched(string _file)
        {

DataTable dtRec = {Record};

            MemoryStream MyMemoryStream = null;
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dtRec, "Order");

                MyMemoryStream = new MemoryStream();
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);

                return File(MyMemoryStream, "application/vnd.ms-excel", _file+".xlsx");
            }
         }
       
       


          Call from client side:
       
          window.location = "/Report/OrderReport/DownloadSearched?_file=Report



Cheers!

Shortest way to split strings

CREATE FUNCTION FN_SplitSTRING (@Text nVarchar(max), @Delimeter nVarchar(50))
RETURNS @Table Table (Item Varchar(200))
/*
Written By: Nitish Kumar.
Objective: To split string with delimeter.
Written On: 28-July-2015.
      SELECT * FROM FN_SplitSTRING('1,2,3,5,6,4,8',',')
      SELECT * FROM FN_SplitSTRING('NITISH, KUMAR, JHA, FROM, SAMASTIPUR',',')
      SELECT * FROM FN_SplitSTRING('17*858*858*8569*89*58*nitish','*')
*/
AS
BEGIN
   
      DECLARE @i INT= LEN(@Text)

      WHILE @i <> 0
      BEGIN

            INSERT INTO @Table SELECT LTRIM(RTRIM(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text))))

            SET @Text= RIGHT(@Text,LEN(@Text) - LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) )

            IF(LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) = 0)
            BEGIN    
                  INSERT INTO @Table SELECT LTRIM(@Text)
                  SET @i= 1;
            END

            SET @i= @i-1
      END
   
      RETURN
END
GO

jQuery click events firing multiple times

Cause: If your table is binding multiple times then obviously page.bootpag will bind that many times.

So, to resolve this must unbind this on each table bind call.
ex:   $("#page").unbind();

Move and remove Listbox item to another listbox using jQuery/ JavaScript

// Option 1: jQuery


<script type="text/javascript" language="javascript">
$(document).keydown(function (e) {
    var element = e.target.nodeName.toLowerCase();
    if (element != 'input' && element != 'textarea') {
        if (e.keyCode === 8) {
            return false;
        }
    }
});

$(document).ready(function () {
                
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    var sAllValue = '';
    $('#<%=lstAssigenedModules.ClientID %> option').each(function () {

    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";

    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);

   
    $('#<%=lstAllModules.ClientID %>').click(function () {
    if ($('#<%=lstAllModules.ClientID %> > option:selected').val() != '')
    $('#btnAddModule').removeAttr('disabled');
    else
    $('#btnAddModule').attr('disabled', 'disabled');
    });
    $('#<%=lstAssigenedModules.ClientID %>').click(function () {
    if ($('#<%=lstAssigenedModules.ClientID %> > option:selected').val() != '')
    $('#btnRemModule').removeAttr('disabled');
    else
    $('#btnRemModule').attr('disabled', 'disabled');
    });
    
    $('#btnAddModule').click(
    function (e) {
    if ($('#<%=lstAllModules.ClientID %> > option:selected').appendTo($('#<%=lstAssigenedModules.ClientID %>')));
    $('#btnAddModule').attr('disabled', 'disabled');

    var sAllValue = '';
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    $('#<%=lstAssigenedModules.ClientID %> > option').each(function () {
    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";
    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);
    });
   
    $('#btnRemModule').click(function (e) {
    if ($('#<%=lstAssigenedModules.ClientID %> > option:selected').appendTo($('#<%=lstAllModules.ClientID %>')));
    $('#btnRemModule').attr('disabled', 'disabled');
        
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    var sAllValue = '';
    $('#<%=lstAssigenedModules.ClientID %> > option').each(function () {
    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";
    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);
    });
 });    



 </script>



 // Option 2: Javascript
  <script type="text/javascript" language="javascript">
        function move(tbFrom, tbTo) {
            var arrFrom = new Array(); var arrTo = new Array();
            var arrLU = new Array();
            var i;
            for (i = 0; i < tbTo.options.length; i++) {
                arrLU[tbTo.options[i].text] = tbTo.options[i].value;
                arrTo[i] = tbTo.options[i].text;
            }
            var fLength = 0;
            var tLength = arrTo.length;
            for (i = 0; i < tbFrom.options.length; i++) {
                arrLU[tbFrom.options[i].text] = tbFrom.options[i].value;
                if (tbFrom.options[i].selected && tbFrom.options[i].value != "") {
                    arrTo[tLength] = tbFrom.options[i].text;
                    tLength++;
                }
                else {
                    arrFrom[fLength] = tbFrom.options[i].text;
                    fLength++;
                }
            }

            tbFrom.length = 0;
            tbTo.length = 0;
            var ii;

            for (ii = 0; ii < arrFrom.length; ii++) {
                var no = new Option();
                no.value = arrLU[arrFrom[ii]];
                no.text = arrFrom[ii];
                tbFrom[ii] = no;
            }

            for (ii = 0; ii < arrTo.length; ii++) {
                var no = new Option();
                no.value = arrLU[arrTo[ii]];
                no.text = arrTo[ii];
                tbTo[ii] = no;
            }

            document.getElementById("SiteBody_hdnAssignedModules").value = "";
            var sAllValue = '';
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            for (i = 0; i < list.options.length; i++) {
                sAllValue = sAllValue + list.options[i].value + ";";
            }
            document.getElementById("SiteBody_hdnAssignedModules").value = sAllValue;

            document.getElementById("btnAddModule").disabled = true;
            document.getElementById("btnRemModule").disabled = true;
        }

        function enableAddButton() {
            var list = document.getElementById('SiteBody_lstAllModules');
            if (list.options.length != 0) {

                var indx = list.selectedIndex;

                if (list[indx].value != '')
                    document.getElementById("btnAddModule").disabled = false;
                else
                    document.getElementById("btnAddModule").disabled = true;
            }
        }

        function enableRemoveButton() {
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            if (list.options.length != 0) {
                var indx = list.selectedIndex;

                if (list[indx].value != '')
                    document.getElementById("btnRemModule").disabled = false;
                else
                    document.getElementById("btnRemModule").disabled = true;
            }
        }

        function SetAssignedModules() {
            document.getElementById("SiteBody_hdnAssignedModules").value = "";
            var sAllValue = '';
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            for (i = 0; i < list.options.length; i++) {
                sAllValue = sAllValue + list.options[i].value + ";";
            }
            document.getElementById("SiteBody_hdnAssignedModules").value = sAllValue;
        }
    </script>
    
    
    
    PAGE:
    
<tr>
<td>
<asp:ListBox ID="lstAllModules" onClick="enableAddButton()" runat="server"></asp:ListBox>
</td>
<td>
<input type="button" id="btnAddModule" value="Add &gt;&gt;" style="width: 90px;"
disabled="disabled" onclick="move(this.form.SiteBody_lstAllModules,this.form.SiteBody_lstAssigenedModules)" />
<input type="button" id="btnRemModule" value="&lt;&lt; Remove" style="width: 90px;"
disabled="disabled" onclick="move(this.form.SiteBody_lstAssigenedModules,this.form.SiteBody_lstAllModules)" />
</td>
<td>
<asp:ListBox ID="lstAssigenedModules" onClick="enableRemoveButton()" runat="server">
</asp:ListBox>
<asp:HiddenField ID="hdnAssignedModules" runat="server" />
</td>
</tr>


NOTE:

* SetAssignedModules()-> Setting dropdown values to hidden feilds.
* Change Controls' Id.

Finding Unmatched Records in DatatTables Using Linq

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;



namespace MyApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("Bay", typeof(string));
            dt1.Rows.Add("S30");
            dt1.Rows.Add("S31");
            //dt1.Rows.Add("S32");

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("Bay", typeof(string));
            dt2.Rows.Add("S31");
            dt2.Rows.Add("S41");
            dt2.Rows.Add("S51");

            var vardt1 = dt1.AsEnumerable().Select(a => a.Field<string>("Bay"));
            var vardt2 = dt2.AsEnumerable().Select(a => a.Field<string>("Bay"));
            var varUnMatched = vardt1.Except(vardt2);
            
            Response.Write(varUnMatched.Count().ToString()+"<br/>");
            foreach (var element in varUnMatched)
            {
                Response.Write(element + "<br/>");
            }
        }
    }
}



Cheers!!!

Is SQL Server Case-Sencitive? If Yes, then How to compare to values.

-- Example of case-sencivity.
Declare @v1 varchar(20), @v2 varchar(20)

SET @v1='nitisH' ; SET @v2='niTisH'

IF(@v1=@v2 COLLATE Latin1_General_CS_AS )
    PRINT 'Matched'
Else
    PRINT 'Unmatched'   
   
   
   

Select Name from MemberMaster where Name = 'niTisH' COLLATE Latin1_General_CS_AS


Keyword ->

COLLATE: Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression
         to apply a collation cast.

        The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
       
        You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. You can also
        specify a collation when you create a database using SQL Server Management Studio. If you do not specify a collation, the database is assigned the
        default collation of the instance of SQL Server.   
       
        You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify
        a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation
        of the database. You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation
        default of the current user database for the connection instead of tempdb.   
       
        You can use the COLLATE clause to apply a character expression to a certain collation. Character literals and variables are assigned the default collation
        of the current database. Column references are assigned the definition collation of the column.
       
       
database_default: Causes the COLLATE clause to inherit the collation of the current database.   

collation_name: Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified
        Windows_collation_name or a SQL_collation_name. collation_name must be a literal value. collation_name cannot be represented by a variable or expression.

CaseSensitivity: CI specifies case-insensitive, CS specifies case-sensitive.
AccentSensitivity: AI specifies accent-insensitive, AS specifies accent-sensitive.



/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('test_base', 'Collation')

/* Find Collation of SQL Server Database Table Column */
USE test_base

SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'EmployeeDetails')
--AND name = 'City' -- > can apply over particular column too.


Cheers!!!


Ref:

http://msdn.microsoft.com/en-us/library/ms184391.aspx
http://sqlservernet.blogspot.in/2012/02/case-sensitive-sql-query-search-and.html
http://msdn.microsoft.com/en-us/library/ms180175(v=sql.105).aspx

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE 'SQL%';

How to get QueryStirng on client side like ASPX or html etc

 function getQueryString(name) {
        name = name.replace(/[\[]/, "\\[").replace(/[\]]/, "\\]");
        var regex = new RegExp("[\\?&]" + name + "=([^&#]*)"),
        results = regex.exec(location.search);
        return results === null ? "" : decodeURIComponent(results[1].replace(/\+/g, " "));
    }


Call this method as :
    var ClassSectionID = getQueryString('Classsectionid');



Cheers!!!