Microsoft Sql Server 2014 Features

Although it doesn’t seem possible so soon, Microsoft announced SQL Server 2014 at this year’s TechEd 2013 conference in New Orleans. Quentin Clark, Microsoft Corporate Vice President for SQL Server, said that Microsoft is getting ready for the upcoming SQL Server 2014 release. Some of the most important new features in SQL Server 2014 include the following:

In-Memory OLTP Engine
The new In-Memory OLTP Engine (formerly code-named Hekaton) will provide OLTP performance improvements by moving selected tables into memory. The In-memory OTLP Engine works with commodity hardware and won’t require any application code changes. A built-in wizard will help you to choose which tables go in memory and select the stored procedures that will be compiled into machine code for high performance execution.

EdgeNet, an early adopter, saw a 7X performance increase with no code changes. Microsoft claims that some applications can achieve a 50x performance boost using the new In-Memory OTLP engine.

Improved Scalability
SQL Server 2014 will have the ability to scale up to 640 logical processors and 4TB of memory in a physical environment. It can scale to 64 virtual processors and 1TB of memory when running in a virtual machine (VM). New buffer pool enhancements increase performance by extending SQL Server’s in-memory buffer pool to SSDs for faster paging.

Windows Azure Integrated Backup
The new backup option is integrated into SQL Server Management Studio (SSMS). It lets you back up a SQL Server database to Windows Azure. You can also use it to quickly restore database backups to an Azure VM.

Azure Integrated AlwaysOn Availability Groups
AlwaysOn Availability Groups have also been integrated with Azure, providing AlwaysOn capabilities in the cloud. AlwaysOn Azure integration enables you to create asynchronous Availability Group replicas in Azure for disaster recovery.

Like the new Azure backup feature, the Azure AlwaysOn Availability options are completely integrated into SSMS. Other enhancements to AlwaysOn Availability Groups include the ability to have up to eight replicas—up from four in SQL Server 2012.

Business Intelligence and Data Visualization Enhancements
SQL Server 2014 will include the new data visualization tool, code-named Data Explorer. Data Explorer enables data analysis in Microsoft Excel, and its can work with a wide variety of sources including relational, structured, and semi-structured data such as OData, Hadoop, and the Azure Marketplace.

The new feature, code-named GEOFlow, will able to provide visual data mapping in Excel. You can download the previews for Data Explorer and GEOFlow from the Microsoft website. Other BI enhancements include the ability for Power View to work against multidimensional cube data in addition to tabular data models.

Improved Integration with Windows Server 2012
SQL Server 2014 will also provide support for Windows Server 2012’s new Storage Spaces feature. Storage Spaces enables you to create pools of tiered storage that can improve application availability and performance. SQL Server 2014’s Resource Governor can take advantage of Windows Server 2012’s automated storage tiering. Plus, you can use the Resource Governor to manage and limit application IO utilization.

Microsoft skipped the traditional R2 release that it normally puts out between major releases because of the significant changes it made to the database engine to support the new In-Memory OLTP Engine. A preview of the SQL Server 2014 release is expected in late June and the general availability of SQL Server 2014 is planned for early 2014. You can read more in the Microsoft SQL Server Blog.

C# NEW Features in Microsoft .Net Framework 4.0

Covariance and Contravariance

Covariance and contravariance are best introduced with an example, and the best is in the framework. In System.Collections.Generic, IEnumerable<T> and IEnumerator <T> represent, respectively, an object that’s a sequence of T’s and the enumerator (or iterator) that does the work of iterating the sequence. These interfaces have done a lot of heavy lifting for a long time, because they support the implementation of the foreach loop construct. In C# 3.0, they became even more prominent because of their central role in LINQ and LINQ to Objects—they’re the .NET interfaces to represent sequences.

So if you have a class hierarchy with, say, an Employee type and a Manager type that derives from it (managers are employees, after all), then what would you expect the following code to do?

IEnumerable<Manager> ms = GetManagers();

IEnumerable<Employee> es = ms;

It seems as though one ought to be able to treat a sequence of Managers as though it were a sequence of Employees. But in C# 3.0, the assignment will fail; the compiler will tell you there’s no conversion. After all, it has no idea what the semantics of IEnumerable<T> are. This could be any interface, so for any arbitrary interface IFoo<T>, why would an IFoo<Manager> be more or less substitutable for an IFoo<Employee>?

In C# 4.0, though, the assignment works because IEnumerable<T>, along with a few other interfaces, has changed, an alteration enabled by new support in C# for covariance of type parameters.

IEnumerable<T> is eligible to be more special than the arbitrary IFoo<T> because, though it’s not obvious at first glance, members that use the type parameter T (GetEnumerator in IEnumerable<T> and the Current property in IEnumerator<T>) actually use T only in the position of a return value. So you only get a Manager out of the sequence, and you never put one in.

In contrast, think of List<T>. Making a List<Manager> substitutable for a List<Employee> would be a disaster, because of the following:

List<Manager> ms = GetManagers();

List<Employee> es = ms; // Suppose this were possible

es.Add(new EmployeeWhoIsNotAManager()); // Uh oh

As this shows, once you think you’re looking at a List<Employee>, you can insert any employee. But the list in question is actually a List<Manager>, so inserting a non-Manager must fail. You’ve lost type safety if you allow this. List<T> cannot be covariant in T.

The new language feature in C# 4.0, then, is the ability to define types, such as the new IEnumerable<T>, that admit conversions among themselves when the type parameters in question bear some relationship to one another. This is what the .NET Framework developers who wrote IEnumerable<T> used, and this is what their code looks like (simplified, of course):

public interface IEnumerable<out T> { /* ... */ }

Notice the out keyword modifying the definition of the type parameter, T. When the compiler sees this, it will mark T as covariant and check that, in the definition of the interface, all uses of T are up to snuff (in other words, that they’re used in out positions only—that’s why this keyword was picked).

Why is this called covariance? Well, it’s easiest to see when you start to draw arrows. To be concrete, let’s use the Manager and Employee types. Because there’s an inheritance relationship between these classes, there’s an implicit reference conversion from Manager to Employee:

Manager → Employee

And now, because of the annotation of T in IEnumerable<out T>, there’s also an implicit reference conversion from IEnumerable<Manager> to IEnumerable<Employee>. That’s what the annotation provides for:

IEnumerable<Manager> → IEnumerable<Employee>

This is called covariance, because the arrows in each of the two examples point in the same direction. We started with two types, Manager and Employee. We made new types out of them, IEnumerable<Manager> and IEnumerable<Employee>. The new types convert the same way as the old ones.

Contravariance is when this happens backward. You might anticipate that this could happen when the type parameter, T, is used only as input, and you’d be right. For example, the System namespace contains an interface called IComparable<T>, which has a single method called CompareTo:

public interface IComparable<in T> {

  bool CompareTo(T other);

}

If you have an IComparable<Employee>, you should be able to treat it as though it were an IComparable<Manager>, because the only thing you can do is put Employees in to the interface. Because a manager is an employee, putting a manager in should work, and it does. The in keyword modifies T in this case, and this scenario functions correctly:

IComparable<Employee> ec = GetEmployeeComparer();

IComparable<Manager> mc = ec;

This is called contravariance because the arrow got reversed this time:

Manager → Employee
IComparable<Manager> ← IComparable<Employee>

So the language feature here is pretty simple to summarize: You can add the keyword in or out whenever you define a type parameter, and doing so gives you free extra conversions. There are some limitations, though.

First, this works with generic interfaces and delegates only. You can’t declare a generic type parameter on a class or struct in this manner. An easy way to rationalize this is that delegates are very much like interfaces that have just one method, and in any case, classes would often be ineligible for this treatment because of fields. You can think of any field on the generic class as being both an input and an output, depending on whether you write to it or read from it. If those fields involve type parameters, the parameters can be neither covariant nor contravariant.

Second, whenever you have an interface or delegate with a covariant or contravariant type parameter, you’re granted new conversions on that type only when the type arguments, in the usage of the interface (not its definition), are reference types. For instance, because int is a value type, the IEnumerator<int> doesn’t convert to IEnumerator <object>, even though it looks like it should:

IEnumerator <int> image: right arrow with slash  IEnumerator <object>

The reason for this behavior is that the conversion must preserve the type representation. If the int-to-object conversion were allowed, calling the Current property on the result would be impossible, because the value type int has a different representation on the stack than an object reference does. All reference types have the same representation on the stack, however, so only type arguments that are reference types yield these extra conversions.

Very likely, most C# developers will happily use this new language feature—they’ll get more conversions of framework types and fewer compiler errors when using some types from the .NET Framework (IEnumerable<T>, IComparable<T>, Func<T>, Action<T>, among others). And, in fact, anyone designing a library with generic interfaces and delegates is free to use the new in and out type parameters when appropriate to make life easier for their users.

By the way, this feature does require support from the runtime—but the support has always been there. It lay dormant for several releases, however, because no language made use of it. Also, previous versions of C# allowed some limited conversions that were contravariant. Specifically, they let you make delegates out of methods that had compatible return types. In addition, array types have always been covariant. These existing features are distinct from the new ones in C# 4.0, which actually let you define your own types that are covariant and contravariant in some of their type parameters.
Dynamic Dispatch

On to the interop features in C# 4.0, starting with what is perhaps the biggest change.

C# now supports dynamic late-binding. The language has always been strongly typed, and it continues to be so in version 4.0. Microsoft believes this makes C# easy to use, fast and suitable for all the work .NET programmers are putting it to. But there are times when you need to communicate with systems not based on .NET.

Traditionally, there were at least two approaches to this. The first was simply to import the foreign model directly into .NET as a proxy. COM Interop provides one example. Since the original release of the .NET Framework, it has used this strategy with a tool called TLBIMP,  which creates new .NET proxy types you can use directly from C#.

LINQ-to-SQL, shipped with C# 3.0, contains a tool called SQLMETAL, which imports an existing database into C# proxy classes for use with queries. You’ll also find a tool that imports Windows Management Instrumentation (WMI) classes to C#. Many technologies allow you to write C# (often with attributes) and then perform interop using your handwritten code as basis for external actions, such as LINQ-to-SQL, Windows Communication Foundation (WCF) and serialization.

The second approach abandons the C# type system entirely—you embed strings and data in your code. This is what you do whenever you write code that, say, invokes a method on a JScript object or when you embed a SQL query in your ADO.NET application. You’re even doing this when you defer binding to run time using reflection, even though the interop in that case is with .NET itself.

The dynamic keyword in C# is a response to dealing with the hassles of these other approaches. Let’s start with a simple example—reflection. Normally, using it requires a lot of boilerplate infrastructure code, such as:

object o = GetObject();

Type t = o.GetType();

object result = t.InvokeMember("MyMethod",

  BindingFlags.InvokeMethod, null,

  o, new object[] { });

int i = Convert.ToInt32(result);

With the dynamic keyword, instead of calling a method MyMethod on some object using reflection in this manner, you can now tell the compiler to please treat o as dynamic and delay all analysis until run time. Code that does that looks like this:

dynamic o = GetObject();

int i = o.MyMethod();

It works, and it accomplishes the same thing with code that’s much less convoluted.

The value of this shortened, simplified C# syntax is perhaps more clear if you look at the ScriptObject class that supports operations on a JScript object. The class has an InvokeMember method that has more and different parameters, except in Silverlight, which actually has an Invoke method (notice the difference in the name) with fewer parameters. Neither of these are the same as what you’d need to invoke a method on an IronPython or IronRuby object or on any number of non-C# objects you might come into contact with.

In addition to objects that come from dynamic languages, you’ll find a variety of data models that are inherently dynamic and have different APIs supporting them, such as HTML DOMs, the System.Xml DOM and the XLinq model for XML. COM objects are often dynamic and can benefit from the delay to run time of some compiler analysis.

Essentially, C# 4.0 offers a simplified, consistent view of dynamic operations. To take advantage of it, all you need to do is specify that a given value is dynamic, ensuring that analysis of all operations on the value will be delayed until run time.


By Lingraj Gowda

Microsoft VS 2013 with new features

Microsoft has released ASP.NET and Web Tools for Visual Studio 2013 Preview with a new HTML editor for razor and web project HTML files and browser link, which make use of a SignalR channel between browsers and Visual Studio 2013 preview.

With the help of the browser link feature, you will be able to connect multiple browsers to your development site, including mobile emulators, and click refresh to refresh all the browsers all at the same time.

The new HTML editor provides a single unified HTML5 based schema with some improvements such as automatic brace completion, jQuery UI and AngularJS attribute IntelliSense, attribute IntelliSense Grouping. However, the product team has retained the current editor for Web Form and Windows Store JavaScript application HTML files.

The web tools preview includes a simple UI for the development of Web Forms, MVC and Web API based ASP.NET applications with support for automatic test project creation in addition to an Intranet site template and the ability to choose different configuration options from within the one ASP.NET project wizard. It also make use of Bootstrap to provide responsive design and theming capabilities and OWIN middleware for ASP.NET based authentication.

Microsoft has added support for claims based authentication with which users can be verified using username and password, social services or using organizational accounts. It is also possible to authorize requests from client applications to your Web APIs using OAuth2 2.0 bearer tokens. Moreover, MVC5 projects are now considered as standard web applications and do not make use of their own project GUID and are created upon selecting MVC checkbox in One ASP.NET project dialog.

How To TextBox AutoComplete with ASP.NET and jQuery UI

This article demonstrates how to use the jQuery UI AutoComplete widget to consume an ASP.NET Web Service (EmployeeList.asmx) that is JSON Serialized. The data source for this web service is List<Employee> in the Employee.cs class. You can download this class from the source code attached with this article.

The Autocomplete widget is one of the widgets provided in jQuery UI and provides suggestions while you type into the field. jQuery UI is a free widget and interaction library built on top of the jQuery JavaScript Library, that you can use to build highly interactive web applications.

[Note: If you are using jQuery with ASP.NET Controls, you may find my EBook 51 Recipes with jQuery and ASP.NET Controls helpful]

Let us first glance through the entire code with the jQuery UI AutoComplete widget added to the TextBox (tb)

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>AutoComplete Box with jQuery</title>

<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script> 

<script type="text/javascript">

$(function() {

    $(".tb").autocomplete({

        source: function(request, response) {

            $.ajax({

                url: "EmployeeList.asmx/FetchEmailList",

                data: "{ 'mail': '" + request.term + "' }",

                dataType: "json",

                type: "POST",

                contentType: "application/json; charset=utf-8",

                dataFilter: function(data) { return data; },

                success: function(data) {

                    response($.map(data.d, function(item) {

                        return {

                            value: item.Email

                        }

                    }))

                },

                error: function(XMLHttpRequest, textStatus, errorThrown) {

                    alert(textStatus);

                }

            });

        },

        minLength: 2

    });

});

</script>

</head>

<body>

<form id="form1" runat="server">

<div class="demo">

<div class="ui-widget">

    <label for="tbAuto">Enter Email: </label>

     <asp:TextBox ID="tbAuto" class="tb" runat="server">

     </asp:TextBox>

</div>

</div>

</form>

</body>

</html>

Now before explaining to you how this code functions, let us go through the WebService first. Assuming you have downloaded the source code and are looking at the EmployeeList.cs/vb file, you will observe that the method has been decorated with the [WebMethod] attribute to allow calls from client script

C#

[WebMethod]

public List<Employee> FetchEmailList(string mail)

{

    var emp = new Employee();

    var fetchEmail = emp.GetEmployeeList()

    .Where(m => m.Email.ToLower().StartsWith(mail.ToLower()));

    return fetchEmail.ToList();

}

VB.NET

<WebMethod>

Public Function FetchEmailList(ByVal mail As String) As List(Of Employee)

      Dim emp = New Employee()

      Dim fetchEmail = emp.GetEmployeeList().Where(Function(m) m.Email.ToLower().StartsWith(mail.ToLower()))

      Return fetchEmail.ToList()

End Function

Here the FetchEmailList(string mail) method calls the GetEmployeeList() method on the Employee class which returns a List<Employee>. We then filter the list using the filter string (mail) passed from the UI and then return the list of emails that match the filter string.

Note: If a method is not marked with [ScriptMethod] attribute, the method will be called by using the HTTP POST command and the response will be serialized as JSON.

Going back to code we saw above, observe how the TextBox is wired to the AutoComplete widget.

$(function() {

    $(".tb").autocomplete({

        source: function(request, response) {

            $.ajax({

                // consume the webservice

        },

        minLength: 2

    });

});

To consume this web service using jQuery $.ajax(), two important points to note is that the request should be a POST request and the request’s content-type must be ‘application/json; charset=utf-8’. The code structure of the $.ajax() call looks similar to the following:

$.ajax({

    url: "EmployeeList.asmx/FetchEmailList",

    data: "{ 'mail': '" + request.term + "' }",

    dataType: "json",

    type: "POST",

    contentType: "application/json; charset=utf-8",

    success: function(data) {

    },

    error: function(XMLHttpRequest, textStatus, errorThrown) {

    }

});

Observe how the parameter (that the user types in the textbox) is passed to the webservice using data: "{ 'mail': '" + request.term + "' }" .You may need to add additional checks to format the data or validate it. Once the Ajax method is completed, the success function will be executed and the matching results (Email) will be returned using the following code.

dataFilter: function(data) { return data; },

success: function(data) {

    response($.map(data.d, function(item) {

        return {

            value: item.Email

        }

    }))

},

error: function(XMLHttpRequest, textStatus, errorThrown) {

    alert(textStatus);

}

Now when you browse the page and type the first 2 characters in the textbox, the jQuery UI AutoComplete widget added to the TextBox, provides suggestion that it pulls from the JSON enabled WebService as shown below

The entire source code of this article can be downloaded over here . I hope you liked the article and I thank you for viewing it.

gridview edit update delete in asp.net using c#

Abstract:

Here Lingraj Gowda has explained with an example how do insert, update, edit and delete with confirmation and using ASP.Net AJAX features along with JQuery to ASP.Net GridView control

You might have seen many articles explaining ASP.Net GridView Add (Insert), Edit, Update and Delete functionality, but this is different and how I’ll explain as we progress. My main objective in this article is to keep it simple and cover multiple aspects in one article.

Concept

Basically I have tried to make the normal Add (Insert), Edit, Update and delete functions in ASP.Net GridView simple and also combining the powers of ASP.Net AJAX with that of JQuery to give an elegant and charming user experience.

Below is the connection string from the web.config

<connectionStrings>

      <add name="conString" connectionString="Data Source=.\SQLExpress;

      database=Northwind;Integrated Security=true"/>

connectionStrings>

The GridView

Below is the markup of the ASP.Net GridView control that I’ll be using to demonstrate the various features explained in this article.

<div id = "dvGrid" style ="padding:10px;width:550px">

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<ContentTemplate>

<asp:GridView ID="GridView1" runat="server"  Width = "550px"

AutoGenerateColumns = "false" Font-Names = "Arial"

Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

HeaderStyle-BackColor = "green" AllowPaging ="true"  ShowFooter = "true" 

OnPageIndexChanging = "OnPaging" onrowediting="EditCustomer"

onrowupdating="UpdateCustomer"  onrowcancelingedit="CancelEdit"

PageSize = "10" >

<Columns>

<asp:TemplateField ItemStyle-Width = "30px"  HeaderText = "CustomerID">

    <ItemTemplate>

        <asp:Label ID="lblCustomerID" runat="server"

        Text='<%# Eval("CustomerID")%>'>asp:Label>

    ItemTemplate>

    <FooterTemplate>

        <asp:TextBox ID="txtCustomerID" Width = "40px"

            MaxLength = "5" runat="server">asp:TextBox>

    FooterTemplate>

asp:TemplateField>

<asp:TemplateField ItemStyle-Width = "100px"  HeaderText = "Name">

    <ItemTemplate>

        <asp:Label ID="lblContactName" runat="server"

                Text='<%# Eval("ContactName")%>'>asp:Label>

    ItemTemplate>

    <EditItemTemplate>

        <asp:TextBox ID="txtContactName" runat="server"

            Text='<%# Eval("ContactName")%>'>asp:TextBox>

    EditItemTemplate> 

    <FooterTemplate>

        <asp:TextBox ID="txtContactName" runat="server">asp:TextBox>

    FooterTemplate>

asp:TemplateField>

<asp:TemplateField ItemStyle-Width = "150px"  HeaderText = "Company">

    <ItemTemplate>

        <asp:Label ID="lblCompany" runat="server"

            Text='<%# Eval("CompanyName")%>'>asp:Label>

    ItemTemplate>

    <EditItemTemplate>

        <asp:TextBox ID="txtCompany" runat="server"

            Text='<%# Eval("CompanyName")%>'>asp:TextBox>

    EditItemTemplate> 

    <FooterTemplate>

        <asp:TextBox ID="txtCompany" runat="server">asp:TextBox>

    FooterTemplate>

asp:TemplateField>

<asp:TemplateField>

    <ItemTemplate>

        <asp:LinkButton ID="lnkRemove" runat="server"

            CommandArgument = '<%# Eval("CustomerID")%>'

         OnClientClick = "return confirm('Do you want to delete?')"

        Text = "Delete" OnClick = "DeleteCustomer">asp:LinkButton>

    ItemTemplate>

    <FooterTemplate>

        <asp:Button ID="btnAdd" runat="server" Text="Add"

            OnClick = "AddNewCustomer" />

    FooterTemplate>

asp:TemplateField>

<asp:CommandField  ShowEditButton="True" />

Columns>

<AlternatingRowStyle BackColor="#C2D69B"  />

asp:GridView>

ContentTemplate>

<Triggers>

<asp:AsyncPostBackTrigger ControlID = "GridView1" />

Triggers>

asp:UpdatePanel>

div>

The GridView has 3 data columns

1. Customer ID

2. Contact Name

3. Company Name

I have added a LinkButton in 4^th column which will act as custom column for delete functionality. The reason to use a custom button is to provide the JavaScript confirmation box to the user when he clicks Delete. For Edit and Update I have added a command field which will act as the 5^th column.

There’s also a Footer Row with 3 TextBoxes which will be used to add new records to the database and an Add button which will be used to add the records.

I have enabled pagination and finally wrapped the complete Grid in update panel and the update panel in a div dvGrid and the reason to that I’ll explain later in the article

Binding the GridView

Below is the code to bind the GridView in the page load event of the page

C#

private String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

        BindData();

    }

}

private void BindData()

{

    string strQuery = "select CustomerID,ContactName,CompanyName" +

                       " from customers";

    SqlCommand cmd = new SqlCommand(strQuery);

    GridView1.DataSource = GetData(cmd);

    GridView1.DataBind();

}

VB.Net

Private strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

   If Not IsPostBack Then

         BindData()

   End If

End Sub

Private Sub BindData()

  Dim strQuery As String = "select CustomerID,ContactName,CompanyName" & _

                                " from customers"

  Dim cmd As New SqlCommand(strQuery)

  GridView1.DataSource = GetData(cmd)

  GridView1.DataBind()

End Sub

Below is the screenshot of the GridView being populated using the above code

Adding new record

As discussed above I have placed 3 textboxes and a button in the Footer Row of the ASP.Net GridView control in order to add new record to the database. On the onclick event if the button the records are inserted into the SQL Server Database and the GridView is updated

C#

protected void AddNewCustomer(object sender, EventArgs e)

{

  string CustomerID=((TextBox)GridView1.FooterRow.FindControl("txtCustomerID")).Text;

  string Name = ((TextBox)GridView1.FooterRow.FindControl("txtContactName")).Text;

  string Company = ((TextBox)GridView1.FooterRow.FindControl("txtCompany")).Text;

  SqlConnection con = new SqlConnection(strConnString);

  SqlCommand cmd = new SqlCommand();

  cmd.CommandType = CommandType.Text;

  cmd.CommandText = "insert into customers(CustomerID, ContactName, CompanyName) " +

  "values(@CustomerID, @ContactName, @CompanyName);" +

  "select CustomerID,ContactName,CompanyName from customers";

  cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID;

  cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name;

  cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company;

  GridView1.DataSource = GetData(cmd);

  GridView1.DataBind();

}

VB.Net

Protected Sub AddNewCustomer(ByVal sender As Object, ByVal e As EventArgs)

   Dim CustomerID As String = DirectCast(GridView1.FooterRow _

                .FindControl("txtCustomerID"), TextBox).Text

   Dim Name As String = DirectCast(GridView1 _

                .FooterRow.FindControl("txtContactName"), TextBox).Text

   Dim Company As String = DirectCast(GridView1 _

                .FooterRow.FindControl("txtCompany"), TextBox).Text

   Dim con As New SqlConnection(strConnString)

   Dim cmd As New SqlCommand()

   cmd.CommandType = CommandType.Text

   cmd.CommandText = "insert into customers(CustomerID, ContactName, " & _

        "CompanyName) values(@CustomerID, @ContactName, @CompanyName);" & _

        "select CustomerID,ContactName,CompanyName from customers"

   cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID

   cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name

   cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company

   GridView1.DataSource = GetData(cmd)

   GridView1.DataBind()

End Sub

You will notice I am firing two queries one to insert the data and second to select the updated data and then rebind the GridView. The figure below displays how new records are added.

Edit and Update existing records

As described above I have used command field in order to provide the Edit functionality. Below is the code snippet which is used to edit and update the records

C#

protected void EditCustomer(object sender, GridViewEditEventArgs e)

{

    GridView1.EditIndex = e.NewEditIndex;

    BindData();

}

protected void CancelEdit(object sender, GridViewCancelEditEventArgs e)

{

    GridView1.EditIndex = -1;

    BindData();

}

protected void UpdateCustomer(object sender, GridViewUpdateEventArgs e)

{

    string CustomerID = ((Label)GridView1.Rows[e.RowIndex]

                        .FindControl("lblCustomerID")).Text;

    string Name = ((TextBox)GridView1.Rows[e.RowIndex]

                        .FindControl("txtContactName")).Text;

    string Company = ((TextBox)GridView1.Rows[e.RowIndex]

                        .FindControl("txtCompany")).Text;

    SqlConnection con = new SqlConnection(strConnString);

    SqlCommand cmd = new SqlCommand();

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "update customers set ContactName=@ContactName," +

     "CompanyName=@CompanyName where CustomerID=@CustomerID;" +

     "select CustomerID,ContactName,CompanyName from customers";

    cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID;

    cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name;

    cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company;

    GridView1.EditIndex = -1;

    GridView1.DataSource = GetData(cmd);

    GridView1.DataBind();

}

           

VB.Net

Protected Sub EditCustomer(ByVal sender As Object, ByVal e As GridViewEditEventArgs)

   GridView1.EditIndex = e.NewEditIndex

   BindData()

End Sub

Protected Sub CancelEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)

   GridView1.EditIndex = -1

   BindData()

End Sub

Protected Sub UpdateCustomer(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)

   Dim CustomerID As String = DirectCast(GridView1.Rows(e.RowIndex) _

                                .FindControl("lblCustomerID"), Label).Text

   Dim Name As String = DirectCast(GridView1.Rows(e.RowIndex) _

                                .FindControl("txtContactName"), TextBox).Text

   Dim Company As String = DirectCast(GridView1.Rows(e.RowIndex) _

                                .FindControl("txtCompany"), TextBox).Text

   Dim con As New SqlConnection(strConnString)

   Dim cmd As New SqlCommand()

   cmd.CommandType = CommandType.Text

   cmd.CommandText = "update customers set ContactName=@ContactName," _

   & "CompanyName=@CompanyName where CustomerID=@CustomerID;" _

   & "select CustomerID,ContactName,CompanyName from customers"

   cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID

   cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name

   cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company

   GridView1.EditIndex = -1

   GridView1.DataSource = GetData(cmd)

   GridView1.DataBind()

End Sub

You can view above I am simply getting the data from the textboxes in the Footer Row and then firing an update query along with the select query so that the ASP.Net GridView control is also updated. The figure below displays the Edit and Update functionality.

Deleting existing record with Confirmation

As said above I am using custom delete button instead of ASP.Net GridView delete command field and the main reason for that is to add a confirmation.

      

C#

protected void DeleteCustomer(object sender, EventArgs e)

{

    LinkButton lnkRemove = (LinkButton)sender;

    SqlConnection con = new SqlConnection(strConnString);

    SqlCommand cmd = new SqlCommand();

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "delete from  customers where " +

    "CustomerID=@CustomerID;" +

     "select CustomerID,ContactName,CompanyName from customers";

    cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value

        = lnkRemove.CommandArgument;

    GridView1.DataSource = GetData(cmd);

    GridView1.DataBind();

}

VB.Net

Protected Sub DeleteCustomer(ByVal sender As Object, ByVal e As EventArgs)

   Dim lnkRemove As LinkButton = DirectCast(sender, LinkButton)

   Dim con As New SqlConnection(strConnString)

   Dim cmd As New SqlCommand()

   cmd.CommandType = CommandType.Text

   cmd.CommandText = "delete from customers where " & _

   "CustomerID=@CustomerID;" & _

   "select CustomerID,ContactName,CompanyName from customers"

   cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value _

       = lnkRemove.CommandArgument

   GridView1.DataSource = GetData(cmd)

   GridView1.DataBind()

End Sub

Based on the sender argument I am getting the reference of the LinkButton that is clicked and with the CommandArgument of the LinkButton I am getting the ID of the record to be deleted. After the delete query I am firing a select query and the rebinding the GridView.

Pagination

For pagination I have added the OnPageIndexChanging event on which I am assigning the new page index to the ASP.Net GridView control and then rebinding the data.

      

C#

protected void OnPaging(object sender, GridViewPageEventArgs e)

{

    BindData();

    GridView1.PageIndex = e.NewPageIndex;

    GridView1.DataBind();

}

VB.Net

Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)

   BindData()

   GridView1.PageIndex = e.NewPageIndex

   GridView1.DataBind()

End Sub

ASP.Net AJAX and JQuery

As you have seen in the start I had added an Update Panel and a DIV along with ASP.Net GridView Control.

Basically the Update Panel will give the asynchronous calls thus not reloading the complete page and the JQuery will block the UI until the update panel is refreshed completely. But instead of blocking the complete page I am blocking only the contents of the DIV dvGrid. To achieve this I am using the JQuery BlockUI Plugin

<script type = "text/javascript" src = "scripts/jquery-1.3.2.min.js">script>

<script type = "text/javascript" src = "scripts/jquery.blockUI.js">script>

<script type = "text/javascript">

    function BlockUI(elementID) {

    var prm = Sys.WebForms.PageRequestManager.getInstance();

    prm.add_beginRequest(function() {

    $("#" + elementID).block({ message: '<img src="images/loadingAnim.gif" />'

     ,css: {},

     overlayCSS: {backgroundColor:'#000000',opacity: 0.6, border:'3px solid #63B2EB'

    }

    });

    });

    prm.add_endRequest(function() {

        $("#" + elementID).unblock();

    });

    }

    $(document).ready(function() {

            BlockUI("dvGrid");

            $.blockUI.defaults.css = {};           

    });

script>

That’s all the scripting required and the following is achieved with the above scripts. It will block the Grid until the update panel finishes its work. Refer the figure below

That’s it. With this the article comes to an end, hope you liked it I’ll get back soon with a new one. Download the sample in VB.Net and C# using the link below.