Paritosh-DotNet: 2011

Sunday, 5 June 2011

Navigating between Records without using any built-in Data Control in ASP.Net

Default.aspx

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td style="width: 127px">
                    <asp:Label ID="Label1" runat="server" Text="Employee ID:"></asp:Label></td>
                <td style="width: 354px">
                    <asp:Label ID="LblEmployeeID" runat="server" Text="Label"></asp:Label></td>
            </tr>
            <tr>
                <td style="width: 127px">
                    <asp:Label ID="Label3" runat="server" Text="Employee Name:"></asp:Label></td>
                <td style="width: 354px">
                    <asp:Label ID="LblEmployeeName" runat="server" Text="Label"></asp:Label></td>
            </tr>
            <tr>
                <td style="width: 127px">
                    <asp:Label ID="Label5" runat="server" Text="Date of Join:"></asp:Label></td>
                <td style="width: 354px">
                    <asp:Label ID="LblDateOfJoin" runat="server" Text="Label"></asp:Label></td>
            </tr>
            <tr>
                <td style="width: 127px">
                    <asp:Label ID="Label7" runat="server" Text="Salary:"></asp:Label></td>
                <td style="width: 354px">
                    <asp:Label ID="LblSalary" runat="server" Text="Label"></asp:Label></td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="BtnFirst" runat="server" Text="|<" OnClick="BtnFirst_Click" Width="20px" />
                    <asp:Button ID="BtnPrior" runat="server" Text="<" OnClick="BtnPrior_Click" Width="20px" />
                    <asp:TextBox ID="TxtCurrentRecord" runat="server" Width="17px" AutoPostBack="True" OnTextChanged="TxtCurrentRecord_TextChanged"></asp:TextBox>
                    <asp:Label ID="LblTotalRecords" runat="server"></asp:Label>
                    <asp:Button ID="BtnNext" runat="server" Text=">" OnClick="BtnNext_Click" Width="20px" />
                    <asp:Button ID="BtnLast" runat="server" Text=">|" OnClick="BtnLast_Click" Width="20px" /></td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

Default.aspx.cs

static DataTable dt;
    static int currec, totrec;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            dt = GetData();
        }
        totrec = dt.Rows.Count;
        if (!Page.IsPostBack)
            ShowData(0);
        ScrollControl();
    }

    protected void BtnFirst_Click(object sender, EventArgs e)
    {
        currec = 0;
        ShowData(currec);
        ScrollControl();
    }

    protected void BtnPrior_Click(object sender, EventArgs e)
    {
        currec -= 1;
        ShowData(currec);
        ScrollControl();
    }

    protected void BtnNext_Click(object sender, EventArgs e)
    {
        currec += 1;
        ShowData(currec);
        ScrollControl();
    }

    protected void BtnLast_Click(object sender, EventArgs e)
    {
        currec = totrec - 1;
        ShowData(currec);
        ScrollControl();
    }

    protected void TxtCurrentRecord_TextChanged(object sender, EventArgs e)
    {
        currec = Convert.ToInt32(TxtCurrentRecord.Text) - 1;
        if (currec + 1 > totrec)
        {
            currec = totrec - 1;
        }
        else if (currec < 0)
        {
            currec = 0;
        }
        ShowData(currec);
        ScrollControl();
    }

    public void ShowData(int cr)
    {
        DataRow dr = dt.Rows[cr];
        LblEmployeeID.Text = Convert.ToString(dr["empid"]);
        LblEmployeeName.Text = Convert.ToString(dr["empname"]);
        LblDateOfJoin.Text = Convert.ToString(dr["dateofjoin"]);
        LblSalary.Text = Convert.ToString(dr["salary"]);
        TxtCurrentRecord.Text = (currec + 1).ToString();
        LblTotalRecords.Text = " of " + totrec.ToString();
    }

    public DataTable GetData()
    {
        DataTable DtEmp = new DataTable();
        DataColumn dc = new DataColumn("empid", typeof(string));
        DtEmp.Columns.Add(dc);
        dc = new DataColumn("empname", typeof(string));
        DtEmp.Columns.Add(dc);
        dc = new DataColumn("dateofjoin", typeof(int));
        DtEmp.Columns.Add(dc);
        dc = new DataColumn("salary", typeof(DateTime));
        DtEmp.Columns.Add(dc);
        DtEmp.Rows.Add("E001", "Rahul Mehera", 12000, new DateTime(2010, 1, 1));
        DtEmp.Rows.Add("E002", "Bhuban Agarwal", 16000, new DateTime(2009, 10, 1));
        DtEmp.Rows.Add("E003", "Ranjjit Roy", 18500, new DateTime(2009, 7, 5));
        DtEmp.Rows.Add("E004", "Mohit Garg", 32000, new DateTime(2003, 12, 1));
        DtEmp.Rows.Add("E005", "Ipsita Patel", 22000, new DateTime(2002, 9, 10));
        DtEmp.Rows.Add("E006", "Anisha Basu", 20000, new DateTime(2007, 11, 1));
        DtEmp.Rows.Add("E007", "Bibek Shetty", 35000, new DateTime(2010, 7, 3));
        DtEmp.Rows.Add("E008", "Namrata Roy", 45000, new DateTime(2010, 12, 3));
        return DtEmp;
    }

    void ScrollControl()
    {
        if (totrec == 0)
        {
            BtnFirst.Enabled = false;
            BtnPrior.Enabled = false;
            BtnNext.Enabled = false;
            BtnLast.Enabled = false;
        }
        else if (currec >= totrec - 1)
        {
            BtnFirst.Enabled = true;
            BtnPrior.Enabled = true;
            BtnNext.Enabled = false;
            BtnLast.Enabled = false;
        }
        else if (currec <= 0)
        {
            BtnFirst.Enabled = false;
            BtnPrior.Enabled = false;
            BtnNext.Enabled = true;
            BtnLast.Enabled = true;
        }
        else
        {
            BtnFirst.Enabled = true;
            BtnPrior.Enabled = true;
            BtnNext.Enabled = true;
            BtnLast.Enabled = true;
        }
    }

Ajax AutoComplete Exender with Database Connectivity

Default.aspx

<%@ 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">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
        </asp:ToolkitScriptManager>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="TextBox1"
            ServicePath="WebService.asmx" ServiceMethod="GetNames" MinimumPrefixLength="0"
            CompletionInterval="100" EnableCaching="true" CompletionSetCount="12">
        </asp:AutoCompleteExtender>
    </div>
    </form>
</body>
</html>

WebService.cs

using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Configuration;
using System.Data.SqlClient;

/// 
/// Summary description for WebService
/// 
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{

    public WebService()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    public string[] GetNames(string prefixText, int count)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
        count = 10;
        string str = "SELECT * FROM student WHERE sname like '" + prefixText + "%'";
        con.Open();
        SqlCommand com = new SqlCommand(str, con);
        SqlDataReader dr = com.ExecuteReader();
        int i = 0;
        ArrayList AlNames = new ArrayList();
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                string StrTemp = dr["sname"].ToString();
                AlNames.Add(StrTemp);
                i++;
            }
        }
        return AlNames.ToArray(typeof(string)) as string[];
    }
}

Numeric TextBox in ASP.Net

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>

    <script type="text/javascript">
    function isNumberKey(evt)
    {
        var charCode = (evt.which) ? evt.which : event.keyCode
        if (charCode > 31 && (charCode < 48 || charCode > 57))
            return false;
        return true;
    }
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <asp:TextBox ID="TextBox2" runat="server" onkeypress="return isNumberKey(event)"></asp:TextBox>
    </form>
</body>
</html>

Saturday, 4 June 2011

Reading the Content of an URL in HTML format

using System.Net;
using System.Text;
using System.IO;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string contents = ReadHtmlFromUrl("http://www.google.com");
        Literal1.Mode = LiteralMode.Encode;
        Literal1.Text = contents;
    }

    public string ReadHtmlFromUrl(string Url)
    {
        const int TIMEOUT = 3000;
        HttpWebRequest myWebRequest = null;
        HttpWebResponse myWebResponse = null;
        Stream receiveStream = null;
        Encoding encode = null;
        StreamReader readStream = null;
        string content = null;

        try
        {
            myWebRequest = HttpWebRequest.Create(Url) as HttpWebRequest;

            myWebRequest.Timeout = TIMEOUT;
            myWebRequest.ReadWriteTimeout = TIMEOUT;

            myWebResponse = myWebRequest.GetResponse() as HttpWebResponse;
            receiveStream = myWebResponse.GetResponseStream();
            encode = System.Text.Encoding.GetEncoding("utf-8");
            readStream = new StreamReader(receiveStream, encode);
            content = readStream.ReadToEnd().ToLower();
            if (readStream != null) readStream.Close();
            if (receiveStream != null) receiveStream.Close();
            if (myWebResponse != null) myWebResponse.Close();
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
        finally
        {
            readStream = null;
            receiveStream = null;
            myWebResponse = null;
            myWebRequest = null;
        }
        return content;
    }
}

Generating VarChar Sequence Number for Primary Key Columns in any Database Table

The Requirement

In Relational Database, every table should have a Primary Key Column. Sometimes it is required to generate a sequence number automatically for that column, whenever a record is inserted into the table. For example an Employee ID primary key can have a sequence number like E001, E002, E003, …, E999.

protected void Page_Load(object sender, EventArgs e)
{
        string id = GenerateId("TblStudents", "sid", 4, "S", true);
        Response.Write(id);
}

public string GenerateId(string TableName, string ColumnName, int ColumnLength, string Prefix, bool Padding)
{
        SqlConnection con = new SqlConnection("server=.;integrated security=true;database=DotNetSpider");
        string Query, Id;
        int PrefixLength, PadLength;
        PrefixLength = Convert.ToInt32(Prefix.Length);
        PadLength = ColumnLength - PrefixLength;
        if (Padding == true)
        {
            Query = "SELECT '" + Prefix + "' + REPLACE(STR(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(PrefixLength + 1) + "," + PadLength + ") AS INTEGER))+1," + PadLength + "),' ',0) FROM " + TableName;
        }
        else
        {
            Query = "SELECT '" + Prefix + "' + CAST(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(PrefixLength + 1) + "," + PadLength + ") AS INTEGER))+1 AS VARCHAR) FROM " + TableName;
        }
        SqlCommand com = new SqlCommand(Query, con);
        con.Open();
        if (com.ExecuteScalar().ToString() == "")
        {
            Id = Prefix;
            if (Padding == true)
            {
                for (int i = 1; i <= PadLength - 1; i++)
                {
                    Id += "0";
                }
            }
            Id += "1";
        }
        else
        {
            Id = Convert.ToString(com.ExecuteScalar());
        }
        con.Close();
        return Id;
}

C# code to check the existence of an item in a Database Table using a single method

The Requirement

In any application, sometimes it is required to check if a particular value exists in a Database table or not. For example, you want to check if an user with user id U001 exists in the database or not. For this purpose we can use a single method that will check for the existence of a particular value from a particular table.

protected void Page_Load(object sender, EventArgs e)
{
        bool result = CheckExist("TblStudents", "sid", "S001");
        Response.Write("Result: " + result.ToString());
}

public bool CheckExist(string TableName, string ColumnName, string Value)
{
        string query, result;
        bool IsExists;
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
        query = "SELECT " + ColumnName + " FROM " + TableName + " WHERE " + ColumnName + " = '" + Value + "'";
        SqlCommand com = new SqlCommand(query, con);
        con.Open();
        result = Convert.ToString(com.ExecuteScalar());
        if (result != "")
            IsExists = true;
        else
            IsExists = false;
        con.Close();
        return IsExists;
}

C# Code to get value by key or id from a database table

The Requirement

In any application, sometimes it is required to retrieve a value from the database by passing an Id. For example, you want to get the name of the student whose rollno is 32. For this purpose we can use a single method that will retrieve the reqiuired data from any table.

protected void Page_Load(object sender, EventArgs e)
{
        string studentname = GetValueById("TblStudents", "S001", "sid", "sname");
        Response.Write(studentname);
}

public string GetValueById(string TableName, string Id, string IdColumnName, string ValueColumnName)
{
        try
        {
            string query, result;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
            query = "SELECT " + ValueColumnName + " FROM " + TableName + " WHERE " + IdColumnName + " = '" + Id + "'";
            SqlCommand com = new SqlCommand(query, con);
            con.Open();
            result = Convert.ToString(com.ExecuteScalar());
            if (result == "")
            {
                result = "Not Found";
            }
            con.Close();
            return result;
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
}

Binding List Controls dynamically using a single method

In ASP.Net, we have number of List Controls such as DropDownList, ListBox, CheckBoxLists, RadioButtonList and BulletedList which contains a list of items in key value pair. Sometimes, it is required to bind these types of controls with values from any Database table.

The following code exaplains how to bind a DropDownList with values from any Database table:

protected void Page_Load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
        {
            DataTable dt = GetKeyValuePair("TblStudents", "sname", "sid", "-SELECT-");
            DropDownList1.DataSource = dt;
            DropDownList1.DataTextField = "sname";
            DropDownList1.DataValueField = "sid";
            DropDownList1.DataBind();
        }
}

public DataTable GetKeyValuePair(string TableName, string KeyColumn, string ValueColumn, string DefaultValue)
{
        string query;
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
        SqlDataAdapter sqlda;
        DataTable dt;
        query = "SELECT '" + DefaultValue + "' AS " + KeyColumn + " , '" + DefaultValue + "' AS " + ValueColumn + " FROM " + TableName + " UNION ";
        query += "SELECT " + KeyColumn + ", " + ValueColumn + " FROM " + TableName;
        sqlda = new SqlDataAdapter(query, con);
        dt = new DataTable();
        sqlda.Fill(dt);
        return dt;
}

Saturday, 28 May 2011

Blinking Text within GridView depending on some condition

The Requirement

Some the text within a GridView Row should blink, depending upon certain conditions.

Suppose I have a student table with columns sid, sname, sadd and smarks. If student mark is greater than or equal to 50, then the color of the mark within the GridView should be green else the color of the mark should be red and it should blink.

Note: It should be compatible with Internet Explorer as well as Firefox.

Code for Internet Explorer

The following JavaScript will work only for Internet Explorer:

<script type="text/javascript">
    window.onload = showdata;
    function showdata()
    {
        var gridViewCtlId = '<%=GridView1. ClientID%>';
        var grid = document.getElementById(gridViewCtlId);
        var gridLength = grid.rows.length;
        for (var i = 1; i < gridLength; i++)
        {
            elementid=grid.rows[i].cells[3];
            if(elementid.innerText>=50)
            {
               elementid.style.color = 'green';
            }
            else if(elementid.innerText < 50)
            {
                elementid.style.color = 'red';
                if(elementid.style.visibility == 'hidden')
                    elementid.style.visibility = 'visible';
                else
                    elementid.style.visibility = 'hidden';
                
            }
         }
        setTimeout('showdata()', 250);
    }
    </script>

Code for Firefox

The following code will work for Firefox

<style type="text/css">
    .blinkytext
    {
     text-decoration: blink;
    }
</style>

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            if (e.Row.RowState != DataControlRowState.Edit)
            {
                Label lbl = e.Row.FindControl("LblMarks") as Label;
                if (Convert.ToInt32(lbl.Text) >= 50)
                {
                    lbl.ForeColor = Color.Green;
                }
                else
                {
                    lbl.ForeColor = Color.Red;
                    lbl.CssClass = "blinkytext";
                }
            }
        }
    }

Paritosh-DotNet