public static ModelResult <DTO.News.Media> MediaSave(DTO.News.Media oMedia) { var oResult = new ModelResult <DTO.News.Media>(); var conn = new SqlConnection(DbConnection.ConnectionString); try { using (conn) { using (var cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.CommandText = "SP_MediaSave"; if (oMedia.Id >= 0) { cmd.Parameters.AddWithValue("@Id", oMedia.Id); } if (!string.IsNullOrEmpty(oMedia.Caption)) { cmd.Parameters.AddWithValue("@Caption", oMedia.Caption); } cmd.Parameters.AddWithValue("@MediaType", oMedia.MediaType); if (!string.IsNullOrEmpty(oMedia.FilePath)) { cmd.Parameters.AddWithValue("@FilePath", oMedia.FilePath); } cmd.Parameters.AddWithValue("@IsInMainPage", oMedia.IsInMainPage); if (!string.IsNullOrEmpty(oMedia.ExternalLink)) { cmd.Parameters.AddWithValue("@ExternalLink", oMedia.ExternalLink); } conn.Open(); oMedia.Id = Convert.ToInt32(cmd.ExecuteScalar()); oResult.HasResult = true; oResult.Results = oMedia; } } } catch (Exception ex) { oResult.Message = ex.Message; } finally { conn.Close(); } return(oResult); }
public static ModelResult <List <DTO.News.Media> > MediaInMainPageGet(DTO.News.Media oMedia) { var oResult = new ModelResult <List <DTO.News.Media> >(); var conn = new SqlConnection(DbConnection.ConnectionString); try { using (conn) { using (var cmd = new SqlCommand()) { cmd.Connection = conn; #region SQLCOMMAND Builder var command = @"SELECT TBL1.*, TBL3.NameEn TypeNameEn,TBL3.NameAr TypeNameAr FROM Media TBL1 JOIN Constant TBL3 ON TBL3.Id = TBL1.MediaType WHERE TBL1.IsDeleted = 0 AND TBL1.IsInMainPage = 1"; if (oMedia.MediaType > 0) { command += " AND TBL1.MediaType = @MediaType"; cmd.Parameters.AddWithValue("@MediaType", oMedia.MediaType); } if (!oMedia.IsList) { command += " order by @SortCol @SortType OFFSET(@Page - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY"; command = command.Replace("@SortCol", oMedia.SortCol); command = command.Replace("@SortType", oMedia.SortType); command = command.Replace("@Page", oMedia.Page.ToString()); command = command.Replace("@RowsPerPage", oMedia.RowPerPage.ToString()); } #endregion if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.CommandText = command; SqlDataReader reader = cmd.ExecuteReader(); var lstMedia = new List <DTO.News.Media>(); if (reader.HasRows) { while (reader.Read()) { var obMedia = new DTO.News.Media(); obMedia.MediaType = Convert.ToInt32(reader["MediaType"]); if (reader["TypeName"] != DBNull.Value) { obMedia.OMediaType.Name = Convert.ToString(reader["TypeName"]); } if (reader["Caption"] != DBNull.Value) { obMedia.Caption = Convert.ToString(reader["Caption"]); } obMedia.FilePath = Convert.ToString(reader["FilePath"]); obMedia.ExternalLink = Convert.ToString(reader["ExternalLink"]); obMedia.IsInMainPage = Convert.ToBoolean(reader["IsInMainPage"]); lstMedia.Add(obMedia); } } int count = 0; if (!oMedia.IsList) { using (SqlConnection connCount = new SqlConnection(DbConnection.ConnectionString)) { using (var cmdCount = new SqlCommand()) { cmdCount.Connection = connCount; command = @"SELECT COUNT(1) FROM Media WHERE 1=1 AND IsDeleted = 0"; if (oMedia.Id > 0) { command += " AND Id = @Id"; cmdCount.Parameters.AddWithValue("@Id", oMedia.Id); } if (oMedia.MediaType > 0) { command += " AND MediaType = @MediaType"; cmdCount.Parameters.AddWithValue("@MediaType", oMedia.MediaType); } cmdCount.CommandText = command; if (connCount.State != ConnectionState.Open) { connCount.Open(); } count = Convert.ToInt32(cmdCount.ExecuteScalar()); connCount.Close(); } } } if (lstMedia.Count > 0) { oResult.HasResult = true; oResult.Results = lstMedia; oResult.RowCount = count; } } } } //catch (Exception ex) //{ // oResult.Message = ex.Message; // oResult.HasResult = false; //} finally { conn.Close(); } return(oResult); }