public static bool addUser(User user)
{
int result = 0;
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
string str = "insert into Users (name, password, email, mobile, street, city, landmark, pincode, dob)";
str += "values (@name, @password, @email, @mob, @street, @city, @landmark, @pincode, @dob);";
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue("@name", user.name);
cmd.Parameters.AddWithValue("@password", user.password);
cmd.Parameters.AddWithValue("@email", user.email);
cmd.Parameters.AddWithValue("@mob", user.mobile);
cmd.Parameters.AddWithValue("@street", user.street);
cmd.Parameters.AddWithValue("@city", user.city);
cmd.Parameters.AddWithValue("@landmark", user.landmark);
cmd.Parameters.AddWithValue("@pincode", user.pincode);
cmd.Parameters.AddWithValue("@dob", user.dob);
con.Open();
result = cmd.ExecuteNonQuery();
}
return(result > 0 ? true : false);
}
public static List <Cart> getCartDetail(string userId)
{
List <Cart> allCategories = new List <Cart>();
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String selectAllStr = "select * from Cart where User_id = @userId;";
SqlCommand cmd = new SqlCommand(selectAllStr, con);
con.Open();
cmd.Parameters.AddWithValue("@userId", userId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Cart p = new Cart();
p.id = Convert.ToInt32(reader["Id"]);
p.user_id = Convert.ToInt32(reader["User_id"]);
p.prod_id = Convert.ToInt32(reader["Product_id"]);
allCategories.Add(p);
}
}
return(allCategories);
}
public static List <TypePoco> getAllTypes()
{
List <TypePoco> allTypes = new List <TypePoco>();
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String selectAllStr = "select * from Product_Type";
SqlCommand cmd = new SqlCommand(selectAllStr, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TypePoco p = new TypePoco();
p.id = Convert.ToInt32(reader["Id"]);
p.typeDesc = Convert.ToString(reader["Type_desc"]);
allTypes.Add(p);
}
}
return(allTypes);
}
public static List <Language> getAllLanguages()
{
List <Language> allLanguages = new List <Language>();
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String selectAllStr = "select * from Product_Language";
SqlCommand cmd = new SqlCommand(selectAllStr, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Language p = new Language();
p.id = Convert.ToInt32(reader["Id"]);
p.lang = Convert.ToString(reader["Lang_desc"]);
allLanguages.Add(p);
}
}
return(allLanguages);
}
public static List <Category> getAllCategories()
{
List <Category> allCategories = new List <Category>();
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String selectAllStr = "select * from Product_Category";
SqlCommand cmd = new SqlCommand(selectAllStr, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Category p = new Category();
p.id = Convert.ToInt32(reader["Id"]);
p.category = Convert.ToString(reader["Category_desc"]);
allCategories.Add(p);
}
}
return(allCategories);
}
public static List <Product> getAllProductsByType(string typeId)
{
List <Product> allProducts = new List <Product>();
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String insertStr = "select * from Products where type_id = @id";
SqlCommand cmd = new SqlCommand(insertStr, con);
con.Open();
cmd.Parameters.AddWithValue("@id", typeId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Product p = new Product();
p.id = Convert.ToInt32(reader["Id"]);
p.type_id = Convert.ToInt32(reader["type_id"]);
p.language_id = Convert.ToInt32(reader["language_id"]);
p.category_id = Convert.ToInt32(reader["category_id"]);
p.title = Convert.ToString(reader["title"]);
p.price = Convert.ToDecimal(reader["price"]);
p.sellingPrice = Convert.ToDecimal(reader["selling_price"]);
p.specialPrice = Convert.ToDecimal(reader["special_price"]);
p.saleFromDate = Convert.ToString(reader["special_price_from_date"]);
p.saleToDate = Convert.ToString(reader["special_price_to_date"]);
p.daysOfSale = Convert.ToInt32(reader["days_of_sale"]);
p.shortDescription = Convert.ToString(reader["short_description"]);
p.longDescription = Convert.ToString(reader["long_description"]);
p.authors = Convert.ToString(reader["author"]);
p.releaseDate = Convert.ToString(reader["release_date"]);
p.isRentable = Convert.ToBoolean(reader["is_rentable"]);
p.isInLibrary = Convert.ToBoolean(reader["is_in_library"]);
p.rentAmount = Convert.ToDecimal(reader["rent_amount"]);
p.minimumRentDays = Convert.ToInt32(reader["min_rent_days"]);
p.publisher = Convert.ToString(reader["publisher"]);
p.imagePath = Convert.ToString(reader[" image_path"]);
allProducts.Add(p);
}
}
return(allProducts);
}
public static bool addProduct(Product p)
{
//Console.WriteLine("Got Request");
//string conStr = ConfigurationManager.ConnectionStrings["bookwormDB"].ConnectionString;
int result = 0;
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String insertStr = "insert into Products (type_id, language_id, category_id, title, price, selling_price, special_price, special_price_from_date, special_price_to_date, days_of_sale," +
"short_description, long_description, author, release_date, is_rentable, is_in_library, rent_amount, min_rent_days, publisher, [ image_path])" +
"values (@typeId, @langID, @catID, @title, @price, @sell, @splPrice, @fromDate, @toDate, @daysOfSale, @shortDesc, @longDesc, @author, @releaseDate, @isRentable, @isInLib, @rentAmount, @minRentDays, @publisher, @imagePath)";
SqlCommand cmd = new SqlCommand(insertStr, con);
cmd.Parameters.AddWithValue("@typeId", p.type_id);
cmd.Parameters.AddWithValue("@langID", p.language_id);
cmd.Parameters.AddWithValue("@catID", p.category_id);
cmd.Parameters.AddWithValue("@title", p.title);
cmd.Parameters.AddWithValue("@price", p.price);
cmd.Parameters.AddWithValue("@sell", p.sellingPrice);
cmd.Parameters.AddWithValue("@splPrice", p.specialPrice);
cmd.Parameters.AddWithValue("@fromDate", DateTime.Parse(p.saleFromDate));
cmd.Parameters.AddWithValue("@toDate", DateTime.Parse(p.saleToDate));
cmd.Parameters.AddWithValue("@daysOfSale", p.daysOfSale);
cmd.Parameters.AddWithValue("@shortDesc", p.shortDescription);
cmd.Parameters.AddWithValue("@longDesc", p.longDescription);
cmd.Parameters.AddWithValue("@author", p.authors);
cmd.Parameters.AddWithValue("@releaseDate", DateTime.Parse(p.releaseDate));
cmd.Parameters.AddWithValue("@isRentable", p.isRentable);
cmd.Parameters.AddWithValue("@isInLib", p.isInLibrary);
cmd.Parameters.AddWithValue("@rentAmount", p.rentAmount);
cmd.Parameters.AddWithValue("@minRentDays", p.minimumRentDays);
cmd.Parameters.AddWithValue("@publisher", p.publisher);
cmd.Parameters.AddWithValue("@imagePath", p.imagePath);
con.Open();
result = cmd.ExecuteNonQuery();
}
return(result > 0 ? true : false);
}
public static Boolean removeFromCart(string userId, string productId)
{
int result = 0;
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String insertStr = "delete from Cart where User_id = @user_id and Product_id = @prod_id;";
SqlCommand cmd = new SqlCommand(insertStr, con);
cmd.Parameters.AddWithValue("@user_id", userId);
cmd.Parameters.AddWithValue("@prod_id", productId);
con.Open();
result = cmd.ExecuteNonQuery();
}
return(result > 0 ? true : false);
}
public static Boolean addToCart(string userId, string productId)
{
int result = 0;
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String insertStr = "insert into Cart (User_id, Product_id) values(@user_id, @prod_id)";
SqlCommand cmd = new SqlCommand(insertStr, con);
cmd.Parameters.AddWithValue("@user_id", userId);
cmd.Parameters.AddWithValue("@prod_id", productId);
con.Open();
result = cmd.ExecuteNonQuery();
}
return(result > 0 ? true : false);
}
public static bool authUser(User user)
{
Object result = 0;
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
string str = "select count(*) from Users where email = @email and password = @password;";
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue("@password", user.password);
cmd.Parameters.AddWithValue("@email", user.email);
con.Open();
result = cmd.ExecuteScalar();
}
return(Int32.Parse(result.ToString()) > 0 ? true : false);
}
public static TypePoco getTypeById(string searchId)
{
TypePoco result = new TypePoco();
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String selectAllStr = "select * from Product_Type where id = @id";
SqlCommand cmd = new SqlCommand(selectAllStr, con);
cmd.Parameters.AddWithValue("@id", searchId);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result.id = Convert.ToInt32(reader["Id"]);
result.typeDesc = Convert.ToString(reader["Type_desc"]);
}
}
return(result);
}
public static Language getLanguageById(string searchId)
{
Language result = new Language();
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String selectAllStr = "select * from Product_Language where id = @id";
SqlCommand cmd = new SqlCommand(selectAllStr, con);
cmd.Parameters.AddWithValue("@id", searchId);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result.id = Convert.ToInt32(reader["Id"]);
result.lang = Convert.ToString(reader["Lang_desc"]);
}
}
return(result);
}
public static Category getCategoryById(string searchId)
{
Category result = new Category();
using (SqlConnection con = GetSqlConnection.getSqlConnection())
{
String selectAllStr = "select * from Product_Category where id = @id";
SqlCommand cmd = new SqlCommand(selectAllStr, con);
cmd.Parameters.AddWithValue("@id", searchId);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result.id = Convert.ToInt32(reader["Id"]);
result.category = Convert.ToString(reader["Category_desc"]);
}
}
return(result);
}
