Monday, July 30

How to insert multiple record as DataTable into SQL Server database in a single time without using looping?


My Gridview(Source Code):
<asp:GridView ID="grdEducation" runat="server" AutoGenerateColumns="false"CssClass="ShibashishMohantyStyleSheet" ShowFooter="true">
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Examination Passed
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:TextBox ID="txtExamination" runat="server"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        College/University
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:TextBox ID="txtCollege" runat="server"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Total Mark
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:TextBox ID="txtTotalMark" runat="server"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Marks Secured
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:TextBox ID="txtMarksSecured" runat="server"></asp:TextBox>
                    </ItemTemplate>
                    <FooterTemplate><asp:Button ID="btnSave" runat="server"Text="Save" /></FooterTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>


My Gridview(Design View):


Code Behind method to set the default view of the Gridview : 
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            FillGrid();
        }
    }

    public void FillGrid()
    {
        DataTable table = new DataTable();
        DataRow row;
        DataView view;
        for (int i = 0; i < 4; i++)
        {
            row = table.NewRow();

            table.Rows.Add(row);
        }
        view = new DataView(table);
        grdEducation.DataSource = view;
        grdEducation.DataBind();
    }



My Table (Education_Details):


 

After that add a Type in your Database.

CREATE TYPE Educations
    AS TABLE
    (
         Education_Name varchar(50),
         College_Name varchar(50),
         Total_Mark decimal(18,2),
         Marks_Secured decimal(18,2)
    )

Then create a stored procedure

CREATE PROCEDURE InsertEducationDetails
(
@Education as Educations READONLY
/* "Educations" is the Type you have created earlier */
/* "@Education" is a type of "Educations" like we are using int,varchar etc*/
)
AS
BEGIN

INSERT INTO Education_Details
(
       Education_Name,College_Name,Total_Mark,Marks_Secured
)
SELECT Education_Name,College_Name,Total_Mark,Marks_Secured FROM @Education;

END


Now write this code-behind method in your page and add this as a delegate for the button present inside the gridview.

protected void SaveButton_Click(object sender, EventArgs e)
    {
        DataTable dtEducation = new DataTable();
        DataColumn dcExam = new DataColumn("Exam_Name"typeof(string));
        DataColumn dcCollege = new DataColumn("College_Name"typeof(string));
        DataColumn dcTotalMark = new DataColumn("Total_Mark"typeof(decimal));
        DataColumn dcSecurdedMark = new DataColumn("Secured_Mark",typeof(decimal));
       
        dtEducation.Columns.Add(dcExam);
        dtEducation.Columns.Add(dcCollege);
        dtEducation.Columns.Add(dcTotalMark);
        dtEducation.Columns.Add(dcSecurdedMark);
        foreach (GridViewRow gr in grdEducation.Rows)
        {
            TextBox txtExamination = (TextBox)gr.FindControl("txtExamination");
            TextBox txtCollege = (TextBox)gr.FindControl("txtCollege");
            TextBox txtTotalMark = (TextBox)gr.FindControl("txtTotalMark");
            TextBox txtMarksSecured = (TextBox)gr.FindControl("txtMarksSecured");
            DataRow drNew = dtEducation.NewRow();
            drNew["Exam_Name"] = txtExamination.Text;
            drNew["College_Name"] = txtCollege.Text;
            drNew["Total_Mark"] = Decimal.Parse(txtTotalMark.Text);
            drNew["Secured_Mark"] = Decimal.Parse(txtMarksSecured.Text);
            dtEducation.Rows.Add(drNew);
        }
        SqlConnection con = new SqlConnection("---My Connection String---");
        SqlCommand insertEducations = new SqlCommand("InsertEducationDetails", con);
        insertEducations.CommandType = CommandType.StoredProcedure;
        insertEducations.Parameters.AddWithValue("@Education", dtEducation);
        con.Open();
        insertEducations.ExecuteNonQuery();
        con.Close();
    }

I have entered values to be inserted into database without using for loop.


After clicking the save button, the multiple values will be inserted into the database at a time.

Result:-

 

Thanks
Shibashish Mohanty

Saturday, July 28

How to calculate difference between two dates using JQuery?

In the following example I have discussed about calculating the difference between two days. Here, I have taken three text boxes, two are for entering "from date" and "to date". The third one is meant for showing the result after calculating the difference between those two days.

.aspx Page :(Source)


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DateDifference.aspx.cs"Inherits="pages_DateDifference" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit"TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">   
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <table class="style1">
            <tr>
                <td>
                    <asp:Label ID="lblFromDate" runat="server" Text="From Date:"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtFromDate" runat="server"></asp:TextBox>
                    <asp:CalendarExtender ID="calFromDate" runat="server"TargetControlID="txtFromDate" PopupButtonID="txtFromDate" Format="dd/MM/yyyy">
                    </asp:CalendarExtender>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblToDate" runat="server" Text="To Date :"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtToDate" runat="server"></asp:TextBox>
                    <asp:CalendarExtender ID="calToDate" runat="server"TargetControlID="txtToDate" PopupButtonID="txtToDate"
                        Format="dd/MM/yyyy">
                    </asp:CalendarExtender>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblDifference" runat="server" Text="Total Days :"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtDifference" runat="server"></asp:TextBox>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

.aspx Page :(Design)

JQuery Method

<script type="text/javascript">
        function DateDifference() {
            $("#txtDifference").val('');
            //To check if both From Date and To Date are not null
            if ($("#txtFromDate").val() != '' && $("#txtToDate").val() != '') {
            // To convert the entered Date string (dd/MM/yyyy) to a valid date format(yyyy,MM,dd)
                var FromDateArr = $("#txtFromDate").val().split('/');
                var FromDate = new Date(FromDateArr[2], FromDateArr[1], FromDateArr[0]);
                var ToDateArr = $("#txtToDate").val().split('/');
                var ToDate = new Date(ToDateArr[2], ToDateArr[1], ToDateArr[0]);
                if (ToDate >= FromDate) {
                    var Differnece = new Date(ToDate - FromDate);
                    $("#txtDifference").val((Differnece.getTime() / 86400000) + 1);
                }
            }
        }           
    </script>


Then call the 'DateDifference()' method in 'onchange' event of the text boxes: 

<asp:TextBox ID="txtFromDate" runat="server" onchange='DateDifference();'></asp:TextBox>

<asp:TextBox ID="txtToDate" runat="server" onchange='DateDifference();'></asp:TextBox>

Output

Thanks Shibashish Mohanty



ShibashishMnty
shibashish mohanty