Databases Reference
In-Depth Information
{
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
;
