/// <summary>
/// Add new al to database
/// </summary>
/// <returns>Newly created Album</returns>
public static Al AddAl()
{
Al album = new Al()
{
Name = "New Album",
Description = "Enter Description"
};
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("InsertAlbum", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
// Add the return value parameter
SqlParameter param = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
// Add the name parameter and set the value
cmd.Parameters.AddWithValue("@name", album.Name);
// Add the description parameter and set the value
cmd.Parameters.AddWithValue("@desc", album.Description);
// Execute the command
cmd.ExecuteNonQuery();
// The return value is the index of the newly added album
album.Id = (int)cmd.Parameters["RETURN_VALUE"].Value;
}
}
return(album);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void OnAddPh(object sender, EventArgs e)
{
if (DialogResult.OK == openFileDialog1.ShowDialog())
{
// Retrieve the Album to add Ph(s) to
Al album = (Al)treeAlbums.SelectedNode.Tag;
// We allow multiple selections so loop through each one
foreach (string file in openFileDialog1.FileNames)
{
// Create a new stream to load this Ph into
System.IO.FileStream stream = new System.IO.FileStream(file, System.IO.FileMode.Open, System.IO.FileAccess.Read);
// Create a buffer to hold the stream bytes
byte[] buffer = new byte[stream.Length];
// Read the bytes from this stream
stream.Read(buffer, 0, (int)stream.Length);
// Now we can close the stream
stream.Close();
Ph Ph = new Ph()
{
// Extract out the name of the file an use it for the name
// of the Ph
Name = System.IO.Path.GetFileNameWithoutExtension(file),
Image = buffer
};
// Insert the image into the database and add it to the tree
Data.AddPh(album.Id, Ph);
buffer = null;
// Add the Ph to the album node
TreeNode node = treeAlbums.SelectedNode.Nodes.Add(Ph.Name);
node.Tag = Ph;
}
}
}
/// <summary>
/// Get all albums and associated Ph values
/// </summary>
/// <returns>Collection of Albums</returns>
public static ReadOnlyCollection <Al> GetPhAl()
{
List <Al> albums = new List <Al>();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("GetAlbums", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
// Use using here so SqlDataReader will be closed automatically
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
albums.Add(new Al()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Description = reader.GetString(2)
}
);
}
}
}
// Now get all the Phs for each each album
// This could be obtained by a single query with multiple
// result sets but for illustrative purposes it is broken
// into two processes
using (SqlCommand cmd = new SqlCommand("GetPhsByAlbum", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@albumId", SqlDbType.Int);
for (int x = 0; x < albums.Count; x++)
{
cmd.Parameters["@albumId"].Value = albums[x].Id;
List <Ph> Phs = new List <Ph>();
// Use using here so SqlDataReader will be closed automatically
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Phs.Add(new Ph()
{
Id = reader.GetString(0),
Name = reader.GetString(1),
Description = reader.GetString(2),
Image = (byte[])reader.GetValue(3)
}
);
}
}
// Annoying because
// albums[x].Phs = Phs.AsReadOnly();
// produces the error, Cannot modify the return value of xxx because it is not a variable
// The error could be avoided by using class rather than struct
Al temp = albums[x];
temp.Phs = Phs.AsReadOnly();
albums[x] = temp;
}
}
}
return(albums.AsReadOnly());
}
