Line of Business Accelerator 2008 – Part 1
Manage store procedure with SQL Compact 3.5
This article is the first of a series
were I will explain in small chunk the best practices use in
Microsoft Line of Business Accelerator 2008.
Prerequisites
To be able to do it you will need:
-
Visual studio 2008 – with SQL
compact 3.5
-
Line of Business Accelerator 2008
-
SDK Mobile 6.0 Standard
(smartphone, no touch screen)
-
Microsoft Active Sync 4.5
Create the Project
Open Visual studio and create a new
Mobile 6 project. You can pick any platform for this demo but a
select the framework 3.5 standard.
-
On the main Form Add a DataGrid
and name it grdProduct.
-
In the Main Menu:
-
Add a Menu Item Close (mnuClose).
Double-click and add the following code to close the application.
1: [code:c#;ln=on]
2: private void mnuClose_Click(object sender, EventArgs e)
3: {
4: this.close();
5: }
6: [/code]
- Add a menuItem Fill mnuFill and double click on it to add the handler.
Now you should have something like
this.
Add the Database
Now we need a database. I use the
Northwind database. You will found it in the SDK folder (\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile - Northwind.sdf). Right-click on the project and add
existing item.
When the dataset dialogue will prompt
select the in the Product table the column: [Product ID], [Product
Name] and [Unit Price].
Fill the datagrid with the “normal” method
Before we add the code in the mnuTest
click Handler we need to know the connectionstrng of our database.
In NetCF this is the full path and name of the database. The
relative path seem not be supported. So in our case we put the
database in the same folder that the application, so we could hard
coded or use something more generic (and reusable like:
1: [code:c#;ln=on]
2:
4: string
5: runAppFolder =
6: System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
7:
9: [/code]
So now we can use it in the click
function. Everything else now is like any “regular” .Net
application. But instead of using SQL object were using SqlCe.
1: [code:c#;ln=on]
2: private void mnuTest1_Click(object sender, EventArgs e)
3: {
4: try
5: {
6: SqlCeDataAdapter oAdap = new SqlCeDataAdapter();
7: SqlCeConnection oConn = new SqlCeConnection(@"Data Source = " + runAppFolder + @"\Northwind.sdf");
8: SqlCeCommand GetProduct = new SqlCeCommand(“SELECT [Product ID], [ProductName], [Unit Price] FROM Products”);
9:
10: oAdap.SelectCommand= GetProduct;
11: oAdap.Fill(tblProduct);
12: grdProduct.DataSource= tblProduct;
13: }
14: catch(Exception ex)
15: {
16: MessageBox.Show(ex.Message);
17: }
18: }
19: [/code]
Now everything is in place the
application should works. So Select youre emulator and run it.
Use the manage store procedure
Now it's time to use the “new” that
the NetCF Team call:Manage Store Procedure. In fact this is not
realty a new, because it was available in VS2005.
Add a Resource call StoreProc to the
project.
Add a file to the project
ProductGet.sql.
From the resource add an existing text
file... select the ProductGet.sql. You should have something like
this:
Double-click on the ProductGet icon,
the file will open with the SQL syntax hi-lighter. So now we will
move the SQL query there in the file you should have:
To use this we will get back in the
code where we were building the sqlcqcommand and replace the string
by the ProguctGet. So the new code will be
1: [code:c#;ln=on]
2:
4: ...
5:
7: SqlCeCommand GetProduct = new
8: SqlCeCommand(StoreProc.ProductGet,oConn);
9:
11: oAdap.SelectCommand =
12: GetProduct;
13:
15: oAdap.Fill(tblProduct);
16:
17: "margin-bottom: 0cm">
18: ...
19:
20: "margin-bottom: 0cm">
21: [/code]
Conclusion
Now you can test again the application.
It's works like before but this time you can edit the sql code
without re-compiling.
To add some parameter just add some ?
in you re query and add the parameter(s) to the SqlCeCommand, like
usual.
I hope this simple tutorial help you,
feel free to ask any question or let me know you're comment.
Thanks
Franky