Get data of current month only in sql

This is very Simple, Just get month of both of dates which dates you want to compare but both dates should be in date format or you have to cast in date format

get month of any date MONTH() method

MONTH('2019/02/22')

it will return only month of this date, just pass date in month method.

if your date in varchar or string format than you should cast in date format

CAST('2019/02/22'AS DATE)

Complete Example Is

MONTH(CAST(YourDate AS DATE)) = MONTH(GETDATE())

Select multiple count in one result set in sql and C#.

Here, you can select multiple counts from different – 2 tables in one result set. with multiple coulumn names.

i used insight databse ORM in C#.

First i created a Model in c# where result set will bind with Columns name and model property name.

public class AnalyticsModel
    {
        public int totalAppointments { get; set; }
        public int totalPrescriptionRenewal { get; set; }
        public int totalNumberOfDropInsPerDay { get; set; }
        public int totalRequest { get; set; }
    }

And Now My Store Procedure in SQL

CREATE PROC [dbo].[AnalyticsTotal]
AS
BEGIN 
	SET NOCOUNT ON;

	SELECT
	(SELECT COUNT(*) FROM Appointment WHERE MeetingType IN(1,3) AND Status=1) AS [totalAppointments],

	(SELECT COUNT(*) FROM Appointment WHERE MeetingType=2 AND Status=1) AS [totalPrescriptionRenewal],

	(SELECT COUNT(*) FROM Appointment WHERE IsDropIn=1 AND Status=1) AS [totalNumberOfDropInsPerDay],

	(SELECT COUNT(*) FROM Appointment WHERE MeetingType IN(1,2,3) AND Status=1) AS [totalRequest]

END

And in last i got my result set in my DataClass.

var Result = DB.QuerySql<AnalyticsModel>(@"Exec [dbo].[AnalyticsTotal]").FirstOrDefault();

Here QuerySql is the method of Insight Database ORM.

You can Install in your visual studio from nuget package manager.

Install-Package Insight.Database -Version 6.2.8

How to sort alphanumeric string in SQL ?

Suppose we have a column with data in sql table below:

CT6
CT4
CT3
CT2
CT23
CT50
CT52
CT15
CT14
CT15
CT16


SELECT * FROM dbo.tbl_Data WHERE Status=1
ORDER BY 
LEFT(AlphaNumComun, PATINDEX('%[0-9]%', AlphaNumComun)-1) ASC,
CONVERT(INT, SUBSTRING(AlphaNumComun, PATINDEX('%[0-9]%', AlphaNumComun), LEN(AlphaNumComun))) ASC

How to prevent static files opening without login in ASP.NET MVC

Hello,
If you are working on mvc application, and uploading pdf, images in your application than you have to prevent those files to open without login the application.
for this you have to use HttpHandler in MVC.

First you have to enable debug mode for static files from WebConfig, because static files route does not go in application pipeline.

Step 1 Enable runAllManagedModulesForAllRequests in webconfig and put Custom httphandler in


<system.webServer>
....
 <modules runAllManagedModulesForAllRequests="true" />
    <handlers>
      <add name="PDF" path="*.pdf" verb="GET" type="ApplicationNameSpace.FileProtectionHandler" resourceType="File" />
      <add name="JPG" path="*.jpg" verb="GET" type="ApplicationNameSpace.FileProtectionHandler" resourceType="File" />
      <add name="PNG" path="*.png" verb="GET" type="ApplicationNameSpace.FileProtectionHandler" resourceType="File" />
      <add name="BMP" path="*.bmp" verb="GET" type="ApplicationNameSpace.FileProtectionHandler" resourceType="File" />
    </handlers>
....
</system.webServer>

Step 2 : Create Custom HttpHandler in RouteConfig file, You can create a separate class instead in App_Start folder.
Here is Complete route.config

Here you have to implement 2 interfaces IHttpHandler and IRequiresSessionState.

namespace ApplicationNamespace
{
 public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");


            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Account", action = "Login", id = UrlParameter.Optional }
            );
        }
    }


    public class FileProtectionHandler : IHttpHandler, IRequiresSessionState
    {
        public bool IsReusable { get { return true; } }

        public void ProcessRequest(HttpContext context)
        {
            switch (context.Request.HttpMethod)
            {
                case "GET":
                    {
                        if (SessionHelpers.Userid != 0) // if Application is Login
                        {
                            string requestedFile = context.Server.MapPath(context.Request.FilePath);

                            SendContentTypeAndFile(context, requestedFile);
                        }
                        else
                        {
                            if (context.Request.FilePath.Contains("LoginPageLogo")) //it used for login page logo
                            {
                                string requestedFile = context.Server.MapPath(context.Request.FilePath);
                                SendContentTypeAndFile(context, requestedFile);
                            }
                            else
                            {
                                context.Response.Redirect("~/Account/Login");
                            }
                        }
                        break;
                    }
            }

        }

        private HttpContext SendContentTypeAndFile(HttpContext context, String strFile)
        {
            context.Response.ContentType = GetContentType(strFile);
            context.Response.TransmitFile(strFile);
            context.Response.End();
            return context;
        }
        private string GetContentType(string filename)
        {
            // used to set the encoding for the reponse stream
            string res = null;
            FileInfo fileinfo = new FileInfo(filename);
            if (fileinfo.Exists)
            {
                switch (fileinfo.Extension.Remove(0, 1).ToLower())
                {
                    case "pdf":
                        {
                            res = "application/pdf";
                            break;
                        }
                }
                return res;
            }
            return null;
        }
    }
}

Here we are handling appplication login trough Session, if there is session Userid that we saved on Login Time, if it is found than file will out through response.
You have to use those methods SendContentTypeAndFile and GetContentType for file out response.

How to pass dictionary from JQuery Ajax to C# MVC Model

We have a model class in mvc c#, that also contains a dictionary property, and we want to pass data in this dictionary from jquery ajax.

Here we go,
We have a Model Class

 public class ModelClass
    {
      
        private Dictionary<string, string> _parameter= new Dictionary<string, string>();
        public Dictionary<string, string> Parameter{ get { return _parameter; } set { _parameter= value; } }
    }

And Here is Ajax and jquery code to send the dictionary as parameter


var parameter = [];
            parameter.push({ "key": "FromDate", "value": $('#FromDate').val() });
            parameter.push({ "key": "ToDate", "value": $('#ToDate').val() });
            
            var jsonParameters = { "Parameter": parameter };

            $.ajax({
                type: "POST",
                url: '@Url.Action("Home","Index")',
                contentType: 'application/json',
                data: JSON.stringify({ ModelClass: jsonParameters }),
                success: function (data) {
                    
                },
                error: function () {
                    alert('error');
                }
            })


And Here is controller that will accept parameters in ModelClass


public class HomeController : Controller
    {
       public ActionResult Index(ModelClass model) // Here You will get Dictionary as parameter
        {
            return View();
        }
    }

US Mobile Number Format validate JQuery (123)456-7890

Validation Mobile Number Field in US Format Mobile Number with Regexp in javascript/JQuery

 

 


<input type="text" class="form-control" name="Phone" placeholder="(123)456-7890" id="MobileNumber" onblur="return validateUsNumber(this);" maxlength="14" />

function validateUsNumber(e) {
        var value = $(e).val();
        if (value.trim() != '') {
            var pattern = new RegExp("^[\\(]{0,1}([0-9]){3}[\\)]{0,1}[ ]?([^0-1]){1}([0-9]){2}[ ]?[-]?[ ]?([0-9]){4}[ ]*((x){0,1}([0-9]){1,5}){0,1}$");
            if (!pattern.test(value)) {
                alert("Please enter mobile number in valid format ! (123)456-7890");
                $(e).val('');
                return false;
            }
        }
    }


Error log in catch block write Exception Detail in C#

This method helps you to trace or keeping log of your exception in a text file with a formatted manner. Just use as extension method and pass object of you Catch Exception obj.

 

public class ErrorLog
{
 public static void WriteError(Exception ex)
 {
  string err = "";
  try
  {

   string path = System.AppDomain.CurrentDomain.BaseDirectory + "/ErrorLog/" + 
   DateTime.Today.ToString("dd-MM-yy") + ".txt";

   if (!File.Exists(path))
   {
    File.Create(path).Close();
   }
  using (StreamWriter w = File.AppendText(path))
  {
    w.WriteLine("\r\nLog Entry : ");
    w.WriteLine("{0}", DateTime.Now.ToString(CultureInfo.InvariantCulture));
    err = "Error Log at : " + ex.ToString() + "___. Error Message:" + ex.StackTrace;
    w.WriteLine(err);
    w.WriteLine("________________________________________________");
    w.Flush();
    w.Close();
  }
 }
    catch (Exception ex1)
    {
       return;
     }
  }
}


And you can use this extension method anywhere in catch block with class name.


try
{
  //your Code
}
catch(Exception ex)
{
  ErrorLog.WriteError(ex);
}

 

Highlight the tab according to action and controller in asp.net MVC

In Menu bar, while navigating, “Active” class will add in menu item according to your controller and action, that is fixed by you in your html code.

Just use this extension method in your menu item class.

 

public static string IsActive(this HtmlHelper html,
                                      string control,
                                      string action)
        {
            var routeData = html.ViewContext.RouteData;

            var routeAction = (string)routeData.Values["action"];
            var routeControl = (string)routeData.Values["controller"];

            // both must match
            var returnActive = control == routeControl &&
                               action == routeAction;

            return returnActive ? "active" : ""; //Here you can change class name according to your theme like "active" or anything
        }

Now you have to use this method as HTML helper in your razor page.



<li class="@Html.IsActive("About","Index")">
 <a href="@Url.Action("Index","About")">About Us</a>
</li>

                       
                        
 
<li class="@Html.IsActive("Contact","Index")">
 <a href="@Url.Action("Index","Contact")">Contact Us</a>
 </li>


Now if your controller and action will “About” and “Index” then “active” named class will add in you li class.

Select next and previous id of record in sql with where condition

If you have a table named with Student and having records, and you want all records with next and previous PKId or id with each records and you can use lag and lead functions.

SELECT 
Student.PKId
Student.Name,
Student.Address,
NextPrev.PreviousId,  
NextPrev.NextId  
FROM Student
                   OUTER APPLY  
                   (select PreviousId,NextId  
                    from (  
                        select   
                               lag(PKId) OVER (ORDER BY Student.PKId DESC) as PreviousId,  
                               PKId,  
                               lead(PKId) OVER (ORDER BY Student.PKId DESC) as NextId  
                        from Student WHERE Status=1 --Here Condition should be matched with main condition
                    ) as t  
                    WHERE PKId=@PKId) AS NextPrev  
                                           WHERE Student.Status=1 AND Student.Name='Veer' --you can remove this condition if you want all records


--or simply with all records 

SELECT 
Student.PKId
Student.Name,
Student.Address,
NextPrev.PreviousId,  
NextPrev.NextId  
FROM Student
                   OUTER APPLY  
                   (select PreviousId,NextId  
                    from (  
                        select   
                               lag(PKId) OVER (ORDER BY Student.PKId DESC) as PreviousId,  
                               PKId,  
                               lead(PKId) OVER (ORDER BY Student.PKId DESC) as NextId  
                        from Student
                    ) as t  
                    WHERE PKId=@PKId) AS NextPrev