Paritosh-DotNet

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;
        }
}