Search This Blog

Sunday, March 15, 2015

Conditionally Styling a columns rows in GridView Control

In .aspx.cs file Write the following code:  
private static int count = 0;
    protected void Page_Load(object sender, EventArgs  e)
    {
        if(!IsPostBack)
        {
            SqlConnection cn = new SqlConnection("data source=your sql server name;initial catalog=your db name;integrated security=sspi");
            string qry = "select * from employees";
            SqlCommand cmd = new SqlCommand(qry, cn);
            cn.Open();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            cn.Close();
            Session["ds"] = ds;
            gvEmps.DataSource = ds;
            DataTable dt = ds.Tables[0];
            DataColumn dc = new DataColumn("Eligibility",typeof(string));
            dt.Columns.Add(dc);
            gvEmps.DataBind();
        }
    }
    protected void gvEmps_RowDataBound(object sender, GridViewRowEventArgs e)
    {
     
        if (count > 0)
        {
            GridViewRow gvr = e.Row;
            TableCell tc = gvr.Cells[3];

            string data = tc.Text;
            if (data != " "&&data!="doj")
            {
                DateTime dt = Convert.ToDateTime(data);
                int yrsDiff = DateTime.Now.Year - dt.Year;
                if (yrsDiff >= 5)
                {
                    TableCell tc1 = gvr.Cells[4];
                    tc1.Text = "Eligible";
                    tc1.Style.Add("color", "red");
                }
            }
        }
        if (count == 0)
        {
            count = 1;
        }
    }
Table Structure:

eidfnameagedojEligibility
1rijin2304-Oct-09 12:00:00 AMEligible
2kishore2304-Oct-14 12:00:00 AM

Sunday, August 3, 2014

Gridview with Hyperlink sample code

In .aspx file:

<asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="false">
    <AlternatingRowStyle BackColor="green" />
    <Columns>
      <asp:HyperLinkField HeaderText="Name" DataTextField="PName"
         DataNavigateUrlFields="pname"
       DataNavigateUrlFormatString="Sales.aspx?pname={0}" />
      <asp:BoundField HeaderText="MinQty" DataField="MinOrdQty" />
      <asp:BoundField HeaderText="MaxQty" DataField="MaxOrdQty" />
      <asp:BoundField HeaderText="UnitPrice" DataField="UnitPrice" />
    </Columns>
    </asp:GridView>

In .aspx.cs file:

protected void Page_Load(object sender, EventArgs e)
    {
        string strcn = "Data source=name-of-sql-server;initial catalog=database-name;integrated security=sspi";
        SqlConnection cn = new SqlConnection(strcn);
        SqlCommand cmd = new SqlCommand("select pname,minordqty,MaxOrdQty,UnitPrice from dbo.products", cn);
        try
        {
            cn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter DA = new SqlDataAdapter(cmd);
            DA.Fill(ds);
            DataTable dt = ds.Tables[0];
            //Session["ds"] = ds;
            gvProducts.DataSource = dt;
            gvProducts.DataBind();

        }
        catch (SqlException ex)
        {

        }
        finally
        {
            cn.Close();
        }
    }
In Details page (Sales.aspx) :
<asp:GridView ID="gvSales" runat="server"></asp:GridView>

In Sales.aspx.cs:

protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["pname"] != null)
        {
            string productName = Request.QueryString["pname"].ToString();
            if (productName != null && productName != string.Empty)
                DisplaySalesDetails(productName);
        }
    }
    private void DisplaySalesDetails(string pname)
    {
        string strcn = "Data source=ADMIN-PC\\SQLEXPRESS;initial catalog=adomaterial;integrated security=sspi";
        SqlConnection cn = new SqlConnection(strcn);
        string qry=@"select * from dbo.sales where pid in(select pid from products
        where pname='";
        qry+=pname+"');";
        SqlCommand cmd = new SqlCommand(qry,cn);
        try
        {
            cn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter DA = new SqlDataAdapter(cmd);
            DA.Fill(ds);
            Session["ds"] = ds;
            gvSales.DataSource = ds;
            gvSales.DataBind();

        }
        catch (SqlException ex)
        {

        }
        finally
        {
            cn.Close();
        }
    }
Table structure:
Products table:
PidPNameMidMinOrdQtyMaxOrdQtyActiveUnitPrice
1colgate15050065
2DoveSoap1100100025
3DoveShampoo150500150
4ParachuteOil2205080
5RinSoap25050015
6SurfExcel15050055
7VIM21010015
8EXO31015035
9Medimix32515025
10Pears41020026
11NoteBook45127048
12Cinthol2030023
13fiama42020067
Sales table:
SidQtyOriginalPriceTotalPriceDiscountActiveSaleDatePid
1101350120015023-Dec-11 12:00:00 AM3
2252000180020021-Aug-11 12:00:00 AM4
3201300100030013-Jun-10 12:00:00 AM1
41505500490060023-Dec-09 12:00:00 AM6
5501300110020002-Jun-08 12:00:00 AM10
6153753502501-Feb-12 12:00:00 AM2
7253375310027502-Feb-11 12:00:00 AM3
82003000250050007-Oct-10 12:00:00 AM5
91503300320010029-Jul-11 12:00:00 AM9
101003500320030015-Nov-10 12:00:00 AM8
11105505005002-Feb-11 12:00:00 AM6
12501100100010011-Aug-09 12:00:00 AM9
1380120095025023-Dec-10 12:00:00 AM7
14100250090040020-Oct-11 12:00:00 AM9
151201600150010003-Oct-12 12:00:00 AM7

Tuesday, July 22, 2014

Paging in GridView

In .aspx file :

<asp:GridView ID="GridView1" runat="server" PageSize="5" AllowPaging="true"         onpageindexchanging="GridView1_PageIndexChanging">
<PagerSettings Mode="Numeric" />
</asp:GridView>

In .aspx.cs file :

public partial class ADO_DATACONTROLS_Paging : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //sspi=Security Support Provider Interface ~= trusted connection
        if(!IsPostBack)
            DisplayEmployees();
    }

    private void DisplayEmployees()
    {
        string strcn = "data source=ADMIN-PC\\SQLEXPRESS;initial catalog=db17;integrated security=sspi";
        SqlConnection cn = new SqlConnection(strcn);
        SqlCommand cmd = new SqlCommand("select * from employees", cn);
        try
        {
            cn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter DA = new SqlDataAdapter(cmd);
            DA.Fill(ds);
            Session["ds"] = ds;
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        catch (SqlException ex)
        {

        }
        finally
        {
            cn.Close();
        }
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        DataSet ds = (DataSet)Session["ds"];
        DataTable dt = ds.Tables[0];
        GridView1.DataSource = dt;
        GridView1.PageIndex = e.NewPageIndex;//this code must be written between Datasource and DataBind
        GridView1.DataBind();
    }
}

Table structure:

eidfnamelnameagesalarydeptdoj
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM
2sowmyakumari2319000db13-Nov-10 12:00:00 AM
3kishorekumar2736000android16-Oct-11 12:00:00 AM
4abimanyubiswal22nullandroid20-Feb-10 12:00:00 AM
5sonikumar2421800.net21-Jun-09 12:00:00 AM
6anu_singh2212000db23-Oct-10 12:00:00 AM
7_dineshmoh%anty2315000.net26-Aug-09 12:00:00 AM
8nishala_kumari2218000db19-Jul-08 12:00:00 AM
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM

Sorting in Gridview Control

Write the following code In .aspx page :


<asp:GridView ID="gvstudents" runat="server" AllowSorting="true" 

    OnSorting="gvstudents_sorting">

    </asp:GridView>

Code in .aspx.cs:


public partial class SortingEx : System.Web.UI.Page
{
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            string strcon = "data source=name of sql server ;initial catalog=database name;integrated security=sspi or user id=sqlserver userid ; password=somepassword";
            string sConnection = strcon;
            DataSet ds = new DataSet();
            SqlConnection cn = new SqlConnection(sConnection);
            using (cn)
            {
                SqlCommand cmd = new SqlCommand("select * from employees", cn);
                using (cmd)
                {
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    da.Fill(ds);
                    gvstudents.DataSource = ds;
                    gvstudents.DataBind();
                    Session["ds"] = ds;
                    ViewState["sortcolumn"] = string.Empty;
                    ViewState["sortdirection"] = string.Empty;
                }
            }
        }
    }
    protected void gvstudents_sorting(object sender, GridViewSortEventArgs e)
    {
        if (Session["ds"] != null)
        {
           DataSet ds = (DataSet)Session["ds"];
           DataTable dt = ds.Tables[0];
           DataView dv = dt.DefaultView;//DataView ado.net view in t-sql 
            if (ViewState["sortcolumn"].ToString() == e.SortExpression.ToString())
            {
                if ("asc" == ViewState["sortdirection"].ToString())
                {
                    ViewState["sortdirection"] = "DESC";
                }
                else
                {
                    ViewState["sortdirection"] = "asc";
                }
            }
            else
            {
                ViewState["sortcolumn"] = e.SortExpression.ToString();
                ViewState["sortdirection"] = "asc";
            }
            if (dv != null)
            {
                dv.Sort = e.SortExpression + " " + ViewState["sortdirection"].ToString();
                //dv.Sort= "ename asc";
                gvstudents.DataSource = dv;
                gvstudents.DataBind();
            }
        }

    }
}

Table structure :

eidfnamelnameagesalarydeptdoj
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM
2sowmyakumari2319000db13-Nov-10 12:00:00 AM
3kishorekumar2736000android16-Oct-11 12:00:00 AM
4abimanyubiswal22nullandroid20-Feb-10 12:00:00 AM
5sonikumar2421800.net21-Jun-09 12:00:00 AM
6anu_singh2212000db23-Oct-10 12:00:00 AM
7_dineshmoh%anty2315000.net26-Aug-09 12:00:00 AM
8nishala_kumari2218000db19-Jul-08 12:00:00 AM
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM

Monday, July 21, 2014

Asp.net Gridview edit update and delete code

In GridViewEditUpdateDelete.aspx add the following code ( in the form tag )


<asp:GridView ID="gvPatients" AutoGenerateColumns="false" runat="server"
    AutoGenerateEditButton="true" AutoGenerateDeleteButton="true"
    DataKeyNames="PatientID"
        onrowediting="gvPatients_RowEditing"
        OnRowCancelingEdit="gvPatients_RowCancel" OnRowUpdating="gvPatients_RowUpdating"
        OnRowDeleting="gvPatients_RowDeleting"
        onselectedindexchanged="gvPatients_SelectedIndexChanged">
    <HeaderStyle BackColor="AliceBlue" ForeColor="Red"/>
    <Columns>
    <asp:TemplateField HeaderText="PatientID">
    <ItemTemplate>
    <asp:Label ID="lblPatientID" runat="server" Text='<%#Eval("PatientID") %>'/>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="PatientName">
    <EditItemTemplate>
    <asp:TextBox ID="tbName" runat="server" Text='<%#Eval("PatientName")%>' />
    </EditItemTemplate>
    <ItemTemplate>
    <asp:Label ID="lblName" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"PatientName")%>' />
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="AGE">
    <EditItemTemplate>
    <asp:TextBox ID="tbAge" runat="server" Text='<%#Eval("age")%>' />
    </EditItemTemplate>
    <ItemTemplate>
    <asp:Label ID="lblAge" runat="server" Text='<%#Eval("age")%>' />
    </ItemTemplate>
    </asp:TemplateField>  
    </Columns>
    </asp:GridView>


In .aspx.cs page add the following code:


public partial class DataGridColumns : System.Web.UI.Page
{
    DataSet ds = new DataSet();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            FillPatientInfo();
        }
    }
    private void FillPatientInfo()
    {
        //bad approach for getting connection string.
        string strcon = "data source=your sql server name ;initial catalog=your database name ;integrated security=sspi";
        SqlConnection cn = new SqlConnection(strcon);
        SqlCommand cmd = new SqlCommand("select * from patient", cn);
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        da.Fill(ds);
        gvPatients.DataSource = ds;
        gvPatients.DataBind();
        Session["dsPatients"] = ds;
        //GridView1.DataSource = ds;
        //GridView1.DataBind();
    }
    protected void gvPatients_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvPatients.EditIndex = e.NewEditIndex;
        gvPatients.DataSource = (DataSet)Session["dsPatients"];
        gvPatients.DataBind();
    }
    protected void gvPatients_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Label lblPatientID = (Label)gvPatients.Rows[e.RowIndex].FindControl("lblPatientID");
        SqlConnection cn = new SqlConnection("data source=ADMIN-PC\\SQLEXPRESS;initial catalog=palletechnologies;integrated security=sspi");
        SqlCommand cmd = new SqlCommand();
        StringBuilder sb = new StringBuilder();
        sb.Append("delete patient where patientid='" + lblPatientID.Text + "'");
        cmd.CommandText = sb.ToString();
        cmd.Connection = cn;
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
        FillPatientInfo();
    }
    protected void gvPatients_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        Label lblPatientID = (Label)gvPatients.Rows[e.RowIndex].FindControl("lblPatientID");
        TextBox tbAge = (TextBox)gvPatients.Rows[e.RowIndex].FindControl("tbAge");
        TextBox tbName = (TextBox)gvPatients.Rows[e.RowIndex].FindControl("tbName");
        SqlConnection cn = new SqlConnection("data source=ADMIN-PC\\SQLEXPRESS;initial catalog=palletechnologies;integrated security=sspi");
        SqlCommand cmd = new SqlCommand();
        StringBuilder sb = new StringBuilder();
        sb.Append("update patient set patientname='" + tbName .Text+ "',");
        sb.Append("age='"+tbAge.Text+"' where patientid='"+lblPatientID.Text+"'");
        cmd.CommandText = sb.ToString();
        cmd.Connection = cn;
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
        gvPatients.EditIndex = -1;
        FillPatientInfo();
    }
    protected void gvPatients_SelectedIndexChanged(object sender, EventArgs e)
    {
        
    }
    protected void gvPatients_RowCancel(object sender, GridViewCancelEditEventArgs e)
    {
        gvPatients.EditIndex = -1;
        e.Cancel = true;
        gvPatients.DataSource = (DataSet)Session["dsPatients"];
        gvPatients.DataBind();
    }
}

Patient table structure: 

Create a patient table with PatientID,PatientName and Age columns.

Thursday, April 10, 2014

How to set startup url in asp.net mvc

1.Go to MVC web application property page and set Start Action Specific Page value equal to ControllerName/ActionMethodName ( refer below given screen shot )

Friday, December 20, 2013

What is an action method in MVC

An action method in mvc is a method which can able to redirect user to a view ( an action method can optionally pass model data to a view for rendering views ui filled with model data ).
OR
An action method is a method which is returning ActionResult .

Note: I For testing your knowledge in .net , c , cpp , aptitude , java and arithmetic you can visit the links present http://www.skillgun.com

Important links in skillgun .

arithmetic questions and answers
android interview questions and answers
c interview questions and answers
c++ interview questions and answers
java interview questions and answers
c# interview questions and answers