Home

DATALAYER.NET™ - Creative Data Technologies, Inc.

1. Now retrieve the Product records from the database Try mProducts GetAllRows Catch ex As Exception MsgBox ex Message MsgBoxStyle Critical Problem Exit Sub End Try Now connect the DataSet to the DataGridView for displaying the records mBindingSource new BindingSource mProducts DataSet data DataGridViewl DataSource mBindingSource 53 Notice above how we made use of the GetBaseSQL routine to generate most of the SQL Statement We only needed to add the ORDER BY clause to complete our desired SQL Query 54 Also notice in the 2 to last line the mention of the DataSet table called data All DataHandler generated DataSet tables are called data by default These DataHandler DataSet buffers usually only contain a single table object so this is the standard table name that is used to access the row data 55 Compile and run the sample application When you run the program first click on the Connect button wait a couple of seconds to see if any error appears Next click on the Retrieve button to populate the Products row data into the DataGrid Note This display is very raw as none of the columns have been formatted or sized accordingly but it gives you an idea of the power the DataLayer NET library puts into your hands without a lot of programming 56 Next we will be coding the Total button to display the total price of all the products in the database that have a price listed
2. DataLayer NET library is going to be saving you There are many more powerful features for you to learn about Please take time to sit down and read the User s Guide for a more in depth look at the features and capabilities of the DataLayer NET library including Transaction Management Parameterized SQL Sequence Column management and Scalar Functions 24
3. This Total is not particularly useful information but it will serve its purpose demonstrating the strongly typed interface 57 Double click the Total button to generate the btnTotal_Click event skeleton 14 CHAPTER 3 QUICK START SAMPLE APPLICATION 58 Type the following code below into the event Notice that when you are typing the mProducts object name the IntelliSense feature of Visual Studio automatically gives you the column properties of the object in a list All you have to do is type in the first letter or two of the property you are looking for then hit the tab key to accept the selected property name Add up all the product prices and display the total Dim decTotal As Decimal 0 0 Dim I As Integer For I 1 To mProducts RowCount If mProducts AMT_PRICE I lt gt Decimal MinValue Then decTotal mProducts AMT PRICE I End ILE Next MsgBox The Grand Total of all Unit Prices is amp Format decTotal S 0 00 59 Go ahead and compile and run the program and test out the Total button Don t forget that you need to first Connect and Retrieve before clicking the Total button or you will receive an error You should see a messagebox similar to the following the exact total may vary QuickStart The Grand Total of all Unit Prices is 404 13 60 Finally let us code the Update button You may be thinking that this will be the most complex coding of all but the Dat
4. 15 16 17 18 Imported namespaces CDT DATALAYER Microsoft VisualBasic System System Collections System Collections Generic System Data System Drawing 3 9 a K amp System Diagnostics System Windows Forms System Ling KI KI KI KI KI System Xml Ling CDT ECOTDATALAYER Microsoft F Microsoft CSharp Microsoft SqlServer amp E Microsoft SqlServer Server Click on the Save All button at the top of Visual Studio to save all the changes we have made so far Close the Application Properties Window Next we will be using the DataLayer NET Code Generator to generate a new class file to access the PRODUCT_ TYPES table in the OrderEntrySystem database Right click on the QuickStart Application in the Solution Explorer and choose Add New and then choose Folder Rename the folder to Domain This is where all your DataLayer NET classes will reside Minimize the Visual Studio NET Environment and launch the DataLayer NET Code Generator using the desktop icon you created during the installation You should see the main Code Generator Program Window as follows CHAPTER 3 QUICK START SAMPLE APPLICATION 19 20 21 22 23 r T ad DataLayer NET Code Generator bo e File Edit Tools Help This utility will generate a DataLayer NET DataHandler class given an SQL statement that defines a result set Connection Type SQL Server Language to Generate V
5. 55 Finally let us code the Update button You may be thinking that this will be the most complex coding of all but the DataLayer NET library takes care of all the work for you It is essentially a single line of source code 56 Double click the Update button to generate the skeleton for the btnUpdate Click event 23 CHAPTER 3 QUICK START SAMPLE APPLICATION 57 Type the following code into the event Save the changes the user made in the DataGrid back to the database LEY mProducts Update catch Exception ex MessageBox Show ex Message Problem return 58 Compile and run the program You will need to click the Connect and Retrieve buttons to fetch all the Products from the database Next make some changes to several product names do not change the Product ID column values in the leftmost column as this may disrupt some of the existing sample Orders that are in the database After you have made a few changes click on the Update button to save your changes To test whether your changes saved properly you can click on the Retrieve button again to pull the records again from the database to see that your changes were indeed saved Note A fully completed C QuickStart application is included in the following download file http sourceforge net projects datalayernet files QuickStartCS zip download 3 6 Summary In summary you can already see from the QuickStart sample how much time and coding the
6. Output Class Name enter PRODUCT_TYPES NOTE We usually create these particular class names in UPPER CASE This will help you distinguish all the DataLayer NET Code Generated entity classes in your application from all the other classes as you develop your application 18 CHAPTER 3 QUICK START SAMPLE APPLICATION n 21 Enter QuickStartCS Domain for the Namespace 22 For the Output Directory use the Browse button to select the folder C Projects QuickStart 23 lf everything is set up correctly you should notice the following complete path for the VB source code file that will be generated C Projects QuickStart Domain PRODUCTS vb 24 Click the Generate Class Source Code File button You should get the following confirmation window DataLayerCodeGenerator Er Successfully generated source code file PRODUCT_TYPES cs we Import this new source file into your Visual Studio NET Project by right clicking on the project and selecting Add Add Existing Item 25 Click OK and then exit the Code Generator Program using the Exit button 26 Next go back to the Visual Studio NET environment so we can import the PRODUCT_TYPES cs source code file 27 Click on the Refresh button at the top of the Solution Explorer This should make the new file PRODUCT_TYPES cs visible under the Domain folder in the project 28 Right click on the PRODUCT_TYPES cs source code file and choose Include in
7. Project 29 Open the PRODUCT_TYPES cs source file so you can see what was generated for you using System using System Collections Generic using System Text using CDT DATALAYER using System Collections amespace QuickStartCS Domain public class PRODUCT TYPES DataHandl er Declare all the column properties ic DataLayerlndexer String ID PRODUCT ic DataLayerlndexer String TXT DESCRIPTION Datalayerlndexer Integer QTY ON HAND Datalayerlndexer Integer QTY REORDER LEVEL Datalayerlndexer Decimal AMT COST Datalayerlndexer Decimal AMT PRICE Datalayerlndexer String NME MANUFACTURER ess ee c c ig c c 1 Class Constructor public PRODUCT_TYPES DataConnection dc String SQL base dc SQL Set the Update Table Name is UpdateTable PRODUCT TYPES 19 CHAPTER 3 QUICK START SAMPLE APPLICATION II Set the DataLayer object up for C Mode this CSharpMode true 1 Name the Primary Key Colums ArrayList objPKList new ArrayList obj PKLi st Add ID_ PRODUCT this SetPrimaryKeyArrayList obj PKLi st Name the Updateable columns ArrayList obj UpdateColumns new ArrayList obj UpdateColumns Add D PRODUCT obj UpdateColumns Add TXT_DESCRI PTI ON obj UpdateColumns Add QTY_ON_HAND obj UpdateColumns Add QTY REORDER LEVEL obj UpdateColumns Add AMT_COST obj UpdateColumns Add AMT PRICE obj UpdateColumns Add NME_ MA
8. SSPI Connect Timeout 30 For the SQL Statement you can normally enter any SQL Statement that you would like that returns a result set For this sample application enter the following SQL Statement select from PRODUCT_TYPES For the Output Class Name enter PRODUCT TYPES NOTE We usually create these particular class names in UPPER CASE This will help you distinguish all the DataLayer NET Code Generated entity classes in your application from all the other classes as you develop your application CHAPTER 3 QUICK START SAMPLE APPLICATION 24 Leave the Namespace empty 25 For the Output Directory use the Browse button to select the folder C Projects QuickStart 26 If everything is set up correctly you should notice the following complete path for the VB source code file that will be generated C Projects QuickStart Domain PRODUCTS vb 27 Click the Generate Class Source Code File button You should get the following confirmation window DataLayerCodeGenerator Er Successfully generated source code file PRODUCT_TYPES vb we Import this new source file into your Visual Studio NET Project by right clicking on the project and selecting Add Add Existing Item 28 Click OK and then exit the Code Generator Program using the Exit button 29 Next go back to the Visual Studio NET environment so we can import the PRODUCT_TYPES vb source code file 30 Click on the Refresh button at the top of the Sol
9. sure the Visual C folder is selected on the top left under Installed Templates Make sure the Windows Forms Application template is selected Type in QuickStartCS for the projects Name and then click the OK button You will get a simple windows application with one main form called Formi cs On the right side under Solution Explorer click on the Show All Files button so that the References folder is visible for the project Expand the References folder in the Solution Explorer for the QuickStart application and then right click on the References and choose Add Reference from the popup menu Click the Browse button and navigate to the C Program Files DataLayer folder and select the DataLayer dll file and click Open You will see the DataLayer dll assembly selected in the bottom part of the window as follows 16 CHAPTER 3 QUICK START SAMPLE APPLICATION E co Add Reference NET COM Projects Browse Recent Look in di DataLayer 0 2 em Name 5 Date modified Ty CDTLicenselnterface dil 3 15 2008 11 21 AM AJ Si Data LayerCodeGenerator exe 3 22 2012 9 50 AM A in DataLayerCodeGenerator vshost exe 3 22 2012 9 50 AM A _ DataLayerCodeGenerator vshost exe manifest 3 18 2010 2 39 AM M GlobalFuncs dll 3 15 2008 11 21 AM Ay ls SS r File name DataLayer di v Files of type Component Files di tlb olb ocx
10. 1 CREATIVE DATA TECHNOLOGIES INC DATALAYER NET Getting Started Guide TABLE OF CONTENTS Table of Contents Table of CONN Siinain raana aaan aN 1 Chapter LENS TONER s E tir CROP e ES ae eE E aeaa 2 1 1 Installation Ste ao manotemoconenouontonionetonee 2 1 1 Creating the OrderEntrySystem Database nnee 2 Chapter 2 DataLayer NET Overview rsssssnnnnnnneeneneenennnnnnnnnneeeeneeeennnnne 3 2 1 Overview of DataLayer NET Components 3 PAP RANT AN rrr rr ees 4 Chapter 3 Quick Start Sample Aplication nnnensnnnes 5 3 1 Project Source Code Organization 5 3 2 SQL Server Northwind sample database required 5 3 3 High level steps to create the Quick Start Application 5 3 4 VB NET Steps to create the Quick Start Application 6 3 5 C Steps to create the Quick Start Application 16 3 6 SUMMARY a EG aa AAG ee re 24 CHAPTER 1 INSTALLATION Chapter 1 Installation 1 1 Installation Steps 1 Download the DataLayer NET library files using the following hyperlink http sourceforge net projects datalayernet files DataLayer_2 1 zip download 2 Create a Folder called DataLayer in your C Progr
11. B NET Connection String default string provided is formatted for connecting to SQL Server Server localhost Database OrderEntry System Integrated Security SSPl Connect Timeout 30 SQL Statement or just enter a Table Name in this box SELECT FROM PRODUCT_TYPES Output Class Name usually same as table name from which the data is originating PRODUCT_TYPES Namespace Output Directory to place generated source code file C Projects Quick Start Domain Browse Source Code file to be generated will be C Projects QuickStart Domain PRODUCT_TYPES vb Generate Class Source Code File Exit Ready For the Connection Type you have options for a SQL Server b ODBC and c OLE DB databases For this sample application leave it as SQL Server For the Language to Generate you have options for a VB NET and b C NET For this sample application leave it as VB NET For the Connection String this will vary depending on the location machine where you created the OrderEntrySystem database Substitute the machine s network name or IP Address after the key words Server use localhost if the database is on your local workstation Substitute the database name i e OrderEntrySystem for this sample program after the key word Database The completed ConnectionString should look similar to the following Server localhost Database OrderEntrySystem Integrated Security
12. DUCT_TYPES mProducts DataHandler object for Products table Private BindingSource mBindingSource BindingSource for the Grid The first line creates a module wide variable called mSQLConn for the database connection 21 CHAPTER 3 QUICK START SAMPLE APPLICATION The second line creates a module wide variable called mProducts that is a DataHandler object for working with the Products table The third variable mBindingSource helps bind the Products data to the DataGridView control 43 Next go back to the tab page containing the form designer for the Form1 and double click on the Connect button to generate the btnConnect_Click event 44 Enter in the following lines of code for the btnConnect_Click event II Create the DataLayer Connection object set for SQL Server mode mSQLConn new DataConnection DataLayer ConnectionType SQLServer mSQLConn ConnectionString Server localhost Database OrderEntrySystem Integrated Security SSPl Connect Ti meout 30 try mSQLConn Connect catch Exception ex MessageBox Show ex Message Problem return 45 You may need to modify the line of code containing the ConnectionString property in the same manner as you did when you were using the Code Generator Program 46 Next go back to the tab page containing the form designer and double click the Retrieve button to generate the btnRetrieve_Click event 47 Enter in the following source code for the btnRetrieve_Cl
13. ELECT FROM PRODUCT_TYPES Output Class Name usually same as table name from which the data is originating PRODUCT_TYPES Namespace Quick StartCS Domain Output Directory to place generated source code file C Projects QuickStartCS Domain Source Code file to be generated will be C Projects QuickStartCS Domain PRODUCT_TYPES cs Generate Class Source Code File Exit Ready 16 For the Connection Type you have options for a SQL Server b ODBC and c OLE DB databases For this sample application leave it as SQL Server 17 For the Language to Generate you have options for a VB NET and b C NET For this sample application leave it as C NET 18 For the Connection String this will vary depending on the location machine where you created the OrderEntrySystem database Substitute the machine s network name or IP Address after the key words Server use localhost if the database is on your local workstation Substitute the database name i e OrderEntrySystem for this sample program after the key word Database The completed ConnectionString should look similar to the following Server localhost Database OrderEntrySystem Integrated Security SSPI Connect Timeout 30 19 For the SQL Statement you can normally enter any SQL Statement that you would like that returns a result set For this sample application enter the following SQL Statement select from PRODUCT_TYPES 20 For the
14. NUFACTURER this SetUpdateColumnArrayList obj UpdateCol umns Initialize all the Property Indexers D_PRODUCT new Datalayerlndexer String this ID PRODUCT TXT_ DESCRIPTION new Datalayerlndexer String this TXT DESCRIPTION QTY_ON_HAND new DataLayerlndexer_Integer this QTY ON HAND QTY_REORDER LEVEL new Datalayerlndexer Integer this QTY REORDER LEVEL AMT_COST new Datalayerlndexer Decimal this AMT COST AMT_ PRICE new Datalayerlndexer Decimal this AMT PRICE NME_MANUFACTURER new Datalayerlndexer String this NME MANUFACTURER public static String GetBaseSQL String SQL SQL SELECT id_product txt_description qty_on_hand qty_reorder level amt_cost amt_price nme_manufacturer FROM PRODUCT TYPES return SQL 30 The general structure of these files is the Namespace declaration is at the top followed by the Class name declaration Next is all of the public column properties each column type has a different kind of accessor type Next is the constructor which includes code to set the Update Table s name CSharpMode flag a list of the Primary Key Columns and Update Columns and then the code that actually initializes all of the property indexers Finally there is a function called GetBaseSQL This function is useful for building SQL statements in your descendant classes as it provides all the columns in a bare SQL statement Notice that the C form o
15. QL statements for best practices The use of parameterized SQL protects your applications from SQL Injection attacks and the SQL also CHAPTER 2 DATALAYER NET OVERVIEW runs much faster as the back end database particularly SQL Server can cache the SQL command signatures on the cache and run the SQL much faster upon subsequent matching SQL execution B Fully support the use of the DataLayer NET library for VB NET programmers as well as C NET programmers R Provide a strongly typed interface to the column data exposing the actual database column types read from the database B Leverage the IntelliSense feature of the Visual Studio NET environment to save typing time and avoid typing errors during programming This is an additional benefit of the strongly typed interface of the DataLayer NET library R Trust and empower the programmers but do not tie their hands behind their backs Provide them with the data access objects to make their lives easier but do not prevent them from directly sending some custom SQL to the database should they see fit Extend their SQL Knowledge do not try to replace it B From the programmer s perspective the library must be flexible to changes in the database structures tables The programmer will only have to update the schema information in a single class the inherited DataHandler class for the table R Performance must be a key goal in the design as programmers will not want to use the library if t
16. RODUCT End Get set SetData RowNum ID PRODUCT Value End Set End Property 37 Click the Save All button to save your changes 38 Next let s work on creating the user interface for this sample application 39 Click on the tab page containing the Form1 vb with the blank form visible on the screen 40 Drag the lower right corner of the form to make it about twice as tall and twice as wide as it gets created originally 41 Here is a print screen of the controls placed on the form so you can see what it should look like as you place the controls on the form 12 CHAPTER 3 QUICK START SAMPLE APPLICATION 42 Using the toolbox on the left add all four buttons and the DataGridView in the middle 43 Set the Text property of each button to read Connect Retrieve Total and Update as shown above 44 Set the Name property to be btnConnect btnRetrieve btnTotal and btnUpdate accordingly 45 Leave the DataGridView control s name as DataGrid1 46 Click on the View Code button at the top of the Solution Explorer 47 In the source code editor enter the following two lines just below the line Public Class Formi Private mSQLConn As DataConnection DataLayer connection object Private mProducts As PRODUCT_TYPES DataHandler object for Products table Private mBindingSource As BindingSource BindingSource for the Grid The first line creates a module wide variable called mSQLConn for the databa
17. aLayer NET library takes care of all the work for you It is essentially a single line of source code 61 Double click the Update button to generate the skeleton for the btnUpdate_Click event 62 Type the following code into the event Save the changes the user made in the DataGrid back to the database Try mProducts Update Catch ex As Exception MsgBox ex Message MsgBoxStyle Critical Problem Exit Sub End Try 63 Compile and run the program You will need to click the Connect and Retrieve buttons to fetch all the Products from the database Next make some changes to several product names do not change the Product ID column values in the leftmost column as this may disrupt some of the existing sample Orders that are in the database After you have made a few changes click on the Update button to save your changes To test whether your 15 CHAPTER 3 QUICK START SAMPLE APPLICATION changes saved properly you can click on the Retrieve button again to pull the records again from the database to see that your changes were indeed saved Note A fully completed VB NET QuickStart application is included in the following download file http sourceforge net projects datalayernet files QuickStartVB zip download 3 5 C Steps to create the Quick Start Application akOND O Create the C Projects folder as mentioned above in section 3 1 Start Visual Studio NET and click on the New Project link Make
18. am Files folder so it will be C Program Files DataLayer Note A Setup program was not created on purpose so that you as developers would not have to worry about malicious activity from a Setup exe or msi from an unknown or untrusted party 3 Unzip the contents of the Zip file to the new DataLayer folder you just created 4 Create a desktop shortcut icon to the C Program Files DataLayer DataLayerCodeGenerator exe program 5 That s it The DataLayer dll file is the one main assembly you will be adding as a reference in all of your projects 1 2 Creating the OrderEntrySystem Database In order to follow along with the code examples in these manuals you will need to create the sample OrderEntrySystem database Please follow the steps in Appendix A of the DataLayer Users Guide to create the database CHAPTER 2 DATALAYER NET OVERVIEW Chapter 2 DataLayer NET Overview 2 1 Overview of DataLayer NET Components The DataLayer NET Library is broken into two main components The first component is called the DataConnection class It is used to help you manage the database connection manage transactions if needed and it provides scalar database functions for your use The second component is called the DataHandler class This is the main component that does most of the database work for you in data retrieval and managed updates Here is the design philosophy we used when we built the DataLayer NET Library B Make t
19. est simply creating a directory called Projects directly at the root of your C drive i e C Projects and creating a new folder under the C Projects folder for each one of your new projects Where you actually choose to keep the source code for your new projects is completely up to you of course However for the purposes of the samples contained in the 3 DataLayer NET Users Manuals we will be assuming that you are saving the projects under the folder C Projects 3 2 SQL Server sample database OrderEntrySystem required All of the sample programs provided with the DataLayer NET library use the sample OrderEntrySystem database that is provided Follow the instructions in Appendix A of the DataLayer NET User s Guide if you haven t already done so 3 3 High level steps to create the Quick Start Application Here is a high level view of the steps required to create the Quick Start Application You will be given specific instructions in the next section to perform these steps This is presented here to give you an overview of the steps that are required Create the new project using Visual Studio Add the Reference to the DataLayer dll Assembly Add a project level Imports declaration for the CDT DATALAYER namespace VB NET only Use the Code Generator to generate a new class file for the PRODUCT_TYPES table Import the new PRODUCT_TYPES class file into your project Decorate the new PRODUCT_TYPES class with the appropriate A
20. exe manifest Es 10 Click OK to accept the new reference to the DataLayer NET assembly 11 Click on the Save All button at the top of Visual Studio to save all the changes we have made so far You will be prompted to save the Project files Make sure the project folder is created under C Projects 12 Next we will be using the DataLayer NET Code Generator to generate a new class file to access the PRODUCT_TYPES table in the OrderEntrySystem database 13 Right click on the QuickStart Application in the Solution Explorer and choose Add New and then choose Folder Rename the folder to Domain This is where all your DataLayer NET classes will reside 14 Minimize the Visual Studio NET Environment and launch the DataLayer NET Code Generator using the desktop icon you created during the installation 15 You should see the main Code Generator Program Window as follows 17 CHAPTER 3 QUICK START SAMPLE APPLICATION r ail DataLayer NET Code Generator File Edit Tools Help This utility will generate a DataLayer NET DataHandler class given an SQL statement that defines a result set Connection Type SQL Server Language to Generate C NET Connection String default string provided is formatted for connecting to SQL Server Server localhost Database OrderEntry System Integrated Security SSPl Connect Timeout 30 SQL Statement or just enter a Table Name in this box S
21. f these DataHandler class files do not use Attributes the way that the VB NET DataHandler classes do The Primary Keys and Updateable columns are initialized in the Constructor instead for C 31 Click the Save All button to save your changes 20 CHAPTER 3 QUICK START SAMPLE APPLICATION 32 Next let s work on creating the user interface for this sample application 33 Click on the tab page containing the Form1 vb with the blank form visible on the screen 34 Drag the lower right corner of the form to make it about twice as tall and twice as wide as it gets created originally 35 Here is a print screen of the controls placed on the form so you can see what it should look like as you place the controls on the form 36 Using the toolbox on the left add all four buttons and the DataGridView in the middle 37 Set the Text property of each button to read Connect Retrieve Total and Update as shown above 38 Set the Name property to be btnConnect btnRetrieve btnTotal and btnUpdate accordingly 39 Leave the DataGridView control s name as DataGrid1 40 Click on the View Code button at the top of the Solution Explorer 41 Add the following two lines of code to the list of using statements at the top of the file using CDT DATALAYER using QuickStartCS Domain 42 Next enter the following two lines just inside the class declaration Private DataConnection mSQLConn DataLayer connection object Private PRO
22. he References and choose Add Reference from the popup menu Click the Browse button and navigate to the C Program Files DataLayer folder and select the DataLayer dll file and click Open You will see the DataLayer dll assembly selected in the bottom part of the window as follows CHAPTER 3 QUICK START SAMPLE APPLICATION o0 Add Reference NET coM Projects Browse Recent Lookin DataLayer 0 2 eam A Name Date modified Ty CDTLicenselnterface dll 3 15 2008 11 21 AM Ay CS Datel ayer dl 3 21 2012 947 AM Ay ga DataLayerCodeGenerator exe 3 22 2012 9 50 AM A DataLayerCodeGenerator vshost exe 3 22 2012 9 50 AM A _ DataLayerCodeGenerator vshost exe manifest 3 18 2010 2 39 AM M GlobalFuncs dll 3 15 2008 11 21 AM Ay 4 I File name DataLayer dil v Files of type Component Files dl tlb olb ocx exe manifest 10 Click OK to accept the new reference to the DataLayer NET assembly 11 Next right click on the QuickStart Project in the Solution Explorer and select Properties from the popup menu 12 Click on the References item on the left side 13 At the bottom under Imported Namespaces select check the CDT DATALAYER namespace This makes it so that you don t have to add the statement Imports CDT DATALAYER to the top of every form and module in your application CHAPTER 3 QUICK START SAMPLE APPLICATION 14
23. he components intuitive and easily understood by most programmers R Provide functionality that saves the programmer from having to work at such a low level with the ADO NET Framework directly interacting with ADO NET classes such as the SqlConnection SqlDataAdapter SalDataReader SalTransaction SqiCommand SqlParameter and all the equivalent classes for the ODBC and OLE DB interfaces B Provide a programming platform that minimizes the dependence of any particular back end database where none of the interaction with the ADO NET classes above will need to be coded by the programmer making it much easier to scale an application from SQL Server to Oracle OLE DB driver for example B Make the library capable of accessing any of the following three types of databases Microsoft SQL Server Any ODBC database Any OLE DB database Oracle DB2 Connect etc B Handle NULL values for the VB programmer When reading or setting database values VB programmers do not have the ability to represent a NULL value with program variables The DataLayer NET has a feature you can enable or disable that will automatically convert null values to each data type s MinValue constant For example a NULL integer will be read in as Integer MinValue and a NULL datetime value will be read in as DateTime MinValue B Dramatically decrease the amount of coding that is required to create typical database interactive programs B Provide support for Parameterized S
24. hey find out that there is a 10 20 reduction in performance by doing so On the contrary our clients experience a significant increase in performance because they adopt the parameterized SQL methodology as well as using the DataLayer NET library B Provide a record retrieval system that can either retrieve ALL of the rows of the result set into a buffer DataSet or retrieve the rows one page at a time where page size and which page to retrieve can be specified by the user at runtime 2 2 Summary You can see from the above list of features above the DataLayer NET is going to save you a lot of coding and make your applications a lot easier to create and maintain Some developers have stated that they saved as much as 60 to 70 code savings in the data access layers of their programs In the next chapter for this Quick Start Guide you will be led through the construction of a very simplistic sample application to give you an idea how to start using the DataLayer NET library CHAPTER 3 QUICK START SAMPLE APPLICATION Chapter 3 Quick Start Sample Application 3 1 Project Source Code Organization As you create new programs using Visual Studio NET the default location where projects are stored is in a folder name under your My Documents folder called Visual Studio Projects or Visual Studio 2010 Projects if you are using the new Visual Studio 2010 The physical path to these folders can get quite long so we sugg
25. ick event I Create the PRODUCT TYPES Datalayer object to do all the work for you mProducts new PRODUCT TYPES mSQLConn PRODUCT TYPES GetBaseSQL ORDER BY txt description Now retrieve the Product records fromthe database try mProducts Get Al Rows catch Exception ex MessageBox Show ex Message Problem return Now connect the DataSet to the DataGridView for displaying the records mBindingSource new BindingSource mProducts DataSet data dataGridViewl DataSource mBindingSource 48 Notice above how we made use of the GetBaseSQL routine to generate most of the SQL Statement We only needed to add the ORDER BY clause to complete our desired SQL Query 22 CHAPTER 3 QUICK START SAMPLE APPLICATION 49 Also notice in the 2 to last line the mention of the DataSet table called data All DataHandler generated DataSet tables are called data by default These DataHandler DataSet buffers usually only contain a single table object so this is the standard table name that is used to access the row data 50 Compile and run the sample application When you run the program first click on the Connect button wait a couple of seconds to see if any error appears Next click on the Retrieve button to populate the Products row data into the DataGrid Note This display is very raw as none of the columns have been formatted or sized accordingly but it gives you an idea of the powe
26. r the DataLayer NET library puts into your hands without a lot of programming 51 Next we will be coding the Total button to display the total price of all the products in the database that have a price listed This Total is not particularly useful information but it will serve its purpose demonstrating the strongly typed interface 52 Double click the Total button to generate the btnTotal_Click event skeleton 53 Type the following code below into the event Notice that when you are typing the mProducts object name the IntelliSense feature of Visual Studio automatically gives you the column properties of the object in a list All you have to do is type in the first letter or two of the property you are looking for then hit the tab key to accept the selected property name Add up all the product prices and display the total decimal decTotal 0 0m for int 1 i lt mProducts RowCount i if mProducts AMT_PRICE i deci mal Mi nValue decTotal mProducts AMT_PRICE i MessageBox Show The Grand Total of all Unit Prices is decTotal ToString 0 00 54 Go ahead and compile and run the program and test out the Total button Don t forget that you need to first Connect and Retrieve before clicking the Total button or you will receive an error You should see a message box similar to the following the exact total may vary QuickStart The Grand Total of all Unit Prices is 404 13
27. se connection The second line creates a module wide variable called mProducts that is a DataHandler object for working with the Products table The third variable mBindingSource helps bind the Products data to the DataGridView control 48 Next go back to the tab page containing the form designer for the Form1 and double click on the Connect button to generate the btnConnect_Click event 49 Enter in the following lines of code for the btnConnect_Click event Create the DataLayer Connection object set for SQL Server mode mSQLConn New DataConnection DataLayer_ConnectionType SQLServer 13 CHAPTER 3 QUICK START SAMPLE APPLICATION mSQLConn ConnectionString Server localhost Database OrderEntrySystem Integrated Security SSPl Connect Timeout 30 TEY mSQLConn Connect Catch ex As Exception MsgBox ex Message MsgBoxStyle Critical Problem Exit Sub End Try 50 You may need to modify the line of code containing the ConnectionString property in the same manner as you did when you were using the Code Generator Program 51 Next go back to the tab page containing the form designer and double click the Retrieve button to generate the btnRetrieve Click event 52 Enter in the following source code for the btnRetrieve Click event Create the PRODUCT_ TYPES DataLayer object to do all the work for you mProducts New PRODUCT_ TYPES mSQLConn PRODUCT_TYPES GetBaseSQL amp ORDER BY txt_description
28. ttributes for each column CHAPTER 3 QUICK START SAMPLE APPLICATION Design a simple window with several controls for the user interface for the program Code the Connect button to connect to the Northwind database Code the Retrieve button to retrieve the rows into the DataGrid control Test the Connect and Retrieval portions of the application Add a Total button to demonstrate using the strongly typed interface Add an Update button to demonstrate sending updates to the database 3 4 VB NET Steps to create the Quick Start Application Note See section 3 5 if you prefer to create a C Application Use the following steps to create your first DataLayer NET database application 1 2 3 4 5 Create the C Projects folder as mentioned above in section 3 1 Start Visual Studio NET and click on the New Project link Make sure the Visual Basic folder is selected on the top left box called Installed Templates Make sure the Windows Forms Application template is selected Type in QuickStart for the project s Name and then click the OK button You will get a simple windows application with one main form called Form1 vb On the right side under Solution Explorer click on the Show All Files button so that the References folder is visible for the project Expand the References folder in the Solution Explorer for the QuickStart application and then right click on t
29. um QTY ON HAND End Get Set SetData RowNum QTY_ON HAND Value End Set End Property lt Updateable gt Public Property QTY REORDER LEVEL ByVal RowNum As Integer As Integer Get QTY REORDER LEVEL GetlntegerData RowNum QTY REORDER LEVEL End Get Set Set Data RowNum QTY REORDER LEVEL Value End Set End Property lt Updateable gt Public Property AMT _COST ByVal RowNum As Integer As Decimal Get AMT_COST Get Decimal Data RowNum AMT_ COST End Get Set SetData RowNum AMT_COST Value End Set End Property lt Updateable gt Public Property AMT_PRICE ByVal RowNum As Integer As Deci mal Get AMT_PRICE Get Decimal Data RowNum AMT PRICE End Get Set Set Data RowNum AMT_PRICE Value End Set End Property lt Updateable gt Public Property NME MANUFACTURER ByVal RowNum As Integer As String Get NME MANUFACTURER GetStringData RowNum NME MANUFACTURER End Get Set SetData RowNum NME MANUFACTURER Value End Set End Property 11 CHAPTER 3 QUICK START SAMPLE APPLICATION End Class 33 The general structure of these files is the Class name declaration at the top followed by a New constructor and then a function called GetBaseSQL This function is useful for building SQL statements in your descendant classes as it provides all the columns in a bare SQL statement After that you have Get Set properties defined for each column in the table Notice that the data type and methods called b
30. ution Explorer This should make the new file PRODUCT_TYPES vb visible under the Domain folder in the project 31 Right click on the PRODUCT_TYPES vb source code file and choose Include in Project 32 Open the PRODUCT _TYPES vb source file so you can see what was generated for you Imports CDT DATALAYER Class PRODUCT_TYPES generated by DataLayer NET Code Generator lt Serializable gt Public Class PRODUCT TYPES Inherits DataHandl er Sub New ByVal DataConn As DataConnection ByVal SQL As String MyBase New DataConn SQL Me UpdateTable PRODUCT TYPES End Sub Public Shared Function GetBaseSQL As String Dim SQL As String SQL SELECT id_product txt_description qty_on_hand qty_reorder_ level amp _ amt_cost amt_price nme_manufacturer amp _ FROM PRODUCT TYPES Return SQL End Function lt Updateable gt Public Property ID PRODUCT ByVal RowNum As Integer As String Get D_ PRODUCT GetStringData RowNum D_ PRODUCT End Get 10 CHAPTER 3 QUICK START SAMPLE APPLICATION Set SetData RowNum ID PRODUCT Value End Set End Property lt Updateable gt Public Property TXT DESCRIPTION BYVal RowNum As Integer As String Get TXT_DESCRIPTION GetStringData RowNum TXT_DESCRI PTI ON End Get Set SetData RowNum TXT DESCRIPTION Value End Set End Property lt Updateable gt Public Property QTY ON HAND ByVal RowNum As Integer As Integer Get QTY_ON_HAND GetI ntegerData RowN
31. y each property are particular to the datatype of each column 34 Next you need to modify this class to decorate each column with the appropriate attributes Here is a list of the available attributes Updateable The column is an updateable column to be included in update statements sent to the database PrimaryKey The column is part of the primary key for the table Identity The column is an IDENTITY column automatically generated number in the database When you use this attribute the DataLayer NET Library will read the new key value assigned after an INSERT statement into the buffer object Sequencer The column is a child table sequencer don t worry about this for now more on this can be found in the User s Manual DB2Timestamp The column is a DB2 timestamp DB2 requires a very specific format for sending updates and inserts based on these column types 35 As you can see from looking at the generated source code the Updateable attribute is added by default by the code generator for every column Note that when you are adding the attributes you do not have to enter the parenthesis they will automatically be added 36 For the Products table in this example the Primary Key is the ID_PRODUCT column You need to add the PrimaryKey attribute as follows lt PrimaryKey Identity gt Public Property ID_PRODUCT ByVal RowNum As Integer As Integer Get ID_PRODUCT GetIntegerData RowNum ID P

DATALAYER.NET™ - Creative Data Technologies, Inc.

Contents

Download Pdf Manuals

Related Search

Related Contents

DATALAYER.NET&trade; - Creative Data Technologies, Inc.

Contents

Download Pdf Manuals

Related Search

Related Contents

DATALAYER.NET™ - Creative Data Technologies, Inc.