以前为了学习SQL Server数据库,需要从头安装操作系统和数据库,打上补丁,把玩一下,然后慢慢维护。如果数据库不幸安装在自己的工作站上,不就就会发现SQL Server还是很占用系统资源的,不用时又需要暂时卸载掉,真是相当麻烦。有了虚拟机技术之后的确方便了一些,可以把SQL Server装在虚拟机里,用的时候再激活,但是仍然需要维护这个磁盘镜像中的操作系统和数据库软件。不如把这个运维的任务也外包出去吧!SQL Azure就是这么一个解决方案,开发者可以瞬间创建好数据库,倒入数据,直接开搞!本来么,操作系统和数据库本身的运维这种辛苦活可以完全交给运营商,开发者应该把精力放在数据库设计和使用上,术业有专攻。
创建SQL Azure数据库的操作相当简单,在Windows Azure管理门户按照操作做就是了。至于AdventureWorks,微软也提供了脚本导入,搜索一下便能在CodePlex上找到,这里不再赘述。
下面让我们来看看连接SQL Azure的方法。
第一种就是标准的SqlClient方法:
string server = "***.database.windows.net";
string database = "AdventureWorks2012";
string user = "***";
string password = "***";
string GetConnectionString()
{
// Creates a connection string for the master database.
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = server;
builder.InitialCatalog = database;
builder.UserID = user;
builder.Password = password;
return builder.ToString();
}
[TestMethod]
public void TestConnection()
{
// Connects to the adventure works database and pulls from employee table.
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
conn.Open();
using (SqlCommand command = conn.CreateCommand())
{
command.CommandText = "select count(*) from HumanResources.Employee";
int count = (int)command.ExecuteScalar();
Assert.AreEqual(290, count);
}
}
}
第二种是通过OleDb:
string server = "***.database.windows.net";
string database = "AdventureWorks2012";
string userId = "***";
string password = "***";
string GetConnectionString()
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Provider = "SQLNCLI11";
builder.Add("Server", server);
builder.Add("Database", database);
builder.Add("Uid", userId);
builder.Add("Pwd", password);
return builder.ToString();
}
[TestMethod]
public void TestConnection()
{
using (OleDbConnection conn = new OleDbConnection(GetConnectionString()))
{
conn.Open();
using (OleDbCommand command = conn.CreateCommand())
{
command.CommandText = "select count(*) from HumanResources.Employee";
int count = (int)command.ExecuteScalar();
Assert.AreEqual(290, count);
}
}
}
需要注意的是,微软官方声称并不支持通过OleDb连接SQL Azure,但是实际上是可以的,包括Analysis Service也在这么用,臆测只是测试覆盖还不够。
第三种是经典的ODBC:
string server = "***.database.windows.net";
string database = "AdventureWorks2012";
string userId = "***";
string password = "***";
string GetConnectionString()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder.Driver = "SQL Server";
builder.Add("Server", server);
builder.Add("Database", database);
builder.Add("UID", userId);
builder.Add("PWD", password);
return builder.ToString();
}
[TestMethod]
public void TestConnection()
{
using (OdbcConnection conn = new OdbcConnection(GetConnectionString()))
{
conn.Open();
using (OdbcCommand command = conn.CreateCommand())
{
command.CommandText = "select count(*) from HumanResources.Employee";
int count = (int)command.ExecuteScalar();
Assert.AreEqual(290, count);
}
}
}
可见,SQL Azure帮助开发者省下安装部署以及运维的时间,从而开发者可以专注于逻辑,以及提前完成任务后的那份恬淡。