using System;

using System.Data;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using DotNetNuke;

using DotNetNuke.Data;

namespace DotNetNuke.Modules.SilverlightToolBar

{

    public class Edit_DAL

    {

        #region GetPictures

        public static List<PicturesInfo> GetPictures(int ModuleId)

        {

            StringBuilder mySqlString = new StringBuilder();

            mySqlString.Append("SELECT ");

            mySqlString.Append("PictureID, ModuleId, Url, 'images/' + Picture as Picture ");

            mySqlString.Append("FROM {databaseOwner}{objectQualifier}SilverlightToolBar_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"]);

                    objPicturesInfo.Url = Convert.ToString(dr["Url"]);

                    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 {databaseOwner}{objectQualifier}SilverlightToolBar_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 {databaseOwner}{objectQualifier}SilverlightToolBar_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)

        {

            StringBuilder mySqlString = new StringBuilder();

            mySqlString.Append("INSERT INTO {databaseOwner}{objectQualifier}SilverlightToolBar_Pictures ");

            mySqlString.Append("(ModuleId, Picture, Url) ");

            mySqlString.Append("VALUES( ");

            mySqlString.Append("@ModuleId, ");

            mySqlString.Append("@Picture, ");

            mySqlString.Append("@Url ");

            mySqlString.Append(") ");

            SqlParameter parmModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4);

            SqlParameter parmPicture = new SqlParameter("@Picture", SqlDbType.NVarChar, 250);

            SqlParameter parmUrl = new SqlParameter("@Url", SqlDbType.NVarChar, 250);

            parmModuleId.Value = PicturesInfo.ModuleId;

            parmPicture.Value = PicturesInfo.Picture;

            parmUrl.Value = PicturesInfo.Url;

            DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmModuleId, parmPicture, parmUrl);

        }

        #endregion

        #region DeletePicture

        public static void DeletePicture(PicturesInfo PicturesInfo)

        {

            StringBuilder mySqlString = new StringBuilder();

            mySqlString.Append("DELETE ");

            mySqlString.Append("FROM {databaseOwner}{objectQualifier}SilverlightToolBar_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

    }

}