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: private void mnuClose_Click(object sender, EventArgs e)
2: { 3: this.close();
4: }
- 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: "margin-bottom: 0cm">
2:
3: string runAppFolder = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
4:
5: "margin-bottom: 0cm">
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:
3:
4: ...
5:
6:
7: SqlCeCommand GetProduct = new
8: SqlCeCommand(StoreProc.ProductGet,oConn);
9:
10:
11: oAdap.SelectCommand =
12: GetProduct;
13:
14:
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
