ASP.Net handle database connection

When you are not closing the connection correctly. This lead to following error

System.InvalidOperationException Timeout expired. 

The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Today era is fast and completion is so tuff .so programmer does the coding fast. In that case we have forgotten to close connection. But my advice to do the quality work.  So here is example to use database connection

1)First use the try catch to handle exception

// Create a new SQL Connection

        SqlConnection conn = new SqlConnection(Utility.Conn);

        try

        {

            // Open the connection to the database

            conn.Open();

            // Create a new SQL Command

            SqlCommand cmd = new SqlCommand("select * FROM user", conn);

            try

            {

                // Execute the command

                cmd.ExecuteNonQuery();

            }

            finally

            {

                // Dispose of the command

                cmd.Dispose();

            }

            // Close the database connection

            conn.Close();

        }

        finally

        {

            // Dispose of the connection object

            conn.Dispose();

        }

1)      Use the using statement. It automatically clean up object whether error occur. So here is example of using statement

using (SqlConnection connNew = new SqlConnection(Utility.Conn))

        {

            // Create a new SQL Command

            using (SqlCommand cmd = new SqlCommand("select * FROM user", connNew))

            {

                // Open the connection to the database

                conn.Open();

                // Execute the command

                cmd.ExecuteNonQuery();

                // Close the connection to the database

                conn.Close();

            }

        }

Simply you have use sql helper library and Microsoft application library. These libraries are very good fast development and quality work

GridView Row Command event firing twice

I am facing a strange problem for last 2 days. My gridview's Row Command event is firing twice. I have seen several posts all dealing with that same problems. I am developing web application in asp.net using vb.net.  Some people says that its bug in asp.net 2.0. So lots of try finally I have resolved this problem.

I have resolved this problem by changing AutoEventWireup="true"     and remove the row_command handler “'Handles GridView1.RowCommand”    Fixed the problem .so I think its problem event wireup

Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) 'Handles GridView1.RowCommand

        If e.CommandName = "check" Then

            Dim lb As ImageButton = TryCast(e.CommandSource, ImageButton)

            Dim row As GridViewRow = DirectCast(lb.NamingContainer, GridViewRow)

            Dim lvl As Label = TryCast(row.FindControl("lvl"), Label)

            'BindGrid();

            lvl.Text = "second"

        End If

    End Sub

Here are other possible solutions

1)      converting the button column to a template column

<asp:TemplateField HeaderText="Action">

                <ItemTemplate>

                    <asp:ImageButton ID="imgBtn" ImageUrl="~/icn_profile.png" CommandName="check" runat="server" />

                    <asp:Label ID="lvl" Text="first" runat="server"></asp:Label>

                </ItemTemplate>

           </asp:TemplateField>

2)      Converting the image button to link button

<asp:LinkButton ID="lkBtn" runat="server"><img src="icn_profile.png" /></asp:LinkButton>

The following given solution is not working for you. Simply remove the row command event  and used the click event of the button . it‘s firing once

Datatable Plugin implement Asp Net

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table.

DataTable jquery is very effective plugin for tabular data such as gridview in asp.net. In this example we have learn how to implement the datatable jquery plugin in asp.net. So follow the example

Database Stuff:  so I am created the table tmt_user in oracle

CREATE TABLE TMT_USER

(

  PK_USER_ID                  NUMBER,

  PBX_ID                      NUMBER,

  PASSWORD                    VARCHAR2(128 BYTE),

  USER_NAME                   VARCHAR2(500 BYTE),

  DESIGNATION                 VARCHAR2(500 BYTE),

  DOJ                         DATE,

  REPORTING_HEAD_ID           NUMBER,

  LAST_ACTIVITY_DATE          DATE,

  LAST_LOGIN_DATE             DATE,

  LAST_PASSWORD_CHANGED_DATE  DATE,

  IS_LOCKED_OUT               VARCHAR2(100 BYTE),

  LAST_LOCKED_OUT_DATE        DATE,

  ACTIVE_STATUS               NUMBER,

  SORT_ORDER                  NUMBER,

  CREATED_BY                  NUMBER,

  CREATED_DATE                DATE,

  MODIFIED_BY                 NUMBER,

  MODIFIED_DATE               DATE,

  IS_LOGIN                    NUMBER            DEFAULT 0,

  FK_CIRCLE_ID                NUMBER,

  FK_FORM_ID                  VARCHAR2(30 BYTE)

)

Insert records

Insert into WPD.TMT_USER

   (PK_USER_ID, PBX_ID, USER_NAME, DESIGNATION, DOJ, ACTIVE_STATUS, CREATED_BY, CREATED_DATE, PASSWORD, IS_LOGIN, FK_CIRCLE_ID, FK_FORM_ID)

Values

   (571, 240109, 'Ratna_Kotwani', 'Agent', TO_DATE('02/22/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 433, TO_DATE('03/05/2014 12:53:25', 'MM/DD/YYYY HH24:MI:SS'), 'Pass123', 0, 4, '1');

Insert into WPD.TMT_USER

   (PK_USER_ID, PBX_ID, USER_NAME, DESIGNATION, ACTIVE_STATUS, CREATED_BY, CREATED_DATE, PASSWORD, IS_LOGIN, FK_CIRCLE_ID, FK_FORM_ID)

Values

   (572, 240100, 'Santosh Koli', 'Agent', 1, 433, TO_DATE('03/05/2014 12:53:25', 'MM/DD/YYYY HH24:MI:SS'), 'Pass123', 0, 4, '1');

Insert into WPD.TMT_USER

   (PK_USER_ID, PBX_ID, USER_NAME, DESIGNATION, ACTIVE_STATUS, CREATED_BY, CREATED_DATE, PASSWORD, IS_LOGIN, FK_CIRCLE_ID, FK_FORM_ID)

Values

   (573, 240098, 'Shubham Rane', 'Agent', 1, 433, TO_DATE('03/05/2014 12:53:25', 'MM/DD/YYYY HH24:MI:SS'), 'Pass123', 0, 4, '1');

So here I have completed the database stuff. now we can go for asp.net application

ASPX Page: you need datatable and jquery scripting library .you can download the library “datatable.net”.  jquery and jquery ui theme download from jquery and jquery ui site

      <script src="/Scripts/jquery-1.9.1.min.js " type="text/javascript"></script>

  

    <script src="/Scripts/jquery.dataTables.js" type="text/javascript"></script>

    <link rel="stylesheet" href="/css/style.css" />

    <link href="/media/css/jquery-ui-1.9.2.custom.css" rel="stylesheet" type="text/css" />

    <link href="/media/css/jquery.dataTables_themeroller.css" rel="stylesheet" type="text/css" />

    <link href="/Content/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" />

    <script src="/Scripts/jquery-ui-1.10.3.min.js" type="text/javascript"></script>

<script type="text/javascript">

        $(function () {

            var oTable = $('#example').dataTable({

                "sPaginationType": "full_numbers",

                "bJQueryUI": true,

                "bProcessing": true,

                "bServerSide": true,

               "sAjaxSource": "datatable.ashx",

                "aoColumnDefs": [

        {

            "mRender": function (data, type, row) {

                return '<a class="Upd" href= "UserEdit.aspx?UserId=' + data + '" >Update</a> | <a onclick="resetPassword(' + data + ' )" class="ResetPwd" dat-val= "' + data + '" >Reset Password</a>';

            },

            "aTargets": [7], "bSortable": false

        },

        {

            "aTargets": [6], "bSortable": false

        }

                ]

            });

        }

        )

    </script>

<table cellpadding="0" class="dataTable" cellspacing="0" border="0" id="example">

            <thead>

                <tr>

                    <th width="8%">pbx ID

                    </th>

                    <th width="15%">Name

                    </th>

                    <th width="15%">Designation

                    </th>

                    <th width="15%">Date Of Joining

                    </th>

                    <th width="10%">Roles

                    </th>

                    <th width="10%">Status

                    </th>

                    <th width="15%">Created Date

                    </th>

                    <th>Action

                    </th>

                </tr>

            </thead>

           <tbody>

            </tbody>

        </table>

datatable.ashx : in datatable scripting we have seen set sAjaxSource =” datatable. Ashx” . here is the code this file

<%@ WebHandler Language="C#" Class="datatable" %>

using System;

using System.Web;

using Oracle.ManagedDataAccess.Client;

using System.Data;

using System.Text;

public class datatable : IHttpHandler, System.Web.SessionState.IRequiresSessionState

{

    public void ProcessRequest(HttpContext context) {

        context.Response.ContentType = "application/json";

        var conn = new OracleConnection("your connection string"//Utility.Conn);

        try

        {

          

            int echo = Int32.Parse(context.Request.Params["sEcho"]);

            int displayLength = Int32.Parse(context.Request.Params["iDisplayLength"]);

            int displayStart = Int32.Parse(context.Request.Params["iDisplayStart"]);

            string search = context.Request.Params["sSearch"];

            ///////////

            //SEARCH (filter)

            //- build the where clause

            ////////

            var sb = new StringBuilder();

            string whereClause = string.Empty;

            if (!String.IsNullOrEmpty(search))

            {

                sb.Append(" WHERE pbx_id LIKE '%");

                sb.Append(search.ToUpper());

                sb.Append("%' OR  upper(user_name) LIKE '%");

                sb.Append(search.ToUpper());

                sb.Append("%' OR upper(DESIGNATION) LIKE '%");

                sb.Append(search.ToUpper());

                sb.Append("%' OR DOJ LIKE '%");

                sb.Append(search);

                sb.Append("%'");

                whereClause = sb.ToString();

            }

            sb.Clear();

            //Check which column is to be sorted by in which direction

            for (int i = 0; i < 11; i++)

            {

                if (context.Request.Params["bSortable_" + i] == "true")

                {

                    sb.Append(context.Request.Params["iSortCol_" + i]);

                    sb.Append(" ");

                    sb.Append(context.Request.Params["sSortDir_" + i]);

                }

            }

            string orderByClause = sb.ToString();

            //Replace the number corresponding the column position by the corresponding name of the column in the database

            if (!String.IsNullOrEmpty(orderByClause))

            {

                orderByClause = orderByClause.Replace("0", ", pbx_id");

                orderByClause = orderByClause.Replace("1", ", user_name");

                orderByClause = orderByClause.Replace("2", ", DESIGNATION");

                orderByClause = orderByClause.Replace("3", ", DOJ");

                orderByClause = orderByClause.Replace("4", ", ACTIVE_STATUS");

                orderByClause = orderByClause.Replace("5", ", CREATED_DATE");

                //Eliminate the first comma of the variable "order"

                orderByClause = orderByClause.Remove(0, 1);

            }

            else

                orderByClause = "pbx_id pk_user_id desc";

            orderByClause = "ORDER BY " + orderByClause;

            /////////////

            //T-SQL query

            //- ROW_NUMBER() is used for db side pagination

            /////////////

            sb.Clear();

           

            string query = @"SELECT * FROM

                        (SELECT rownum  AS RowNumber,TotalDisplayRows,TotalRows,pbx_id,user_name,DESIGNATION,DOJ,user_role,ACTIVE_STATUS,CREATED_DATE,PK_USER_ID,CIRCLE_NAME FROM

                           (SELECT ( SELECT COUNT(*)FROM tmt_user U inner join tmt_circle C  on C.PK_CIRCLE_ID = U.FK_CIRCLE_ID  {1} )AS TotalDisplayRows,

                         ( SELECT COUNT(*)FROM tmt_user U inner join tmt_circle C  on C.PK_CIRCLE_ID = U.FK_CIRCLE_ID  where 1=1  )AS TotalRows,pbx_id,user_name,DESIGNATION,DOJ,

                            (select  listagg (MASTERROLE.ROLE_NAME , '-') WITHIN GROUP (ORDER BY MASTERROLE.ROLE_NAME) Role from  TMT_USER_IN_ROLE UserRole inner join TMT_USER_ROLE MasterRole on UserRole.FK_USER_ROLE_ID=MasterRole.PK_USER_ROLE_ID where UserRole.FK_USER_ID=PK_USER_ID) user_role,

                             ACTIVE_STATUS, CREATED_DATE,PK_USER_ID , (select circle_name from tmt_circle where pk_circle_id= fk_circle_id  ) CIRCLE_NAME FROM  tmt_user {1}  {0}  ) RawResults  where 1=1 ) Results

                           WHERE  RowNumber BETWEEN {2} AND {3}";

            ;

            query = String.Format(query, orderByClause, whereClause, displayStart + 1, displayStart + displayLength);

            //Get result rows from DB

            conn.Open();

            OracleCommand cmd = conn.CreateCommand();

            cmd.CommandText = query;

            cmd.CommandType = CommandType.Text;

            IDataReader rdrBrowsers = cmd.ExecuteReader();

            sb.Clear();

            int totalDisplayRecords = 0;

            int totalRecords = 0;

            while (rdrBrowsers.Read())

            {

                if (totalRecords == 0)

                    totalRecords = Int32.Parse(rdrBrowsers["TotalRows"].ToString());

                if (totalDisplayRecords == 0)

                    totalDisplayRecords = Int32.Parse(rdrBrowsers["TotalDisplayRows"].ToString());

                sb.Append("[");

                sb.Append("\"" + rdrBrowsers["pbx_id"] + "\",");

                sb.Append("\"" + rdrBrowsers["user_name"] + "\",");

                sb.Append("\"" + rdrBrowsers["DESIGNATION"] + "\",");

                if (!string.IsNullOrEmpty(rdrBrowsers["DOJ"].ToString()))

                    sb.Append("\"" + Convert.ToDateTime(rdrBrowsers["DOJ"].ToString()).ToString("MM/dd/yyyy") +

                              "\",");

                else

                    sb.Append("\"" + rdrBrowsers["DOJ"] + "\",");

                sb.Append("\"" + rdrBrowsers["user_role"] + "\",");

                sb.Append("\"" + rdrBrowsers["ACTIVE_STATUS"] + "\",");

                if (!string.IsNullOrEmpty(rdrBrowsers["CREATED_DATE"].ToString()))

                    sb.Append("\"" +

                              Convert.ToDateTime(rdrBrowsers["CREATED_DATE"].ToString()).ToString("MM/dd/yyyy") +

                              "\",");

                else

                    sb.Append("\"" + rdrBrowsers["CREATED_DATE"] + "\",");

                sb.Append("\"" + rdrBrowsers["PK_USER_ID"] + "\"");

                //

                sb.Append("],");

            }

            var outputJson = sb.ToString();

            if (outputJson.Length > 0)

                outputJson = outputJson.Remove(outputJson.Length - 1);

            sb.Clear();

            cmd.Dispose();

            sb.Append("{");

            sb.Append("\"sEcho\": ");

            sb.Append(echo);

            sb.Append(",");

            sb.Append("\"iTotalRecords\": ");

            sb.Append(totalRecords);

            sb.Append(",");

            sb.Append("\"iTotalDisplayRecords\": ");

            sb.Append(totalDisplayRecords);

            sb.Append(",");

            sb.Append("\"aaData\": [");

            sb.Append(outputJson);

            sb.Append("]}");

            outputJson = sb.ToString();

           context.Response.Clear();

           context.Response.ClearHeaders();

           context.Response.ClearContent();

           context.Response.Write(outputJson);

           context.Response.Flush();

           context.ApplicationInstance.CompleteRequest();

        }

        finally

        {

            conn.Close();

            conn.Dispose();

        }

       

    }

    public bool IsReusable

    {

        get

        {

            return false;

        }

    }

}

So check out that example .if you need help contact me email “anilsharma1983.blog@gmail.com”