public static ModelResult <DTO.News.Visitors> AddEditVisitor(DTO.News.Visitors oVisitor) { var oResult = new ModelResult <DTO.News.Visitors>(); try { using (var conn = new SqlConnection(DbConnection.ConnectionString)) { using (var cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "SP_VisitorAddEdit"; cmd.CommandType = CommandType.StoredProcedure; if (oVisitor.Id > 0) { cmd.Parameters.AddWithValue("@Id", oVisitor.Id); } if (!string.IsNullOrEmpty(oVisitor.Name)) { cmd.Parameters.AddWithValue("@Name", oVisitor.Name); } if (!string.IsNullOrEmpty(oVisitor.Avatar)) { cmd.Parameters.AddWithValue("@Avatar", oVisitor.Avatar); } if (!string.IsNullOrEmpty(oVisitor.Email)) { cmd.Parameters.AddWithValue("@Email", oVisitor.Email); } if (!string.IsNullOrEmpty(oVisitor.Pass)) { cmd.Parameters.AddWithValue("@Pass", Common.Md5(oVisitor.Pass)); } if (!string.IsNullOrEmpty(oVisitor.ResetPassToken)) { cmd.Parameters.AddWithValue("@ResetPassToken", oVisitor.ResetPassToken); } if (oVisitor.IsApproved.HasValue) { cmd.Parameters.AddWithValue("@IsApproved", oVisitor.IsApproved.Value); } conn.Open(); oVisitor.Id = Convert.ToInt32(cmd.ExecuteScalar()); oResult.HasResult = true; oResult.Results = oVisitor; } //Command } // Connection } catch (Exception ex) { oResult.Message = ex.Message; oResult.HasResult = false; } return(oResult); }
//***************************************// public static ModelResult <List <DTO.News.Visitors> > VisitorsGet(DTO.News.Visitors oVisitor) { var oResult = new ModelResult <List <DTO.News.Visitors> >(); var conn = new SqlConnection(DbConnection.ConnectionString); try { using (conn) { using (var cmd = new SqlCommand()) { cmd.Connection = conn; #region SQLCOMMAND Builder var command = ""; command = @"SELECT * FROM Visitors WHERE 1=1 "; if (oVisitor.Id > 0) { command += " AND Id = @Id"; cmd.Parameters.AddWithValue("@Id", oVisitor.Id); } if (!string.IsNullOrEmpty(oVisitor.Email)) { command += " AND Email = @Email"; cmd.Parameters.AddWithValue("@Email", oVisitor.Email); } if (!string.IsNullOrEmpty(oVisitor.Name)) { command += " And Name Like @Name "; cmd.Parameters.AddWithValue("@Name", "%" + oVisitor.Name + "%"); } if (!oVisitor.IsList) { command += " order by @SortCol @SortType OFFSET(@Page - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY"; command = command.Replace("@SortCol", oVisitor.SortCol); command = command.Replace("@SortType", oVisitor.SortType); command = command.Replace("@Page", oVisitor.Page.ToString()); command = command.Replace("@RowsPerPage", oVisitor.RowPerPage.ToString()); } #endregion if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.CommandText = command; SqlDataReader reader = cmd.ExecuteReader(); var lstUsers = new List <DTO.News.Visitors>(); if (reader.HasRows) { while (reader.Read()) { #region User Account Parms var obUsers = new DTO.News.Visitors(); obUsers.Id = Convert.ToInt32(reader["Id"]); if (reader["Name"] != DBNull.Value) { obUsers.Name = Convert.ToString(reader["Name"]); } if (reader["Email"] != DBNull.Value) { obUsers.Email = Convert.ToString(reader["Email"]); } if (reader["Pass"] != DBNull.Value) { obUsers.Pass = Convert.ToString(reader["Pass"]); } if (reader["IsApproved"] != DBNull.Value) { obUsers.IsApproved = Convert.ToBoolean(reader["IsApproved"]); } if (reader["Avatar"] != DBNull.Value) { obUsers.Avatar = Convert.ToString(reader["Avatar"]); } if (reader["ResetPassToken"] != DBNull.Value) { obUsers.ResetPassToken = Convert.ToString(reader["ResetPassToken"]); } #endregion lstUsers.Add(obUsers); } } int count = 0; if (!oVisitor.IsList) { using (SqlConnection connCount = new SqlConnection(DbConnection.ConnectionString)) { using (var cmdCount = new SqlCommand()) { cmdCount.Connection = connCount; command = @"SELECT COUNT(1) FROM Visitors WHERE 1=1 AND IsDeleted = 0"; if (oVisitor.Id > 0) { command += " AND Id = @Id"; cmdCount.Parameters.AddWithValue("@Id", oVisitor.Id); } if (!string.IsNullOrEmpty(oVisitor.Name)) { command += " And Name Like @Name "; cmdCount.Parameters.AddWithValue("@Name", "%" + oVisitor.Name + "%"); } cmdCount.CommandText = command; if (connCount.State != ConnectionState.Open) { connCount.Open(); } count = Convert.ToInt32(cmdCount.ExecuteScalar()); connCount.Close(); } } } if (lstUsers.Count > 0) { oResult.HasResult = true; oResult.Results = lstUsers; oResult.RowCount = count; } } } } catch (Exception ex) { oResult.Message = ex.Message; oResult.HasResult = false; } finally { conn.Close(); } return(oResult); }
//private static RijndaelCrypt rC = new RijndaelCrypt("ambiabhhm2883772"); public static ModelResult <List <DTO.News.Visitors> > VisitorLogin(DTO.Account.LoginModel oVisitor) { using (SqlConnection conn = new SqlConnection(DbConnection.ConnectionString)) { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; //var command = @"select * from MD_Media where isDeleted = 0 "; var command = @"select * from dbo.Visitors a WHERE (Email=@Email OR Name=@Email) and Pass=@Pass COLLATE SQL_Latin1_General_CP1_CS_AS AND IsApproved=1"; if (!string.IsNullOrEmpty(oVisitor.Email)) { cmd.Parameters.AddWithValue("@Email", oVisitor.Email); } if (!string.IsNullOrEmpty(oVisitor.Password)) { cmd.Parameters.AddWithValue("@Pass", Common.Md5(oVisitor.Password)); } cmd.CommandText = command; conn.Open(); var reader = cmd.ExecuteReader(); var lstVisitor = new List <DTO.News.Visitors>(); var oResult = new ModelResult <List <DTO.News.Visitors> >(); if (reader.HasRows) { while (reader.Read()) { var opVisitor = new DTO.News.Visitors(); if (reader["Avatar"] != DBNull.Value) { opVisitor.Avatar = Convert.ToString(reader["Avatar"]); } if (reader["Email"] != DBNull.Value) { opVisitor.Email = Convert.ToString(reader["Email"]); } opVisitor.Id = Convert.ToInt32(reader["Id"]); if (reader["Name"] != DBNull.Value) { opVisitor.Name = Convert.ToString(reader["Name"]); } if (reader["Pass"] != DBNull.Value) { opVisitor.Pass = Convert.ToString(reader["Pass"]); } if (reader["IsApproved"] != DBNull.Value) { opVisitor.IsApproved = Convert.ToBoolean(reader["IsApproved"]); } lstVisitor.Add(opVisitor); } } if (lstVisitor.Count > 0) { oResult.HasResult = true; oResult.Results = lstVisitor; //oResult.RowCount = count; } return(oResult); } } }