(SQL SERVER) SIMPLE EXAMPLE OF USING A CURSOR

The below example explains the retrieval of column values in single column separated by commas.

  

DECLARE Holiday CURSOR FOR

SELECT HolidayName FROM HolidayMaster

DECLARE @HolidayName NVARCHAR(MAX),

            @Output NVARCHAR(MAX)

SET @Output = ''

OPEN Holiday

FETCH NEXT FROM Holiday INTO @HolidayName

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @Output = @Output + '' + @HolidayName + ','

 FETCH NEXT FROM Holiday INTO @HolidayName

END

--To remove the comma at the end

SET @Output = SUBSTRING(@Output, 0, LEN(@Output))

CLOSE Holiday

DEALLOCATE Holiday

SELECT @Output AS Output

OUTPUT

Adding control to gridview cell dynamically

A control can be added to a gridview using the rowdatabound event.

protected void GridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        Image img = new Image();
        img.ImageUrl = "~/images/logo.png";

        TableCell tcell = new TableCell();
        tcell.Controls.Add(img);
 
        //The control will be added to second cell of the gridview.
        e.Row.Cells[2].Controls.Add(img); 
    }
}

Getting the ID of recently inserted record

To retrieve the ID of the recently inserted record in a sqldatasource control,

<asp:SqlDataSource ID="SqlDataSource" runat="server"
            ConflictDetection="CompareAllValues"
            ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
            InsertCommand="INSERT INTO [ClaimListing] ([UserID], [ClaimTypeCode]) VALUES (@UserID, @ClaimTypeCode); SELECT @NewClaimNo = SCOPE_IDENTITY();"
            OldValuesParameterFormatString="original_{0}"
            OnInserted="SqlDataSource_Inserted">
            <InsertParameters>
                <asp:Parameter Name="UserID" />
                <asp:Parameter Name="ClaimTypeCode" Type="Int32" />
                <asp:Parameter Direction="Output" Type="Int32" Name="NewClaimNo" />
            </InsertParameters>
        </asp:SqlDataSource>

And in the code behind,

protected void SqlDataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        //Read the value of the @Identity OUTPUT parameter
        string ClaimNo = e.Command.Parameters["@NewClaimNo"].Value.ToString();
    }


The identity can be assigned to a hiddenfield control to be accessed from other function.

Sorting in a Repeater

Even though ASP.NET Repeater control does not have sorting option as the Gridview does, it can be manually coded easily using the Dataview. To implement the sorting include in

.aspx page

<asp:Repeater ID="Repeater" runat="server" ClientIDMode="Static" OnItemCommand="Repeater_ItemCommand">
            <HeaderTemplate>
                <table cellspacing="2" cellpadding="5" >
                    <tr style="background-color: #D2B48C;">
                        <th align="center" style="width: 20px;">
                        </th>
                        <th align="center" style="width: 55px;">
                            <asp:LinkButton ID="ClaimNoLinkButton" runat="server" Text="ClaimNo" CommandName="ClaimNo"></asp:LinkButton>
                        </th>
                        <th align="left" style="width: 100px;">
                            <asp:LinkButton ID="LastUpdatedOnLinkButton" runat="server" Text="LastUpdatedOn"
                                CommandName="LastUpdatedOn"></asp:LinkButton>
                        </th>
                    </tr>
            </HeaderTemplate>
            <FooterTemplate>
                <tr>
                    { contents }
                </tr>
                </table>
            </FooterTemplate>
            <ItemTemplate>
                <tr>
                    { contents }
                </tr>
            </ItemTemplate>
            <AlternatingItemTemplate>
                <tr>
                    { contents }
                </tr>
            </AlternatingItemTemplate>
        </asp:Repeater>

.cs page

In page load, if the page is not postback,

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.Page.IsPostBack)
    {
            //Set View State Value for Initial Sort Order
            ViewState["Column"] = "ClaimNo";
            ViewState["Order"] = "DESC";

             BindRepeater();
    }   
}

protected void Repeater_ItemCommand(object source, RepeaterCommandEventArgs e)
    {
            if (e.CommandName == ViewState["Column"].ToString())
            {
                if (ViewState["Order"].ToString() == "ASC")
                    ViewState["Order"] = "DESC";
                else
                    ViewState["Order"] = "ASC";
            }
            else
            {
                ViewState["Column"] = e.CommandName;
                ViewState["Order"] = "ASC";
            }
        }


public void BindRepeater()
{
      DataTable dt = new DataTable();

        dt.Columns.Add("ClaimNo", typeof(int));
        dt.Columns.Add("LastUpdatedOn", typeof(DateTime));

        dt.Rows.Add(1, "2012-03-16 00:00:00.000");
        dt.Rows.Add(2, "2012-02-16 00:00:00.000");

        Repeater.DataSource = dt;
        Repeater.DataBind();

        DataView dvSort = new DataView(dt);
        dvSort.Sort = ViewState["Column"].ToString() + " " + ViewState["Order"].ToString();                //Sorting columns in Repeater

        Repeater.DataSource = dvSort;
        Repeater.DataBind();
}



   

Auto Size the DIV based on its contents

To have a <DIV> shrink and enlarge based on its content, use either,

div { margin: 0 auto;  }

or

div.autosize { display: table; width: 1px; }
div.autosize > div { display: table-cell; }

FREQUENTLY ASKED ASP.NET QUESTIONS (PART-1)

1. 4 main OOPS concept?

Encapsulation, Abstraction, Inheritance, Polymorphism

2. Explain Interface?

-> Interface contains only declaration of members. Classes and structures implement these interface members.

-> It has no implementation.

-> contains signature of method, properties, indexes, events.

-> declares only methods, functions and properties in interfaces.cannot declare a variable in interface.

Eg :-

public interface IOrderDetails

{

//Declare an interface member

void UpdateCustStatus();

void TakeOrder();

}

public void UpdateCustStatus()

{

--------

}

public void TakeOrder()

{

--------

}

3. Properties?

-> Attributes of an object.

eg;- a car has color as property

private string m_color;

public string color

{

get { return m-color; }

set { m_color = value; }

}

car maruti = new car();

maruti.color = "white";

console.write(maruti.color);

4. Define Encapsulation

Packing one or more components together thus preventing access to non-essential details thereby resulting in abstraction which displays only relevant information.

5. Access specifiers

Protected, internal, protected internal, public, private

6. How to prevent your class from being inherited by another class?

use sealed class

7. What is the .NET collection class that allows an element to be accessed using a unique key?

Hash table

8. Define abstract class?
1. A class that cannot be instantiated.
2. An abstract class is a class that must be inherited and have the methods overridden.
3. An abstract class is essentially a blueprint for a class without any implementation

9. Name two properties common in every validation control?

ControlToValidate property and Text property

10. What’s a bubbled event?

Events raised by child controls is handled by parent controls. Eg:- Consider datagrid as a parent control in which there are several child controls. There can be a column of link buttons. Instead of writing event routine for each link button, write one routine for parent which will handle the click events of child link buttons.

11. Define smart navigation?

Cursor position is maintained when the page gets refreshed due to server side validation and the page gets refreshed.

12. What is master page?

A single master page defines the look and feel and standard behaviour that you want for all of the pages in a n application.

SORTING AND PAGING IN A GRIDVIEW

 To manually allow paging and sorting  in a gridview, include
AllowSorting="True" AllowPaging="True" onpageindexchanging="GridView1_PageIndexChanging" onsorting="GridView1_Sorting" in the gridview control.

The code behind is,

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
           
            GridView1.PageIndex = e.NewPageIndex;
            GridView1.DataBind();
            //Bindgrid();
        }

        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
           string sortExpression = e.SortExpression;

            if (GridViewSortDirection == SortDirection.Ascending)
            {
                GridViewSortDirection = SortDirection.Descending;
                SortGridView(sortExpression, DESCENDING);
            }
            else
            {
                GridViewSortDirection = SortDirection.Ascending;
                SortGridView(sortExpression, ASCENDING);
            }
        }

        private void SortGridView(string sortExpression, string direction)
        {
            //  You can cache the DataTable for improving performance
            SqlConnection con = new SqlConnection(connection);
            con.Open();
            DA = new SqlDataAdapter("Select * from table_name", con);
            DataTable dt = new DataTable();
            DA.Fill(dt);

            GridView1.DataSource = dt;
            GridView1.DataBind();
            con.Close();

            DataView dv = new DataView(dt);
            dv.Sort = sortExpression + direction;

            GridView1.DataSource = dv;
            GridView1.DataBind();
        }

        public SortDirection GridViewSortDirection
        {
            get
            {
                if (ViewState["sortDirection"] == null)
                    ViewState["sortDirection"] = SortDirection.Ascending;

                return (SortDirection)ViewState["sortDirection"];
            }
            set { ViewState["sortDirection"] = value; }
        }