Disadvantages of the SqlDataSource in ASP

Disadvantages of the SqlDataSource
As you have seen, when you use the SqlDataSource control, you can often avoid writing any data access
code. However, you also sacrifice a fair bit of flexibility. Here are the most significant disadvantages:
❑ Data access logic embedded in the page:To create a SqlDataSource control, you need to
hard-code the SQL statements in your web page. This means that you cannot fine-tune your
query without modifying your web page. In an enterprise application, this limitation is not
acceptable, as it is common to revise the queries after the application is deployed in response to
profiling, indexes, and expected loads. You can improve this situation a fair bit by restricting
your use of the SqlDataSource to stored procedures. However, in a large-scale web applica-
tion, the data access code will be maintained, tested, and refined separately from the business
logic (and it may even be coded by different developers). SqlDataSource just does not give
you that level of flexibility.
❑ Maintenance in large applications: Every page that accesses the database needs its own set of
SqlDataSource controls. This can turn into a maintenance nightmare, particularly if you have
several pages using the same query (each of which requires a duplicate instance of the
SqlDataSource ). In a component-based application, you will use a higher-level model. The
web pages will communicate with a data access library, which will contain all the database
details.
❑ Inapplicability to other data tasks: The SqlDataSource does not properly represent some
types of tasks. The SqlDataSource is intended for data display and data-editing scenarios.
However, this model breaks down if you need to connect to the database and perform another
task, such as placing a shipment request into an order pipeline or logging an event. In these sit-
uations, you will need custom database code. It will simplify your application if you have a sin-
gle database library that encapsulates these tasks along with data retrieval and updating
operations.
Although the SqlDataSource control is powerful, there are a number of other data source controls that
might better suit your specific data access scenario. One such control is XmlDataSource control, which
is the topic of focus in the next section.

Data Source Controls in ASP

Data Source Controls
In ASP.NET 1.x, you typically performed a data-binding operation by writing some data access code to
retrieve a DataReaderor a DataSetobject, then you bound that data object to a server control, such as
a DataGrid , DropDownList, or ListBox. If you wanted to update or delete the bound data, you were
then responsible for writing the data access code to do that.
ASP.NET 2.0 introduces an additional layer of abstraction through the use of data source controls. The
data source controls abstract the use of an underlying data provider, such as the SQL data provider or
the OLE DB data provider. This means that you no longer need to concern yourself with the underlying
complexities of using the data providers. Instead, the data source controls do all the heavy lifting for
you. All you need to know is the location of your data and, if necessary, how to construct a query for
performing CRUD (create, retrieve, update, and delete) operations.

SqlDataSource Control
The SqlDataSource control is the data source control to use if your data is stored in a SQL Server,
Oracle Server, ODBC data source, OLE DB data source, or Windows SQL CE Database. The
SqlDataSource represents a database connection that uses an ADO.NET provider. However, this has a
catch. The SqlDataSource needs a generic way to create the Connection, Command, and DataReader
objects it requires. The only way this is possible is if your data provider includes a data provider factory,
which has the responsibility of creating the provider-specific objects that the SqlDataSource needs in
order to access the data source.
As you know, .NET ships with these four provider factories:
❑ System.Data.SqlClient
❑ System.Data.OracleClient
❑ System.Data.OleDb
❑ System.Data.Odbc
These are registered in the machine.config file, and as a result you can use any of them with the
SqlDataSource . You specify the provider name as part of the SqlDataSource control declaration. Here
is a SqlDataSource that connects to a SQL Server database:

The next step is to supply the required connection string — without it, you cannot make any connections:


Once you have specified the provider name and connection string, the next step is to add the query logic
that the SqlDataSource will use when it connects to the database. The complete declaration of the data
source control is:

Now that you have the SqlDataSource control with all the attributes, the next step is to add a data-
bound control that uses the data from the data source control. In this case, a DropDownListcontrol is
used as the data-bound control:


Connection Strings from the Configuration File
In ASP.NET 2.0, Microsoft has tried to address the common shortcomings in the Web.config file with
ASP.NET 1.x. One such shortcoming is that there is not a well-defined location for storing connection
strings. Because database connection information is so frequently stored in the Web.configfile, you
now have an entirely new configuration section in that file, , specifically for stor-
ing the connection string information.
Although you can hard-code the connection string directly in the SqlDataSource tag (as shown in the
previous section), you should always place it in the section of the Web.config
file to guarantee greater flexibility and ensure that you won’t inadvertently change the connection
string, which minimizes the effectiveness of connection pooling. For example, add the below connection
string in the Web.configfile:

...

With the above connection string placed in the Web.configfile, you would reference it in the
SqlDataSource using the $ expression, as shown below:

DataSets in .NET

XQuery is an excellent example of how XML is baked into the experience of manipulating data on the

.NET Framework. The System.Data namespace and System.Xmlnamespace have started mingling

their functionality for some time. DataSets are another example of how relational data and XML data

meet in a hybrid class library. During the COM and XML heyday, the ADO 2.5 recordset sported the

capability to persist as XML. The dramatic inclusion of XML functionality in a class library focused

entirely on manipulation of relational data was a boon for developer productivity. XML could be pulled

out of SQL Server and manipulated.

Persisting DataSets to XML

Classes within System.Data use XmlWriter and XmlReader in a number of places. Now that you’re

more familiar with System.Xmlconcepts, be sure to take note of the method overloads provided by the

classes within System.Data . For example, the DataSet.WriteXml method has four overloads, one of

which takes in XmlWriter. Most of the methods with System.Data are very pluggable with the classes

from System.Xml. Listing 13-14 shows another way to retrieve the XML from relational data by loading

a DataSet from a SQL command and writing it directly to the browser with the Response object’s

TextWriterproperty using DataSet.WriteXml .

DataSets have a fairly fixed format, as seen in this example. The root node of the document isCustomers,

which corresponds to the DataSetName property. DataSets contain one or more named DataTableobjects,

and the names of these DataTablesdefine the wrapper element — in this case,Customer . The name of the

DataTableis passed into the loadmethod of the DataSet. The correlation between the DataSet’s name,

DataTable’s name, and the resulting XML is not obvious when using DataSets. The resulting XML is shown

in the browser in Figure 13-4.

DataSets present a data model that is very different from the XMLway of thinking about data. Much of the

XML-style of thinking revolves around the InfoSet or the DOM, whereas DataSets are row- and column-based. The XmlDataDocumentis an attempt to present these two ways of thinking into one relatively unified

model.

XmlDataDocument

Although DataSets have their own relatively inflexible format for using XML, the XmlDocument class

does not. In order to bridge this gap, an unusual hybrid object, the XmlDataDocument, is introduced.

This object maintains the full fidelity of all the XML structure and allows you to access XML via the

XmlDocument API without losing the flexibility of a relational API. An XmlDataDocumentcontains a

480

Chapter 13

DataSet of its own and can be called DataSet-aware. Its internal DataSet offers a relational view of the

XML data. Any data contained within the XML data document that does not map into the relational

view is not lost, but becomes available to the DataSet’s APIs

The XMLDataDocumentis a constructor that takes a DataSet as a parameter. Any changes made to the

XmlDataDocumentare reflected in the DataSet and vice versa.

Now take the DataSet loaded in Listing 13-14 and manipulate the data with the XmlDataDocument and

DOM APIs you’re familiar with. Next, jump back into the world of System.Data and see that the DataSets

underlying DataRows have been updated with the new data, as shown in Listing 13-15

Listing 13-15 extends Listing 13-14 by first commenting out changing the HTTP ContentType and the call

to DataSet.WriteXml. After the DataSet is loaded from the database, it is passed to the XmlDataDocument

constructor. At this point, the XmlDataDocument and the DataSet refer to the same set of information. The

EnforceConstraints property of the DataSet is set to false to allow changes to the DataSet. When

EnforceConstraints is later set to true, if any constraint rules were broken, an exception is thrown. An

XPath expression is passed to the DOM method SelectSingleNode, selecting the ContactTitlenode of

a particular customer, and its text is changed to Boss. Then by calling GetRowFromElement on the

XmlDataDocument , the context jumps from the world of the XmlDocument back to the world of the DataSet.

Column names are passed into the indexing property of the returned DataRow, and the output is shown in

this line:

Ana Trujillo is the Boss

The data is loaded from the SQL server and then manipulated and edited with XmlDocument -style

methods; a string is then built using a DataRowfrom the underlying DataSet.

XML is clearly more than just angle brackets. XML data can come from files, from databases, from infor-

mation sets like the DataSet object, and certainly from the Web. Today, however, a considerable amount

of data is stored in XML format, so a specific data source control has been added to ASP.NET 2.0 just for

retrieving and working with XML data.

Running Codes

VB

Dim connStr As String = “database=Northwind;Data Source=localhost; “ _

& “User id=sa;pwd=wrox”

Using conn As New SqlConnection(connStr)

Dim command As New SqlCommand(“select * from customers”, conn)

conn.Open()

Dim ds As New DataSet()

ds.DataSetName = “Customers”

ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, “Customer”)

Response.ContentType = “text/xml”

ds.WriteXml(Response.OutputStream)

End Using

C#

string connStr = “database=Northwind;Data Source=localhost;User id=sa;pwd=wrox”;

using (SqlConnection conn = new SqlConnection(connStr))

{

SqlCommand command = new SqlCommand(“select * from customers”, conn);

conn.Open();

DataSet ds = new DataSet();

ds.DataSetName = “Customers”;

ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, “Customer”);

Response.ContentType = “text/xml”;

ds.WriteXml(Response.OutputStream);

}

VB

Dim connStr As String = “database=Northwind;Data Source=localhost; “ _

& “User id=sa;pwd=wrox”

Using conn As New SqlConnection(connStr)

Dim command As New SqlCommand(“select * from customers”, conn)

conn.Open()

Dim ds As New DataSet()

ds.DataSetName = “Customers”

ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, “Customer”)

‘Response.ContentType = “text/xml”

‘ds.WriteXml(Response.OutputStream)

‘Added in Listing 13-15

Dim doc As New XmlDataDocument(ds)

doc.DataSet.EnforceConstraints = False

Dim node As XmlNode = _

doc.SelectSingleNode(“//Customer[CustomerID = ‘ANATR’]/ContactTitle”)

node.InnerText = “Boss”

doc.DataSet.EnforceConstraints = True

Dim dr As DataRow = doc.GetRowFromElement(CType(node.ParentNode, XmlElement))

Response.Write(dr(“ContactName”).ToString() & “ is the “)

Response.Write(dr(“ContactTitle”).ToString())

End Using

C#

string connStr = “database=Northwind;Data Source=localhost; “

+ “User id=sa;pwd=wrox”;

using (SqlConnection conn = new SqlConnection(connStr))

{

SqlCommand command = new SqlCommand(“select * from customers”, conn);

conn.Open();

DataSet ds = new DataSet();

ds.DataSetName = “Customers”;

ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges,”Customer”);

//Response.ContentType = “text/xml”;

//ds.WriteXml(Response.OutputStream);

//Added in Listing 13-15

481

Working with XML

XmlDataDocument doc = new XmlDataDocument(ds);

doc.DataSet.EnforceConstraints = false;

XmlNode node = doc.SelectSingleNode(@”//Customer[CustomerID

= ‘ANATR’]/ContactTitle”);

node.InnerText = “Boss”;

doc.DataSet.EnforceConstraints = true;

DataRow dr = doc.GetRowFromElement((XmlElement)node.ParentNode);

Response.Write(dr[“ContactName”].ToString() + “ is the “);

Response.Write(dr[“ContactTitle”].ToString());

}

Procedure Example in Oracle

Procedure:
Also KNown As “Stored Procedures”
· Named block
· Can process several variables
· Returns no values
· Interacts with application program using
IN, OUT, or INOUT parameters are Valid parameters include all data types such as Integer,Char,Varchar2,date etc.
However, for char, varchar2, and number no length and scale, respectively, can be specified.
For example, the parameter number (6) results in a compile error and must be replaced by number

Procedure Basic Syntax

CREATE [OR REPLACE] PROCEDURE name
[( argument [IN|OUT|IN OUT] datatype
[{, argument [IN|OUT|IN OUT] datatype }] )]
AS
/* declaration section */
BEGIN
/* executable section - required*/
EXCEPTION
/* error handling statements */
END;
/

Parameter direction: The optional clauses IN, OUT, and IN OUT specify the way in which the parameter is used. The default mode is IN
» IN (default) : means that the parameter can be referenced inside the procedure body, but it cannot be changed
» OUT: means that a value can be assigned to the parameter in the body, but the parameter’s value cannot be referenced.
The OUT qualifier signifies that the procedure passes a value back to the caller through this argument
» IN OUT: allows both assigning values to the parameter and referencing the parameter.

Executing procedures
» EXECUTE ()
» EXECUTE ;

A procedure can be deleted using the command:

drop procedure


Procedure Example

CREATE OR REPLACE PROCEDURE
myProcedure(bookname in varchar2,quantity integer,price number) AS
BEGIN
INSERT INTO books Values(bookname,quantity,price);
END;
/

To Execute This Procedure Using this Command:
exec myProcedure('Teach Yourself C++',100,125);

Triggers in Oracle

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or
view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example,
when a new record (representing a new Employee) is added to the Employee_Info table, new records should be created also in the
tables of the taxes, vacations, and salaries.

Before a trigger can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and
location of this script depend on your operating system.
1.To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have
the CREATE TRIGGER system privilege.
2.To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must
have the CREATE ANY TRIGGER system privilege.
3.In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE
TRIGGER system privilege.

If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.
Examaple :
Create A Table On books where bookid,authorname,price,quantity,edition etc are stored .There are also another table where
sale information are stored.

CREATE TABLE books(
bookname varchar2(20),
authorname varchar2(30),
quantity integer,
price number(10,2),
edition varchar2()
);

CREATE TABLE sales(
saleid integer,
bookname varchar2(20) FOREIGN KEY,
price number(10,2),
quantity integer
);

when a record insert into sales table then quantity of corresponding book in books table must be updated. we will do it
using trigger . This trigger run when a record insert into sales table.

CREATE OR REPLACE TRIGGER Book_TRIGGER
AFTER INSERT ON sales
FOR EACH ROW
DECLARE

BEGIN
UPDATE books SET quantity=quantity-:new.quantity where bookname=:new.bookname;
END;
/

if books table have a book named teach yourself C++ which quantity is 20. IF A teach yourself C++ book sells then teach yourself C++ book will
book's quantity will be 19 .we will do it using above Book_TRIGGER trigger.

Oracle Job Scheduler

Oracle Job Schedular

The DBMS_JOB package is actually an API into an Oracle subsystem known as the job queue . The Oracle job queue allows for
the scheduling and execution of PL/SQL routines (jobs) at predefined times and/or repeated job execution at regular intervals.
The DBMS_JOB package provides programs for submitting and executing jobs, changing job execution parameters, and removing or
temporarily suspending job execution. This package is the only interface with the Oracle job queue.

DBMS_JOB is used to schedule many different types of tasks that can be performed in PL/SQL and that require regular execution.
The job queue is used extensively by Oracle replication facilities, and was originally developed for the purpose of refreshing
Oracle snapshots. DBMS_JOB is often used by DBAs to schedule regular maintenance activities on databases, typically during
periods of low usage by end users. It can similarly be used by applications to schedule large batch operations during off
hours. The job queue can also be used to start up service programs that listen on database pipes and respond to service requests
by user sessions.

Example:

CREATE TABLE employee_info(
emp_name varchar2(20),
emp_dob date
);

CREATE TABLE message(
emp_name varchar2(20),
msg varchar2(50)
);

CREATE OR REPLACE PROCEDURE send_msg
CURSOR emp_cur IS select emp_name from employee_info ;
var date;
BEGIN
for variable in emp_cur
LOOP
select emp_dob into var from employee_info where empo_name=variable.name;
IF TO_CHAR(var,'dd-mm-yyyy')=TO_CHAR(emp_dob,'dd-mm-yyyy') THEN
INSERT INTO message VALUES(variable.emp_name,'Happy Birthday');
END IF;
END LOOP;

\\here job schedular example which call send_msg procedure to check birthday

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_full_job_definition',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN send_msg; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/

Internet Information Server in .NET

Internet Information Server (IIS) is the application server for the Windows platform. This

application server has been evolving for the past 10 years, and will continue to evolve into t

foreseeable future. IIS has gone from serving static markup to a full-featured application ho

ing environment. ISAPI applications have been available as a point of extensibility for years

A number of the technologies you’ll be looking at in this chapter are implemented as ISAPI

applications: ASP.NET, SQLXML, and SOAP exposure of Enterprise Services. Others, such as

the COM SOAP stack, are beyond the scope for this book.

■Note As IIS continues to evolve, you can expect to see ASP.NET leveraged for more and more functiona

ity, as this is the ISAPI extension of choice for extending the behavior of IIS using the .NET Framework. We

provide full coverage of ASP.NET as an application pipeline in Chapter 2.

ASP.NET Framework

With the ASP.NET Framework, Microsoft has created an ISAPI application that enables the

functionality of IIS to be extended using the .NET Framework. We examined a number of wa

this is done within the Framework Class Library in Chapter 2. In this section, we’ll focus on

some of these implementations, and how they enable you to leverage IIS as a network end-

point for cross boundary and cross machine communication.Web Services

In addition to serving requests for Web Forms, the ASP.NET Framework also acts as the

.NET SOAP Stack. (We examined some details of the generalized concept of a SOAP Stack in

Chapter 6.) A SOAP Stack is a process that listens to a well-defined network endpoint for

incoming SOAP messages posted to the server. It maps these requests to a service implemen-tation, and translates the results of the service into a SOAP response.

In the .NET Framework, Web Services are so well integrated into IIS and ASP.NET that

most people don’t even realize they’re using a SOAP Stack at all if they learn Web Services

using the .NET Framework. For any other platform, you’d have to go out and pick a SOAP

Stack, install it, and take specific steps to map types to service operations you want to expose.

This is true for Java platforms, and it is even true for exposing COM types as Web Services.

Visual Studio .NET and ASP.NET make this so easy that it can be taken for granted by most

developers. By mapping asmxfiles to types, by automatically handling requests for asmxdocu-ments in ASP.NET, by auto-generating WSDL documents, and by auto-generating client-side

proxies, the Web Service handler built into ASP.NET hides all of the standards-based details of

the underlying protocols and wire format in use.

This can be a good thing or a bad thing. A service-oriented purist would shudder at the

thought of it, and would advocate a “WSDL first” approach to service development. The other

extreme would be to acknowledge the simple fact that if you need a method on a type exposed

across your network, you can slap the WebMethod attribute on it, put an asmxdocument in front

of it, and you’re done (as long as chunky statelessness is a given for the method design).

CHAPTER 8 ■ HOSTING AND COMMUNICATIONS 275

Exposing your .NET types as Web Services vastly increases the reach of your managed

code. If you’re in an environment where there are several platforms and languages in use, Web

Services dramatically decreases the amount of time and churn spent integrating packages and

applications. By hosting your Web Services within IIS, you can also give them exactly the reach

you want them to have. You may have services within a department, services exposed to the

entire enterprise, and services exposed over the Internet to partners and vendors. You can

even publish public services for general consumption. These can be subscription based or free

(see www.xmethods.net). The broader the reach of your Web Services, the greater the chances

you’ll want to adopt some of the WS-* specifications for functionality such as authentication,

message routing, and transactions. You can do this with the Web Service Enhancements add-on available for free and supported by Microsoft (see Chapter 6 or http://msdn.microsoft.com/

webservices/webservices/building/wse/default.aspx).

Remoting

ASP.NET also acts as a host for remoted components. The Remoting handler is automatically

mapped to requests of files with extensions of .soap or .remvia a configuration document that

gets added to the root of your web application, requests of specific network endpoints are

mapped to types living in assemblies in the application’s bin directory.

Discussions about Remoting internals are beyond the scope of this book (see Tom Barnaby’s

book, Distributed .NET Programming in C# (Apress, 2002) for excellent coverage). However, in

this chapter, we’ll still take a look at a couple of ways .NET types can be exposed via Remoting.

Here’s a simple type that by inheriting from MarshalByRefObjectis pinned in the process it’s

created within; and so it can be called via Remoting.

This component then can be exposed via ASP.NET using the following entry in the

web.configof an IIS application:

The client requires a configuration file to use the remoted type. Here’s a configuration file

you can use as the app.configfor a simple console application:

Running Codes

class BookService : MarshalByRefObject

{

public DataTable getBookList()

{

SqlConnection cn = new SqlConnection(connStr);

SqlCommand cm = new SqlCommand(

"select BookID, Title From Book Order by Title", cn);

DataTable dt = new DataTable();

try

{

cn.Open();

dt.Load(cm.ExecuteReader());

return dt;

}

catch { }

finally

{

if (cn.State == ConnectionState.Open) cn.Close();

}

}

}

type="BookLib.BookService, BookLib"

objectUri="BookService.soap" />

url="http://localhost:13101/BookHost/BookService.soap" />