ASP.NET USING C#
(52 Hrs Training. Get Online Study materials @ http://www.industrialtrainingkolkata.com)
• Object Oriented Programming Using C#
 Object oriented features of C#
 Delegates, Events
 Single, Multithreaded Applications

• .NET Framework and DLL Creation
 Forms, controls – Entrance into GUI
 Concept of classes
 Programs with usage of controls
 DLL Creation

• Dialog Boxes, Setup and Deployment Wizard and Reports
 Message boxes, MDI and menu forms
 Printing, Reporting
 Setup Applications

• Database connectivity using ADO.NET with XML
 Why XML?
 Introduction to XML
 Connections using ADO.NET
 Managing XML Data
 Insert / Update / Delete Statements
 Normalization, Transaction, Stored Procedures, Triggers, Cursors

• Web Applications using ASP.NET 3.5/4.0
 Create Web Applications
 Client and Server Controls
 Master and Content Pages
 Publish a Web application
 Themes and Skins

• Web Services
 Create Web Services

• Live Project
 Get to do a live project

Visual Studio .NET makes .NET programming simple and accelerates the development process. It hides a lot of repetitive and configuration details from the user and improves productivity.

In this section we would be basically going through Web Services in ASP.NET.



As has been stated by some experts  – “Today, the principal use of the World Wide Web is for interactive access to documents and applications.
In almost all cases, such access is by human users, typically working through Web browsers, audio players, or other interactive front-end systems. The Web can grow significantly in power and scope if it is extended to support communication between applications, from one program to another. ”

A web service can be defined as a service available through the World Wide Web that makes use of the XML as message system and is not tied to any programming language nor operating systems (OS). For now, we should note that web services are something which do not have any GUI and aredesigned to be used by a  Web based program to deliver a service.

Cross platform programming is one of the most important feature of web services as they are completely independent of the OS or the programming language used to develop it. All that is necessary is that both server and client support the industry standard protocols HTTP, SOAP, and XML.
HTTP is the protocol used by the Web. SOAP (Simple Object Access Protocol) is a lightweight, object-oriented protocol based on XML, which in turn is a cross-platform standard for formatting and organizing information.

Web Services actually provides services to clients over the world wide web. This is possible via some standard internet protocols used by the internet. Therefore, web services can be seen as functions that are called over the Internet by clients in order to be “served”. This kind of function calls are almost similar to Remote Procedure Calls.

The components of Web Services are presented as follows: a Web Service consumer (i.e., a program that uses a particular web service, sometimes called the consuming program) makes a call to the Web Service. To the consumer it seems that it is talking directly to the Web Service over the Internet. But this is just an illusion!

The actual call is being made to a proxy class which is local to the consumer. The proxy handles all the complex infrastructure of sending the request over the Internet to the server machine, as well as getting results back and presenting them to the consumer. This is how the interaction takes place between the web service and the consumer who calls the web service.

A Web Service page in ASP. NET consists of 2 parts -

a) Visual Part

b) Code Behind Part

The visual part is practically meaningless since Web Services have no GUI. the main thing is the code behind part which consists of all the coding details.

This Web Service consists of two methods (HelloWorld and SendMail) to be used by applications. ASP.NET Web Services have the .asmx extension. You may note that the methods that have been exposed as a Web Service has the WebMethod attribute.

ASP.NET makes it possible to map traditional methods to Web Service operations through the System.Web.Services.WebMethod attribute. For example, the following class: mail – exposes the sendEmail method as a Web Service:

using System.Web.Services;

public class mail : System.Web.Services.WebService {

public mail () {

//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public string HelloWorld() {
return “Hello World”;
}

[WebMethod]
public void sendEmail()
{

MailMessage message = new MailMessage(“banthia.gourav@gmail.com”, “banthia.saurav@gmail.com”, “Subject”, “Hello body”);
SmtpClient emailClient = new SmtpClient(“smtp.gmail.com”);
System.Net.NetworkCredential SMTPUserInfo = new System.Net.NetworkCredential(“banthia.gourav@gmail.com”, “vtsestd2009″);
emailClient.EnableSsl = true;
emailClient.UseDefaultCredentials = false;
emailClient.Credentials = SMTPUserInfo;
emailClient.Send(message);

}

}

To invoke this WebMethod, you must provide an .asmx file that refers to the class: mail – as shown here:

<%@ WebService Language=”C#” CodeBehind=”~/App_Code/mail.cs” %>

It’s more common to keep the source code in a separate file. But it’s also possible to include the class definition within the .asmx file itself. Eg:

I/P:- SOAP Request:-

<soap:Envelope
xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”>
<soap:Body>
<sendEmail xmlns=”http://example.org/mail/”>
// no input parameter — no arguments expected
</sendEmail>
</soap:Body>
</soap:Envelope>

O/P:- SOAP Response:-

<soap:Envelope
xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”>
<soap:Body>
<sendEmail xmlns=”http://example.org/mail/”>
// no output parameter — since return is void
</sendEmail>
</soap:Body>
</soap:Envelope>

When a request comes in for mail.asmx, the WebServiceHandler class (.asmx HTTP handler) translates the request into a traditional method call. In a nutshell, the WebMethod infrastructure takes care of dispatching incoming SOAP requests to the appropriate class and method and serializing/deserializing the XML message into the common language runtime (CLR) objects, including the translation of .NET-style exceptions into SOAP Fault elements.

Now , we move on to the Scheduling of the Web Services. We can pretty easily do that through placing of Meta tag in Head tag in HTML which causes the page to get automatically refreshed after every 30 seconds.

<META HTTP-EQUIV=”REFRESH” CONTENT=”5″>

Just placing this in the Head tag would cause the web page to get automatically refreshed and suppose if we place the code to call the web service to send email in the Load event of the web page then after 15 seconds the page would get automatically refreshed causing the web sevice containing the mail sending to get called after every 15 miniutes automatically. in this way, we can schedule a web service – calling a web service after regular intervals – like email sending.

Click here to skip this advertisement

Have you ever experienced a situation where you would need to execute a code against every table of every database present on your system. Well suppose if you come against such a situation then you just need to use some undocumented stored procedures.

The most basic question that may come to your mind would be:

Why its called Undocumented?

The word meaning of Undocumented is Unsupported.

The reason the Undocumented Stored Procedures are called Undocumented is that they can be remove or modified by the MS SQL Team anytime they wish to without prior information to its users. In short, MS SQL Team needs to issue no notification when upgrading or deleting these Stored Procedures. These are generally used by the Management Tools itself and not by the end users i.e. programmers.

These Undocumented Stored Procedures have been developed for the usage of the Internal SQL Developers which they may change any time they need to move on to the next project without any warnings or so.


MS Sql Server provides you with 2 such undocumented stored procedures -

1) sp_MSforeachtable – allows you to process some code against each and every table in a single database.

2) sp_MSforeachdb – allows you to execute a statement against each and every database present in the server.

First things first. So, let us begin with sp_MSforeachtable.

sp_MSforeachtable:-


This is a stored procedure which comes with SQL server by default. Its present in the master database. This is used to process a single command or multiple commands against tables in sinlge database.

Now, we would be proceeding on to discuss that how do we use it. Suppose, I am building a table where we would be having a series of records. Each record would contain two columns – one column would contain the name of the table present in the database and other column would contain the count of the rows in that particular table. What we would do in that case is:

We would want to run a command like, “select ”, count(*) from ” where “” was replaced with every table in your database and insert the results into my temporary table. Since this would be done for all the tables in the database, so we need to use a cursor. See, how we do this:



Code:
———————————————————————————————–

use
go
set nocount on
declare @cnt int
declare @table varchar(128)
declare @cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int) // creation of a temporary table
declare tables cursor for
select table_name from information_schema.tables
where table_type = ‘base table’
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
set @cmd = ‘select ”’ + @table + ”’, count(*) from ‘ + @table
insert into #rowcount exec (@cmd)
fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
select top 5 * from #rowcount
order by tablename
drop table #rowcount

The output of the cursor would be something like:


Here is the output of my CURSOR example when the above code in run on my machine:

tablename rowcnt
————- ———–
authors 23
discounts 3
employee 43
jobs 8
pub_info 8

Now here is another code that produces similar results using the undocumented SP “sp_MSforeachtable”:

use
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
‘insert into #rowcount select ”?”, count(*) from ?’
select top 5 * from #rowcount
order by tablename
drop table #rowcount

Here is the output from the above code when run on my machine:

tablename rowcnt
—————– ———–
[dbo].[authors] 23
[dbo].[discounts 3
[dbo].[employee] 43
[dbo].[jobs] 14
[dbo].[pub_info] 8

Now, let us take another example. Suppose we are going from Kolkata to Delhi. Then, we have a direct route between them through plane or train which is the shortest. But, we may take a route which goes from Kolkata to Pune to Chennai and then to Delhi. Now, you have to decide which route is appropriate for you. Obviously most of us would find the direct route appropriate for us.

In short, the reason behind my giving this example was to explain you that why do so much work when we are getting our results with relatively less amount of work. When we have a procedure -

sp_MSforeachtable – supplied by SQL Server for doing the computations like calculating the number of rows in all the tables in a database, then we can directly use it instead of writing our own stored procedure for evaluating the same result set.

Below is the syntax for calling the sp_MSforeachtable Stored procedure:

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
@command3, @whereand, @precommand, @postcommand
Where:

@RETURN_VALUE – is the return value which will be set by “sp_MSforeachtable”
@command1 – is the first command to be executed by “sp_MSforeachtable” and is defined as a nvarchar(2000)
@replacechar – is a character in the command string that will be replaced with the table name being processed (default replacechar is a “?”)
@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
@whereand – this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
@precommand – is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
@postcommand – is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

As has been explained above, the stored procedure – “sp_MSforeachtable” can be used in a number of ways more than one. Now, we will see that how can we use it in different ways to get different results.

Eg: Return the row counts for only those tables which have their names starting with ‘a’.


use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
@command1 = ‘insert into #rowcount select ”?”,
count(*) from ?’,
@whereand = ‘and name like ”a%”’
select top 5 * from #rowcount
order by tablename
drop table #rowcount

tablename rowcnt
—————– ———–
[dbo].[authors] 23