App_global.asax.dll ClosedXML.dll Excel.dll
Microsoft.Web.Infrastructure.dll
System.Net.Http.Formatting.dll
Code Collection
Filter any element text matching with jquery
$('#txtSearch').keyup(function () {
var filter = this.value.toLowerCase();
$('.entry').each(function () {
var _this = $(this);
var title = _this.find('a,p').text().toLowerCase();
if (title.indexOf(filter) < 0) {
_this.hide();
}
if (title.indexOf(filter) == 0) {
_this.show();
}
});
});
Export data in Asp.Net MVC
To Export data we need firstly two namespace
1-using System.IO;
2-using ClosedXML.Excel;
3-When You call this function ,need to provide datatable And FileName
Paste this method to your file
@using (@Html.BeginForm("ExportExecutiveReport", "Home", FormMethod.Post, new { target = "_blank" })) {
//your code Start here
}//Here Home is Controller Name And ExportExecutiveReport Action Name
public static MemoryStream ExportData(DataTable dt, string ExcelFileName)
{
MemoryStream MyMemoryStream = new MemoryStream();
if (dt.Rows.Count > 0)
{
using (XLWorkbook wb = new XLWorkbook())
{
dt.TableName = ExcelFileName;
wb.Worksheets.Add(dt);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + ExcelFileName + "_" + DateTime.Now + ".xlsx");
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
return MyMemoryStream;
}
Paste this method to your Controller (this method call from UI)
public ActionResult ExportExecutiveReport(FormCollection Fc)
{
TempData["Error"] = "";
try
{
var txtName = Fc["txtName"] == null ? "" : Fc["txtName"];// getting value from formcollection to vaiable with null
var txtDOB=Fc["txtDOB"]==null?"":Fc["txtDOB"];
var txtMobileNo = Fc["txtMobileNo"] == null ? "" : Fc["txtMobileNo"];
var Gender = Fc["Gender"] == null ? "" : Fc["Gender"];
var ddlCountry = Fc["ddlCountry"] == null ? "" : Fc["ddlCountry"];
var ddlState = Fc["ddlState"] == null ? "" : Fc["ddlState"];
var ddlCity = Fc["ddlCity"] == null ? "" : Fc["ddlCity"];
var txtAddress = Fc["txtAddress"] == null ? "" : Fc["txtAddress"];
var chkMusic = Fc["chkMusic"] == null ? "" : Fc["chkMusic"];
var chkDance = Fc["chkDance"] == null ? "" : Fc["chkDance"];
var chkReadingBooks = Fc["chkReadingBooks"] == null ? "" : Fc["chkReadingBooks"];
var txtDOJFrom = Fc["txtDOJFrom"] == null ? "" : Fc["txtDOJFrom"];
var txtDOJTo = Fc["txtDOJTo"] == null ? "" : Fc["txtDOJTo"];
string[,] Param = new string[,]// Create any two dimension array
{
{"@Name",txtName.Trim()},
{"@DOB",txtDOB.Trim()},
{"@Gender",Gender.Trim()},
{"@Country",ddlCountry.Trim()},
{"@State",ddlState.Trim()},
{"@City",ddlCity.Trim()},
{"@Hobbies_Dance",chkDance},
{"@Hobbies_Music",chkMusic},
{"@Hobbies_ReadingBooks",chkReadingBooks},
{"@DOJFrom",txtDOJFrom.Trim()},
{"@DOJTo",txtDOJTo.Trim()},
};
DataTable dt = CommonMethod.ExecuteProc(Param, "Proc_ExecutiveMaster_Get");
if (dt.Rows.Count > 0)
{
CommonMethod.ExportData(dt, "ExecutiveReport");//Export data function created in common method class which have function to exprt data
}
}
catch (Exception ex)
{
TempData["Error"] = ex.Message;//Getting error to TempData[""]
}
return RedirectToAction("ExecutiveReport", "Home");
}
There are two overloaded method to execute procedure
public static DataTable ExecuteProc(string[,] Param, string ProcName)//here created Static method because when we call it,does not need to create object of class ,call it directly with className
{
SqlConnection Con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("DbConnection"));
SqlCommand Cmd = new SqlCommand(ProcName, Con);
Cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Param.Length / 2; i++)//Adding dynamic key and value to SqlCommand
{
Cmd.Parameters.AddWithValue(Param[i, 0], Param[i, 1]);
}
SqlDataAdapter da = new SqlDataAdapter(Cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public static DataTable ExecuteProc(string ProcName)//this function take procedure and execute ,return DataTable
{
SqlConnection Con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("DbConnection"));//Getting Connection String from WebConfig file
SqlCommand Cmd = new SqlCommand(ProcName, Con);
Cmd.CommandType = CommandType.StoredProcedure;//Telling what type of your command
SqlDataAdapter da = new SqlDataAdapter(Cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
Create Timer With javaScript
function ShowTimer()
{
var time = "00: 02: 00";
var timer_div = document.getElementById('lblTimer');
applyCSS(timer_div);
timer_div.innerHTML = time;
var my_timer = setInterval(function () {
var hr = 0;
var min = 0;
var sec = 0;
var time_up = false;
t = time.split(":")
hr = parseInt(t[0]);
min = parseInt(t[1]);
sec = parseInt(t[2]);
if (sec == 0) {
if (min > 0) {
sec = 59;
min--;
}
else if (hr > 0) {
min = 59;
sec = 59;
hr--;
}
else {
time_up = true;
}
}
else {
sec--;
}
if (hr < 10)
{
hr = "0" + hr;
}
if (min < 10)
{
min = "0" + min;
}
if (sec < 10)
{
sec = "0" + sec;
}
time = hr + " :" + min + " :" + sec;
if (time_up)
{
time = "Times UP";
timer_div = document.getElementById('lblTimer');
timer_div.style.color = "red";
}
timer_div = document.getElementById('lblTimer');
timer_div.innerHTML = time;
if (time_up)
{
clearInterval(my_timer);
}
}, 1000);
}
function applyCSS(timer_div) // this function will apply css dynamically
{
timer_div.style.fontSize = "22px";
timer_div.style.color = "green";
timer_div.style.fontWeight = "bold";
timer_div.style.width = "150px";
timer_div.style.padding = "1px";
timer_div.style.textAlign = "center";
}
Get Current Financial Year with JavaScript
function getCurrentFinancialYear() {
var fiscalyear = "";
var today = new Date();
if ((today.getMonth() + 1) <= 3) {
fiscalyear = (today.getFullYear() - 1) + "-" + today.getFullYear()
} else {
fiscalyear = today.getFullYear() + "-" + (today.getFullYear() + 1)
}
return fiscalyear
}
Send Mail in ASP.NET MVC
public JsonResult SendMailReceivedMessageByContactUs(String Email, String Name, string Message)
{
Dictionary
Dic["Result"] = "";
Dic["Status"] = "0";
try
{
if (Message.Trim() == "")
{
Dic["Result"] = CommonMethod.PleaseEnterMessage("1", "Message");
}
else
{
string[,] Param = new string[,]
{
{"@Email",Email.Trim()},
{"@Name",Name.Trim()}
};
DataTable DT = CommonMethod.ExecuteProcedure(Param, "USP_UpdateIsmailSentByAdmin");//create your method to execute procedure with parametere instead of this
if (DT.Rows.Count > 0)
{
if (DT.Rows[0]["Status"].ToString() == "1")
{
SmtpClient smtp = new SmtpClient();
MailMessage msg = new MailMessage();
MailAddress from = new MailAddress("");//Fill here Mail from
NetworkCredential nt = new NetworkCredential("", "");//fill your mail id and password
MailAddress to = new MailAddress(Email);
smtp.Port = 587;
smtp.EnableSsl = true;
smtp.UseDefaultCredentials = false;
smtp.Host = "smtp.gmail.com";
smtp.Credentials = nt;
msg.Subject = "CodeWithSurya WebSite : Contact Us";
msg.Body = CommonMethod.SendMailForReceivedMessageByContactUs(Name, Email, Message);
msg.IsBodyHtml = true;
msg.From = from;
msg.Sender = from;
msg.To.Add(to);
smtp.Send(msg);
Dic["Status"] = "1";
}
else
{
Dic["Result"] = "Mail Not Sent. Something Went Wrong.!";
}
}
}
}
catch (Exception ex)
{
Dic["Result"] = ex.Message;
}
return Json(Dic);
}
Check valid Email with C#
public static bool IsValidEmail(string Email)
{
return Regex.IsMatch(Email, @"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$");
}
Generate OTP With C#
public static int GenerateOTP()
{
int min = 1000;
int max = 9999;
Random rdm = new Random();
return rdm.Next(min, max);
}
How to validate password in C#
public static bool IsValidPassword(string Password)
{
return Regex.IsMatch(Password, @"^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-]).{8,}$");
}
Validation with jQuery(when user leave textbox blank then give a proper message and focus to respective textbox)
$("#btnSubmit").click(function () {//provide submit id ,when user Click button then this function triggered (Use it into document ready method)
if ($("#txtFirstName").val() == "")
{
alert("Please enter First Name..!!");
$("#txtFirstName").focus();//respective textbox id to give focus
}
else if ($("#txtLastName").val() == "")
{
alert("Please enter Last Name..!!");
$("#txtLastName").focus();
}
else if ($("#txtDOB").val() == "") {
alert("Please enter Date Of Birth..!!");
$("#txtDOB").focus();
}
else if ($("#ddlCountry").val() == "") {
alert("Please select Country..!!");
$("#ddlCountry").focus();
}
else if ($("#ddlState").val() == "") {
alert("Please select State..!!");
$("#ddlState").focus();
}
else if ($("#ddlCity").val() == "") {
alert("Please Select City..!!");
$("#ddlCity").focus();
}
else if ($("#txtAddress").val() == "") {
alert("Please enter Address..!!");
$("#txtAddress").focus();
}
else if ($("#txtDOJ").val() == "") {
alert("Please enter DOJ");
$("#txtDOJ").focus();
}
else { InsertUpdate(); }//into else block call your function to send data to server
})
Textbox allow only string data in jQuery
$("#txtFirstName,#txtLastName").keypress(function (event) {//provide here your textbox id
var inputValue = event.charCode;
if (!(inputValue >= 65 && inputValue <= 120) && (inputValue != 32 && inputValue != 0)) {
event.preventDefault();
}
});
Textbox allow Numeric
var input = document.getElementById("txtMobileN0");
input.onkeypress = function (evt) {
evt = evt || window.event;
if (!evt.ctrlKey && !evt.metaKey && !evt.altKey) {
var charCode = (typeof evt.which == "undefined") ? evt.keyCode : evt.which;
if (charCode && !/\d/.test(String.fromCharCode(charCode))) {
return false;
}
}
};
Textbox allow Numeric in jQuery
var input = document.getElementById("txtMobileN0");
input.onkeypress = function (evt) {
evt = evt || window.event;
if (!evt.ctrlKey && !evt.metaKey && !evt.altKey) {
var charCode = (typeof evt.which == "undefined") ? evt.keyCode : evt.which;
if (charCode && !/\d/.test(String.fromCharCode(charCode))) {
return false;
}
}
};
Autocomplete in ASP.NET MVC,Jquery and SQL
Add this CDN to your head section
https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js">
https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
Controller Code
public JsonResult Index(string Prefix, string Type, string contextKey, string contextKey2)
{
DataTable dt = new DataTable();
if (contextKey == null) { contextKey = ""; }
if (contextKey2 == null) { contextKey2 = ""; }
string[,] Param = new string[,] {
{"@prefixText",Prefix.Trim() } ,
{"@Type", Type.Trim() } ,
{"@contextKey", contextKey.Trim()} ,
{"@contextKey2",contextKey2.Trim() },
};
dt = CommonMethod.ExecuteProc(Param, "USP_AutoComplete");
if (dt != null)
{
//create List
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
items.Add(dr[0].ToString());
}
}
return Json(items.ToArray(), JsonRequestBehavior.AllowGet);
}
else
{
return Json("");
}
}
jqueryCode
function AutoComplete(ID, Type, contextKey, contextKey2) {
$("#" + ID).autocomplete({
autoFocus: true,
source: function (request, response) {
$.ajax({
url:"/AutoComplete/Index",
type: "POST",
dataType: "json",
data: { Prefix: request.term, Type: Type, contextKey: $("#" + contextKey).val(), contextKey2: $("#" + contextKey2).val() },
success: function (data) {
response(data);
},
error: function (xhr, err) {
var responseTitle = $(xhr.responseText).filter('title').get(0);
alert("Error");
UnBlockUI();
}
});
},
messages: {
noResults: '',
results: function (resultsCount) { }
}
});
}
Call It Onfocus event to textbox onfocus="AutoComplete('ddlCountry', 'GetCountry', '')
Create a procedure to fetch data for AutoComplete
Create Proc USP_AutoComplete
(
@prefixText NVarchar(50),
@Type NVarchar(50),
@contextKey NVarchar(max)=Null,
@contextKey2 NVarchar(max)=Null,
@UserCode NVarchar(10)=Null
)-- USP_AutoComplete '','GetCountry','In'//this procedure works with Type means when type Country thn fetch data from CountryTable
As
Begin
Declare @Category Numeric(18,0)=0,@VendorID Numeric(18,0)
IF @Type='GetCountry'
Begin
IF @PrefixText='.'
Begin
Select Top 10 (CountryName) As CountryName From mtbl_CountryMaster
Order By CountryName
End
Else
Begin
Select Top 10 (CountryName) As CountryName From mtbl_CountryMaster Where (CountryName Like @PrefixText+'%') Order By CountryName
End
End
Else IF @Type='GetSateCountryBy'
Begin
IF @prefixText='.'
Begin
Select Top 10 Sm.StateName From mtbl_StateMaster SM
where SM.CountryName=@contextKey
Order By StateName
End
Else
Begin
Select Top 10 Sm.StateName From mtbl_StateMaster SM
where SM.CountryName=@contextKey
AND (SM.StateName Like '%'+@PrefixText+'%') Order By SM.StateName
End
End
Else IF @Type='GetCityStateBy'
Begin
IF @prefixText='.'
Begin
Select Top 10 CM.CityName From mtbl_CityMaster CM
where CM.StateName=@contextKey
Order By StateName
End
Else
Begin
Select Top 10 CM.CityName From mtbl_CityMaster CM
where CM.CityName=@contextKey
AND (CM.CityName Like '%'+@PrefixText+'%') Order By CM.CityName
End
End
End
Get Current Time In C#
string CurrentTime = DateTime.Now.ToString("hh:mm tt");
Get All Checked checkbox value with comma seperated in jQuery
Hobby: $('input:checkbox:checked').map( function() {
return this.value;
}).get().join(","),
Drop Constraint in SQL Server with Alter command
ALTER TABLE table_name DROP CONSTRAINT constrait_name;
Create Table in SQL Server With proper constraint primary key,default key
Create TABLE PickUPPointMaster
(
[PickUpPointId] Numeric(18,0) IDENTITY(1,1) NOT NULL Constraint [PK_PickUPPointMaster_PickUpPointId] Primary Key,
[PickUpPointCode] [varchar](20) Not NULL,
[PickUpPointName] [varchar](50) NULL,
[Client] [varchar](20) NULL,
[AddressLine1] [varchar](250) NULL,
[AddressLine2] [varchar](250) NULL,
[PinCode] [varchar](15) NULL,
[State] [varchar](10) NULL,
[City] [varchar](50) NULL,
[MobileNo] [varchar](15) NULL,
[EmailID] [varchar](250) NULL,
[IsActive] [bit] NULL Constraint [DF_PickUPPointMaster_IsActive] Default(0),
[CreatedBy] [varchar](10) NULL,
[CreatedDate] DateTime NULL Constraint [DF_PickUPPointMaster_CreatedDate] Default(GetDate()),
[CreatedByIPAddress] [varchar](50) NULL,
[ModifiedBy] [varchar](10) NULL,
[ModifiedDate] DateTime NULL,
[ModifiedByIPAddress] [varchar](50) NULL,
)
convert 24 hour to 12 hour formate in SQL Server
Select * from FORMAT(CAST(StatusTime AS DATETIME),''hh:mm tt'')As [Status Time]
Generate Random Number in SQL Server
select floor(1000 + rand() * 8999);
Select Convert(Int,LEFT(CAST(RAND()*1000000000+99999 AS INT),6))
Get DataType Of A Column IN SQL Server
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Packet'
and COLUMN_NAME = 'Boxes'
Get Time IN SQL Server
Select Substring(Convert(Varchar,GETDATE(),108),1,11) As Time
Get How many table exists columnName IN SQL Server
Select distinct Table_Name From INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='AwbNo'
Amount Seperator with comma IN SQL Server
DECLARE @BigNumber BIGINT
SET @BigNumber = 100000021547524
SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@BigNumber),1), '.00','')
Get All Userdefined function in SQL Server
SELECT ROUTINE_NAME FROM information_schema.routines WHERE routine_type = 'function'
Get All Userdefined View in SQL Server
SELECT * FROM sys.objects WHERE [type]='V'
Create InsertUpdate Procedure in SQL Server
Create Procedure USP_INSUPDBlogMaster
(
@BlogId Numeric(18,0),
@BlogType Varchar(50),
@BlogTitle VarChar(150),
@IsActive Bit,
@Description Varchar(Max)
) --USP_INSUPDBlogMaster 0,'Tt','Test',1,'Des'
As
Begin
Begin Try
Begin Tran
Declare @Msg Varchar(Max),@Status Int=0
IF Exists(Select BlogId From BlogMaster Where BlogId=@BlogId)
Begin
Update BlogMaster Set BlogType=@BlogType,BlogTitle=@BlogTitle,Description=@Description
,ModifiedBy='Surya Prakash',ModifiedDate=GETDATE(),IsActive=@IsActive Where BlogId=@BlogId
Set @Status=2
Set @Msg='Blog Has Been Updated Succcessfully.!'
End
Else
Begin
Insert Into BlogMaster(BlogType,BlogTitle,IsActive,Description)
Values(@BlogType,@BlogTitle,@IsActive,@Description)
Set @Status=1
Set @Msg='Blog Has Been Saved Succcessfully.!'
End
Commit Tran
End try
Begin Catch
Rollback Tran
Set @Msg=ERROR_MESSAGE()+' : On Line No.'+Cast(Error_Line() As varchar)
End Catch
End
Select @Msg As[Msg],@Status As [Status]
Dynamic Query to show data in SQL Server
Create Proc USP_ShowExBlogMaster
(
@BlogId Numeric(18,0),
@Type Varchar(1)='S'
)--USP_ShowExBlogMaster 1,'E'
As
Begin
Declare @Query Nvarchar(Max)='',@Cond Varchar(Max)=''
If @BlogId<>0 Set @Cond=' And Bm.BlogId=@BlogId'
IF @Type='S'
Begin
Set @Query='Select Bm.BlogId As [BlogId],Bm.BlogType,BM.BlogTitle,BM.Description
,(Case When BM.IsActive=1 Then ''YES'' Else ''NO'' End) As [Active]
,BM.CreatedBy,Dbo.FN_ConvertDateDDMMYYYY(BM.CreatedDate) as [Created Date]
,Bm.ModifiedBy,Dbo.FN_ConvertDateDDMMYYYY(BM.ModifiedDate) As [ModifiedDate]
,''HideColumns'' As [HideColumns]
From BlogMaster BM Where 1=1'+@Cond
End
Else
Begin
Set @Query='Select Row_Number() Over(Order By Bm.BlogId) As [Sr No],Bm.BlogType,BM.BlogTitle,BM.Description
,(Case When BM.IsActive=1 Then ''YES'' Else ''NO'' End) As [Active]
,BM.CreatedBy,Dbo.FN_ConvertDateDDMMYYYY(BM.CreatedDate) as [Created Date]
,Bm.ModifiedBy,Dbo.FN_ConvertDateDDMMYYYY(BM.ModifiedDate) As [ModifiedDate]
From BlogMaster BM Where 1=1'+@Cond
End
Exec Sys.Sp_ExecuteSql
@Stmt=@Query,
@Param=N'
@BlogId Numeric(18,0),
@Type Varchar(1)',
@BlogId=@BlogId,
@Type=@Type;
Print(@Query)
End
Execute Procedure in ASP.NET MVC Return DataTable
public static DataTable ExecuteProcedure(string[,] Param, string ExecuteProcedure)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnecTionString"));
SqlCommand cmd = new SqlCommand(ExecuteProcedure, con);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Param.Length / 2; i++)
{
cmd.Parameters.AddWithValue(Param[i, 0], Param[i, 1]);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
Execute Procedure With Parameter in ASP.NET MVC Return DataTable
public static DataTable ExecuteProcedure(string ExecuteProcedure)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnecTionString"));
SqlCommand cmd = new SqlCommand(ExecuteProcedure, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
Bind Dyanmic DropDown With Procedure
@{
DataTable dt11 = CommonMethod.BindDropDownList(" And FormName='CityMaster' Order By SortOrder ", "SearchingColumns", "SearchingValue", "SearchingText");
StringBuilder strBuild11 = new StringBuilder();
if (dt11.Rows.Count > 0)
{
for (int i = 0; i < dt11.Rows.Count; i++)
{
strBuild11.Append("//Bind Here your Option tag");
}
}
@Html.Raw(strBuild11.ToString());
}
Execute Procedure With Parameter in ASP.NET MVC Return DataTable
public static DataTable ExecuteProcedure(string ExecuteProcedure)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnecTionString"));
SqlCommand cmd = new SqlCommand(ExecuteProcedure, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}