/// <summary>
/// We use the primary key to calculate the connection string and the as well
/// as a parameter to the SqlCommand.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
static String AccountName(Guid id)
{
var accountDataReader = SQLAzureHelper.ExecuteReader(
SQLAzureHelper.ConnectionString(id),
sqlConnection =>
{
String sql = @"SELECT [Name] FROM [Accounts] WHERE Id = @Id";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.Parameters.AddWithValue("@Id", id);
return(sqlCommand.ExecuteReader());
});
return((from row in accountDataReader select(string) row["Name"]).
FirstOrDefault());
}
/// <summary>
/// When inserting a single row, we need to know the primary key before connecting to the database.
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
static Guid InsertAccount(String name)
{
Guid id = Guid.NewGuid();
SQLAzureHelper.ExecuteNonQuery(
SQLAzureHelper.ConnectionString(id),
sqlConnection =>
{
String sql = @"INSERT INTO [Accounts] ([Id], [Name]) VALUES (@Id, @Name)";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.Parameters.AddWithValue("@Name", name);
sqlCommand.Parameters.AddWithValue("@Id", id);
sqlCommand.ExecuteNonQuery();
});
return(id);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Load data.
var studentDataReader = SQLAzureHelper.ExecuteReader(
ConfigurationManager.ConnectionStrings["StudentsConnectionString"].ConnectionString,
sqlConnection =>
{
SqlCommand sqlCommand =
new SqlCommand("SELECT StudentId, StudentName FROM Student",
sqlConnection);
return(sqlCommand.ExecuteReader());
});
var courseDataReader = SQLAzureHelper.ExecuteReader(
ConfigurationManager.ConnectionStrings["CoursesConnectionString"].ConnectionString,
sqlConnection =>
{
SqlCommand sqlCommand =
new SqlCommand("SELECT CourseName, StudentId FROM Course",
sqlConnection);
return(sqlCommand.ExecuteReader());
});
// Join two tables on different SQL Azure databases using LINQ.
var query =
from student in studentDataReader
join course in courseDataReader on
(Int32) student["StudentId"] equals(Int32) course["StudentId"]
select new
{
CourseName = (string)course["CourseName"],
StudentName = (string)student["StudentName"]
};
this.GridView3.DataSource = query;
this.GridView3.DataBind();
}
}
