Contact Me

Nirav Prabtani

Mobile : +91 738 308 2188

Email : niravjprabtani@gmail.com

Nirav Prabtani

Wednesday 28 May 2014

SQL Get the last date of the month

Introduction

In this tip and trick i am going to write about how to get last day of a perticular month of passed date?

Background

We can get last day of a month by several manipulations and use of built in date functions of sql server.

Using the code

Here I have declared @InputDate DateTime variable to pass date then i have set @InputDate Manually you can pass it from codebehind ,then i have created small snippet for retrieving last day of passed date
declare @InputDate datetime
set @InputDate='06/26/2014' --MM/DD/YYYY Format
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@InputDate))),DATEADD(mm,1,@InputDate)),101) as ReturnedDate 
Here i have Convert date in MM/DD/YYYY format using
Convert(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@InputDate))),DATEADD(mm,1,@InputDate)),101)
here 101 return date in MM/DD/YYYY format you can convert it in different format for more information you can see this Article
We can create function to return last date of a month in this way
 CREATE FUNCTION [dbo].[func_LastDate](@Date DATETIME)
returns nvarchar(100)
AS
BEGIN
    DECLARE @ReturnedDate nvarchar(100)
    select @ReturnedDate=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))),DATEADD(mm,1,@Date)),103)

    return @ReturnedDate

END 
We can call this function in this way
 declare @Date varchar(100)
select @Date=  [dbo].[func_LastDate] (getdate())
print @Date 

History

Alternate version of SQL Get the last date of the month[^]

Bind Gridview using AJAX

Introduction

In this tip, I am going to write about binding of a gridview using Ajax post method.
Now a days, technology has become much faster so Ajax is very useful in our web development instead of classic code behind style of C#.
I have used Ajax POST method to bind gridview. First of all, let's start from AJAX.

What is AJAX ?

AJAX = Asynchronous JavaScript and XML.
In short; AJAX is about loading data in the background and displaying it on the webpage, without reloading the whole page.
Examples of applications using AJAX: Gmail, Google Maps, Youtube, and Facebook tabs.
You can learn more about AJAX from AJAX tutorial.
ReferenceWhat is Ajax?

Background

I bind gridview using Ajax POST method and jQuery.
In Ajax post method, there is a building block for retrieving data from database using web method.
Let's see the structure of Ajax POST method.
             $.ajax({
                type: "POST",    //Method name GET or POST
                url: "PAGENAME/FUNCTION NAME",
                contentType: "application/json;charset=utf-8",
                data: {},        //Parameters to pass in to function at web method
                dataType: "json",
                success: function (data) {
   
                   //On success
                },
                error: function (result) { //On Error
           });  
As seen in above snippet, there is a structure of a Ajax POST method where:
  1. type is for Ajax method type whether it is GET or POST
  2. url specifies the page url and function name of a web method at code behind
  3. data is used to pass parameters to web method function
  4. success used is a function which will be executed on a complete success callback of a web method request, from where you can manipulate returned data from web method
  5. error is executed when any error occurs during Ajax requests and response
Let us see code snippets for Gridview binding using AJAX.

Using the Code

First of all, create database having name DemoDatabase.
Then run that SQL script for create table DemoTable and insert data into table:
USE [DemoDatabase]
GO
create table DemoTable
  (
      id int identity(1,1),
      Username varchar(50),
      Firstname varchar(50),
      Lastname varchar(50),
      EmailID nvarchar(50)  
  )
SET IDENTITY_INSERT [dbo].[DemoTable] ON 

GO
INSERT [dbo].[DemoTable] ([id], [Username], [Firstname], [Lastname], 
[EmailID]) VALUES (1, N'nirav9', N'Nirav', N'Prabtani', N'niravjprabtani@gmail.com')
GO
INSERT [dbo].[DemoTable] ([id], [Username], [Firstname], [Lastname], 
[EmailID]) VALUES (2, N'Raj', N'Rajan', N'Mrug', N'raj@hotmail.com')
GO
INSERT [dbo].[DemoTable] ([id], [Username], [Firstname], [Lastname], 
[EmailID]) VALUES (3, N'Dhamo', N'Dharmendra', N'Pansara', N'dhams@live.com')
GO
INSERT [dbo].[DemoTable] ([id], [Username], [Firstname], [Lastname], 
[EmailID]) VALUES (4, N'Rajesh', N'Rajesh', N'Saradhara', N'rajesh@gmail.com')
GO
SET IDENTITY_INSERT [dbo].[DemoTable] OFF
GO 
Suppose there is one page having name Index.aspx.

Index.aspx

1) Index.aspx
 <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="http://code.jquery.com/jquery-1.11.1.min.js" 
    type="text/javascript"></script>
    <script type="text/javascript">

        $(document).ready(function () {
            BindGridView();

        });

        function BindGridView() {

            $.ajax({
                type: "POST",
                url: "Index.aspx/GetData",
                contentType: "application/json;charset=utf-8",
                data: {},
                dataType: "json",
                success: function (data) {

                    $("#grdDemo").empty();

                    if (data.d.length > 0) {
                        $("#grdDemo").append("
Username  
                        Firstname  Lastname  
                        EmailID
");
                        for (var i = 0; i < data.d.length; i++) {

                            $("#grdDemo").append("
"
 + 
                            data.d[i].Firstname + "
" + data.d[i].Lastname + " " + data.d[i].Username + " " + data.d[i].EmailID + ""); } } }, error: function (result) { //alert("Error login"); } }); } </script> </head> <body> <form id="frm1" runat="server"> <asp:GridView ID="grdDemo" runat="server"> </asp:GridView> </form> </body> </html>
2) Index.aspx.cs
 protected void Page_Load(object sender, EventArgs e)
        {
            BindDummyItem(); //Bind dummy datatable to grid view to bind data in it.
        }
        public void BindDummyItem()
        {
            DataTable dtGetData = new DataTable();
            dtGetData.Columns.Add("Username");
            dtGetData.Columns.Add("Firstname");
            dtGetData.Columns.Add("Lastname");
            dtGetData.Columns.Add("EmailID");
            dtGetData.Rows.Add();

            grdDemo.DataSource = dtGetData;
            grdDemo.DataBind();
        }

        [WebMethod] 
        public static DetailsClass[] GetData() //GetData function
        {
            List Detail = new List();

            string SelectString = "Select Username,Firstname,Lastname,EmailID from DemoTable";
            SqlConnection cn = new SqlConnection("Data Source=servername;
            Initial Catalog=DemoDatabase;User ID=User;Password=*****");
            SqlCommand cmd = new SqlCommand(SelectString,cn);
            cn.Open();

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtGetData = new DataTable();

            da.Fill(dtGetData);

            foreach(DataRow dtRow in dtGetData.Rows)
            {
                DetailsClass DataObj = new DetailsClass();
                DataObj.Username = dtRow["Username"].ToString();
                DataObj.Firstname = dtRow["Firstname"].ToString();
                DataObj.Lastname = dtRow["Lastname"].ToString();
                DataObj.EmailID = dtRow["EmailID"].ToString();

                Detail.Add(DataObj);            
            }

            return Detail.ToArray();
        }
        public class DetailsClass //Class for binding data
        {
            public string Username { get; set; }
            public string Firstname { get; set; }
            public string Lastname { get; set; }
            public string EmailID { get; set; }
        
        }

[Note]

Here, I have used classic method for retrieving data from database for simplicity and for ease of understanding, you can do it by three tier, n-tier and any other secure framework as you want.
In this manner, you can bind and design gridview using Ajax POST method and jquery.

Points of Interest

I have used this functionality many times and in a different way, you can do it on DIV also and bind it like that, it is very fast.
You can apply stylesheet also in success function to give an appearance to element.
You can build this gridview expandable or draggable and many other functionalities with jQuery.
Here is one article on Expandable Table like a Gridview using C#.

History

  • Initial post: 20 May 2014

Convert Datetime to string in many formats

-- SQL Server string to date / datetime conversion - datetime string format sql server

-- MSSQL string to datetime conversion - convert char to date - convert varchar to date
-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)
SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000

-- Without century (yy) string date conversion - convert string to datetime function
SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000

-- Convert string to datetime sql - convert string to date sql - sql dates format
-- T-SQL convert string to datetime - SQL Server convert string to date
SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy
-- mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss
-- 1900-01-01 20:10:44.000

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format
SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013
SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy
SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd
-- YYYYMMDD ISO date format works at any language setting - international standard
SELECT convert(datetime, '20161023')
SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577
SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000
SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-10-23 20:44:11.500

-- Style 126 is ISO 8601 format: international standard - works with any language setting
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)
-- 2008-10-23 18:52:47.513
SELECT convert(datetime, N'23 شوال 1429  6:52:47:513PM', 130) -- Islamic/Hijri date
SELECT convert(datetime, '23/10/1429  6:52:47:513PM',    131) -- Islamic/Hijri date

-- Convert DDMMYYYY format to datetime - sql server to date / datetime
SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
-- 2016-01-31 00:00:00.000
-- SQL Server T-SQL string to datetime conversion without century - some exceptions
-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc
SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default
SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.
SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI
SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR
SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German
SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian
SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.
SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.
SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss
SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec
SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.
SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan
SELECT convert(datetime, '161023', 12) -- yymmdd ISO
SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt
SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)
SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.
SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC
------------

-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss
-- String to datetime - mssql datetime - sql convert date - sql concatenate string
DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)

SELECT @DateValue = '20120718',
       @TimeValue = '211920'
SELECT @DateTimeValue =
convert(varchar, convert(datetime, @DateValue), 111)
+ ' ' + substring(@TimeValue, 1, 2)
+ ':' + substring(@TimeValue, 3, 2)
+ ':' + substring(@TimeValue, 5, 2)
SELECT
DateInput = @DateValue, TimeInput = @TimeValue,
DateTimeOutput = @DateTimeValue;
/*
DateInput   TimeInput   DateTimeOutput
20120718    211920      2012/07/18 21:19:20 */

Monday 19 May 2014

Get last inserted row id from database


You can get latest generated id of a table in two ways..

1) Select top 1 id from a table and order by it desc like this..

 
SELECT TOP 1 id FROM TABLENAME ORDER BY DESC


2) You can use @@IDENTITY it gives latest inserted identity column like this..

INSERT INTO (.............)

SELECT @@IDENTITY





Convert Datatable to JSON String in C#

This Article is about how to convert DataTable into json string using c#


Code


 public string ConvertDataTabletoString()
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("select * from TableName", con))
                {
                    con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
                    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
                    Dictionary<string, object> row;
                    foreach (DataRow dr in dt.Rows)
                    {
                        row = new Dictionary<string, object>();
                        foreach (DataColumn col in dt.Columns)
                        {
                            row.Add(col.ColumnName, dr[col]);
                        }
                        rows.Add(row);
                    }
                    return serializer.Serialize(rows);
                }
            }
        }

Monday 12 May 2014

how to set session timeout in second in asp.net web.config file

Here I will explain how to set session timeout in web.config,IIS and in application using asp.net manually.




By default our websites session timeout is 20 mins after that session will gets expire suppose if we want to set our custom timeout in our applications we can set it in different ways (web.config, global.asax and in IIS)

Check below methods to set session timeout in web.config, global.asax and in iis

In Web.config file we can set session timeout like as shown below 



<configuration>

<system.web>
 <sessionState mode="InProc" timeout="60">
 </sessionState>
 </system.web>
</configuration>


Saturday 10 May 2014

How to check file is exists using c#


you can check it very simple way by using that code


 protected void Page_Load(object sender, EventArgs e)

        {
            string Filestatus = FileStatusFunc("MyPath");
        }

        public string FileStatusFunc(string FilePath)
        {
            if (System.IO.File.Exists(FilePath))
            {
                return "Exists";
            }
            else
            {
                return "is not Exists";
            }
        }