In my previous post I have shown with a simple example on how you call an ASP.NET web service using JQuery. In this post I will show how you on how can you pass parameters to the web method and get the result using the web service.
If you have read my previous post, you have seen that we have just called a web method which returns a simple string back to us. In this example, instead of returning a plain simple string back to the client, we will ask the user to enter two numbers and the call the web service which in turn add the two numbers and returns the result back to the client.

First start up with the design of the page. As we are going to add two numbers, we will add a label, textboxes and a button as shown in the below screenshot:

Add a web service to your project as I have described in my previous post. Follow the same steps to add a web service to your project. The only change that you have to make is to add a method which accepts two numbers as parameters and return the sum of the two numbers. For your convenience, the method is as follows.

[WebMethod]
public int Add(int num1, int num2)
{
	return num1 + num2;
}

The script which I am using here to call the web service is the same as I have shown in my previous post, but the data parameter in the ajax function needs to be changed. When we are calling a web service which returns a normal string to us we can set the data parameter to blank. But this is not the case when we have to pass parameters to the web service to return the result. The data parameter in the Jquery ajax script goes something like this:

data: "{'num1': " + $("#txt_num1").val() + ", 'num2': " + ("#txt_num2").val() + "}"

In this parameter the ‘num1’ and ‘num2’ are the name of the parameters in the web service which we have to fulfill if we want the result from the web service. The parameter variable name should always remains the same as defined in the web service method. The parameters and their values are separated by a ‘:’( colon) and a ‘,’ (comma) is used to separate the parameters. The Jquery script $(“txt_num1”).val() will set the value to the parameter and then pass it to the web service method to get the result.

The code for Jquery script to call a web service in this example goes like this:

function CallService() {
            $("#lblResult").addClass("loading");
            $("#lblResult").html('');
            $.ajax({
                type: "POST",
                url: "Service.asmx/Add",
                data: "{'num1': " + $("#txt_num1").val() + ", 'num2': " + $("#txt_num2").val() + "}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess,
                error: OnError
            });

            function OnSuccess(data, status) {
                $("#lblResult").removeClass("loading");
                $("#lblResult").html(data.d);
            }

            function OnError(request, status, error) {
                $("#lblResult").removeClass("loading");
                $("#lblResult").html(request.statusText);

            }
        }

I have added a bit of CSS to show ajax loading style animation. I am not covering it here but sure I will in the later Jquery posts. In my next post I will cover on how to fetch data from the SQL Server using a web service.

Download: JqueryAjaxParams.zip (94.76 kb)

Jquery is now one of the most favorite JavaScript frameworks to play around. It offers some more advance feature to the developers and UI designers to accomplish their task easily and more conveniently. Take an example of AJAX and think where you can face the problems when you have to deal with different browsers who support XMLHTTP request and the one who don’t. I remember, I use to initialize the AJAX’s XMLHTTP object keeping in mind what will be my client’s browser. So, on the first go I have to detect the client’s browser and then set the XMLHTTP object and then process requests.
But Jquery gives us a tons of features to be happy and so as with handling data. In Jquery we have not to worry about what will our client’s browser and what will be the request object. Jquery handles all this for us and makes its pretty easy to use.

So start up with creating a new ASP.NET website project.

When you create a new project a new page named Default.aspx is added by default. I am going to use the same page in this example. If you wish you can change the name of the page. Add a button on the page and a label to show text.

Right-Click the project and add choose New Item. Add a new Web Service.

After this add the below code to the web service code behind file. But before you do that there are some things we need to keep in mind. Let’s talk about a normal web service which we are going to consume in a normal way i.e. using the server-side code. But this is not the case I am explaining here. What we are going to do is to consume the web service on the client-side using Jquery.

Now the changes that you have to made in the code-behind file to allow the web service to be consumed by the client-side script is as follows:
First you have to add or un-comment the attribute above of the web service class. This attribute allows the web service to be called from the client-side script (Jquery or other client-side scripts).

[System.Web.Script.Services.ScriptService]

After this you can write your method the same way you use to write with attribute [WebMethod]. I am just writing a simple method which will return a string.

[WebMethod]
Public string Hello()
{
	Return “Welcome to ASP.NET Web Services and Jquery”;
}

We have our web service ready and now we have to write client-side script to consume this webservice. The method in our web service will return only a plain simple string to the client. First I have added a button and a label to the page. On button click I have called the method (Jquery method) which will call the web service. The response of the web service is then shown on the label. On the page from where we are calling the web service, add the following Jquery script to call the web service.

function CallService()
        {
            $.ajax({
                type: "POST",
                url: "Service.asmx/Hello",
                data: "{}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess,
                error: OnError
            });
        }

        function OnSuccess(data, status) 
        {
            $("#lblResult").html(data.d);
        }

        function OnError(request, status, error)
        {
            $("#lblResult").html(request.statusText);
        }

In the above code I have created a method and named it CallService(). Inside this method there are several parameters that we have to set to call the web service.

• type: Can be POST or GET. Web Services do not work with "GET" by default, as to prevent cross-site request forgeries. (Thanks Lee Dumond for pointing this out to me)
• url: Name of the web service. In the above code you can see I have called the web method ‘Hello’ from the web service named ‘Service’. If you are consuming or calling other web service which is not a part of your project or solution then you need to enter the fully qualified name of the web service with method name you are going to call.
• data: In this example the data will remain empty, as we are only calling a method which return a simple string and don’t accept any parameter. If the method has some parameters then we will pass the parameters. I will explain on passing parameters in my coming posts.
• contentType: Should remain the same.
• datatype: Should remain as it is.
• success: Here I have called the OnSuccess when the call is complete successfully. If you check the OnSuccess method you will see that I have set the returned result from the web service to the label. In the OnSuccess method body you see ‘data.d’. The ‘d’ here is the short form of data.
• Error: Same as I have done with OnSuccess. If any error occurred while retrieving the data then the OnError method is invoked.

Run the project and see it in action.

In my coming post on calling web service in ASP.NET using Jquery I will show on how to pass parameters to a web service and get the result and on how can we interact with SQL Server to fetch data and lots of other stuff in Jquery and ASP.NET.

Download: JqueryAjaxDemo.zip (87.55 kb)

Reporting is an important part for every application. Crystal reports are widely used and also available in Visual Studio for reporting purposes. I personally never like designing application interfaces and working on web designs, it needs good designing skills which I lack and so I never put my hands on designing anything, but sometimes we have to. In crystal reports when you create a report using wizard or just adding a blank report to the project and then fetching data using code (after designing), the report works fine on the machine where you have designed and develop it and the problem occurs when you have to deploy or distribute your application with reports on multiple machines or clients. Of course, the connection string or you can say in simple words the server name, database name, user name and password is different than the name you used in your connection string while you test and make your application ready to be distributed. Therefore, to overcome this problem I wrote a class which will help me to achieve this in one line and also keep my code neat and clean. So this simple class will let you set the connection for your report dataset and let your reports work properly without any problems.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;


namespace ReportExportDemo
{
    class Reports
    {
        static TableLogOnInfo crTableLogonInfo;
        static ConnectionInfo crConnectionInfo;
        static Tables crTables;
        static Database crDatabase;

        public static void ReportLogin(ReportDocument crDoc, string Server, string Database, string UserID, string Password)
        {
            crConnectionInfo = new ConnectionInfo();
            crConnectionInfo.ServerName = Server;
            crConnectionInfo.DatabaseName = Database;
            crConnectionInfo.UserID = UserID;
            crConnectionInfo.Password = Password;
            crDatabase = crDoc.Database;
            crTables = crDatabase.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
            {
                crTableLogonInfo = crTable.LogOnInfo;
                crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                crTable.ApplyLogOnInfo(crTableLogonInfo);
            }
        }

        public static void ReportLogin(ReportDocument crDoc, string Server, string Database)
        {
            crConnectionInfo = new ConnectionInfo();
            crConnectionInfo.ServerName = Server;
            crConnectionInfo.DatabaseName = Database;
            crConnectionInfo.IntegratedSecurity = true;
            crDatabase = crDoc.Database;
            crTables = crDatabase.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
            {
                crTableLogonInfo = crTable.LogOnInfo;
                crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                crTable.ApplyLogOnInfo(crTableLogonInfo);
            }
        }
    }
}

This class contains ReportLogIn method which is overloaded which lets you choose the type of datasourde you want to connect to. The first method in the class requires SQL Server authentication to log-on to the server and the second method is used when the server is configured on windows authentication, similar to Integrated Security = true.

Sample usage of the Reports class is as follows. I have used AdventureWorks database for this example. If you dont have adventure works database then you can download the sample database from Microsoft's website or from codeplex or you can create your own report and check it with this example.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;


namespace ReportExportDemo
{
    public partial class frm_main : Form
    {
        public frm_main()
        {
            InitializeComponent();
        }

        private void btn_prvrpt_Click(object sender, EventArgs e)
        {
            int Cust_Id = Convert.ToInt32(txt_customerid.Text);
            //string Cust_Id = txt_customerid.Text.Trim();

            //Initialize report document object and load the report in the report document object
            ReportDocument crReportDocument = new ReportDocument();
            crReportDocument.Load(Application.StartupPath+"\\Reports\\AdventureCustReport.rpt");
            
            //login to the server to get details from the server and populate to the report           
            Reports.ReportLogin(crReportDocument, "MX\\SERVER", "AdventureWorks", "sa", "pass#w0rd1");
            
            //Pass parameter to the report object
            crReportDocument.SetParameterValue("id", Cust_Id);

            //To create PDF from the crystal report
            crReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, "CustomerReport" + Cust_Id + ".pdf");
            
            //Preview the generated PDF
            System.Diagnostics.Process.Start(Application.StartupPath + "\\CustomerReport" + Cust_Id + ".pdf");
        }
    }
}

Note the namespaces I have used in the above code and also how I have used the Reports class ReportLogin method. For more clarification of the code download the sample application with the report below.

One important thing that you have to keep in mind while designing report is to set the parameter. After you have set the parameter and you use your report with the above code, the report will then populate the whole lot of records in the table trrespective of the parameter you pass to the report. So to avoid this and to view only the record of your choice you needs to set the formula in the crystal report. the formula goes like this:

Sample Syntax:

{<Table's Column Name>}={?<Parameter Name>}

In my report this formula looks something like this:

{CustomerID}={?ID}

Download: ReportExportDemo.zip (55.76 kb)

With the launch of Windows Vista, Microsoft introduces brand new UI, eye-catching aero effects (glass effects) for their new Operating System. I have seen a lot of programmers who are much concerned about the looks of their applications. So this time with the launch of Windows 7, Microsoft provides the Windows 7 API Code Pack. The Windows 7 API Code Pack not only just allows incorporating the aero glass effect, but many other features which will let you to work more conveniently with Windows 7. I will cover more of the features of Windows 7 API in my later posts. So, I am starting with a first tutorial on how to enable aero effects for the applications running on Windows 7. Create a new windows form application in Visual Studio. Design the form as normally you do as per your requirements. Now to give your form aero glass effect, we first need to add reference for the Windows API. Windows API provides a class called “GlassForm” which we are going to use in this example. When you add a new form to your project you see the following line in your code window:

public partial class AeroForm : Form

When you execute the code you will see a normal windows form which is obviously not we wanted. Here the “GlassForm” class comes into play. As we know we have all are forms are partial class, we have to inherit it with Form class which in turn completes our form. Now instead of using Form class use the “GlassForm” and you will see the Aero Glass Effects on your form.

public partial class AeroForm : GlassForm

This is how you normal Windows form looks like:

And your Aero form look like this:

Download: WindowsAero.zip (790.00 kb)

Performance of SQL server matters while working with large enterprise applications and where performance really bothers the business workflow. When NOCOUNT is ON, the count of rows by the execution of the query (T-SQL statements) is not returned. You must have noticed that when you perform operations by executing INSERT, UPDATE, DELETE or SELECT statements, the server returns the number of rows. The count of rows is necessary while debugging your queries, when you are done with debugging you can turn NOCOUNT ON. To view the number of rows affected use the command SET NOCOUNT OFF. You can try writing a normal select query with NOCOUNT as OFF, when NOCOUNT is OFF you can see the number of rows affected, but when you set NOCOUNT ON, you will not see any message related to the count of rows which are affected when you execute select statement or T-SQL statement.

In MDI application we can have multiple forms and can work with multiple forms i.e. MDI childs at a time but while developing applications we don't pay attention to the minute details of memory management. Take this as an example, when we develop application say preferably an MDI application, we have multiple child forms inside one parent form. On MDI parent form we would like to have menu strip and tab strip which in turn calls other forms which build the other parts of the application. This also makes our application looks pretty and eye-catching (not much actually). Now on a first go when a user clicks a menu item or a button on a tab strip an application initialize a new instance of a form and shows it to the user inside the MDI parent, if a user again clicks the same button the application creates another new instance for the form and presents it to the user, this will result in the un-necessary usage of the memory. Therefore, if you wish to have your application to prevent generating new instances of the forms then use the below method which will first check if the the form is visible among the list of all the child forms and then compare their types, if the form types matches with the form we are trying to initialize then the form will get activated or we can say it will be bring to front else it will be initialize and set visible to the user in the MDI parent window. The method we are using:

private bool CheckForDuplicateForm(Form newForm)
{
     bool bValue = false;
     foreach (Form frm in this.MdiChildren)
     {
         if (frm.GetType() == newForm.GetType())
         {
             frm.Activate();
             bValue = true;
         }
      }
      return bValue;
}

Usage:

First we need to initialize the form using the NEW keyword

ReportForm ReportForm = new ReportForm();

We can now check if there is another form present in the MDI parent. Here, we will use the above method to check the presence of the form and set the result in a bool variable as our function return bool value.

bool frmPresent = CheckForDuplicateForm(Reportfrm);

Once the above check is done then depending on the value received from the method we can set our form.

if (frmPresent)
	return;
else if (!frmPresent)
{
    Reportfrm.MdiParent = this;
    Reportfrm.Show();
}

In the end this is the code you will have at you menu item or tab strip click:

ReportForm Reportfrm = new ReportForm();
bool frmPresent = CheckForDuplicateForm(Reportfrm);
if (frmPresent)
return;
else if (!frmPresent)
{
    Reportfrm.MdiParent = this;
    Reportfrm.Show();
}

Microsoft SQL Server provides the mechanism to save XML data to the SQL table using OpenXML. We can use a stored procedure and pass a XML text as a string. It is useful when we have a large data in XML format and we want to save the whole data in different tables.

So first start declaring the variables. The first declare the document variable and a XML document variable as Varchar (8000) or Varchar(MAX) - It's a same thing

DECLARE @idoc int
DECLARE @doc varchar(8000)

Now we can set the value to the @doc variable, the @doc variable will accept the XML file as a parameter. We can also have a complete XML data in the parameter only if the XML data is less.

OpenXML takes 3 parameters:

• The handler which we have declare at the begining will holds the XML document in the memory.
• XPath variable to access the various elements of the XML document.
• The last parameter here '2' allows us to access XPath as elements.

Here I have read a XML file in the document variable and store in the table. Here is the full SQL query to achieve this. You can also convert the below code to stored procedure and pass XML file or string as a parameter

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<ROOT><student>
<id>1</id>
<name>Prashant</name>
<age>32</age>
</student>
<student>
<id>2</id>
<name>Swami</name>
<age>42</age>
</student>
<student>
<id>3</id>
<name>Ash</name>
<age>23</age>
</student>
<student>
<id>4</id>
<name>Kris</name>
<age>12</age>
</student>
<student>
<id>5</id>
<name>Derek</name>
<age>75</age>
</student>
</ROOT>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
Insert into Students SELECT id, name, age from 
OPENXML (@idoc, '/ROOT/student',2)
WITH (id  int, name varchar(50), age int)

GUESS WHO IS BIG