DALCategoryMaster.cs
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DALCategoryMaster
/// </summary>
public class DALCategoryMaster : Connection
{
SqlConnection sqlcon = new SqlConnection();
public DALCategoryMaster()
{
sqlcon = FetchConnection();
}
public DataTable SelectCategory()
{
sqlcon = FetchConnection();
DataTable dt = new DataTable();
string cate = "select * from tbl_category";
SqlDataAdapter da = new SqlDataAdapter(cate, sqlcon);
da.SelectCommand.CommandType = CommandType.Text;
da.Fill(dt);
return dt;
}
public void insert_CategoryMaster(BALCategoryMaster bal)
{
string insert = "insert into tbl_category (category) values (@category)";
SqlCommand sqlCmd = new SqlCommand(insert, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter paraCategoryName = new SqlParameter("@Category", SqlDbType.VarChar);
paraCategoryName.Value = bal.CategoryName;
sqlCmd.Parameters.Add(paraCategoryName);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
sqlCmd.ExecuteNonQuery();
sqlcon.Close();
}
public DataTable Select_CategoryMaster()
{
string str = "select * from tbl_category order by id asc";
SqlCommand sqlCmd = new SqlCommand(str, sqlcon);
sqlCmd.CommandType = CommandType.Text;
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(str, sqlcon);
adp.Fill(ds);
sqlcon.Close();
return ds.Tables[0];
}
public DataTable Select_CategoryMasterSearch(string search)
{
string str = "select * from tbl_category where category like '%[" + search + "]%' order by id asc";
SqlCommand sqlCmd = new SqlCommand(str, sqlcon);
sqlCmd.CommandType = CommandType.Text;
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(str, sqlcon);
adp.Fill(ds);
sqlcon.Close();
return ds.Tables[0];
}
public void delete_CategoryMaster(BALCategoryMaster bal)
{
string delete = "delete from tbl_category where id=@CategoryId";
SqlCommand sqlCmd = new SqlCommand(delete, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter CategoryId = new SqlParameter("@CategoryId", SqlDbType.Int);
CategoryId.Value = bal.CategoryId;
sqlCmd.Parameters.Add(CategoryId);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
sqlCmd.ExecuteNonQuery();
sqlcon.Close();
}
public void update_CategoryMaster(BALCategoryMaster bal)
{
string str = "update tbl_category set category=@CategoryName where id=@CategoryId";
SqlCommand sqlCmd = new SqlCommand(str, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter paraCategoryName = new SqlParameter("@CategoryName", SqlDbType.VarChar);
paraCategoryName.Value = bal.CategoryName;
sqlCmd.Parameters.Add(paraCategoryName);
SqlParameter paraCategoryId = new SqlParameter("@CategoryId", SqlDbType.Int);
paraCategoryId.Value = bal.CategoryId;
sqlCmd.Parameters.Add(paraCategoryId);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
try
{
sqlCmd.ExecuteNonQuery();
}
catch (SqlException xe)
{
}
sqlcon.Close();
}
public DataTable select_CategoryMasterByCategoryId(BALCategoryMaster bal)
{
string strcat = "select * from tbl_category where id=@CategoryId";
SqlCommand sqlCmd = new SqlCommand(strcat, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter CategoryId = new SqlParameter("@CategoryId", SqlDbType.Int);
CategoryId.Value = bal.CategoryId;
sqlCmd.Parameters.Add(CategoryId);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(sqlCmd);
adp.Fill(dt);
sqlcon.Close();
return dt;
}
}
DALProductMaster.cs
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DALProductMaster
/// </summary>
public class DALProductMaster : Connection
{
SqlConnection sqlcon = new SqlConnection();
public DALProductMaster()
{
sqlcon = FetchConnection();
}
public void insert_ProductMaster(BALProductMaster bal)
{
string insert = "insert into tbl_product (productname,productcode,cat_id,image,detail) values (@productname,@productcode,@cat_id,@image,@detail)";
SqlCommand sqlCmd = new SqlCommand(insert, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter paraCategoryName = new SqlParameter("@productname", SqlDbType.VarChar);
paraCategoryName.Value = bal.ProductName;
sqlCmd.Parameters.Add(paraCategoryName);
SqlParameter paraCategorycode = new SqlParameter("@productcode", SqlDbType.VarChar);
paraCategorycode.Value = bal.ProductCode;
sqlCmd.Parameters.Add(paraCategorycode);
SqlParameter cat_id = new SqlParameter("@cat_id", SqlDbType.Int);
cat_id.Value = bal.CategoryId;
sqlCmd.Parameters.Add(cat_id);
SqlParameter image = new SqlParameter("@image", SqlDbType.VarChar);
image.Value = bal.Image;
sqlCmd.Parameters.Add(image);
SqlParameter detail = new SqlParameter("@detail", SqlDbType.VarChar);
detail.Value = bal.Detail;
sqlCmd.Parameters.Add(detail);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
sqlCmd.ExecuteNonQuery();
sqlcon.Close();
}
public DataTable Select_ProductMaster()
{
string str = "select * from tbl_product order by id desc";
SqlCommand sqlCmd = new SqlCommand(str, sqlcon);
sqlCmd.CommandType = CommandType.Text;
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(str, sqlcon);
adp.Fill(ds);
sqlcon.Close();
return ds.Tables[0];
}
public void delete_ProductMaster(BALProductMaster bal)
{
string delete = "delete from tbl_Product where id=@ProductID";
SqlCommand sqlCmd = new SqlCommand(delete, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter ProductID = new SqlParameter("@ProductID", SqlDbType.Int);
ProductID.Value = bal.ProductID;
sqlCmd.Parameters.Add(ProductID);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
sqlCmd.ExecuteNonQuery();
sqlcon.Close();
}
public void updateR_ProductMaster(BALProductMaster bal)
{
string str = "update tbl_product set productname=@productname,productcode=@productcode,cat_id=@cat_id,image=@image,detail=@detail where id=@ProductID";
SqlCommand sqlCmd = new SqlCommand(str, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter productid = new SqlParameter("@ProductID", SqlDbType.Int);
productid.Value = bal.ProductID;
sqlCmd.Parameters.Add(productid);
SqlParameter paraCategoryName = new SqlParameter("@productname", SqlDbType.VarChar);
paraCategoryName.Value = bal.ProductName;
sqlCmd.Parameters.Add(paraCategoryName);
SqlParameter paraCategorycode = new SqlParameter("@productcode", SqlDbType.VarChar);
paraCategorycode.Value = bal.ProductCode;
sqlCmd.Parameters.Add(paraCategorycode);
SqlParameter cat_id = new SqlParameter("@cat_id", SqlDbType.Int);
cat_id.Value = bal.CategoryId;
sqlCmd.Parameters.Add(cat_id);
SqlParameter image = new SqlParameter("@image", SqlDbType.VarChar);
image.Value = bal.Image;
sqlCmd.Parameters.Add(image);
SqlParameter detail = new SqlParameter("@detail", SqlDbType.VarChar);
detail.Value = bal.Detail;
sqlCmd.Parameters.Add(detail);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
try
{
sqlCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
}
sqlcon.Close();
}
public DataTable select_ProductMasterByProductId(BALProductMaster bal)
{
string strcat = "select * from tbl_product where id=@ProductId";
SqlCommand sqlCmd = new SqlCommand(strcat, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter CategoryId = new SqlParameter("@ProductId", SqlDbType.Int);
CategoryId.Value = bal.ProductID;
sqlCmd.Parameters.Add(CategoryId);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(sqlCmd);
adp.Fill(dt);
sqlcon.Close();
return dt;
}
public void Image_remove(BALProductMaster bal)
{
string updateimage = "update tbl_product set image=@image where id=@productid";
SqlCommand sqlCmd = new SqlCommand(updateimage, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter ProductID = new SqlParameter("@productid", SqlDbType.VarChar);
ProductID.Value = bal.ProductID;
sqlCmd.Parameters.Add(ProductID);
SqlParameter image = new SqlParameter("@image", SqlDbType.VarChar);
image.Value = "";
sqlCmd.Parameters.Add(image);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
sqlCmd.ExecuteNonQuery();
sqlcon.Close();
}
public DataTable Select_ProductMasterSearch(string search)
{
string str = "select * from tbl_product where productname like '%[" + search + "]%' order by id asc";
SqlCommand sqlCmd = new SqlCommand(str, sqlcon);
sqlCmd.CommandType = CommandType.Text;
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(str, sqlcon);
adp.Fill(ds);
sqlcon.Close();
return ds.Tables[0];
}
public DataTable selectR_ProductMasterByCategoryId(BALCategoryMaster bal)
{
string strcat = "select * from tbl_product where cate_id=@CategoryID";
SqlCommand sqlCmd = new SqlCommand(strcat, sqlcon);
sqlCmd.CommandType = CommandType.Text;
SqlParameter CategoryId = new SqlParameter("@CategoryID", SqlDbType.Int);
CategoryId.Value = bal.CategoryId;
sqlCmd.Parameters.Add(CategoryId);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(sqlCmd);
adp.Fill(dt);
sqlcon.Close();
return dt;
}
}
