"Using Relational Databases and Custom Queries With .NET CASE Tools"
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 data. 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!