public List <Publication> GetPublishers()
{
string sql = "Select Id, name, StatusId, Country, [State], City, Category from Publisher Where UserId = @userId";
DataSet ds = _db.GetDataSetFromSql(sql, new SqlParameter("@userId", CurrentUser.Id));
List <Publication> publishers = new List <Publication>();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
publishers.Add(new Publication()
{
Name = row.Field <string>("name"),
StatusId = row.Field <int>("StatusId"),
Id = row.Field <int>("Id"),
SelectedCountry = row.Field <int>("Country"),
SelectedState = row.Field <int>("State"),
SelectedCity = row.Field <int>("City"),
//SelectedCategory = row.Field<int>("Category")
});
}
}
return(publishers);
}
public User UserContext(string token)
{
string sql = @"Select U.[Id]
,[username]
--,[password]
,[email]
,[type]
,[StatusId]
,UT.Token
from UserToken UT
INNER JOIN [User] U ON UT.UserId = U.Id Where UT.Token = @token" ;
DataSet ds = _db.GetDataSetFromSql(sql, new SqlParameter("@token", token));
User user = new User();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
user = new User()
{
Username = row.Field <string>("username"),
Email = row.Field <string>("email"),
Type = row.Field <string>("type"),
StatusId = row.Field <int>("StatusId"),
Id = row.Field <int>("Id"),
Token = row.Field <string>("Token")
};
}
}
return(user);
}
public List <City> GetCities(int stateId)
{
string sql = "Select Id, Name, StateId from Cities Where StateId = " + stateId;
DataSet ds = _db.GetDataSetFromSql(sql);
List <City> cities = new List <City>();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
cities.Add(new City()
{
Name = row.Field <string>("Name"),
Id = row.Field <int>("Id"),
StateId = row.Field <int>("StateId")
});
}
}
return(cities);
}
public News GetNewsById(int Id)
{
string sql = @"SELECT N.[Id]
,[Title]
,[PublishDate]
,[BannerUrl]
,[Body]
,Publication = P.name
,Category = C.name
FROM [News] N
INNER JOIN Category C ON C.Id = N.Category
INNER JOIN Publisher P ON P.Id = N.Publication
Where N.[Id] = @newsId
ORDER BY PublishDate DESC" ;
DataSet ds = _db.GetDataSetFromSql(sql, new SqlParameter("@newsId", Id));
News news = new News();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < 1; i++)
{
var row = ds.Tables[0].Rows[i];
news = new News()
{
Title = row.Field <string>("Title"),
BannerUrl = row.Field <string>("BannerUrl"),
Body = row.Field <string>("Body"),
Id = row.Field <int>("Id"),
PublicationName = row.Field <string>("Publication"),
CategoryName = row.Field <string>("Category")
};
}
}
return(news);
}
public List <Category> GetCategories()
{
string sql = "Select Id, name, StatusId from Category";
DataSet ds = _db.GetDataSetFromSql(sql);
List <Category> categories = new List <Category>();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
categories.Add(new Category()
{
Name = row.Field <string>("name"),
StatusId = row.Field <int>("StatusId"),
Id = row.Field <int>("Id")
});
}
}
return(categories);
}
