/// <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()); }