Sybase ASE 15 with ASP.NET 2

ostentationabioticInternet και Εφαρμογές Web

5 Ιουλ 2012 (πριν από 5 χρόνια και 1 μήνα)

434 εμφανίσεις

30 I S U G T E C H N I C A L J O U R N A L
S
ybase continues to make significant
enhancements to its flagship enter-
prise class database Adaptive Server
Enterprise (ASE). The release of version
15 in 2005 along with its subsequent sib-
lings, 15.0.1 and 15.0.2, in later years has
awoken the curiosity of more and more
developers who are now taking ASE for a
spin. Couple this with Sybase's commit-
ment to supporting additional develop-
ment platforms, like .NET and J2EE in
addition to Power Builder - and you can
see why this is an exciting time for us
developers.
What has changed?
Sybase ASE and .NET have now become
so much easier to use than the previous
releases – and Visual Studio 2005 promises
a whole lot of features that Sybase has
taken advantage of in its Data Window
.NET software. In this piece,
I am happy to share with you my
experience of using SYBASE ASE 15
with ASP.NET 2.0 within the Visual
Studio 2005 IDE – and I’m going to do
so without writing a single line of code.
In VS 2005 and ASP.NET 2.0, using
ASE 15 has never been as good as it is
now. In this first segment, I will take you
through building a data-driven website
using Sybase ASE 15’s ADO.NET Data
Provider with ASP.NET 2.0. The goal of
the application is that it must display the
authors from the authors table, it must
also perform sorting and pagination.
We are going to be using the Grid
View Control for this – so here are the
requirements for this tutorial.
Software requirements
1) Windows 2000 and above.
2) A personal copy of SYBASE ASE 15
(Any edition will do, as we are devel-
oping for test purposes only, I will be
using the Developer Edition of
SYBASE ASE 15 for Windows.)
You can download SYBASE ASE 15
from the Sybase website and try it out.
It’s FREE for development purposes
only (not to be deployed in a produc-
tion environment) as specified by the
license agreement. www.sybase.com/
ase_1500devel
3) Microsoft Visual Studio.NET 2005
(Professional or Team System) - you
can order a free Trial DVD from
Microsoft.
4) Install the SYBASE ASE 15 Data
Provider for ADO.NET during the
installation of SYBASE ASE 15.
We shall be using the pubs2 sample
database and authors table. For those
of you who do not have the pubs2
database installed. Follow these steps
to install them on your ASE Server.
a) Open Interactive SQL and from
the File Menu choose Run Script.
b) Navigate to your Sybase ASE
installation folder and drill down
to the scripts folder.
c) Under that, filter by all files and
choose pubs2.
d) Now the database starts getting
created for you – and you can view
it from within Sybase Central.
Now that we have our database and tables
ready, we can visually build applications in
Visual Studio 2005.
Anil Mahadev provides the ISUG
Technical Journal with a
‘QuickStart’ tutorial on web
application development with
Sybase ASE 15 and ASP.NET 2.0
Sybase ASE 15
with ASP.NET 2.0
By Anil Mahadev
Anil Mahadev is an experienced data-
base consultant and writer on database
technologies with several years training
& consulting experience. He is
available at anilm001@gmail.com
M A Y - J U N E 2 0 0 8 31
DESIGN & DEVELOPMENT
Steps to building the application
Launch Visual Studio 2005. Click on File ➠New Website.
Now you can choose the language and select from C# or VB
as shown in figure 1.
Figure 1
We now will be in the source view by default – so switch to
the Design View.
Then you can add the following controls onto the page
and set their properties as indicated in the table below.
Control Name Properties
Label Control Building ASP.NET Apps with
Sybase ASE 15
GridView Control None needed - only
configuration required.
We now have our controls in place so we need to perform one
more step before proceeding. We need to add a reference to
the ASE Data Provider Namespace Assembly.
a) Right click on the Project and choose Add reference.
Now under the tabs, choose under the .NET tab you can
see the Sybase.Data.AseClient or alternatively you can
navigate to the Sybase ASE Installation folder
x:\SYBASEASE15\DataAccess\ADONET\dll
where x is your drive in your own hard drive.
Next choose the Sybase.Data.AseClient.dll. Then click on
OK and your project now hosts the ASE for .NET Assembly
as shown below. The Sybase ASE .NET Client is registered as
a Native Assembly within the Windows operating system.
This is shown in figure 2.
Figure 2
Our application should now look like figure 3.
Figure 3
Configuring our Data Source
We are now going to create a new connection to the ASE
Server. Click on your Grid View Control and under Data
Source choose New Data Source.
S Y B A S E A S E 1 5 W I T H A S P.N E T 2.0
You can download SYBASE ASE 15 from the
Sybase website and try it out. It’s FREE for
development purposes only and not to be
deployed in a production environment as
specified by the license agreement.
32 I S U G T E C H N I C A L J O U R N A L
Under Data Sources➠Data Source Configuration Wizard
choose create a new Connection. Now we will need to build a
new Sybase Data Source. Under the System DSN Tab, click
new and choose Adaptive Server Enterprise as the chosen
database server. Now you will be taken to a dialog where you
will need to specify an existing connection to a Sybase ASE
15 database.
The connection dialog will be as shown in the figure 4.
Figure 4
Next you will need to test the connection. Once you get a
message that says Login Succeeded, it means that the connec-
tion to your server is now ready.
[Note: As a security measure ASE does not allow the user to
enter the password directly. Instead the user is given an option to
enter the password in the next dialog once the user clicks on Test
Connection.]
You will now get a message (as shown in figure 5) for
successful connection.
Figure 5
Reasons for connections not succeeding
1) Check your Server status ➠ Running.
2) Check the port numbers on your machines.
3) Verify the credentials.
The Data Source name will be the connection parameter
that the GridView will take when connecting to your Sybase
ASE Database Server and displaying the contents onto your
WebForm.
The final screen that appears is shown in figure 6.
Figure 6
Configuring the grid view
You will need to choose the connection you have created to
connect to Sybase ASE 15.
Click next and give this ConnectionString a name, say,
pubs2 - and then you have the option to re-use this connection
string for multiple pages. Click ‘next’ as shown in figure 7.
Figure 7
You will be asked to select a table from Sybase ASE 15. Since
only the views are displayed, choose the ‘Specify a custom
SQL statement’ option and manually enter the SQL
Statement by clicking next as shown in figure 8.
Figure 8
We will now get a list of all the author details from the
authors table. The query that we will be using is a simple
select statement. Select * from authors.
S Y B A S E A S E 1 5 W I T H A S P.N E T 2.0
M A Y - J U N E 2 0 0 8 33
Figure 9
Figure 10
At this point you have the option to customize your query by
clicking on the Query Builder button. We are not going to be
doing anything with our query so let’s move along. Click next
and in the last section let’s make sure that we get the desired
results – so click on the Test Query button. This is shown in
figure 10.
Click Finish to complete the configuration.
There can be times when a table consists of a thousand or
more rows; it’s not advisable to load all the rows onto one
page, so therefore Pagination needs to be enabled for our Grid
View control. To enable Pagination, Sorting and Selection
click on the three checkboxes as shown in figure 11.
Our Grid View is now ready. You may want to auto-format
your Grid View and Details View for clearer presentation.
Now press F5 to run the application.
Figure 11
You’ll now see a figure like the one shown below in figure 12.
Figure 12
Problem:After configuring your data source, your password
does not get saved in the web.config file.
Resolution:Add the password as given below in your
web.config file under the connection string setting.
<connectionStrings>
<add name="ConnectionString"
connectionString="DSN=SybaseASE15;UID=your ase
username;PWD=your ase password"
providerName="System.Data.Odbc"/>
</connectionStrings>
After making the appropriate changes, we now have our Grid
View configured and we are ready to run the application.
Our final application will appear as shown in figure 13.
Figure 13
You should be able to view the final application as shown
in the browser with page 1 and page 3 screens below.
To demonstrate the sorting functionality click on the
au_fname link and see how all author’s first names are
alphabetized. This is shown in the figure 14.
Figure 14
Wow, now wasn’t that easy to work with Sybase ASE 15 using
ASP.NET?
There are more incredible things you can do; I’ll let you
know about more options in forthcoming features and tutorials.
Sybase is a great database platform - and you can leverage
your existing skills from SQL Server to Sybase by following
what is a very efficient learning curve.

S Y B A S E A S E 1 5 W I T H A S P.N E T 2.0
DESIGN & DEVELOPMENT