DotNetNukeŽ Silverlight Album

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using DotNetNuke;
using DotNetNuke.Data;
 
namespace DotNetNuke.Modules.SilverlightAlbum
{
    public class Edit_DAL
    {
 
        #region GetPictures
        public static List<PicturesInfo> GetPictures(int ModuleId)
        {
            StringBuilder mySqlString = new StringBuilder();
 
            mySqlString.Append("SELECT ");
            mySqlString.Append("PictureID, ModuleId, 'images/' + Picture as Picture ");
            mySqlString.Append("FROM SilverlightAlbum_Pictures ");
            mySqlString.Append("WHERE ");
            mySqlString.Append("ModuleId = @ModuleId ");
 
            SqlParameter parmModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4);
            parmModuleId.Value = ModuleId;
 
            List<PicturesInfo> colPicturesInfo = new List<PicturesInfo>();
            IDataReader dr = ((IDataReader)(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmModuleId)));
            using (dr)
            {
                while (dr.Read())
                {
                    PicturesInfo objPicturesInfo = new PicturesInfo();
                    objPicturesInfo.PictureID = Convert.ToInt32(dr["PictureID"]);
                    objPicturesInfo.ModuleId = Convert.ToInt32(dr["ModuleId"]);
                    objPicturesInfo.Picture = Convert.ToString(dr["Picture"]);
 
                    colPicturesInfo.Add(objPicturesInfo);
                }
            }
 
            return colPicturesInfo;
 
        }
        #endregion
 
        #region CountPictures
        public static int CountPictures(int ModuleId)
        {
            int intPictures = 0;
            StringBuilder mySqlString = new StringBuilder();
 
            mySqlString.Append("SELECT count(*) as mycount ");
            mySqlString.Append("FROM SilverlightAlbum_Pictures ");
            mySqlString.Append("WHERE  ");
            mySqlString.Append("ModuleId = @ModuleId ");
 
            SqlParameter parmModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4);
            parmModuleId.Value = ModuleId;
 
            IDataReader dr = ((IDataReader)(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmModuleId)));
            using (dr)
            {
                while (dr.Read())
                {
                    intPictures = Convert.ToInt32(dr["mycount"]);
                }
            }
 
            return intPictures;
        }
        #endregion
 
        #region GetPictureName
        public static string GetPictureName(int PictureID)
        {
            string strPicture = "";
            StringBuilder mySqlString = new StringBuilder();
 
            mySqlString.Append("SELECT Picture ");
            mySqlString.Append("FROM SilverlightAlbum_Pictures ");
            mySqlString.Append("WHERE  ");
            mySqlString.Append("PictureID = @PictureID ");
 
            SqlParameter parmPictureID = new SqlParameter("@PictureID", SqlDbType.Int, 4);
            parmPictureID.Value = PictureID;
 
            IDataReader dr = ((IDataReader)(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmPictureID)));
            using (dr)
            {
                while (dr.Read())
                {
                    strPicture = Convert.ToString(dr["Picture"]);
                }
            }
 
            return strPicture;
        }
        #endregion
 
        #region InsertPicture
        public static void InsertPicture(PicturesInfo PicturesInfo)
        {
            if (CountPictures(PicturesInfo.ModuleId) < 12)
            {
                StringBuilder mySqlString = new StringBuilder();
 
                mySqlString.Append("INSERT INTO SilverlightAlbum_Pictures ");
                mySqlString.Append("(ModuleId, Picture) ");
                mySqlString.Append("VALUES( ");
                mySqlString.Append("@ModuleId, ");
                mySqlString.Append("@Picture ");
                mySqlString.Append(") ");
 
                SqlParameter parmModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4);
                SqlParameter parmPicture = new SqlParameter("@Picture", SqlDbType.NVarChar, 100);
                parmModuleId.Value = PicturesInfo.ModuleId;
                parmPicture.Value = PicturesInfo.Picture;
 
                DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmModuleId, parmPicture);
            }
        }
        #endregion
 
        #region DeletePicture
        public static void DeletePicture(PicturesInfo PicturesInfo)
        {
            StringBuilder mySqlString = new StringBuilder();
 
            mySqlString.Append("DELETE ");
            mySqlString.Append("FROM SilverlightAlbum_Pictures ");
            mySqlString.Append("WHERE ");
            mySqlString.Append("PictureID = @PictureID ");
 
            SqlParameter parmPictureID = new SqlParameter("@PictureID", SqlDbType.Int, 4);
            parmPictureID.Value = PicturesInfo.PictureID;
 
            DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmPictureID);
        }
        #endregion
 
    }
}