Maximum Capacity Specifications for SQL Server

SQL Server (32/64-bit)
Nested stored procedure levels	32
Nested subqueries	32
Nested trigger levels	32
Nonclustered indexes per table	999
Parameters per stored procedure	2100
Parameters per user-defined function	2100
REFERENCES per table	253
User connections	32767
XML indexes	249
Rows per table	Limited by available storage
Column in table	1024

How many ways are there to implement locking in ADO.NET?

Following are the ways to implement locking using ADO.NET:-

• When we call “Update” method of Data Adapter it handles locking internally. If the Dataset values are not matching with current data in Database, it raises concurrency exception error. We can easily trap this error using Try. Catch block and raise appropriate error message to the user.
• Define a Date time stamp field in the table. When actually you are firing the UPDATE SQL statements, compare the current timestamp with one existing in the database.
• Check for original values stored in SQL SERVER and actual changed values. In stored procedure check before updating that the old data is same as the current data.

How to Programmatically add JavaScript File to Asp.Net page?

If you do not want to add your JavaScript files declaratively via HTML to your ASP.Net page, you can do it in code, and here is how:
Just add this code to the Page_Init event handler on your page:

    protected void Page_Init(object sender, EventArgs e)

    {

        HtmlGenericControl js = new HtmlGenericControl("script");

        js.Attributes["type"] = "text/javascript";

        js.Attributes["src"] = "jscript/formfunctions.js";

        Page.Header.Controls.Add(js);

    }

How do I dynamically add CSS file for ASP.NET ASPX page?

To add CSS Stylesheeet file programatically to ASPX page we can use .NET HtmlLink class in the Page_Init event handler:

protected void Page_Init(object sender, EventArgs e)
  {
    // Define an HtmlLink control.
    HtmlLink myHtmlLink = new HtmlLink();
    myHtmlLink.Href = "~/StyleSheet.css";
    myHtmlLink.Attributes.Add("rel", "stylesheet");
    myHtmlLink.Attributes.Add("type", "text/css");

    // Add the HtmlLink to the Head section of the page.
    Page.Header.Controls.Add(myHtmlLink);
  }

How to maintain ScrollBar position on postbacks in ASP.NET Page?

If your pages are high/long and controls that cause postback are placed on the bottom it can be really annoying for users to manually scroll down after every postback.

<%@ Page Language="C#" AutoEventWireup="true" MaintainScrollPositionOnPostback="true" %>

// or (on page load)

 Page.MaintainScrollPositionOnPostBack = true;

After the postback and reload of the page, the previous scroll position will be restored.

How to get the selected item from UL using jQuery?

<script src="jquery-1.6.2.js" type="text/javascript"></script>
    <script type="text/javascript" language="javascript">
        $(document).ready(function () {
            $('#myUl li').click(function () {
                $('#myUl li').removeClass('selected');
                $(this).addClass('selected');
            });
        });
    </script>
    <style type="text/css">
        .selected
        {
            color: red;
            background-color:Yellow;
            width:100px;
        }
    </style>
 <div>
    <h1>College Courses</h1>
        <ul id="myUl">
            <li>BASIC</li>
            <li>PGDCA</li>
            <li>MBA</li>
            <li>MCA</li>
            <li>B-TECH</li>
            <li>M-TECH</li>
        </ul>
    </div>

Why using "Document Ready Function" with jQuery functions ?

$(document).ready(function(){
   // jQuery functions go here...
});

This is to prevent any jQuery code when form/document is not completed loading.

jQuery Syntax

Basic syntax is: $(selector).action()
   * A dollar sign to define jQuery
   * A (selector) to find HTML elements
   * A jQuery action() to be performed on the element(s)

How to call Webmethod using Javascript and JQuery in asp.net ?

//-- .Aspx Code
 <script src="jquery-1.6.2.js" type="text/javascript"></script>

 //---- Using Javascript -----

    <script type="text/javascript">
        function myServiceFun() {
            PageMethods.myWebMethods(webMethodSuccess, webMethodFail);
        }
        function webMethodSuccess(name) {
            alert(name);
        }
        function webMethodFail(myError) {
            alert(myError);
        }
    </script>

 //---- Using JQuery ----

    <script type="text/javascript">
        $(document).ready(function () {
            $("#btnJQuery").click(function () {
                $.ajax({
                    type: "POST",
                    url: "Default.aspx/myWebMethods",
                    data: "{}",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (msg) {
                        AjaxSucceeded(msg);
                    },
                    error: AjaxFailed
                });
            });
        });

        function AjaxSucceeded(result) {
            if (result.d == true)
                alert(result.d);
            else
                alert(result.d);
        }

        function AjaxFailed(result) {
            alert(result.status + ' ' + result.statusText);
        }   
    </script>

   <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">
    </asp:ScriptManager>
    <input id="btnJS" type="button" value="JS-WebMethod" onclick="myServiceFun();" />
    <input id="btnJQuery" type="button" value="JQuery-WebMethod" />

//--- .cs code

 [WebMethod]
    public static string myWebMethods()
    {
        string str = "";
        SqlConnection con = new SqlConnection("Data Source=MyserverName;Initial Catalog=DBName;User  ID=Rajesh;Password=Rolen");
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter adp = new SqlDataAdapter();
        con.Open();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        DataTable dt = new DataTable();
        cmd.CommandText = "select * from student order by NewId()";
        adp.SelectCommand = cmd;
        adp.Fill(dt);
        cmd.ExecuteNonQuery();
        str = dt.Rows[1]["stuname"].ToString();
        con.Close();
        return str;
    }

Note : If have any problem then set blow tag in web.config

<httpModules>
      <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
    </httpModules>

Distinct with Row_number() in sql

create table StudentAttendance(AttendId int,AttendDate datetime,BatchName nvarchar(50),FacultyName nvarchar(50))
insert into StudentAttendance values(1,'08/03/2011','DotNet Batch','Rajesh Rolen')
insert into StudentAttendance values(2,'09/03/2011','DotNet Batch','Rajesh Rolen')
insert into StudentAttendance values(3,'10/03/2011','DotNet Batch','Rajesh Rolen')

create table StudentAttendanceTrans(AttTransId int,AttendId int,StudentId int)
insert into  StudentAttendanceTrans values (1,1,1001)
insert into  StudentAttendanceTrans values (2,1,1002)
insert into  StudentAttendanceTrans values (3,1,1003)
insert into  StudentAttendanceTrans values (4,2,1001)
insert into  StudentAttendanceTrans values (5,2,1002)
insert into  StudentAttendanceTrans values (6,2,1003)
insert into  StudentAttendanceTrans values (7,3,1001)
insert into  StudentAttendanceTrans values (8,3,1002)
insert into  StudentAttendanceTrans values (9,3,1003)

select distinct row_number() over(order by P.AttendDate )as Sno,

    P.AttendId,P.AttendDate,P.BatchName,P.FacultyName from StudentAttendance P

    inner join StudentAttendanceTrans T on T.AttendId = P.AttendId

SNo Id   Date              BatchName      Faculty Name
1      1    2011-08-03    DotNet Batch    Rajesh Rolen
2      1    2011-08-03    DotNet Batch    Rajesh Rolen
3      1    2011-08-03    DotNet Batch    Rajesh Rolen
4      2    2011-09-03    DotNet Batch    Rajesh Rolen
5      2    2011-09-03    DotNet Batch    Rajesh Rolen
6      2    2011-09-03    DotNet Batch    Rajesh Rolen
7      3    2011-10-03    DotNet Batch    Rajesh Rolen
8      3    2011-10-03    DotNet Batch    Rajesh Rolen
9      3    2011-10-03    DotNet Batch    Rajesh Rolen
Note : here distinct is not working fine.

select distinct row_number() over(order by P.AttendDate )as Sno,

    P.AttendId,P.AttendDate,P.BatchName,P.FacultyName from StudentAttendance P

    inner join StudentAttendanceTrans T on T.AttendId = P.AttendId

    group by P.AttendId,P.AttendDate,P.BatchName,P.FacultyName
SNo Id   Date              BatchName      Faculty Name

1      1    2011-08-03    DotNet Batch    Rajesh Rolen
2      2    2011-09-03    DotNet Batch    Rajesh Rolen
3      3    2011-10-03    DotNet Batch    Rajesh Rolen
Note : here distinct is working fine.

Get record in random order ?

You can get table records randomly with the help of NEWID() function.
NEWID() is a built-in function which returns unique identifier.
Syntax:
select * from TblStudent Order by NEWID()

Bulk insert in sql using C#.Net ?

// Create table in sql
            CREATE TABLE Student(
          StuId int,
          StuName nvarchar(50),
          StuEmail nvarchar(100),
          StuAdd nvarchar(100),
          StuMobile nvarchar(50))
// Implement in C#.Net
using System.IO;
using System.Data.SqlClient;
using System.Configuration;

 private void bulkInser_InSql()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("Name");
            dt.Columns.Add("Email");
            dt.Columns.Add("Add");
            dt.Columns.Add("Mobile");

            dt.Rows.Add(1, "Amit", "Amit@yahoo.com", "Jodhpur", "98979843534");
            dt.Rows.Add(2, "Ashok", "Ashok@yahoo.com", "Jaipur", "097895674745");
            dt.Rows.Add(3, "Vijay", "Vijay@yahoo.com", "Udaipur", "96784565654");
            dt.Rows.Add(4, "Dinesh", "Dinesh@yahoo.com", "Raj", "978657657");
            dt.Rows.Add(5, "Hunny", "Hunny@yahoo.com", "Jodhana", "84556756456");

            using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString()))
            {
                cn.Open();
                using (SqlBulkCopy copy = new SqlBulkCopy(cn))
                {
                    copy.ColumnMappings.Add(0, 0);
                    copy.ColumnMappings.Add(1, 1);
                    copy.ColumnMappings.Add(2, 2);
                    copy.ColumnMappings.Add(3, 3);
                    copy.ColumnMappings.Add(4, 4);

                    copy.DestinationTableName = "Student";
                    copy.WriteToServer(dt);
                }
            }
        }

SqlBulkCopy uses ADO.NET to connect to a database to bulk load the data. I have created an SqlConnection object, and that object reference is used to create the SqlBulkCopy object. The DestinationTableName property references a table in the database where the data is to be loaded. A handy feature of SqlBulkCopy is the SqlBulkCopyColumnMappingCollection. Column mappings define the relationships between columns in the data source and columns in the destination. This is handy if the data source file has columns that don’t need to be inserted into the database. Column mappings can be set by an index, such as the example above, or they can be set by the name of the column. Using the index is handy when you’re working with files that contain no column names. Finally the data is sent to the database by running the WriteToServer method.

Prevent the Back option after Log Out:

Step 1 In the Page_Load event of your master page: 

protected void Page_Load(object sender, EventArgs e)
 {
   if(!IsPostBack)
   {
    if (Session["LoginId"] == null)
      Response.Redirect("frmLogin.aspx");
    else
    {
      Response.ClearHeaders();
      Response.AddHeader("Cache-Control", "no-cache, no-store,
      max-age=0, must-revalidate");
      Response.AddHeader("Pragma", "no-cache");
    }
  }
}

Step 2 Do clear the session on the Logout button as:

Session.Abandon();
 Session.Clear(); 

Step 3 

<script language="text/javascript">
function back()
{
 windows.history.forward(1);
}
</script>
<body onload="back()">

how to add videos in your application using HTML5 ?

<body>
<video controls="controls" >
  <source src="http://clips.vorwaerts-gmbh.de/VfE_html5.mp4"
  type="video/mp4" /><!-- Safari / iOS, IE9 -->
  <source src="http://clips.vorwaerts-gmbh.de/VfE.webm"
  type="video/webm" /><!-- Chrome10+, Ffx4+, Opera10.6+ -->
  <source src="http://clips.vorwaerts-gmbh.de/VfE.ogv"
  type="video/ogg"  /><!-- Firefox3.6+ / Opera 10.5+ -->
</video>
</body>

Scrollbar in gridview ?

<div style="OVERFLOW: auto; WIDTH: 800px; HEIGHT: 240px">
       Gidview
</div>

Found interesting? Add this to