C# Database Basics (2012)

Chapter 7. Additional C# and Database Topics

There are a few other items in C# that you will find useful as you start to build applications—whether online or on the desktop. In this chapter, you can add some additional code to the projects that you’ve already built (or downloaded), or you can just put the extra code into a new project.

Referring to Connection Strings

If you think back to the Windows Forms applications that you worked on earlier, you would often have a line that looked like:

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data

Source=C:\\users\\michael\\documents\\Northwind 2007.accdb";

While this is fine to do, if you had database connections on multiple forms and then you wanted to point to a different database, you would have a lot of changes to make. You may have noticed in the ASP.NET application that there was always a Web.config file that could hold the connection strings, among other settings. Well, you have the ability to create a similar file in your Windows Forms applications to do the same thing.

To do this, go to Project→Add New Item and select Application Configuration File, then leave the file name as App.Config and press Add. Then edit the App.Config file and enter the lines that you see below:

<?xml version="1.0" encoding="utf-8" ?>

</connectionStrings>

</configuration>

Then, either add a new Windows Form or edit a form you already have. Put on a button and add the following code in the button_click event:

MessageBox.Show(ConfigurationManager.ConnectionStrings["NW_Data"].ConnectionString, "Connection String", MessageBoxButtons.OK);

OleDbDataAdapter dadapter = new OleDbDataAdapter("Select * from Customers", ConfigurationManager.ConnectionStrings["NW_Data"].ConnectionString);

DataTable dt = new DataTable();

dadapter.Fill(dt);

MessageBox.Show(dt.Rows.Count.ToString(), "Row Count", MessageBoxButtons.OK);

This code will show you a message box with the connection string that you put in your App.Config file, then it will open the Customers table and show you a message box with the row count. If you see errors when you enter the code above, you will need to make sure the following lines are in the top section of the code:

using System.Data;

using System.Data.OleDb;

using System.Configuration;

If you enter these lines and still see an error (particularly on the ConfigurationManager line), make sure that you have a reference to System.Configuration in your project. You do this by going to Project > Add Reference and then click on the .Net tab as seen in Figure 7-1. Then, find System.Configuration and click on it and then press OK. You code will run properly then.

Adding a reference to System.Configuration

Figure 7-1. Adding a reference to System.Configuration

It is also possible to have multiple connection strings in the App.Config file, and you can loop through the connection strings. There are a couple of objects you need to use. Take a look at the code below:

private void button1_Click(object sender, EventArgs e)

{

ConnectionStringSettingsCollection csc = ConfigurationManager.ConnectionStrings;

if (csc != null)

{

foreach (ConnectionStringSettings connstr in csc)

{

MessageBox.Show(connstr.ConnectionString, connstr.Name, MessageBoxButtons.OK);

var result = MessageBox.Show("Do you want to open?", "Question", MessageBoxButtons.YesNo);

if (result == DialogResult.Yes)

{

OleDbDataAdapter dadapter = new OleDbDataAdapter("Select * from Customers", connstr.ConnectionString);

DataTable dt = new DataTable();

dadapter.Fill(dt);

MessageBox.Show(dt.Rows.Count.ToString(), "Row Count", MessageBoxButtons.OK);

}

It isn’t likely that you will have a Customer table in each database, but this is just for demonstration. Also, you will see that I have included a MessageBox to ask if you want to open the connection. I did that because there is a default LocalSqlServer connection that may show up for you that isn’t going to be in your App.Config file. I say that it may show up for you because that connection is defined in the machine.config file when you install .NET Framework 4. You can also add other connections to that file if you want. In any event, I wrote this code in a way that lets you skip any connection that you don’t want to open.

Also, it is worth noting that you should really only do your database connections in the App.Config file when you aren’t using passwords (many Access databases), or when you are using integrated security. Any username or password that you show in that file will be viewable by the user if they know where to look. There are some good references online about encrypting data like this; however, that is outside the scope of this book.

Building Strings with Database Data

One of the items that I find myself doing quite often is passing data to the user in a string. For example, let’s say that you have a table with customer data and you want to show the user data with the customer’s name and phone number. Assume it should read: This customer’s last name is Smith and the phone number is 800-555-1212.

You could build that with something like:

private void button1_Click(object sender, EventArgs e)

{

ConnectionStringSettingsCollection csc = ConfigurationManager.ConnectionStrings;

if (csc != null)

{

foreach (ConnectionStringSettings connstr in csc)

{

MessageBox.Show(connstr.ConnectionString, connstr.Name, MessageBoxButtons.OK);

var result = MessageBox.Show("Do you want to open?", "Question", MessageBoxButtons.YesNo);

if (result == DialogResult.Yes)

{

OleDbDataAdapter dadapter = new OleDbDataAdapter("Select * from Customers", connstr.ConnectionString);

DataTable dt = new DataTable();

dadapter.Fill(dt);

DataRow dr = dt.Rows[2];

string str = "This customer's last name is " + dr[2].ToString() + " and the phone number is " + dr[6].ToString();

MessageBox.Show(str, "Info", MessageBoxButtons.OK);

}

This will do the same loop as earlier, and you can see how the string is being built with the + operator. I have it looking at the third row (row 2 is the third row because the rows collection is zero-based). While that works, it can get difficult to work within a large string. You can change the second-to-last line to:

string str = string.Format("This customer's last name is {0} and the phone number is {1}",dr[2].ToString(),dr[6].ToString());

This is taking advantage of the string.Format method. You refer to the data you want to reference with a zero-based index in curly braces right inside the string. That method will replace those references with the data you place after the string set off by commas. You can also reuse the same date field more than once and also use some built-in formatting. As an example, let’s say that you want to reference the current time and you want to look at the hours and minutes independently. You could have a few lines of code like the following:

string clientName = "Michael";

string teststr = string.Format("The client's name is {0} and the current hour is {1:hh} and the current minute is {1:mm}", clientName, DateTime.Now);

MessageBox.Show(teststr, "test", MessageBoxButtons.OK);

You can see that you reference the hours by hh and the minutes by mm, and you refer to the current date and time by using {1} for both spots. There are several ways that you can format your data, and you can also set an alignment setting that is optional. The layout of the reference is {index,alignment:format}. If you are skipping alignment, you eliminate the comma, and if you are skipping the format, you eliminate the colon. You can see all of the built-in formats by searching “Standard Numeric Format Strings” and “Standard Date and Time Format Strings” on MSDN.

Reporting

You may find it useful to perform reporting in an application. There is a report wizard in Visual Studio (not in Express), and you can show those reports in a ReportViewer Control. To use the report viewer, take a Windows Forms form and get to the design view. Then, open up the Toolbox and scroll down to the Reporting section. Drag the ReportViewer onto the form. You will get a menu that will ask you which report you want to show, or it will let you build a new report. There is a Report Definition Language (RDL) you can use to generate a report on the fly. This is very similar to the SQL Server Reporting Services. The difference is that the Visual Studio reports are RDLC files and saved and run locally, while the SQL Server reports are RDL files and they are saved and run remotely.

An entire book could be written on reporting, so I’m not going to go into great detail here. There is a fantastic walk-through on building a report on MSDN that I highly encourage you to check out if you need to do reporting. There are walk-throughs for reporting in Windows Forms, Server Side Web Forms, and Client Side Web Forms.

I’m pointing this out just to let you know it is there and that you don’t need to reinvent the wheel to do reporting. But there is a ton of information to cover to put a working example in this writing, and it would be no more detailed than the walk-through that you can see online on MSDN.

Exporting Tables to XML

The last topic I want to cover is exporting a table to XML. There are times when you want to take a DataTable that you have and store it in a format to access somewhere else. The easiest way to do that is to store it as XML. There are some things to be aware of before you do this. First, you must have the TableName property of the DataTable set before you attempt to export it. Note that the TableName isn’t the name of the table in the database; it is a name that you set. The following code will do the export for you:

OleDbDataAdapter dadapter = new OleDbDataAdapter("Select * from Customers", connstr.ConnectionString);

DataTable dt = new DataTable();

dadapter.Fill(dt);

dt.TableName = "test_table";

dt.WriteXml("c:\\users\\michael\\table.xml");

This code assumes you are using the code that we used earlier in the chapter where the connstr object is already created. But in any code where you have a DataTable, the last two lines will do the export.

When you do the export of the Customers table in the Northwind database, you will get something that looks like the following:

<?xml version="1.0" standalone="true"?>

<test_table>

<Company>Company A</Company> <Last_x0020_Name>Bedecs</Last_x0020_Name>

<First_x0020_Name>Anna</First_x0020_Name>

<Job_x0020_Title>Owner</Job_x0020_Title>

<Business_x0020_Phone>(123)555-0100</Business_x0020_Phone>

<Fax_x0020_Number>(123)555-0101</Fax_x0020_Number>

<Address>123 1st Street</Address>

<City>Seattle</City>

<State_x002F_Province>WA</State_x002F_Province>

<ZIP_x002F_Postal_x0020_Code>99999</ZIP_x002F_Postal_x0020_Code>

<Country_x002F_Region>USA</Country_x002F_Region>

</test_table>

...

</DocumentElement>

I want you to notice a couple of things. First, if you have spaces in your field names, the code will replace the space with _x0020_. So, if you are writing something to import into another application, it would be best to not have spaces in your field names. Second, the name you give the table should not have spaces, or they will be replaced similar to the above. Finally, note that this rendering doesn’t signify the object type. If you remember back to when you wrote the web service, the object type of DataTable was where DocumentElement is. So, where in an XML Web Service, you can return the XML for a DataTable and place that right into a DataTable, you can’t do that directly with this XML. You will get an error that it cannot infer the data schema. So, to be able to load into a new table, you need to also export the schema and then load it before you load the table. To export the schema, place this line right before you write the XML:

dt.WriteXmlSchema(“c:\\users\\michael\\tableschema.xml”);

Then, when you want to read the file into a DataTable, you use the following lines:

DataTable dt = new DataTable();

dt.ReadXmlSchema("c:\\users\\michael\\tableschema.xml");

dt.ReadXml("c:\\users\\michael\\table.xml");

If your user wrote a query and returned data that he wanted to access later, you could let him do this and then you could bind this data to a grid in the future. This is very similar to what you might do in Access for writing an ADO recordset to a file.