Database Reference
In-Depth Information
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 Azure database.
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))
{
try
{
using (SqlCommand cmd = new SqlCommand())
{
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, 'Herve')";
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 Azure database. Why? Go ahead and replace the code in
the button's click event with this code, and run the application. The error you get in the message box
states that SQL Azure 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 Azure.
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.
