Learning Center
Plans & pricing Sign in
Sign Out

Using Relational Databases and Custom Queries With .NET CASE Tools


• - Creating A RAD Web Application • - Customizing Web Applications Quickly • - CASE RAD Tools For ASP.Net • - Using Relational Databases and Custom Queries .NET CASE Tools

More Info
  • pg 1
									            Using Relational Databases and
    Custom Queries With .NET CASE Tools
    One great feature of Iron Speed Designer CASE tool is the way it can connect to
    large database systems like Oracle or SQL Server. In this article, I show you
    how to connect to such a database, and then perform a custom query on the
                                  RAD Tutorials for .Net Topics

          - Creating A RAD Web Application
          - Customizing Web Applications Quickly
          - CASE RAD Tools For ASP.Net
          - Using Relational Databases and Custom Queries .NET CASE Tools

    Creating Your CASE Application
    To begin with, you create the application just as you normally would. If you
    need more instructions or this is the first time you're using a CASE tool in .Net,
    use the tutorials: Creating A RAD Web Application With Iron Speed
    Designer and Customizing Your RAD Web Application
    Click the New App button, and begin putting together your application. But
    when you get to the Database Server part of the tab, under Your Database(s),
    click Microsoft SQL Server. (You can also use MySQL or Oracle, which will
    work similarly. But I'll be using SQL Server for this example.) Your screen
    should look like this:
Next, fill in the Server information. Depending on how you have SQL Server
installed, the Find button may or may not work. (It depends on if SQL Server
Browser is running, and if you're using the default instance names or not.) If
the Find doesn't find your default installation, you can type the fully-qualified
name into the Server box (such as Fred\SqlServer, where Fred is the name of
the computer, and SqlServer is the instance name).
Then choose either Windows authentication or SQL Server authentication. But
remember, if you use Windows authentication, and you move this application
to another computer, then you'll have to configure that computer's login to be
able to connect. Otherwise, use the standard SQL Server authentication. Then
click Test Connection. If the connection works, you should see a message
saying it was verified. Otherwise, after a little while you'll get a message that it
couldn't connect. In that case, you'll have to double-check the name of the
server and fix the name.
Once you can connect, click Next. Now you can choose the database and
table you wish to work with. Although we'll create a custom query shortly,
initially we'll go ahead and grab a couple tables here just so you've seen it
done. For this example I'm using the official SQL Server sample database
Northwind, and I'm adding the tables Categories, Employees, and Products.
I'm choosing the defaults for the Pages to Create section, as shown here:

Because these three tables already have primary keys, and Iron Speed is able
to locate them, the tables don't appear in the next screen regarding Virtual
Primary Keys. Click Next. In the next step you choose the language. In the
final step is the application settings. I'm calling this app SQLServer1, and
putting it in a directory I made for my Iron Speed apps. I'm creating a Web Site
rather than a Web Application. (The difference is minor and technical in the
way they're normally handled by Visual Studio. I usually choose Web Site,
although many people prefer Web Application. The directories are laid out
slightly differently, but from within Iron Speed, it really doesn't matter.) For
language, I'm choosing C#, and I'm targeting the latest .NET, version 4.0. For
the statement generation, I'm using Inline rather than stored procedures. And
I'm using the defaults for the namespaces and virtual directory name. Here's
my screen:

The next screen lists the tables and is just informative. Click Finish to begin
creating the application.
You app will get created and built. You can run it if you like; you'll see the
categories, and you'll see the drop-down menus for getting to the different
tables we chose. This is all just like before. Now let's add a Custom Query.

Adding a Customer Query
For our custom query, we're going to retrieve the rows from the Customer data,
but only those who are in Brazil, and we're only going to get a few of the
columns. This is the SQL we're aiming towards:
select CustomerID, CompanyName, ContactName, Phone from Customers
where Country = 'Brazil'
(However, Iron Speed uses its own dialect, so the query we build won't look
identical to this, but the results of the query will.)<.p>
To start building the custom query, click File -> New -> Custom Query. (Or,
from the Application Explorer, click the Databases tab. Under the Northwind
database, you'll see Custom Queries way at the bottom. Right click that and
click New. Either way does the same thing.)
In the New Custom Query popup, type BrazilCustomers and click Next.
In the Query Wizard, we'll first fill in the FROM portion. Make sure Northwind is
selected on the left. In the Available Tables/Views, double-click Customers (or
single-click it and then click the right-arrow button.) Here's what you should
see, after you click it. Customers is now under Selected Tables/Views:
Click Next. This is where we define the SELECT clause, which contains the
fields we're selecting. We only want a portion of them. Initially they're all
selected, so what I do is first move all of them to the left so none are selected.
Then I go through and add the ones I want. Hold down Shift and click the
bottom one to select them all. Then click the left-arrow to move them all to the
left box. Now one by one add the following by double-clicking them:
Customers.CustomerID Customers.CompanyName
Customers.ContactName Customers.Phone
Your screen should look like this:

Now click Next, and we'll add our Where clause. Click the button labeled Add
Where Clause. This one is a bit more involved. On the left, make sure
Northwind is selected. Under that, click the dropdown that says , and click
Customers. (The reason for this step is you can join multiple tables, in which
case you'll see the other table names here. For this first example, we're
keeping it simple with just a single table.) Next, select the field we're searching
on. Recall we're showing only customers whose country is Brazil, so the field
we want is Country. In the dropdown, click Country.
We'll leave the operator as is; it should say "is equal to". On the right-hand side,
select Formula. Our formula isn't really a formula, however; we'll be putting an
actual country name in it. (In a future installment, I'll show you how you can put
formulas in here and how you can use it to retrieve information from elsewhere
in your application.)
For now, in the box under Formula, type "Brazil" inside double quotes.
(Although SQL itself uses single quotes for strings, we're technically still
working in C#, so we're using double quotes.)
Leave the bottom checkbox checked, and click OK.
We don't need to select a primary key, because this table, Customers, already
has one, and Iron Speed was able to find it. So we're done building the query.
Click Finish to generate it.
Now you can see your custom query is included in the project. Look in the
Application Explorer, in the Databases tab. Under Northwind, at the bottom,
you'll see a Custom Queries section. Under that you'll see BrazilCustomers,
like so:

Using the Custom Query in a Page
Now let's use the query in a set of pages. This is going to show the true power
of Iron Speed. For those of you who are familiar with SQL, I'll lay out a problem
that you might anticipate, and you'll see that Iron Speed handles it just fine. In
our query, we're only retrieving four fields. We're going to be including a screen
where you can edit those four fields. But the Customer table in the database
has many more than four fields. What will happen to those fields when we save?
The answer is that Iron Speed will modify only the fields that the user changed,
and the others, including the fields that weren't even involved in the query
(such as City, PostalCode, and so on) will remain intact. In other words, thanks
to the power of Iron Speed CASE tool, you won't accidentally wipe out any
data. So let's proceed.
We'll open the Application Wizard for adding a new page. As with the custom
queries, there are a couple ways to get there. Either:

      Click File -> New -> Page -> Page From Template or
      In the Application Explorer, in the Pages tab, right-click Presentation Layer (Pages),
    and click New -> Page from Template or
      Press Ctrl+t.

In the Application Wizard, on the left side under Select a Table, we'll tell it to
use our Custom Query. To do so, find Northwind. Under that, scroll down, and
you'll see Custom Queries (after Tables and Views). Expand that, and you'll
see BrazilCustomers. Check it.
For the middle box, leave it as-is, with Add Record, Edit Record, Edit Table,
Enhanced, and Record selected. Your screen should look like this:
Click Finish. (Alternatively you can go through the rest of the wizard, but you'll
want to leave everything as it is.)
Done! Your pages are created.
If the app doesn't run automatically, go ahead and run it. You should now see
a menu item next called Brazil Customers, like so:
These menu dropdowns are functional now. Click Show Brazil Customers, and
you'll see a list of the customers from Brazil. You'll only see the fields we chose:
Phone, Country, and Customers. (The wizard was smart and recognizes that
the CustomerID is a primary key and as such doesn't include it in the table. It's
used to keep track of which records you're editing. Generally primary keys
shouldn't be changed on existing records, anyway.)
You can edit one of the records if you like by clicking the little pen and paper
icon to the left of the record. You can make changes and save it. And this is
what I mentioned earlier: In the database itself, this record actually has more
information that's not showing up here. Those fields won't get wiped out; they'll
still have the same values they did before. Iron Speed is doing its job and
taking care of you just like a good Rapid Application Tool should.

Next RAD Tutorial
For the next installment, we'll continue with this example, making it more
powerful. For example, you might not want to only work with customers from
Brazil. Instead, you might want to be able to choose the country. Further, if you
tried the Add Brazil Customer dropdown, you'll find it doesn't quite function as
we'd like. To fix that, we'll go into the designer and see how easy it is to modify
these screens in Iron Speed. We'll take all that up next. Meanwhile, enjoy!

To top