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

    }

}