How To Insert Short Date Format ("DD/MM/YYYY") Into Database Table In ASP.NET Using Ajax Calendar Extender And Display It Into Gridview

Introduction

 
In many web applications you need to work with date. MS-SQL Server/ OracleDB supports date data type so, it allows you to store date in the  “YYYY-MM-DD” format. But C# supports only DateTime datatype to store the date, and it is stored as “YYYY-MM-DD HH:MI:SS”. So how can you store the date in “YYYY-MM-DD” format using C#? And how can it be implemented using textbox and image control instead of calendar control?
 
Every programmer may have his own way to implement it, but by using Ajax Calendar Extender it can be implemented easily. In this article I will explain how to insert short date format into database table in asp.net using Ajax Calendar Extender.
 
Note
While implementing this demo you need to download AjaxControlToolkit. I am attaching the complete code as well as AjaxControlToolkit with this article so you can download it. When you download this demo, there are two folders inside the “Ajax Calendar Control” folder. From those folders the folder with the name “AjaxControlToolkit” contains “Ajax Control Toolkit.dll” which is needed while adding the reference to the application.
 
Let’s begin…..
 
Suppose we are storing the Employee ID and jJoining Date into the database then we require two columns. In the first column, we store Employee ID and in another column, we store Joining Date through the textbox and that date should be selected using Ajax Calendar Extender. To create a table, write the following code using MS-SQL Server Management Studio.
  1. create table EMP_DOJ(    
  2.    Emp_Id int primary key identity(1,1),    
  3.    DOJ date    
  4. );   
After creating a ‘EMP_DOJ’ table, open Visual Studio and create a new project by selecting the “ASP.NET Empty Web Site” template, as shown in the below image.
 
How To Insert Short Date Format( 
 
Now right click on the project name and select “Add New Item.” As shown in the below image.
 
How To Insert Short Date Format( 
 
From item templates select “Web Form” template and rename it or keep “Default.aspx” as it is. I am renaming it as “page1.aspx”. The following image shows it,
 
How To Insert Short Date Format( 
 
Now right click on project name and select “Add New Folder” and rename it as “IMG”. Download the calendar image and copy and paste it into “IMG” folder and refresh it.
 
Refer to the following image.
 
How To Insert Short Date Format( 
 
After this, again right click on project name and select “Add ASP.NET Folder” from it click on “Bin” option. This is shown in the below image.
 
How To Insert Short Date Format( 
 
After adding ASP.NET “Bin” Folder copy and paste the “AjaxControlToolkit.dll” (which is attached with this demo, download it) file into “Bin” folder. This is shown in the below image.
 
How To Insert Short Date Format(
 
At this stage right click on project name and select “Add Reference…” option. After selecting “Add Reference” one window will appear, the below image shows it.
 
How To Insert Short Date Format( 
 
Now, double click on “Bin” folder which is shown in “Add Reference” window and select “AjaxControlToolkit.dll” file and click on “ok”. Refer to the following image.
 
How To Insert Short Date Format( 
 
When you expand the “AjaxControlToolkit.dll” file in solution explorer, you can see reference is added. At this stage design the “page1.aspx” page as below.
 
How To Insert Short Date Format( 
 
To design the above “page1.aspx” copy and paste the below code.
 
Page1.aspx
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="page1.aspx.cs" Inherits="page1" Debug="true" %>  
  2. <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>  
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head runat="server">  
  6.     <title></title>  
  7. </head>  
  8. <body style="height: 264px">  
  9.     <form id="form1" runat="server">  
  10.     <div style="width: 640px; height: 123px; margin-left: 103px">  
  11.         <br />  
  12.         <table>  
  13.             <tr>  
  14.                 <td>  
  15.                     Joining Date  
  16.                 </td>  
  17.                 <td>  
  18.                     <asp:TextBox ID="TextBox1" runat="server" Height="18px" Width="90px"></asp:TextBox>  
  19.                 </td>  
  20.                 <td>  
  21.                     <asp:Image ID="Imbtn1" runat="server" ImageUrl="~/IMG/cal.png" Image-Align="Top"  
  22.                         Height="24px" Width="23px" />  
  23.                     <cc1:CalendarExtender ID="cal1" PopupButtonID="Imbtn1" runat="server" TargetControlID="TextBox1"  
  24.                         Format="dd/MM/yyyy">  
  25.                     </cc1:CalendarExtender>  
  26.                 </td>  
  27.             </tr>  
  28.         </table>  
  29.         <table>  
  30.             <tr>  
  31.                 <td>  
  32.                     <asp:ScriptManager ID="ScriptManager1" runat="server">  
  33.                     </asp:ScriptManager>  
  34.                 </td>  
  35.             </tr>  
  36.         </table>  
  37.         <table>  
  38.             <tr>  
  39.                 <td>  
  40.                     <asp:Button ID="Submit" runat="server" Text="Submit" OnClick="Submit_Click" />  
  41.                 </td>  
  42.             </tr>  
  43.             <tr>  
  44.                 <td>  
  45.                     <asp:GridView ID="GridView1" runat="server">  
  46.                     </asp:GridView>  
  47.                 </td>  
  48.             </tr>  
  49.         </table>  
  50.     </div>  
  51.     </form>  
  52. </body>  
  53. </html>  
To connect the database, add the following namespaces into the “Default.aspx.cs” code file.
  1. using System.Data;  
  2. using System.Data.SqlClient;  
After adding the above namespaces, open the “Server Explorer” window and create a new database connection. To create a new database connection, from the “Data Source” select “Microsoft SQL Server” then select the server name which may be your local server and also select the Database name. Click on “Test Connection”, and if connection succeeds, go ahead. Refer to the following image.
 
How To Insert Short Date Format( 
 
Now right-click on the newly created connection which is shown in the Server Explorer window and check the connection properties. From properties, copy the connection string and paste it into “page1.aspx.cs” code file while creating the connection object.
 
Finally, write or copy-paste the below code into the “page1.aspx.cs” code file.
 
page1.aspx.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.IO;  
  8. using System.Data;  
  9. using System.Data.SqlClient;  
  10. using System.Configuration;  
  11.   
  12. public partial class page1 : System.Web.UI.Page  
  13. {  
  14.     //Connection Object/string to connect with the database  
  15.     SqlConnection cn = new SqlConnection("Data Source=DESKTOP-AV4LKI9;Initial Catalog=MTDB;Integrated Security=True");  
  16.     protected void Page_Load(object sender, EventArgs e)  
  17.     {  
  18.           
  19.     }  
  20.     // Calling InsertDOB() and DisplayData();   
  21.     protected void Submit_Click(object sender, EventArgs e)  
  22.     {  
  23.         cn.Open();  
  24.         InsertDOB();  
  25.         DisplayData();  
  26.         cn.Close();        
  27.     }  
  28.     // Function To insert the data   
  29.     public void InsertDOB()  
  30.     {        
  31.         DateTime datetime = DateTime.ParseExact(TextBox1.Text, "dd/MM/yyyy"null);  
  32.         datetime = Convert.ToDateTime(datetime, System.Globalization.CultureInfo.GetCultureInfo("hi-IN").DateTimeFormat);  
  33.         //"hi-IN" is culture information about India. You can change as per culture like French, German, etc.  
  34.         SqlCommand cmd = new SqlCommand("insert into EMP_DOJ(DOJ) values('" + datetime.ToString("dd/MM/yyyy") + "')", cn);  
  35.         cmd.ExecuteNonQuery();  
  36.         TextBox1.Text = "";  
  37.     }  
  38.     // Function to display the data  
  39.     public DataTable DisplayData()  
  40.     {  
  41.         SqlDataAdapter adpt = new SqlDataAdapter("select Emp_Id,CONVERT(VARCHAR(10), DOJ, 101) AS Joining_Date from EMP_DOJ ", cn);  
  42.         DataTable dt = new DataTable();  
  43.         adpt.Fill(dt);  
  44.         GridView1.DataSource = dt;  
  45.         GridView1.DataBind();  
  46.         return dt;  
  47.     }  
  48. }  
When you run it, it will show the following output
 
How To Insert Short Date Format( 
 
When you click on calendar icon it will be populated and allow you pick the date. The date which you picked is shown in the text box and when you click on the “submit” button it is stored in the database and instantly reflected in Gridview as shown in the following image.
 
How To Insert Short Date Format( 
 

Summary

 
In this article, we learned how to insert short date format("dd/MM/yyyy") into database table in asp.net using Ajax Calendar Extender and display it into GridView.