C# Database Basics (2012)
Chapter 2. C# Data Access to SQL Server
While building an Access database is usually enough to get data access for your application, you are often trying to get at data that already exists. Many times, that data exists in SQL Server or another ODBC database. So, the examples in this chapter will use data that exists in the sample databases provided by Microsoft. When using SQL Server 2008, the sample databases are not installed by default. You can download Northwind or Pubs and install them, but in this chapter, I will be basing the examples on the AdventureWorks database that is available on the Microsoft website.
The nice thing about accessing the data with SQL Server as the backend database is that almost all of your code will still work. So, if you have data in Microsoft Access and you move it to SQL Server, you don’t need to go back to the drawing board on every line of code. Certainly, you will have to make some changes, but you can get Visual Studio to identify them for you.
In this chapter, you’ll take the example from Chapter 2 and make the minimum changes to get the data to work with SQL Server. First, I will cover what needs to be changed and then I will show you a shortcut. To get started, take your directory from the last chapter, which should have been called EditingDatabaseTest, make a copy of it, and paste it into the same folder. When you do this, change the name from EditingDatabaseTest – Copy to EditingDatabaseTest_SQL. You don’t need to change the project solution file name. (You certainly could, but I didn’t do that here.)
Open up that project and go into the code for Form1 (you can right-click on Form1 in the Solution Explorer and click View Code) and you will see the code that you wrote in the previous chapter. In the last chapter, we used the following line of code to tell Visual Studio that we wanted to use the OleDB provider:
using System.Data.OleDb;
While you can connect to multiple data sources with the OleDB provider, C# has a special type for SQL Server, which is what you want to use when you are working with SQL Server. To do that, you just need to change that line to:
using System.Data.SqlClient;
When you do that (provided that you change the existing line and don’t simply write a new line), you will see nine errors come up in the error list, as shown in Figure 2-1.
Figure 2-1. Trying to make a change simply produces errors
The errors are caused by the change from System.Data.OleDb to System.Data.SqlClient. All of the objects that are in System.Data.OleDb are no longer available to be used because that reference is gone. While that might sound bad, it actually makes it very easy for you to find what to change.
Before you go to fix any errors, you need to make sure that your SQL Server is running and that you are using Integrated Security (meaning it is going to use your Windows User Account to access the database) on the Adventure Works database (if you didn’t install that database yet, you should do that now). There are six Adventure Works databases that install with the download from Microsoft. What you will be using is just the AdventureWorks database, however, this code will work with really any other SQL Server database.
Once you have verified that your server is running, you will need to change the connection string that you have from the Access database to your SQL Server database. The following code:
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\users\\michael\\documents\\Northwind 2007.accdb";
becomes:
connString = "Data Source=.\\Server_Name;Initial Catalog=AdventureWorks;Integrated Security=True;";
For Server_Name, you need to enter the name of your SQL Server Database. In SQL Server Management Studio, it is in the first line in the Object Explorer. It will have your computer name\server name. In this case, I am showing an example of connecting to a server on the same machine, so I use .\\Server_Name because the “.” refers to the local machine, but it could be replaced with something else like: IP Address\Server_Name or Computer_Name\Server_Name. If you run into any issues finding out how you connect to a remote machine (for example, a SQL Server database on a web server), generally the administrator can get you the connection string. Later in this book, connecting to SQL Server with a username and password is also covered.
The only thing the connection string is doing for you is telling it where the SQL Server is, what database you should connect to (Initial Catalog), and that you are using the Windows User’s permissions to log in to the database.
You may have noticed that in the SQL Server Object Browser, there is a single \ in between the computer and server names, but in the line of code, there are two \’s. The reason for that is that a single \ in regular quotes is taken as an escape sequence. It will tell you that it is an unrecognized escape sequence. So, you fix it by putting in two \’s, or you can change the line to:
connString = @"Data Source=.\MJS_SQL;Initial Catalog=AdventureWorks;Integrated Security=True;";
Either way will work—I generally put in the double forward slashes, but it doesn’t matter which one you use. The next thing you need to change is the initial query because the table structure is different. I made the query string:
query = "SELECT * FROM HumanResources.Employee";
You could pick any table in that database; I just chose this one randomly. One of the changes that you’ll notice in SQL Server is that there is a Table_Schema and a Table_Name. In a lot of cases, you will see dbo as the Table_Schema, but in the AdventureWorks database, they use dbo, HumanResources, Person, Production, Purchasing, and Sales. Since you don’t always know ahead of time if you are going to need it, you should always include both in your code.
Now that you have done that, the only other changes that need to be made are with the object types. Here are the lines that should show errors for you:
public OleDbDataAdapter dAdapter;
public OleDbCommandBuilder cBuilder;
dAdapter = new OleDbDataAdapter(query, connString);
cBuilder = new OleDbCommandBuilder(dAdapter);
OleDbConnection xyz = new OleDbConnection(connString);
catch (OleDbException f)
dAdapter = new OleDbDataAdapter(query, connString);
cBuilder = new OleDbCommandBuilder(dAdapter);
You should notice that all of the error object types have OleDb as the prefix. You can edit each of these to the Sql prefixed object. Or, you can do a simple Find .. Replace and do a search for OleDb and set the replace to be Sql (case is important on both). You should get nine replacements (eight lines of code with one line having the object type twice).
If you run this program, it will work until you try to change the table name using the Change Source button. This is because you need the schema name. This is a very easy fix. The lines that read:
string tbl_str = dataGridView2.CurrentRow.Cells[2].Value.ToString();
query = "SELECT * FROM [" + tbl_str + "]";
become:
string tbl_str = "[" + dataGridView2.CurrentRow.Cells[1].Value.ToString() + "].[" + dataGridView2.CurrentRow.Cells[2].Value.ToString() + "]";
query = "SELECT * FROM " + tbl_str + "";
This looks complicated but is very straightforward. Since you can’t be sure of the Schema or Field Names, you need to put brackets around them. (Remember that you already have lines of code that designate the brackets to be used by setting the QuotePrefix and QuoteSuffix, but that only applies to the SqlCommandBuilder object, meaning that when it builds the commands for the Create, Read, Update, and Delete operations, it will automatically include the brackets, but not in normal queries.) Since the columns are a zero-based collection, you refer to Cell 1 and Cell 2 for columns 2 and 3. See Figure 2-2 for the difference in the database table information. We have four columns of data in the SQL Client’s table information compared to the nine columns in the OleDB provider’s.
Figure 2-2. Different database information
If you run the code, you will see that you can edit rows, add rows, etc. The only changes that you needed to make were to the connection string and object types, and then you needed to add the Schema Name to the table that you selected when you clicked the Change Source button. All of the filtering of data, filling of the grid, etc., is exactly the same as it is with the OleDb datasource.
There are some other things that you can use here as well as in the OleDb objects. For example, if the data you wanted was in a view instead of in a table, you would just change one line of code:
DataTable tbl = xyz.GetSchema("Tables");
becomes
DataTable tbl = xyz.GetSchema("Views");
When you do this and open it up, you will see something like Figure 2-3.
Figure 2-3. Looking at a view
When you look at this, you should notice that the IS_UPDATABLE flag is set to NO for all of the views. So, everything will seem great until you try to update a row. If you change the source to vEmployee and try to change data in a cell, you will get an error on this line:
dAdapter.Update(dTable);
This is because the error we were trapping on Cell Update was a SqlException. However, a table that cannot be updated is an InvalidOperationException. There are couple of things you can do here. You can make the code that reads:
catch (SqlException f)
become:
catch (Exception f)
That is fine, but it will catch every type of exception. So, let’s assume that you want to do something different for an invalid operation. For now, you will set it to tell you in a message box and to update the box to tell you that it wasn’t updated. If you don’t know what type of exception that error is, you can run it to get the error and the box will tell you what type of exception was unhandled. See the box in Figure 2-4.
Figure 2-4. Error reporting
You can just enter in the code for the InvalidOperationException inside the current try ... catch statement. It will look like this:
private void Cell_Update(object sender, DataGridViewCellEventArgs e)
{
try
{
dAdapter.Update(dTable);
this.textBox1.Text = "Updated " + System.DateTime.Now.ToString();
}
catch (InvalidOperationException f)
{
MessageBox.Show("Operation is not allowed");
this.textBox1.Text = "Not Updated " + f.Source.ToString();
}
catch (SqlException f)
{
this.textBox1.Text = "Not Updated " + f.Source.ToString();
}
}
While this is a simple example, in a real-world situation, you could have certain exceptions send emails to a particular team of people and have another write data to a log file, etc. The key is that during your testing, you should try to figure out errors that your users could have, then you can trap them in the try ... catch loops. There are a lot of times that I just don’t know what type of errors I could even get, and letting the error happen lets me trap each one differently. Again, you can just catch all with Exception, but you might be sweeping a away a problem that you would want to know about. So, my recommendation is to trap each one separately versus having a catchall.
There are clearly many other things that you might want to do with SQL Server, and there are some additional examples in the book. The key items are covered here, and if you want to populate a grid and let people do editing on a desktop application, this simple application gets it done.
The full code listing follows:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace EditingDatabaseTest
{
public partial class Form1 : Form
{
public string connString;
public string query;
public SqlDataAdapter dAdapter;
public DataTable dTable;
public SqlCommandBuilder cBuilder;
public DataView myDataView;
public Form1()
{
InitializeComponent();
connString = "Data Source=.\\MJS_SQL;Initial Catalog=AdventureWorks;Integrated Security=True;";
query = "SELECT * FROM HumanResources.Employee";
dAdapter = new SqlDataAdapter(query, connString);
dTable = new DataTable();
cBuilder = new SqlCommandBuilder(dAdapter);
cBuilder.QuotePrefix = "[";
cBuilder.QuoteSuffix = "]";
myDataView = dTable.DefaultView;
dAdapter.Fill(dTable);
BindingSource bndSource = new BindingSource();
bndSource.DataSource = dTable;
this.dataGridView1.DataSource = bndSource;
for (int q = 0; q <= dataGridView1.ColumnCount - 1; q++)
{
this.comboBox1.Items.Add(this.dataGridView1.Columns[q].HeaderText.ToString());
}
SqlConnection xyz = new SqlConnection(connString);
xyz.Open();
DataTable tbl = xyz.GetSchema("Tables");
dataGridView2.DataSource = tbl;
DataView tbl_dv = tbl.DefaultView;
}
private void Cell_Update(object sender, DataGridViewCellEventArgs e)
{
try
{
dAdapter.Update(dTable);
this.textBox1.Text = "Updated " + System.DateTime.Now.ToString();
}
catch (InvalidOperationException f)
{
MessageBox.Show("Operation is not allowed");
this.textBox1.Text = "Not Updated " + f.Source.ToString();
}
catch (SqlException f)
{
this.textBox1.Text = "Not Updated " + f.Source.ToString();
}
}
private void filter_click(object sender, EventArgs e)
{
string mystr;
if (myDataView.RowFilter == "")
{
mystr = "[" + dataGridView1.CurrentCell.OwningColumn.HeaderText.ToString() + "]";
mystr += " = '" + dataGridView1.CurrentCell.Value.ToString() + "'";
myDataView.RowFilter = mystr;
}
else
{
mystr = myDataView.RowFilter + " and ";
mystr += "[" + dataGridView1.CurrentCell.OwningColumn.HeaderText.ToString() + "]";
mystr += " = '" + dataGridView1.CurrentCell.Value.ToString() + "'";
myDataView.RowFilter = mystr;
}
}
private void clear_filter(object sender, EventArgs e)
{
myDataView.RowFilter = "";
}
private void change_data_source(object sender, EventArgs e)
{
string tbl_str = "[" + dataGridView2.CurrentRow.Cells[1].Value.ToString() + "].[" + dataGridView2.CurrentRow.Cells[2].Value.ToString() + "]";
query = "SELECT * FROM " + tbl_str + "";
dAdapter = new SqlDataAdapter(query, connString);
dTable = new DataTable();
cBuilder = new SqlCommandBuilder(dAdapter);
cBuilder.QuotePrefix = "[";
cBuilder.QuoteSuffix = "]";
myDataView = dTable.DefaultView;
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
this.dataGridView1.DataSource = bSource;
for (int q = 0; q <= dataGridView1.ColumnCount - 1; q++)
{
this.comboBox1.Items.Add(this.dataGridView1.Columns[q].HeaderText.ToString());
}
}
}
}
What’s Next
Chapter 3 shows how to make a data entry form that isn’t on a grid like you would see in a typical Access database application. What is very straightforward in Microsoft Access becomes challenging to implement in C#. You’ll also learn how easy it is to deal with related records in multiple tables in a DataSet. If you think about how you have multiple forms (Parent/Child) in an Access application, you can do something very similar in C#, but you can do it within a single form. While there is some complexity that initially seems difficult, I think you’ll find the flexibility that you gain is worth the effort.