Get data of current month only in sql

By | SQL | No Comments

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#.

By | C#, Insight Database, ORM, SQL | No Comments

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

Read Excel file (.xls or .xlsx) and copy from one folder to another

By | C# | No Comments

First you have to add reference to read excel files. you can download or add reference from nuget package manager. link is below

https://www.nuget.org/packages/Microsoft.Office.Interop.Excel/ 

After that code is below:

class Program
    {
        public static Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

        static void Main(string[] args)
        {
            string readFolderPath = @"C:\Users\Mahaveer Jangid\Desktop\files";
            DirectoryInfo dir = new DirectoryInfo(readFolderPath);
            if (dir.Exists == true)
            {
                FileInfo[] files = dir.GetFiles("*.xls");
                foreach (FileInfo file in files)
                {
                    if (!file.Attributes.HasFlag(FileAttributes.Hidden))
                    {
                        string originalFilePath = file.FullName;
                        bool IsReadble = ReadFile(originalFilePath); //Method to check file is readble or not.

                        //If file successfully readble
                        if (IsReadble)
                        {
                            string passFolderPath = file.Directory + "\\Pass";
                            string _successDestinationPath = System.IO.Path.Combine(passFolderPath, DateTime.Now.ToString("yyyy-MM-dd fff") + "_" + file.Name);
                            if (!System.IO.Directory.Exists(passFolderPath))
                            {
                                System.IO.Directory.CreateDirectory(file.Directory + "\\Pass");
                            }
                            System.IO.File.Copy(originalFilePath, _successDestinationPath, false);
                        }
                        //If file not readble
                        else
                        {
                            string failFolderPath = file.Directory + "\\Fail";
                            string _failDestinationPath = System.IO.Path.Combine(failFolderPath, DateTime.Now.ToString("yyyy-MM-dd fff") + "_" + file.Name);
                            if (!System.IO.Directory.Exists(failFolderPath))
                            {
                                System.IO.Directory.CreateDirectory(file.Directory + "\\Fail");
                            }
                            System.IO.File.Copy(originalFilePath, _failDestinationPath, false);
                        }
                    }
                }

            }

        }


        public static bool ReadFile(string filepath)
        {
            bool IsReadble = false;
            Workbook workbook = excel.Workbooks.Open(filepath);
            Worksheet worksheet = workbook.Sheets[1];
            Range range = worksheet.UsedRange;
            int rowCount = range.Rows.Count;
            int colCount = range.Columns.Count;
            for (int i = 1; i <= rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {
                    if (range.Cells[i, j] != null && range.Cells[i, j].Value2 != null)
                    {
                        IsReadble = true;
                    }
                }

            }
            return IsReadble;
        }
    }

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

By | JQuery, MVC

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

By | ASP.NET, JQuery

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#

By | ASP.NET, C#, MVC

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

By | ASP.NET, C#, 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

By | SQL

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  

How to get first day date and last day date of a week in jquery ?

By | JQuery

If you want to get first day and last day of a week,  code is here :

 


var curr = new Date; // If you want of any date then just pass date in Date('08/22/2019')
        var first = curr.getDate() - curr.getDay(); // First day is the day of the month - the day of the week
        var last = first + 6; // last day is the first day + 6
        var firstday = new Date(curr.setDate(first)).toLocaleDateString();
        var lastday = new Date(curr.setDate(last)).toLocaleDateString();
        console.log(firstday );
        console.log(lastday );

This will give you Sunday as First Day and Saturday as last day
Tested on Chrome Browser Console :

Remove all li from ul in jquery

By | JQuery

If you have a <ul> and <li> in <ul>, and if you want to clear or remove all li from <ul>, here jquery code to empty this <ul>

HTML Code :

<ul class=”attachmentList”>

<li>First</li>

<li>Second</li>

<li>Third</li>

</ul>

 

$('.attachmentList').empty();

Multiple email validations in multiple text boxes with comma separate email ids in Javascript

By | ASP.NET, JQuery, MVC

Before Form submit, if you have multiple text boxes for emails like To Email and Cc Email, and both text boxes having multiple email ids with comma separate, then before form submit you can add validation through JQuery or java script.

 

<!DOCTYPE html>
<html>
<body>

<form>
To Email: <textarea name=”ToEmail” id=”ToEmail”></textarea><br>
CC Email: <textarea name=”CCEmail” id=”CCEmail”></textarea><br>
<input type=”submit” value=”Submit”>
</form>
</body>
</html>

 


$('form').submit(function () {
        var ToEmail = $('#ToEmail').val();
        var CCEmail = $('#CCEmail').val();
        var validArray = [];
        var emails = ToEmail.replace(/\s/g, '').split(",");
        Array.prototype.push.apply(emails, CCEmail.replace(/\s/g, '').split(","));
        var valid = true;
        var regex = /^(([^&amp;amp;lt;&amp;amp;gt;()[\]\\.,;:\s@\"]+(\.[^&amp;amp;lt;&amp;amp;gt;()[\]\\.,;:\s@\"]+)*)|(\".+\"))@@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;

        for (var i = 0; i &amp;amp;lt; emails.length; i++) {
            if (emails[i] == "" || !regex.test(emails[i])) {
                valid = false;
            }
            validArray.push(valid);
        }
        var isFalse = validArray.includes(false);
        if (isFalse == true) {
alert("Please enter emails in proper format !");
            return false;
        }
        else {
            return true;
        }
    });

Now Here an array will store comma separate email ids of first text box and then same array will append the more email ids from another text box, for this we have used :


Array.prototype.push.apply(emails, CCEmail.replace(/\s/g, '').split(","));

Above line of code will append second text box email ids into array, that already having values of first textbox.(email)

Note:- If you are working on ASP.NET MVC Razor then in Regex Expression you have to replace @ to @@, because @ will determine as razor syntex.