SDS coding examples – Part 1 (C# & ADO.NET)
So, as I did with the original SDS I want to outline some code examples that other developers can use to get a sense of how to interact with the service. In general, this will be much easier now that we have shifted more to the relational model as some of the more abstract (okay maybe not sooo abstract) concepts don’t necessarily need to be explained from scratch. Having said that, there are a few concepts that are worth covering before we dive into the code.
Servers, and Masters, and User Databases, oh my!
There are really two different types of databases that we have present in SDS now. The first, and the one that you’ll generally interact with the most, is the user database. This is really where your user data resides. You’re in charge in this space. You define the tables, schemas and what have you that appear in this database. We may insert some data into locked down tables (and present them to you with views) here to help assist you with debugging and provide some metrics data but by in large this is your house. NOTE: The name you choose here for your user database (“mydatabase” in the example below) is the name of the database you specify in the connection string.
connStringBuilder.InitialCatalog = "mydatabase"; // Specify your user database to connect to.
The second type of database is really what we refer to as the logical master database (or server database) but it actually encapsulates two different things. The first thing is that the server represents a logical master database. This is the database where we keep track of all of the user databases that you have on this logical server. It’s important to note that this is *not* a real server and is more logical in nature. We will also store things your metrics here as well as login information NOTE: Because we store login information here this is why you have specify your server name in the user id as certain elements don’t come across in the initial TDS packets we receive.
The second important thing to note about the server database is that is associated (along with each of the user databases managed by it) with a particular geo-location. You will pick the geo-location when you go through the provisioning process and requests will be routed to your servers by taking advantage of the DNS system as once done previously. The code snippet below illustrates how the server presents itself in the connection string.
connStringBuilder.DataSource = "myserver.data.dev.mscds.com"; // Specify the DNS name of my SDS server (which holds my master db).
connStringBuilder.UserID = "jeff@myserver"; // Specify my user id (and the server name which holds my master db)
On to the code
So now with no further delay is some sample code. It’s simple (on purpose) but it illustrates the types of operations that you’ll be able to do (DDL & DML). In this example, I simply create a table, insert some rows, select some rows from that table, and finally drop the table in the end. Here you go! If you have other questions around the code please feel free to comment and I’ll try to reply as soon as I can.
static void Main(string[] args)
{
// Begin, by constructing the connection string using the SqlConnectionStringBuilder class for
// simplicity. I could just use String.Format but this makes it a bit easier to explain what
// we're doing with each parameter.
SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder();
connStringBuilder.DataSource = "myserver.data.dev.mscds.com"; // Specify the DNS name of my SDS server (which holds my master db).
connStringBuilder.InitialCatalog = "mydatabase"; // Specify your user database to connect to.
connStringBuilder.Encrypt = true; // Specify that I would like the channel to be encrypted.
connStringBuilder.UserID = "jeff@myserver"; // Specify my user id (and the server name which holds my master db)
connStringBuilder.Password = "****"; // Finally, specify my password.
string createTableSql =
@"CREATE TABLE [dbo].[tbl_Person]
(
[FirstName] NVARCHAR(64) NOT NULL,
[LastName] NVARCHAR(64) NOT NULL
CONSTRAINT [personName_PK] PRIMARY KEY CLUSTERED
(
[FirstName] ASC,
[LastName] ASC
)
)";
string insertSql =
@"INSERT INTO dbo.tbl_Person(FirstName, LastName) VALUES ('Jeff', 'Currier');
INSERT INTO dbo.tbl_Person(FirstName, LastName) VALUES ('Nigel', 'Ellis');
INSERT INTO dbo.tbl_Person(FirstName, LastName) VALUES ('David', 'Robinson');
INSERT INTO dbo.tbl_Person(FirstName, LastName) VALUES ('Jeff', 'Smith');";
try
{
// New up a connection to my previously provisioned user database just as I would to any other database.
// and then open it.
using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
{
conn.Open();
// Construct a new SqlCommand object that we'll use to execute execute our Sql code.
using (SqlCommand cmd = conn.CreateCommand())
{
// Use the above defined SQL to create a simple table that we'll to construct a table for this
// example
cmd.CommandText = createTableSql;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
// Insert some simple data into that table.
cmd.CommandText = insertSql;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
// Next, perform a simple select finding all of the persons in the table whose first name is Jeff.
cmd.CommandText = "select FirstName, LastName from dbo.tbl_Person where FirstName = 'Jeff'";
cmd.CommandType = CommandType.Text;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("First Name: {0} LastName: {1}", reader["FirstName"],
reader["LastName"]);
}
reader.Close();
}
// Finally, drop the table since we no longer need it.
cmd.CommandText = "Drop table dbo.tbl_Person";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
}
Now, this example was in C# and used SqlClient (ADO.NET) but my next example will use Java & JDBC. If there are other languages (toolkits) you would like to see us illustrate please comment here or on the main SDS blog and we’ll try to get them out as soon as we can.
Enjoy!
--Jeff--