SSE554_Bridges_Gluvna_Project+3

=Introduction= For the purpose of this project we will be covering selected topics from our text “Professional C#2008”. The topics to be covered are Data Access (Chapter 26), Manipulating XML (Chapter 28), .NET Programming with SQL Server (Chapter 30), Windows Forms (Chapter 31) and Data Binding (Chapter 32). We chose the topics relating to data because almost every application written in the real world today is data driven and knowing how to work with data in while building applications is essential to success in today’s job market. The topics relating to Windows Forms (and binding Data to the controls on the forms, Data Binding) we relevant because we wanted to do something that was not dependent on web programming, although you can build “smart” clients using windows forms that are “web-driven”. =Data Access= Data access is probably the most important aspect of any data-driven application. The application can look great, and behave nicely, but if the data is wrong, or you can’t access the data behind the application, you probably will not count the application as a success when it is complete. In our jobs we use many different types of data storage including SQL Server, Oracle, XML, and even Microsoft Access in some cases. We will cover data access with an Oracle database in this section and cover connecting to a SQL Server database and an XML schema in later sections. The first thing to learn about accessing data is how to set up a database connection. Now, we say database connection, but it can be a connection to a set of XML data that is not stored in any sort of relational database management system. We will use the Oracle .NET client for our Oracle connections. To create our database connection, the first thing we have to do is reference the library we will be using: code format="csharp" using Oracle.DataAccess.Client; code Then we want to actually define the connection. In order to define the connection, we need to have some information about the database, namely a username, password and data source. For an Oracle connection, you have to keep in mind that it will use the file in the TNSNames.ora file (found at \network\admin on the computer running the code) will give you some information about the data source name. You have to make sure the name you use matches a name in the TNSNames.ora file or you will not be able to connect to the database. This is an example of an entry in the TNSNames.ora file: code format="csharp" MYDB= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=DBSRVR)(PORT=1521)) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=DB) ) ) code This is what a connection to an Oracle database would look like when done in C#: code format="csharp" string connString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DBSRVR)(PORT=1521)))(CONNECT_DATA(SERVER=DEDICATED)(SERVICE_NAME=DB)));User Id=scott;Password=tiger;"; OracleConnection conn = new OracleConnection(connString); conn.Open; code In today’s world, most connection strings are managed in a configuration file (app.config for Windows Forms and web.config for Web applications). We would use the ConfigurationManager class to access the settings stored in the *.config file for our connection strings. The following is an example of how we define our connection strings in the configuration files for our Oracle database, followed by the code we use to create our connection strings by accessing the values in the configuration file. When we defined the connection string, notice that we did not “tag” it as a connection string, but added it as a “key” to the “app settings” section of the configuration file: code format="csharp"  code Then we used the ConfigurationManager class to access the value: code format="csharp" string connString = ConfigurationManager.AppSettings[“ConnectionString”]); code As we build our connections, we should remember that we need to use those connections efficiently. Fortunately, .NET has provided us with various ways to manage our connections and ensure that they are closed when we are finished. The first is to use a “try…catch…finally” block. The basic gist of this is to open the connection in the try block, do all the work, catch any exceptions in the catch block and use the finally block to execute any statements that you want to run whether exceptions occur or not. The problem with the first approach is remembering that finally block. Most software engineers use the try and the catch all the time, but not the finally so much. A much better approach is to use the “using” statement. The “using” statement helps the connection to clean up after itself like this: code format="csharp" using (OracleConnection conn = newOracleConnection(connString)) {  using (OracleCommand command = new OracleCommand) {     string sql = "SELECT event, eventData from event"; command.CommandText = sql; command.Connection = conn; conn.Open; using (OracleDataReader rdr = command.ExecuteReader(CommandBehavior.CloseConnection)) {        if (rdr.Read) {           //Do some other stuff here using the DataReader }     }   } } code A database command is basically a string of text containing statements that you want to execute against the database. A command can be almost anything, a select statement at returns a record set, or maybe an insert, update, or delete statement to manipulate the data in the database. Whatever type of command you want to run, .NET libraries offer you the flexibility to do whatever you want to accomplish. The previous example used an Oracle command to run the sql statement, but we could do things like add parameters to a command, run a stored procedure, or any number of database commands. When we use the database connection to call stored procedures in the databases, we can create parameters to pass to the stored procedures as well as receive parameters back from those stored procedures. You just have to define the name of the stored procedure as well as the names and types of the parameters for the stored procedure. The following method makes a call to a stored procedure using parameters. code format="csharp" public static string IsCustomerAccountCurrent(int customerID, String connString) {  string result = “NO” using (OracleConnection dbConn = new OracleConnection(connString)) {     using (OracleCommand cmd = new OracleCommand(TOC.Utility.IsCustomerAcctCurr, dbConn)) {        cmd.CommandType = CommandType.StoredProcedure; OracleParameter oParamOut = cmd.Parameters.Add("curr", OracleDbType. Varchar2, ParameterDirection.ReturnValue); oParamOut.Size = 20; OracleParameter oParamIn = cmd.Parameters.Add("custID", OracleDbType.Int32, ParameterDirection.Input); oParamIn.Value = customerID; dbConn.Open; cmd.ExecuteNonQuery; result = oParamOut.Value; }  }   return result; } code We like to use strongly typed datasets to store the data returned from the database as an offline line container of our data. A Microsoft .NET dataset is a collection of data tables (or table adapters) that have been defined as objects in your code and can be accessed and used inside your program. By defining the datasets we want to use we have certain “characteristics” available to us, like we can access column names of the tables without having to “know” exactly what they are before we instantiate the objects. A dataset can have relationships defined, but for our example we will not define those in the dataset because they are already defined in the database where the data is actually stored. The following is an example of a strongly typed datasets from the designer view in Visual Studio:



By using the strongly typed datasets, we can then make the jobs of using the filled datasets a little easier. This is an example of how we filled the previous dataset with data from Oracle calling a stored procedure. First we called a stored procedure and populated a generic dataset: code format="csharp" public static DataSet GetProcurements {  OracleParameter[] parameters = new OracleParameter[] { parameterFactory.CreateOutputParameter(C130ProcurementParameter.StructuresCursor), parameterFactory.CreateOutputParameter(C130ProcurementParameter.ProcurementsCursor), parameterFactory.CreateOutputParameter(C130ProcurementParameter.DeliveredCursor) };

DataSet dsC130Procurement = ExecuteDataSet(C130ProcurementStoredProcedure.GetProcurements, parameters);

return dsC130Procurement; } code Then we pass the generic dataset back to method that loads the generic dataset to our strongly typed dataset: code format="csharp" protected static C130ProcurementResult RunReport { C130ProcurementResult dsC130ProcurementResult = new C130ProcurementResult;

DataSet ds = DataLayer.ASIP.C130Procurement.GetProcurements; dsC130ProcurementResult.Structures.Load(ds.Tables[0].CreateDataReader); dsC130ProcurementResult.Procurements.Load(ds.Tables[1].CreateDataReader); dsC130ProcurementResult.Delivered.Load(ds.Tables[2].CreateDataReader);

return dsC130ProcurementResult; } code

So when the RunReport method is called, the result will be a populated instance of our strongly typed dataset. By having the strongly typed dataset, we can create rows that match the tables, create new tables and import rows to the tables, like this:

code format="csharp" C130ProcurementResult.DeliveredDataTable dtDeliv = new C130ProcurementResult.DeliveredDataTable; foreach (C130ProcurementResult.DeliveredRow dr in dsReportResult.Delivered.Select(filter)) { dtDeliv.ImportRow(dr); total += dr.TotalDelivered; } code =Manipulating XML= Microsoft .NET framework offers developers the ability to use XML in the applications we are building but framework itself uses XML for configuration files and source code documentation. Because of the extensive use of XML, .NET offers the System.XML namespace that is loaded with classes that can be used for processing XML.

Let’s cover an example of reading an XML document. The following code is a simple example of reading and writing XML from a configuration file: code format="csharp" private void readConfigFile { XmlTextReader xml = null;

try { xml = new XmlTextReader(string.Format(@"{0}\{1}", fileConfigDir, fileConfigName)); xml.Read;

while (xml.Read) { xml.MoveToElement;

string provider = string.Empty; if (xml.Name.ToUpper.Equals("DATABASE")) if (xml.HasAttributes) for (int i = 0; i < xml.AttributeCount; i++) { xml.MoveToAttribute(i); if (xml.Name.ToUpper.Equals("CONNECTIONSTRING")) connectionString = xml.Value;

if (xml.Name.ToUpper.Equals("PROVIDER" )) provider = xml.Value; }

if (xml.Name.ToUpper.Equals("EVENTLOG")) if (xml.HasAttributes) for (int i = 0; i < xml.AttributeCount; i++) { xml.MoveToAttribute(i); if (xml.Name.ToUpper.Equals("")) connectionString = xml.Value; }

if (xml.Name.ToUpper.Equals("FILE")) if (xml.HasAttributes) for (int i = 0; i < xml.AttributeCount; i++) { xml.MoveToAttribute(i); if (xml.Name.ToUpper.Equals("FILEPATH")) filePath = xml.Value; if (xml.Name.ToUpper.Equals("FILENAME")) fileName = xml.Value; } } } catch (Exception ex) { } finally { xml.Close; } } code The code uses the System.XML class to navigate the configuration document to extract specific values for “Database”, “ConnectionString”, “Provider”, “EventLog”, “File”, “FilePath” and “FileName”. These values are used by the application to access a database, add to an event log, and access files. It uses an XmlTextReader, which is a very fast, forward only stream access to XML. The first thing it does is read the xml: code format="csharp" ml = new XmlTextReader(string.Format(@"{0}\{1}", fileConfigDir, fileConfigName)); xml.Read; code

And while it still has XML to read, it navigates to the next node using the MoveToElement method. code format="csharp" while (xml.Read) { xml.MoveToElement; code

While on the node we use the HasAttributes property of the XMLTextReader to determine if we can access any attributes of the current node. code format="csharp" if (xml.HasAttributes) code

Then we used the MoveToAttribute method to access the attributes available for each data node. We wrote a for loop for this process: code format="csharp" for (int i = 0; i < xml.AttributeCount; i++) { xml.MoveToAttribute(i); if (xml.Name.ToUpper.Equals("FILEPATH")) filePath = xml.Value; if (xml.Name.ToUpper.Equals("FILENAME")) fileName = xml.Value; } code And that’s it. You can definitely get more in depth and complex when manipulating XML, but you don’t have to unless absolutely necessary. This has been just a simple example of accessing and manipulating xml. One of the best things about XML is that you can easily convert it to to a DataSet. You just simply provide the xml to an empty dataset (ds) and use the ds.ReadXML method to populate the dataset with tables and data. Very simple, and in a lot of cases it will then get the XML into a more familiar “setting” that developers can work with.

=Windows Forms= Many developers today focus primarily on web-based applications because of all the benefits associated with being able to have all of your logic reside in one centralized location, but a web application can leave a lot to be desired when it comes to providing a rich user experience. Windows Forms applications are the solution for developers wanting to provide rich user experience with all the desired functionality. We will cover examples from a windows form application built for one our customers at work. First thing we do is create a new application in Visual Studio:



Then we have to add a form to the project:





Once the empty form is in the designer, we have a variety of ways of adding controls to those forms. The toolbox from Visual Studio makes it easy to “drag-and-drop” almost any control you may need.

The following form has numerous controls that have been added.

Each of the controls on the page has a set of properties related to its location, size, appearance, and functionality. Most of those properties can be changed at runtime if needed to account for other things in the application. For example, depending on the role of the user, you may want to hide one or more of the buttons on the previous form. The following is an example of the properties available for the controls on the form:

In order to populate the controls on a page with data, it will either have to be populated by the user as inputs or you will be populating the controls from some data source. We will cover Data Binding for these controls in the next section.

Now, we have an application and we have a form in the application. What kind of code to we need to run it? First of all, a file with a default name of Program.cs was created when we created the Windows Forms application. This is where we will tell it what form to launch when the program runs.



Explanations of all the basic controls used in Windows Forms can be found in the text (“Professional C# 2008”) but for the purpose of covering at least one example, we will look at one of the buttons on our page. We will look at the button labeled “Save to PDF”. First the code to declare the button is automatically generated when the button is dropped on the form. The code for the button follows: code format="csharp" private System.Windows.Forms.Button buttonPDF; code

The method InitializeComponent is a required method that is auto generated from the information for the properties of each control on the form. I have removed all code except that relating to buttonPDF so that it is easier to read. code format="csharp" private void InitializeComponent { System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle6 = new System.Windows.Forms.DataGridViewCellStyle; System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(ucAIRCATReport)); this.buttonPDF = new System.Windows.Forms.Button; ((System.ComponentModel.ISupportInitialize)(this.dgData)).BeginInit; // // buttonPDF // this.buttonPDF.Anchor = System.Windows.Forms.AnchorStyles.Bottom; this.buttonPDF.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0))); this.buttonPDF.Location = new System.Drawing.Point(250, 530); this.buttonPDF.Name = "buttonPDF"; this.buttonPDF.Size = new System.Drawing.Size(92, 23); this.buttonPDF.TabIndex = 45; this.buttonPDF.Text = "Save to PDF"; this.buttonPDF.UseVisualStyleBackColor = true; this.buttonPDF.Visible = false; this.buttonPDF.Click += new System.EventHandler(this.btnPDF_Click); // // ucAIRCATReport // this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F); this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; this.AutoSize = true; this.Controls.Add(this.buttonPDF); this.Name = "ucAIRCATReport"; this.Size = new System.Drawing.Size(1016, 560); this.Load += new System.EventHandler(this.ucAIRCATReport_Load); code

None of the previous code (which resides in file .designer.cs) should not be touched or changed in Visual Studio because it is all automatically generated and making changes could have unforeseen consequences. Now lets look at the code we wrote for the button click event (btnPDF_Click) from the code we wrote. We “wired up” the button click event in the line that looks like this: code format="csharp" this.buttonPDF.Click += new System.EventHandler(this.btnPDF_Click); code That line tells the program which method to call when the button is clicked. code format="csharp" private void btnPDF_Click(object sender, EventArgs e) { if (dgData.RowCount > 0) { BuildPDF("PDF"); if (wucsSame && tailSame && isChecked) System.Diagnostics.Process.Start(fullFilePath); } else MessageBox.Show("Please click 'Submit' before trying to create a PDF file.");

} code =Data Binding=

Data binding covers how to bind the data that was retrieved (remember Data Access) to controls. You have to bind data in both web controls and window controls, but the process is basically the same so we will continue down the road of Windows Forms and demonstrate the binding of data to controls for Windows Forms. Our text only covers data binding for the DataGridView Control so this project will also be limited to the same scope.

.NET 2.0 introduced the DataGridView control that offers flexibility and options that had not been available previous to the .NET 2.0 framework. When using a DataGridView control, the most important thing to remember is that you need to have a data source. The data source for a DataGridView can be almost any kind of list, like an array, a datatable, dataview, or any component that implements either the IListSource or IList interface.

The following code is an example of how to bind data from a datatable: code format="csharp" dgData.DataSource = reportTable; dgData.DataBind; code It really is that simple. You can use a BindingSource to create that will both add the data source to the DataGridView and bind at the same time: code format="csharp" BindingSource dataSource = new BindingSource(reportTable, null); dgData.DataSource = dataSource; code Although our example is simple, using a DataGridView offers properties that make the control very flexible. The DataGridView can be made editable so that you can update the records and save to your database without having to edit one record at a time and this can be huge benefit for users. It is very important to set the properties of the DataGridView to ensure that the data will display as desired. The following is a list and explanation of properties for a DataGridView from compiled from the text, the internet, and Visual Studio:

Accessibility options – These are important only depending on the target of your application. Accessibility is very difficult to get right.

AlternatingRowsDefaultCellStyle – This allows you to use two different colors alternating in your DataGridView.

BackgroundColor – Set this to a symbolic color, such as Window or AppWorkspace, for a configuration-dependent style.

BorderStyle – Has three options: None, FixedSingle and Fixed3D.

CellBorderStyle – Has several options, althrough none of them really result in a good appearance.

ColumnHeadersBorderStyle – This changes the appearance of the border on the column headers. As a reminder, the column headers are the gray boxes at the top.

ColumnHeadersDefaultCellStyle – Allows you to specify a default cell style for the column headers.

ColumnHeadersHeight – Specify the height in pixels of the column headers row.

ColumnHeadersVisible – Set this to False if you don't want the headers to appear. This will result in a simpler grid appearance. For most data-driven apps, however, the column headers are very important.

Cursor – This functions the same as it does on other Windows Forms controls.

DefaultCellStyle – Click on the ellipsis on the right of this row in the Properties pane, and you can change properties such as BackColor, Font, ForeColor, SelectionBackColor, SelectionForeColor, as well as Alignment and WrapMode. This lets you specify all aspects of the font and text layout.

EnableHeadersVisualStyle – You can try toggling this option but in my experience it almost always makes the appearance look worse when it is set to False.

GridColor – Set this to a symbolic color enum, such as ControlDark, or WindowText. These special color enums adapt to the user's system.

RightToLeft – This specifies the behavior of the DataGridView based on the user's localization. Many languages in the world are RTL, not LTR, and depending on your market, this could be critical. I have no experience in this area.

RowHeadersBorderStyle – Use this to specify the style of the row headers. As a reminder, the row headers are the boxes on the left, which I hide in this tutorial.

RowHeadersDefaultCellStyle – Like ColumnHeadersDefaultCellStyle above, this allows you to specify the text and alignment properties of the row headers.

RowHeadersVisible – Set this to False to hide the row headers.

RowsDefaultCellStyle – Here you can specify the default cell style for Rows collections. You can access Rows on the DataGridView through the Rows property.

RowTemplate – It allows you to specify a "template" row that the other rows copy.

ShowCellErrors ShowCellToolTips ShowEditingIcon ShowRowErrors These properties specify the errors and icons that are displayed on the cells. These are useful for data entry applications.

AllowUserToAddRows AllowUserToDeleteRows AllowUserToOrderColumns AllowUserToResizeColumns AllowUserToResizeRows These properties let you set what the user is allowed to do. For example, you can set AllowUserToAddRows to false, and the bottom empty cell will disappear. This is because it no longer is needed.

ClipboardCopyMode – This lets you specify how Copy, Ctrl-C, is handled in your DataGridView. You can set this to one of four values. The values are Disable, which prevents all copying; and three more options with specific characteristics.

ColumnHeadersHeightSizeMode – You can specify whether you want the system to dynamically resize the height of the headers here.

EditMode – Allows you to specify how the user can indicate she wants to start editing the DataGridView. The tutorial here doesn't cover editing.

MultiSelect – Here you can indicate whether only one part is able to be selected at once. Depending on the other properties set, this can apply to cells, rows, or columns.

ReadOnly – Set ReadOnly to true when you do not require any editing to take place. This is for when the DataGridView is only used for viewing, not editing or data entry.

RowHeadersWidthSizeMode – Row headers are the boxes on the left, and this option lets you change their sizing mode. I haven't worked carefully with this property.

SelectionMode – SelectionMode is an important property that allows you to specify what units are selected when you click on the DataGridView. The options include FullRowSelect, CellSelect, FullColumnSelect, RowHeaderSelect, and ColumnHeaderSelect.

VirtualMode – The word 'Virtual' when used in the DataGridView context refers to custom grid management methods that you have developed. It applies both for data-management and also painting.

Data -> DataBindings DataMember DataSource Here, we use the DataSource property to display the data onto the screen. It allows you to completely avoid manipulating rows individually.

Layout -> AutoSizeColumnsMode AutoSizeRowsMode These two options let you specify less specific sizings modes for your DataGridView. For DataGridViews where the exact layout isn't critical, these are very useful.

The Column properties on DataGridView provide a template for you to format your data. You add the Column properties, which are independent of the data but style it when it is adapted.
 * Column properties**

Appearance – DefaultCellStyle – You can change the default appearance of cells in the column. This lets you specify BackColor, Font, ForeColor, SelectionBackColor, and SelectionForeColor. Other important properties include Alignment, Padding, and WrapMode. Additionally, you can specify Behavior.

HeaderText – This text appears in the header. We already assigned it in the initial Columns dialog boxes.

ToolTipText – This allows you to specify some tool tip text. These are the little labels that show under the mouse pointer.

Visible – Use this to completely hide this column. This is useful for when you want to remove an entire column from the DataGridView that is in the database.

Behavior – ContextMenuStrip – Assign this property to any ContextMenuStrip you have in your Windows Forms program.

MaxInputLength – Specify the maximum number of characters to be inserted into this cell. This can prevent overly large text from being inserted later.

ReadOnly – Use to prevent user edits entirely.

Resizable – You can specify whether each column could be resized, seperately.

SortMode – The type of sorting that is performed when the column header is clicked. There are three options: NotSortable, Automatic, and Programmatic. NotSortable specifies that the order will never be changed. Automatic is adequate for most programs in my experience. Finally, Programmatic means that you must sort the column manually in your C# code.

Data – DataPropertyName – As seen above, this "links" the database column to your DataGridView.

Design – ColumnType – There are several different options for column types. They are DataGridViewTextBoxColumn, which is used for this tutorial. Others are DataGridViewButtonColumn, which allows you to insert push buttons, DataGridViewCheckBoxColumn for check boxes, DataGridViewComboBoxColumn, and DataGridViewImageColumn.

Layout – AutoSizeMode – AutoSizeMode indicates that the column be resized to fit its contents in a way determined by Microsoft. This reduces your control over layout.

DividerWidth – You can set how wide the dividers, meaning the lines between the columns, are drawn.

FillWeight – Fill weight refers to the relative proportions allotted to this column. If you have a really high fill weight, the column will have the most aggressive fill mode.

Frozen – Visual Studio says: "Indicates whether a column will move when the user scrolls the DataGridView horizontally."

MinimumWidth – Sometimes it is useful to specify a column never shrink too much.

Width – How wide, in pixels, you want the column to be.