Database Applications in C#

1. Database Applications in C# • See the next slide for sample form elements (C# application), from the CPackers demo program.

2. ToolStripMenuItems labels text boxes MenuStrip picturebox radio buttons (in a Group Box) Action buttons Unbound Listbox Bound Listbox Data grid combobox

3. During the next few weeks you will learn how to use Visual Studio’s Visual C# to access a database through an interface standard called ADO.NET.

4. You will be able to download two different C# projects from the course web site, each of which interfaces with a SQL Server database.

5. The first (CPackers) contains a simple form, which interfaces with a single table and contains options to find and list players according to several different constraints.

6. The second (CUniversity) is a multiform project that simulates typical actions performed by a university registrar. These actions include displaying, editing, adding, and deleting student records; adding or dropping courses; listing student schedules; and listing course rosters.

7. The slides that follow provide general instructions in how build a C# solution and connect to a database using ADO.NET controls. • You should supplement these instructions with the Visual Studiosolutions available through the course’s web page at [http://www.uwgb/shayw/courses/451]. • There are also two books on reserve you can consult.

8. Creating a C# program • You should run all of your C# programs from the network share described in a previous email. • Both you and I have access to it. • This is useful if you need help since I can access your program directly. • It is also how I will grade your final project.

9. To create the proper network share: • Right click on computer in your program menu and select Map network drive • Choose drive letter N. • In the Folder textbox enter \\fpsb\groupwork$ • Click the Finish button. • You will see a list of folders. Any programs for this course should be stored in the folder having your name.

10. Start C# (Start  Programs  Microsoft Visual Studio 2010 Microsoft Visual Studio 2010). • You can close the Start page. • Select File  New  Project. • In the left pane (under Installed Templates) select Visual C#  Windows. • In the middle pane select Windows Forms Application.

11. Select the folder to contain your solution. Again, this should correspond to your folder on the network share. • Press the Select Folder button • Give the project a name • Do NOT check the box associated with Create directory for solution. It is not necessaryfor this course and just creates another level of subfolders. • Click OK.

12. When you create a project for the first time, a form (form1) automatically appears. • You can save it using any other name you wish as follows: • Right click on the form and select properties. You will see a properties window appear to the right. • Through this window you can change the name and text attributes of the form along with many other things.

13. Just locate the entry identified by (Name) and enter whatever name you want your form to have. • I usually start all form names with “frm…” to easily identify what identifiers correspond to forms. • There’s a similar entry in the properties window identified by Text. Whatever you enter appears at the top of the form when the program runs.

14. Then select File Save …as and specify the name of the form to save the changes to the solution. • You can use the same form name • You can then remove files corresponding to the old form name. • IMPORTANT: You should do this BEFORE proceeding with any coding on that form!

15. Adding gui elements to your form • Select View  Toolbox to get items that you can drag to the form • Expand All Windows Forms to see your options. • Drag and drop items onto the form as needed.

16. After you drag a gui element to the window, you can right click on it and select Properties. • This allows you to easily change properties, including the element name. • IMPORTANT: You should name ALL elements appropriately BEFORE moving ahead with any of your own coding.

17. You can double click on the form to see the code associated with the form and create a form_loadmethod. • This code will be executed each time the form is loaded. • This is useful if you need information in the form when the program first runs.

18. You can return to viewing the form in design view by selecting the Design tab at the top of the window.

19. Most of the code you write will be associated with button clicks. • To do this: • Drag and drop a button to your form. • Give it a name through the properties window. • Double click on the button to create an onclickmethod for it. • Enter code as needed.

20. Example • Create a textbox (name it txtCount) and a button (name it btnCount). • Insert the following into the form_load method txtCount.Text = “20”; • Insert the following into the button click method int temp; temp = Int32.Parse(txtCount.Text); temp++; txtCount.Text = temp.ToString();

21. Run the program (F5 key) and press the button to increment the count.

22. To open an existing C# project, double click on the slnfile in the project folder. • NOTE: To see file extensions you may need to do the following: • With your folder open choose Organize  Folder and search options. • Select the view tab. • Uncheck the box associated with Hide extensions for known file types.

23. If, after opening a solution, the form or code does not appear, open the solutions explorer via View  Solution Explorer. • A window appears identifying forms in the solution. You can double click on one or select view  code to see its code.

24. To define where form appears, select the StartPositionform property and set to manual. Then set the location properties to define where the form appears. • You can experiment to understand the parameters.

25. Interface standards for database access • It’s important to note that these notes describe only one way to access a database. There are numerous other ways, but we will NOT cover them. Once you understand one set of standards, the others become much more accessible.

26. ODBC (Open Database Connectivity Standard): • Defines interface standards through which SQL commands and results can be sent and received. • Historically, this helped make database processing independent of the data source (e.g databases such as Oracle, Access, SQL server, etc. -- or even spreadsheets). • See Figures 11-1 and 11-2 on pages 429 and 431. • Will not discuss ODBC architecture

27. OLE DB (Object Linking and Embedding): • ODBC was designed for table-like sources (limitation). • Developed by Microsoft OLE DB provides a standardized object-oriented interface to a wider variety of data sources (such as spreadsheets, databases, ISAM and VSAM files, and nonrelational databases)

28. Encapsulates the features and functionality (i.e. updates, query processing, index creation, etc.) of a DBMS into OLE DB objects.

29. Vendors have much more flexibility. For example, a vendor can implement portions of a product to interact with OLE DB. • Provides greater marketability. • Can add functionality later, allowing growth. • Object oriented, so is particularly suited to object oriented languages such as C#. • See Figure 11-11 on page 438.

30. ADO (Active Data Object). • Many database apps used scripting languages such as VBScript. • ADO allows the use of almost any language to access OLE BD functionality. • Built on top of the OLE DB object model;many consider it easier to use than OLE DB. • See Figure 11-15 on page 442.

31. ADO.NET: • http://msdn.microsoft.com/en-us/library/h43ks021(VS.71).aspx • Improvement on ADO that was developed as part of Microsoft’s .NET initiative. • See prose on page 442 and Figures 11-16 and 11-17 on page 443.

32. ADO.NET also provides the ability to create and process client-side in-memory databases called datasets. • A dataset can also be through of as an in-memory disconnected view of data. • Typically it is defined from a base table or a view.

33. ADO.NET classes. • The C# applications in this course interact with a SQL server database via instances of three classes from the toolbox: SqlConnection, SqlDataAdapter, and DataSet.

34. These classes are optimized for SQL Server databases but some of the others (e.g. oleDbDataAdapter) provide access to a wider variety of data sources. • There are other ways. • For course goals, there’s really little difference in the coding.

35. SqlConnection Class • Allows the application to establish a connection to a remote database. • It contains properties that define the database provider, server name, database name, and other things.

36. SqlDataAdapter Class • Provides methods to exchange data between the application and remote database. • It relies on requests being formulated in SQL (Structured Query Language). • Our approach here is to create one data adapter for each base table or view your application is going to query.

37. DataSet Class • It’s an in-memory cache of data. • Represents the results of an SQL command that the data adapter sends to the DBMSthrough the SQLConnection object. • The DataSet typically represents a copy of a database table or view. • A DataSet can hold multiple tables but we will just use one table per DataSet.

38. Figure 11-17 on page 443 shows how these classes are related.

39. NOTE: changes to the dataset are NOT automatically reflected in the database! • Any changes must specifically be written back to the database or go through a stored procedure (discussed later). • This is typical of a disconnected model for database activity.

40. Creating an SqlConnection • Create a C# app as described above. • Make the form design visible and make sure the toolbox is also visible. • In the toolbox, expand Common Controls.

41. Drag and drop SqlConnectiononto the form. It will appear on the tray below the form. • If SqlConnectiondoes not appear in toolbox select Tools  Choose Toolbox Items. Be patient, it may take some time. • Make sure the .NET Framework Components tab is selected. • Find SqlConnection in the list and check the box next to it. Click OK.

42. Right click on the connection icon and select properties. • Give it a name (such as conTest). • From the menu associated with ConnectionStringin the properties window, select <New Connection…>. • In the resulting window select Microsoft SQL Server (SqlClient) as the data source.

43. Select ICSD for the server name. • Select the radio button Use Windows Authentication. • Select the database name (e.g. CS451_packers for the first demo). • Click the OK button. • If interested, you can examine the connection string via the connection properties.

44. Creating a data adapter • Click on SqlDadaAdapterin the toolbox and drag and drop onto form. • If it is not in toolbox select Tools  Choose Toolbox Items and add it as you did the SQLConnectionobject previously.

45. When the wizard appears, do the following: • Specify the data connection (i.e. to which database you want to connect). Click next. • Specify use SQL statements to indicate how the adapter should access the database. Click next. • Enter the select command to specify what data is loaded into the DataSet. You can also use the query builder to create the SQL command. Click next and then Finish. • Through the properties window, you can rename it (example, daTest)

46. If querying from a view, make sure you maintain consistency between view attribute names and the select command. Inconsistencies can especially occur if someone alters the view after the data adapter is created! • NOTE: You can right click on the data adapter icon in the tray and select preview data. In the preview window there is a preview button that will display the data.

47. NOTE: I once had problems when the SQL command queried multiple tables. I never followed through on this, largely because I found it much easier (and probably much more efficient) to first create a view on the server side and specify it during the connection process. • If you need data from multiple tables, I recommend creating a view derived from multiple tables.

48. Creating a DataSet: • Select the data adapter and, from the menu, select Data  Generate DataSet. • Select the tables you want to add to the data set and specify a data set name (i.e., dsTest). • Click OK.

49. NOTE: If you selected dsTest as the dataset name, it will show in the tray as dsTest1. • The properties will show a dataSetName entry as dsTest and a (Name) entry as dsTest1.

50. dsTest1 is the name of the dataset and identifies the instance of a dataset class. Your code uses this. • dsTest is just a property of the dataset • It’s a little confusing and you can click here for a more detailed explanation. • Bottom line is that your C# code should use dsTest1.