In the last example, you found that there is no difference in syntax when using a SqlDataReader to query a SQL Database instance. This example uses the SqlCommand class and the SqlDataAdapter to query SQL Database and populate a dataset. Here are the steps:
1. In the button’s click event, replace the existing code with the following:
using (SqlConnection conn = new SqlConnection(connStr)) {
try {
using (SqlCommand cmd = new SqlCommand()) {
conn.Open();
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandText = "SELECT FirstName, LastName FROM Person.Person"; cmd.Connection = conn;
cmd.CommandType = CommandType.Text; da.SelectCommand = cmd;
DataSet ds = new DataSet("Person"); da.Fill(ds);
listBox1.DataSource = ds.Tables[0]; listBox1.DisplayMember = "FirstName"; }
}
catch (SqlException ex) {
MessageBox.Show(ex.Message.ToString()); }
}
This code creates a new connection, using the same connection information as the previous example, and then creates a new SqlCommand instance. The connection, text, and type of the SqlCommand are set and then executed using the instantiated SqlDataAdapter. A new dataset is created and filled from the SqlDataAdapter, which is then applied to the datasource property of the list box.
2. Run the application, and click the button on the form. Again, the list box is populated with the names from the Users table in the SQL Database instance. Again, you could change the connection string to point to your local database and the code would work fine.
So, when would code like this not work? Suppose your application had code such as the following, which creates a table without a clustered index:
using (SqlConnection conn = new SqlConnection(connStr)) {
Chapter 5 ■ programming with SQL DatabaSe {
conn.Open();
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandText = "CREATE TABLE TestTable(ID int, Name varchar(20))"; cmd.Connection = conn;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO TestTable (ID, Name) VALUES (1, 'Scott'), (2, Chris)";
int val = cmd.ExecuteNonQuery(); }
}
catch (SqlException ex) {
MessageBox.Show(ex.Message.ToString()); }
}
Although this code is certainly valid and runs successfully against your local SQL Server instance, it doesn’t work when executing against your SQL Database instance. Why? Go ahead and replace the code in the button’s click event with this code, and run the application. The error you’ll get in the message box states that SQL Database tables without a clustered index aren’t supported. If you step through the code, you find out that the table is indeed created, but the error comes from trying to insert data into the table. You need to go through your application and look for these sorts of things, to ensure that the application will run successfully against SQL Database.
We have discussed connecting with ADO.NET and the different options we have with ADO.NET, so let’s move on to the other connection option, ODBC.
ODBC
There is nothing earth-shattering or truly groundbreaking here, but let’s walk though an example to see how ODBC connections work and illustrate that your ODBC classes still work as you’re used to. Follow these steps:
1. Do this the proper way and create an enumeration to handle the type of connection you’re using.
2. Modify the GetConString method as shown in the following snippet to take a parameter. The parameter lets you specify the connection type so you can return the correct type of connection string (either ADO.NET or ODBC). Be sure to use your correct password, username, and server name with the correct server. If the value of ADO_NET is passed into this method, the ADO.NET connection string is returned; otherwise the ODBC connection string is returned: enum ConnType { ADO_NET = 1, ODBC = 2 }
string GetConString(ConnType connType) {
if (connType == ConnType.ADO_NET)
return "Server=tcp:servername.database.windows.net;Database=AdventureWorks2012; User ID=username@servername;Password=password;
Trusted_Connection=False;Encrypt=True;"; else
return "Driver={SQL Server Native Client 10.0};Server=tcp:servername.database.windows.net;
Database=AdventureWorks2012;Uid=username@servername;Pwd=password;Encrypt=yes;"; }
3. Add the following declaration to the project:
using System.Data.Odbc;
4. Place a second button on the form, along with a DataGridView. In its click event, add the following code. This code is just like the code from the ADO.NET example, but it uses the Odbc data classes versus the Sql data classes. For clarity, change the Text property of this new button to “ODBC” so you know the difference between this button and the first. Notice in the code that the value “ODBC” is passed in the GetConString method, returning the ODBC connection string:
string connStr = GetConString(ConnType.ODBC);
using (OdbcConnection conn = new OdbcConnection(connStr)) {
try {
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter();
OdbcCommand cmd = new OdbcCommand("SELECT FirstName, LastName FROM Person. Person", conn);
cmd.CommandType = CommandType.Text; da.SelectCommand = cmd;
DataSet ds = new DataSet("Person"); da.Fill(ds);
listBox1.DataSource = ds.Tables[0]; dataGridView1.DataSource = ds.Tables[0]; listBox1.DisplayMember = "FirstName";
}
catch (OdbcException ex) {
MessageBox.Show(ex.Message.ToString()); }
}
5. Before the project is run, you need to modify the code behind the first button to pass the appropriate Enum and return the appropriate connection string. Add the following line to the beginning of the code behind Button1:
Chapter 5 ■ programming with SQL DatabaSe 6. Run the project, and click the ODBC button. As in the previous example, the list box
populates with the names from the Person.Person table. The grid also populates with the same set of names (see Figure 5-2).
Figure 5-2. Finished form with data
From these examples, you can see that connecting to and querying SQL Database is no different from connecting to a local instance of SQL Server. The end of this chapter discusses some guidelines and best practices to help you prepare for your move to SQL Database.
So far we have discussed connecting with ADO.NET and ODBC along with the different options we have with each, so let’s continue the discussion and talk about using the sqlcmd utility.
sqlcmd
If you’ve worked with SQL Server for any length of time, chances are you’ve worked with the sqlcmd utility. This utility lets you enter and execute T-SQL statements and other objects via a command prompt. You can also use the sqlcmd utility via the Query Editor in sqlcmd mode, in a Windows script file, or via a SQL Server Agent job.
This section discusses how to use the sqlcmd utility to connect to a SQL Database instance and execute queries against that database. This section assumes that you have some familiarity with sqlcmd. This utility has many options, or parameters, but this section only discusses those necessary to connect to SQL Database.
Note
■
SQL Database doesn’t support the
-zor
-Zoption for changing user passwords. You need to use
ALTER LOGINafter connecting to the master database in order to change a password.
To use the sqlcmd utility, you first open a command prompt. At the command prompt, you need to provide the options and values necessary to connect to the SQL Database instance. As a minimum, the command syntax is the following:
sqlcmd -U login -P password -S server -d database
The parameters are nearly self-explanatory, but here they are, just in case: • -U is the user login ID.
• -P is the user-specified password. Passwords are case sensitive. • -S specifies the instance of SQL Server to which to connect.
Optionally, you can provide a database name via the -d parameter. Thus, the sqlcmd syntax looks something like the following:
Sqlcmd -U providerlogin@Server -P ProviderPassword -S ProviderServer -d database
Let’s put this syntax to use. Follow these steps:
At the command prompt, use the sqlcmd syntax and type in your connection information, as shown in Figure 5-3. (In the figure, the server name and password are hidden.) Press Enter.
Figure 5-3. Connecting via sqlcmd
When the sqlcmd utility connects, you’re presented with the sqlcmd prompt 1>, at which point you can begin typing in and executing T-SQL commands. The command to execute any T-SQL statement is GO. For example, in Figure 5-4, the following SELECT statement is entered and executed:
Chapter 5 ■ programming with SQL DatabaSe
Press the Enter key on line 1> to create a new line and execute the SELECT query. Type GO on line 2> and press Enter to execute all statements since the last GO statement (see Figure 5-4). Figure 5-5 shows the results of the sqlcmd query entered. As you can see, executing a query isn’t difficult.
Figure 5-4. Executing a SELECT
Let’s work through another example in which you create a table and add data. Here are the steps: 1. After the previous query is finished, you’re back at the 1> prompt. Type in the statement
shown in Figure 5-6.
Figure 5-6. Creating a table
2. Press Enter, type GO on line 2>, and press Enter again, to execute the CREATE statement. 3. When the T-SQL command that you execute is the type that doesn’t return data,
the sqlcmd utility doesn’t give you back a message but takes you to the 1> prompt. However, you can verify that a statement executed successfully by going into SQL Server Management Studio (SSMS), connecting to your SQL Database instance, and expanding the Tables node of your chosen database. Figure 5-7 shows the results from doing that— you can see that the table was indeed created.
Chapter 5 ■ programming with SQL DatabaSe The table you created is called Test, and it has two columns: an ID column that is the primary key (clustered index), and Col1, an nvarchar column. The table is simple, but it’s good enough to demonstrate functionality.
Note
■
You know from earlier in the chapter that the iD column must be a primary key clustered index, or you won’t be
able to add data to the table.
4. Add some data to the table by going back to the command window and typing in the INSERT statements shown in Figure 5-8. The great thing about the sqlcmd utility is that you can enter in as many commands as you want and not execute them until you type GO. Here you use two INSERT statements that add two records the table you created in the previous step.
Figure 5-8. Inserting rows via sqlcmd
5. Type GO on line 3>, and press Enter. Although the sqlcmd utility tells you 1 rows affected, you can query this new table in SSMS and see the two new rows that were added, as shown in Figure 5-9.
As you can see, using the sqlcmd utility is straightforward. Just remember that it doesn’t work with SQL Database if you’re trying to use heap tables. All tables must have a primary key. Also, as mentioned earlier, the -z and -Z parameters don’t work.
This section has discussed the different mechanisms for connecting to and querying SQL Database, including examples for ADO.NET, ODBC, and sqlcmd. You can see that the connection process is quite similar to the way you currently connect to and query an on-premises database. However, given the overall industry push to an SOA architecture, let’s take the discussion to the next level and look at using services, specifically WCF Data Services, to connect to our Azure database.