PageRenderTime 26ms CodeModel.GetById 17ms app.highlight 6ms RepoModel.GetById 1ms app.codeStats 0ms

/pigeoncms/App_Code/EmailNewsletterManager.cs

http://pigeoncms.googlecode.com/
C# | 221 lines | 193 code | 20 blank | 8 comment | 15 complexity | 2a42b438187630a934da2068913a363b MD5 | raw file
  1using System;
  2using System.Data;
  3using System.Configuration;
  4using System.Web;
  5using System.Web.Security;
  6using System.Web.UI;
  7using System.Web.UI.WebControls;
  8using System.Web.UI.WebControls.WebParts;
  9using System.Web.UI.HtmlControls;
 10using System.ComponentModel;
 11using System.Data.SqlClient;
 12using System.Collections.Generic;
 13
 14
 15namespace PigeonCms
 16{
 17    /// <summary>
 18    /// Data Access Layer for EmailNewsletter obj (in table emailInserite)
 19    /// </summary>
 20    [DataObject()]
 21    public static class EmailNewsletterManager
 22    {
 23        [DataObjectMethod(DataObjectMethodType.Select, true)]
 24        public static List<EmailNewsletter> GetListByFilter(EmailNewsletterFilter filter, string sort)
 25        {
 26            SqlConnection myConn = new SqlConnection();
 27            SqlDataReader myRd = null;
 28            SqlCommand myCmd = new SqlCommand();
 29            string sSql;
 30            List<EmailNewsletter> result = new List<EmailNewsletter>();
 31
 32            try
 33            {
 34                myConn.ConnectionString = Database.ConnString;
 35                myConn.Open();
 36                myCmd.Connection = myConn;
 37
 38                sSql = "SELECT EmailId FROM #__EmailInserite t WHERE t.EmailId > 0 ";
 39                if (filter.EmailId > 0 || filter.EmailId == -1)
 40                {
 41                    sSql += " AND t.EmailId = @EmailId ";
 42                    myCmd.Parameters.AddWithValue("EmailId", filter.EmailId);
 43                }
 44                if (!string.IsNullOrEmpty(filter.Email))
 45                {
 46                    sSql += " AND t.Email = @Email ";
 47                    myCmd.Parameters.AddWithValue("Email", filter.Email);
 48                }
 49                if (!string.IsNullOrEmpty(filter.Ip))
 50                {
 51                    sSql += " AND t.Ip = @Ip ";
 52                    myCmd.Parameters.AddWithValue("Ip", filter.Ip);
 53                }
 54                if (!string.IsNullOrEmpty(sort))
 55                {
 56                    sSql += " ORDER BY " + sort;
 57                }
 58                else
 59                {
 60                    sSql += " ORDER BY EmailId ";
 61                }
 62                myCmd.CommandText = Database.ParseSql(sSql);
 63                myRd = myCmd.ExecuteReader();
 64                while (myRd.Read())
 65                {
 66                    EmailNewsletter item = GetById((int)myRd["EmailId"]);
 67                    result.Add(item);
 68                }
 69                myRd.Close();
 70            }
 71            finally
 72            {
 73                myConn.Dispose();
 74            }
 75            return result;
 76        }
 77
 78        [DataObjectMethod(DataObjectMethodType.Select, false)]
 79        public static EmailNewsletter GetById(int emailId)
 80        {
 81            SqlConnection myConn = new SqlConnection();
 82            SqlDataReader myRd = null;
 83            SqlCommand myCmd = new SqlCommand();
 84            string sSql;
 85            EmailNewsletter result = new EmailNewsletter();
 86
 87            try
 88            {
 89                myConn.ConnectionString = Database.ConnString;
 90                myConn.Open();
 91                myCmd.Connection = myConn;
 92
 93                sSql = "SELECT EmailId, InsDate, Email, Ip "
 94                + " FROM #__emailInserite m "
 95                + " WHERE EmailId = @EmailId ";
 96                myCmd.CommandText = Database.ParseSql(sSql);
 97                myCmd.Parameters.AddWithValue("EmailId", emailId);
 98                myRd = myCmd.ExecuteReader();
 99                if (myRd.Read())
100                {
101                    if (!Convert.IsDBNull(myRd["EmailId"]))
102                        result.EmailId = (int)myRd["EmailId"];
103                    if (!Convert.IsDBNull(myRd["InsDate"]))
104                        result.InsDate = Convert.ToDateTime(myRd["InsDate"]);
105                    if (!Convert.IsDBNull(myRd["Email"]))
106                        result.Email = (string)myRd["Email"];
107                    if (!Convert.IsDBNull(myRd["Ip"]))
108                        result.Ip = (string)myRd["Ip"];
109                }
110                myRd.Close();
111            }
112            finally
113            {
114                myConn.Dispose();
115            }
116            return result;
117        }
118
119        [DataObjectMethod(DataObjectMethodType.Delete, true)]
120        public static int DeleteById(int emailId)
121        {
122            SqlConnection myConn = new SqlConnection();
123            SqlCommand myCmd = new SqlCommand();
124            string sSql;
125            int res = 0;
126
127            try
128            {
129                myConn.ConnectionString = Database.ConnString;
130                myConn.Open();
131                myCmd.Connection = myConn;
132
133                sSql = "DELETE FROM #__emailInserite WHERE emailId = @emailId ";
134                myCmd.CommandText = Database.ParseSql(sSql);
135                myCmd.Parameters.AddWithValue("emailId", emailId);
136                res = myCmd.ExecuteNonQuery();
137            }
138            finally
139            {
140                myConn.Dispose();
141            }
142            return res;
143        }
144
145
146        /// <summary>
147        /// Insert a new record in table emailInserite
148        /// </summary>
149        /// <param name="newObj">The info about new EmailNewsletter, newObj.EmaiId and newObj.InsDate will be calculated in automatic</param>
150        /// <returns>The new object</returns>
151        [DataObjectMethod(DataObjectMethodType.Insert, false)]
152        public static EmailNewsletter Insert(EmailNewsletter newObj)
153        {
154            SqlConnection myConn = new SqlConnection();
155            SqlCommand myCmd = new SqlCommand();
156            string sSql;
157            EmailNewsletter result = new EmailNewsletter();
158
159            try
160            {
161                myConn.ConnectionString = Database.ConnString;
162                myConn.Open();
163                myCmd.Connection = myConn;
164
165                result.EmailId = getNextId();
166                result.InsDate = newObj.InsDate;
167                result.Email = newObj.Email;
168                result.Ip = newObj.Ip;
169
170                sSql = "INSERT INTO #__emailInserite(EmailId, InsDate, Email, Ip) "
171                + "VALUES(@EmailId, getdate(), @Email, @Ip) ";
172                myCmd.CommandText = Database.ParseSql(sSql);
173                myCmd.Parameters.AddWithValue("EmailId", result.EmailId);
174                myCmd.Parameters.AddWithValue("Email", result.Email);
175                myCmd.Parameters.AddWithValue("Ip", result.Ip);
176
177                myCmd.ExecuteNonQuery();
178            }
179            finally
180            {
181                myConn.Dispose();
182            }
183            return result;
184        }
185
186
187        private static int getNextId()
188        {
189            SqlConnection myConn = new SqlConnection();
190            SqlDataReader myRd = null;
191            SqlCommand myCmd = new SqlCommand();
192            string sSql;
193            int result = 0;
194
195            try
196            {
197                myConn.ConnectionString = Database.ConnString;
198                myConn.Open();
199                myCmd.Connection = myConn;
200
201                sSql = "SELECT max(EmailId) FROM #__emailInserite ";
202                myCmd.CommandText = Database.ParseSql(sSql);
203                myRd = myCmd.ExecuteReader();
204                if (myRd.Read())
205                {
206                    if (myRd[0] != DBNull.Value)
207                    {
208                        result = (int)myRd[0];
209                    }
210                }
211                myRd.Close();
212                result++;
213            }
214            finally
215            {
216                myConn.Dispose();
217            }
218            return result;
219        }
220    }
221}