Search This Blog

Friday, December 26, 2014

Sql Server - Function to Generate Date Range with differences like day,month,year


Sql Server - Function to Generate Date Range with differences like day,month,year
CREATE FUNCTION [dbo].[DateRange]
(
      @Increment              CHAR(1),
      @StartDate              DATETIME,
      @EndDate                DATETIME,
      @Custom      INT 
)
RETURNS
@SelectedRange    TABLE
(IndividualDate DATETIME)
AS
BEGIN
      ;WITH cteRange (DateRange) AS (
            SELECT @StartDate
            UNION ALL
            SELECT
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                        WHEN @Increment = 'y' THEN DATEADD(yy, 1, DateRange)
                        WHEN @Increment = 'c' THEN DATEADD(DAY, @Custom, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <=
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                        WHEN @Increment = 'y' THEN DATEADD(yy, -1, @EndDate)
                        WHEN @Increment = 'c' THEN DATEADD(DAY, -@Custom, @EndDate)
                  END)

      INSERT INTO @SelectedRange (IndividualDate)
      SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END

Sql Server - Delete ALL Stored Procedure in Database


Sql Server - Delete ALL Stored Procedure in Database
//=================================================== GET ALL Stored PROCEDURE List
 
SELECT 'DROP PROCEDURE ' + p.NAME
FROM sys.procedures p

//=================================================== DELETE ALL stored PROCEDURE

DECLARE @procName VARCHAR(500)
DECLARE cur cursor
 
FOR SELECT [name] FROM sys.objects WHERE TYPE = 'p'
OPEN cur
fetch NEXT FROM cur INTO @procName
while @@fetch_status = 0
BEGIN
    EXEC('drop procedure ' + @procName)
    fetch NEXT FROM cur INTO @procName
END
close cur
deallocate cur

//====================================================
 

Saturday, September 20, 2014

Newtonsoft Json : Convert dynemic Object to Json string

Newtonsoft Json : Convert dynemic Object to Json string



 // Create Dynemic Object
 var msgParams = new { Message = "Contact is associated with property successfully.", UpdateSection = "PropertyContacts", ReloadFullPage = "false" };

 // Convert Dynemic object to json string
 string jsonString = JsonConvert.SerializeObject(msgParams, Formatting.Indented);
 
Serializing and Deserializing JSON with JsonConvert

Product product = new Product();

product.Name = "Apple";
product.ExpiryDate = new DateTime(2008, 12, 28);
product.Price = 3.99M;
product.Sizes = new string[] { "Small", "Medium", "Large" };

string output = JsonConvert.SerializeObject(product);
//{
//  "Name": "Apple",
//  "ExpiryDate": "2008-12-28T00:00:00",
//  "Price": 3.99,
//  "Sizes": [
//    "Small",
//    "Medium",
//    "Large"
//  ]
//}

Product deserializedProduct = JsonConvert.DeserializeObject(output);
 

Sunday, September 7, 2014

Jquery : How to get full html string including the selected element itself with jQuery's $.html()

How to get full html string including the selected element itself with jQuery's $.html()

Sometimes you need to get the selected element's html as well when using .html() function. To make it more clear what I mean, consider you have this HTML markup:
 
Some content
More content
And you need to get not only
 
 
Some con... but
Some con...
Here is the code to get jQuery selector's HTML including its own:
 

var html = $('
').append($('#top').clone()).remove().html();

Here we are:

  • Cloning selected div
  • Creating a new div DOM object and appending created clone of the div
  • Then getting the contents of wrapped div (which would give us all HTML)
  • Finally removing the created DOM object, so it does not clutter our DOM tree.
  • This is a little trick you can use to select self HTML with jQuery's .html() function. But if you can you should surround your markup with some dummy div and avoid this workaround all together. This would be much faster since jQuery would not need to do all the DOM manipulations.

jQuery : Remove (script) tags from a string of html

jQuery : Remove (script) tags from a string of html 


I'm in the middle of writing a bookmarklet to screen scrape web pages. My back-end piece is having trouble parsing pages with lots of script tags. Since I don't actually need the script tags, I thought I would remove them before it even gets to the back-end. Here is how to grab the current page HTML and strip some tags from it, without changing the DOM the user is actually viewing.
 

//Remove (script) tags from a string of html 
var page_content = $("html").clone().find("script,noscript,style").remove().end().html();  

// Remove SCRIPT - LINKS TAG From Ajax Response 

    

That's it! I then POST the html content to the back-end with an ajax call. The key bit is the clone(), which copies the entire page so that we can remove element from the copy and not without removing it from the UI the users if viewing.


ASP.NET MVC Custom Model Binder - Custom DateTime Model binder - Apply Model Binder with Attributes

  • Custom Datetime Model Binder - 1 


Let’s say that you have a model with a DateTime property.

public class Person
{
    //...
    public DateTime DateOfBirth { get; set; }
}
 
Suppose that you want someone to fill out the details and post them back to your site. Simple enough: add a couple of action methods and then insert an EditorFor in your view:
 
//controller
public ActionResult EditDetails()
{
    return View(new Person()); //or loaded from DB, etc
}
 
[HttpPost]
public ActionResult SaveDetails(Person person)
{
    //save updated person to DB
}


@Html.EditorFor(model => model.DateOfBirth)
That’s all you need for basic date editing, though for your users’ sake I suggest you look at a nicer editor than the plain input that this will generate! But what if you want to support some format besides the default? MVC doesn’t handle that out-of-the-box so you’ll need a custom model binder…

Custom Model Binder 

 The model binder is responsible for converting a series of form values into the rich, strongly-typed model that is passed as a parameter to your action method.

 Thankfully there’s no need to re-implement all of the pretty-complex functionality involved in that process just to alter how dates are parsed; a custom model binder can be associated with a single type, and the rest of the binding functionality will work as normal.

Custom model binder implementations need to implement the IModelBinder interface, but the easier approach is to inherit from System.Web.Mvc.DefaultModelBinder and override the one method in which we are interested: BindModel
 
public class DateTimeModelBinder : DefaultModelBinder
{
    public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
    {
        // ???
    }
}

The BindModel method is responsible for returning the value of a model based on the controller- and the binding-context (which between them provide a lot of contextual information about the request), and our custom date parsing implementation will need to:
  • get hold of the custom format that we want to use 
  • use that format with the value that has been posted back to the server 

Specifying the Date Format 

There are a lot of different ways in which you might determine or specify the custom format, but for the purposes of this example I am just going to pass it into the constructor.
 

public class DateTimeModelBinder : DefaultModelBinder
{
    private string _customFormat;
 
    public DateTimeModelBinder(string customFormat)
    {
        _customFormat = customFormat;
    }
 
    public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
    {
        // ???
    }
}

In a real implementation you might grab this from a data annotation attribute, from configuration, from the request information – wherever makes sense for your project.

 Getting the POSTed Value 

The bindingContext that is passed to the BindModel method gives us a couple of properties that we can use to get the POSTed value from the request:
  •  ModelName gives us the name of the property on which the binder is being used 
  • ValueProvider.GetValue(string) will return an instance of ValueProviderResult from which we can get the raw value 

The ValueProvider.GetValue method takes a “key” as the parameter, for which we can use the value of the ModelName property:
 
var value = bindingContext.ValueProvider.GetValue(bindingContext.ModelName);
value.AttemptedValue; //provides the raw value
From here it is a simple step to tie everything together with a DateTime.ParseExact:
 
public class DateTimeModelBinder : DefaultModelBinder
{
    private string _customFormat;
 
    public DateTimeModelBinder(string customFormat)
    {
        _customFormat = customFormat;
    }
 
    public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
    {
        var value = bindingContext.ValueProvider.GetValue(bindingContext.ModelName);
        return DateTime.ParseExact(value.AttemptedValue, _customFormat, CultureInfo.InvariantCulture);
    }
}

Hooking it up 

 Now that we have the model binder, we need to associate it with all DateTime properties throughout our application. We do this by adding an instance of the binder to the ModelBinders.Binders collection from within Global.asax.cs.
 
protected void Application_Start()
{
    //...
    var binder = new DateTimeModelBinder(GetCustomDateFormat());
    ModelBinders.Binders.Add(typeof(DateTime), binder);
    ModelBinders.Binders.Add(typeof(DateTime?), binder);
}

This will associate our new binder with any properties or values of type DateTime or DateTime?. Now, whenever one of these types is encountered by MVC whilst trying to parse a POSTed value it will use our custom binder and therefore our custom format!

//=====================================================================


  • Custom Datetime Model Binder - 2 --- with attribute


In my view I am going to provision three fields for the date within my form. In my model I will only have one property for the three fields called a Date:
 

Day Month Year
public class HomePageModels { public string Title { get; set; } public string Date { get; set; } }

Custom Binding 

 The custom binding class needs to inherit form IModelBinder. Here we capture the current request and extract the Form fields individually. Then we can manipulate these fields any way we like. In this example as you can see I am adding them to a single property called Date.

Alternatively if we do not want to implement custom binding for each and every Model and Property in our application we can inherit from the DefaultModelBinder and override the BindModel method as below
 

public class HomeCustomBinder : IModelBinder
{
    public object BindModel(ControllerContext controllerContext, 
                            ModelBindingContext bindingContext)
    {
        HttpRequestBase request = controllerContext.HttpContext.Request;

        string title = request.Form.Get("Title");
        string day = request.Form.Get("Day");
        string month = request.Form.Get("Month");
        string year = request.Form.Get("Year");

        return new HomePageModels
                   {
                       Title = title,
                       Date = day +"/"+ month +"/"+ year
                   };
    }
} 

public class HomeCustomDataBinder : DefaultModelBinder
    {

        public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
        {
            if (bindingContext.ModelType == typeof(HomePageModels))
            {
                HttpRequestBase request = controllerContext.HttpContext.Request;

                string title = request.Form.Get("Title");
                string day = request.Form.Get("Day");
                string month = request.Form.Get("Month");
                string year = request.Form.Get("Year");

                return new HomePageModels
                {
                    Title = title,
                    Date = day + "/" + month + "/" + year
                };

                //// call the default model binder this new binding context
                //return base.BindModel(controllerContext, newBindingContext);
            }
            else
            {
                return base.BindModel(controllerContext, bindingContext);
            }
        }

    } 

Once we have completed coding our custom class we will need to register the class which I do in the Global.asax under Application_Start().
 
protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    WebApiConfig.Register(GlobalConfiguration.Configuration);
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    RouteConfig.RegisterRoutes(RouteTable.Routes);
    BundleConfig.RegisterBundles(BundleTable.Bundles);
    AuthConfig.RegisterAuth();
    ModelBinders.Binders.Add(typeof(HomePageModels), new HomeCustomBinder());
}

Controller 

Finally we need to inform the controller as to the binding we want it to use. This we can specify using attributes [ModelBinder(typeof(HomeCustomBinder))] as below:
 

[HttpPost]
public ActionResult Index([ModelBinder(typeof(HomeCustomBinder))] HomePageModels home)
{
    if (ModelState.IsValid)
    {
        ViewBag.Title = home.Title;
        ViewBag.Date = home.Date;
    }
    return View();
}

How to Add Syntax Highlighter to Blogger


How to Add Syntax Highlighter to Blogger :

To make this possible, we are using a code highlighter called Syntax Highlighter which is a commonly used highlighter for websites and blogs. The version that we are using is Syntax Highlighter 3 developed by Alex Gorbatchev and let me start with thanking him for such a great tool that has helped a lot of bloggers, blogger users in particular. So follow the steps depicted below to add Syntax Highlighter scripts.

  • Now to go to blogger.com and log in with your credentials.
  • Select your blog and navigate to Template section.
  • Once you reach the Template section, click on Edit HTML and wait for the editor to open up.
  • Now, click on a blank area in the editor and hit Ctrl+F to open the search box.
  • Then, find the code “</head>” .
  • Paste the below set of codes just above the </head> and click on the Save Template button.






  • We have now added the code to the template but have to make the code display in the Syntax Highlighter and for that we have to wrap your code into the <pre> tag with a special attribute to define what language or script that we are using.
  • For instance, If you are using HTML lines then your code segment should be like this

// Your HTML Code here

Friday, September 5, 2014

Watch the ViewState While Debugging

Introduction : 

In my previous post, I briefly showed how to use the ViewState to persist data. This post will overcome the difficulty we have in seeing what data is currently in the ViewState while debugging. The problem? ViewState is a StateBag, which, unlike Hashtable, does not have a default visualizer that makes its values easy for the debugger to look at. In fact, the actual keys and values don't appear to be contained within the StateBag object itself, which makes drilling through its data pointless. (I'm guessing that since the ViewState has per-user-control-per-instance values, it probably stores the real data in the ASP.NET "magic zone" where all the context loading and such takes place. The StateBag probably just provides access methods to wherever the data is really stored.)

So how can you get a quick-and-easy view of the current ViewState? Just add the following line to your Watch panel:

    new System.Collections.Hashtable(ViewState)
    Request.Form["__VIEWSTATE"]
 

The Hashtable constructor iterates through all of the key/value pairs of the StateBag and creates a Hashtable object out of it. Since Hashtable has a key/value pair visualizer in Visual Studio, you get a nice, clean table of keys and values.






Wednesday, July 16, 2014

Conversion Between DataTable and List in C#

Conversion Between DataTable and List in C#

Introduction

Sometimes in projects we may face a situation where we have to convert:

  •     A IList of user defined type to a DataTable or
  •     A DataTable to a List of user defined type

Now rather than making custom conversion mechanism of each different user defined type, let’s make generic converters to save code and time.

List to DataTable

Here is the extension method to convert a List to a DataTable:

/// 
/// Convert Genric List  to DataTable
/// 
public static DataTable ToDataTable(this IList listObject)
{
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
    DataTable myTable = new DataTable();
    foreach (PropertyDescriptor prop in properties)
    {
        myTable.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[properties.Count];
    foreach (T item in listObject)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = properties[i].GetValue(item);
        }
        myTable.Rows.Add(values);
    }
    return myTable;
}







DataTable to List<TSource>



/// 
/// Convert DataTable to Genric List
/// 
public static List DataTableMapToList(DataTable dtSource)
{
    string propName = string.Empty;
    List entityList = new List();
    foreach (DataRow dr in dtSource.Rows)
    {
        // Create Instance of the Type T
        T entity = Activator.CreateInstance();
        // Get all properties of the Type T
        System.Reflection.PropertyInfo[] entityProperties = typeof(T).GetProperties();
        // Loop through the properties defined in the
        // entityList entity object and mapped the value
        foreach (System.Reflection.PropertyInfo item in entityProperties)
        {
            propName = string.Empty;
            if (propName.Equals(string.Empty))
                propName = item.Name;
            if (dtSource.Columns.Contains(propName))
            {
                // Assign value to the property
                item.SetValue
                (
                    entity,
                    dr[propName].GetType().Name.Equals(typeof(DBNull).Name) ? null : dr[propName],
                    null
                );
            }
        }
        entityList.Add(entity);
    }
    return entityList;
}

SQL Server– Concatenate Rows using FOR XML PATH()

SQL Server– Concatenate Rows using FOR XML PATH()

This is probably one of the most frequently asked question – How to concatenate rows? And, the answer is to use XML PATH.

For example, if you have the following data:

USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],

            SUB.Name AS [Sub Category]

FROM        Production.ProductCategory CAT

INNER JOIN  Production.ProductSubcategory SUB

            ON CAT.ProductCategoryID = SUB.ProductCategoryID


=====================================================================

 photo image_thumb30_zps55b27f3b.png
The desired output here is to concatenate the subcategories in a single row as:

We can achieve this by using FOR XML PATH(), the above query needs to be modified to concatenate the rows:

=============================================================================


 photo image_thumb31_zpsa80f40d6.png


USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],

            STUFF((    SELECT ',' + SUB.Name AS [text()]

                        – Add a comma (,) before each value

                        FROM Production.ProductSubcategory SUB

                        WHERE

                        SUB.ProductCategoryID = CAT.ProductCategoryID

                        FOR XML PATH('') – Select it as XML

                        ), 1, 1, '' )

                        – This is done to remove the first character (,)

                        – from the result

            AS [Sub Categories]

FROM  Production.ProductCategory CAT


Executing this query will generate the required concatenated values as depicted in above screen shot.

Sunday, July 6, 2014

SQL Server : Execute Dynamic Query with out parameter : specify output parameters in sp_executesql stored procedure

The sp_executesql system stored procedure is used to execute a T-SQL statement which can be reused multiple times, or to execute a dynamically built T-SQL statement. It takes parameters as inputs in order to process the T-SQL statements or batches. It also allows output parameters to be specified so that any output generated from the T-SQL statements can be stored .

Two scenarios in which the output parameters will be useful with sp_executesql are:
  • If sp_executesql generates output that will be useful, storing this output to an output parameter allows the calling batch to use the parameter for later queries.
  • If sp_executesql is executing a stored procedure that is defined using output parameters, the output parameters for sp_executesql can be used to hold the outputs generated from the stored procedure.

The following two examples demonstrate the use of output parameters with sp_executesql.

Example 1

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname varchar(30)
SET @SQLString = N'SELECT @LastlnameOUT = max(lname)
                   FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,
                        @LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname
    
Example 2
CREATE PROCEDURE Myproc
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
 
 
 

Firebug - get absolute coordinates of Selected Element in firebug


I was searching is there any way to find the left top position of element in page fast.
without each time writing the jquery code  to fetch offset. and refesh page and debug.

i just wanted to know the position of element which i click in firebug.

and i found a great solution.

FindPostion Function :

var findpos = function findPos(obj) {
   var curleft = curtop = 0;
   if (obj.offsetParent) {
      do {
      curleft += obj.offsetLeft;
      curtop += obj.offsetTop;
      } while (obj = obj.offsetParent);
      return [curleft,curtop];
   }
}


(paste it in Firebug, and then you have a findpos function available in the console).
For majority of simplest cases, you will have offsetParent = <body> which has zero offsets (like in the screenshots) so you don't need to add anything to offsetLeft and offsetTop. However if relative positionings take place, then you must traverse the parents.

When you select an element in Firebug, then it's available as $0 in Firebug console.
So, after mixing the two things, you can issue the commands like in the screenshot:

 photo 72b2e203-9275-432d-b694-acb4057feaa3_zpsf8b72307.png

JAVASCRIPT : Add st, nd, rd and th (ordinal) suffix to a number

I would like to dynamically generate a string of text based on a current day. 
So, for example, if it is day 1 then I would like my code to generate  =  "1st".

2 = "2nd"
3 = "3rd"

I Searched And Found Good function to get ordinal suffix for number

function ordinal_suffix_of(i) {
    var j = i % 10,
        k = i % 100;
    if (j == 1 && k != 11) {
        return i + "st";
    }
    if (j == 2 && k != 12) {
        return i + "nd";
    }
    if (j == 3 && k != 13) {
        return i + "rd";
    }
    return i + "th";
}

Sunday, May 25, 2014

Perform LeftOuterJoin on DataTable Using Linq


Perform LeftOuterJoin on DataTable Using Linq

SQL Server – Generating PERMUTATIONS using T-Sql

SQL Server – Generating PERMUTATIONS using T-Sql

 

2014-05-25_1838

SQL Server – Generate Calendar using TSQL

Introduction
Recently, I found the way to display Calendar using SQL Server

Implementation
Below is the TSQL which I came up with to generate the Calendar -

2014-05-25_1816

Wednesday, April 30, 2014

How to Resolve: "Multiple connections to a server or shared resource..." Or Access Shared Folder with different Credentials / remove existing credentials



1. Use "net use" to manually release the old connection
- From a command prompt, run "net use". 
- Delete any existing connections to the backup location. ("net use \\pathto\backups /delete").
- re-run net use and verify the connection is gone.
The next attempt by the agent may then be successful.  This method does not always succeed.

2. Restart the "Workstation" service on the agent machine
The "Workstation" service manages shared resource connections.  Restarting the service will force a release of the network connection.  Because of this, however, there are multiple dependent services, the more notable of which include Exchange services.  Many partners prefer to wait for non-business hours to schedule a restart of this service on a production server.

3. Restart the Server
As a last resort, a restart of the agent server will decisively release the network location.

Remove Shared Access Credentials.
======================================
Use CMD : 

net use * /delete /yes

 

rundll32.exe keymgr.dll,KRShowKeyMgr
======================================

Jquery Ui DatePicker Tip: 1. Allow to Select only current and previous date 2. Allow to select date from two days later from current date




I had requirement for a datepicker  



1. Allow to Select only current and previous date

Allow to select only current date and previous date. And exclude Sunday from selection.
So if todays is Monday than enabled date should be last Saturday and today (Monday).



2. Allow to select date from two days later from current date

Allow to select date from two days later from current date . And exclude Sunday from selection.
So if today is Friday then date selection should start on next Monday. And if todays is Monday then date selection should start on next Wednesday.  

Here Is The Script




 
1. Allow to Select only current and previous date
Date1 :
2. Allow to select date from two days later from current date
Date2 :

Contact Form

Name

Email *

Message *