public DataSet CheckUsernamePassword(string Username, string Password)
{
DBTool dbTool = new DBTool ();
string GetUserDetail = string.Format("SELECT * FROM tb_user WHERE username = '******' and [password] = '{1}' and status = '1'", Username, Password);
return dbTool.ExecuteDataSet(GetUserDetail);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.UserAgent.IndexOf("AppleWebKit") > 0)
{
Request.Browser.Adapters.Clear();
}
if (!IsPostBack)
{
DBTool dbTool = new DBTool();
Category category = new Category();
DataSet dsCategory = category.GetList();
DataTable dtCategory = dsCategory.Tables[0];
Menu1.Items.Add(new MenuItem("Library"));
foreach (DataRow dr in dtCategory.Rows)
{
string CatName = Convert.ToString(dr["name"]);
string CatId= Convert.ToString(dr["categoryid"]);
string SubCatId = dbTool.GetOneString("SELECT TOP 1 subcategoryid FROM mst_subcategory WHERE Categoryid =" + CatId);
MenuItem node = new MenuItem(CatName, "", "", "ViewCat.aspx?Catid=" + CatId + "&SubCatId="+SubCatId, "");
Menu1.Items[0].ChildItems.Add(node);
}
}
txtSearch.Attributes.Add("onkeypress", "return AnyInput_KeyDown(event,'" + btnSearch.ClientID + "')");
}
public Boolean Exists(string name, string categoryid)
{
string SQL = "SELECT COUNT(*) FROM mst_subcategory WHERE name = '" + name + "' AND categoryid =" + categoryid;
DBTool dbTool = new DBTool();
if (dbTool.GetCountRecord(SQL) > 0)
{
return true;
}
else
{
return false;
}
}
public Boolean Exists(string name)
{
string SQL = "SELECT COUNT(*) FROM mst_publisher WHERE name = '" + name + "'";
DBTool dbTool = new DBTool();
if (dbTool.GetCountRecord(SQL) > 0)
{
return true;
}
else
{
return false;
}
}
public DataSet GetList(string categoryid)
{
string SQL = "SELECT * FROM mst_subcategory WHERE categoryid = "+categoryid;
DBTool dbTool = new DBTool();
try
{
return dbTool.ExecuteDataSet(SQL);
}
catch (Exception)
{
return null;
}
}
public Boolean Add(string categoryid, string name, string remark, string status)
{
string SQL = string.Format("INSERT INTO mst_subcategory(categoryid,name,remark,status) VALUES"
+ " ('{0}','{1}','{2}','{3}')",categoryid, name, remark, status);
DBTool dbTool = new DBTool();
if (dbTool.RunCommand(SQL))
{
return true;
}
else
{
return false;
}
}
public Boolean Update(string name, string remark, string publisherid)
{
string SQL = string.Format("UPDATE mst_publisher SET name = '{0}' , remark = '{1}' WHERE"
+ " publisherid = '{3}'", name, remark, publisherid);
DBTool dbTool = new DBTool();
if (dbTool.RunCommand(SQL))
{
return true;
}
else
{
return false;
}
}
public DataSet GetList()
{
string SQL = "SELECT * FROM mst_category where status = 1 ORDER BY [name]";
DBTool dbTool = new DBTool();
try
{
return dbTool.ExecuteDataSet(SQL);
}
catch (Exception)
{
return null;
}
}
public Boolean Add(string name, string remark)
{
string SQL = string.Format("INSERT INTO mst_publisher(name,remark) VALUES ('{0}','{1}')",
name, remark);
DBTool dbTool = new DBTool();
if (dbTool.RunCommand(SQL))
{
return true;
}
else
{
return false;
}
}
public static string[] GetPublisherList(string prefixText, int count, string contextKey)
{
string GetPublisherList = "select name from mst_publisher where name like '" + prefixText + "%'";
DataSet ds = new DBTool().ExecuteDataSet(GetPublisherList);
DataTable dt = ds.Tables[0];
int rows = dt.Rows.Count;
string[] publisher = new string[rows];
for (int i = 0; i < rows; i++)
{
publisher[i] = Convert.ToString(dt.Rows[i][0]);
}
return publisher;
}
public string GetBookIdByISBN(string ISBN)
{
DBTool dbTool = new DBTool();
SqlConnection conn = dbTool.GetConnection();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_GetBookIdByISBN";
command.Parameters.Add("@isbn", SqlDbType.NVarChar).Value = ISBN;
string bookid = Convert.ToString( command.ExecuteScalar());
conn.Close();
return bookid;
}
public DataSet GetBookListByCategory(string SubCategoryId)
{
DBTool dbTool = new DBTool();
SqlConnection conn = dbTool.GetConnection();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_GetBookListByCategory";
command.Parameters.Add("@SubCategoryid", SqlDbType.Int).Value = Convert.ToInt32(SubCategoryId);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "sp_GetBookListByCategory");
conn.Close();
return ds;
}
public DataSet GetBookDetailByISBN(string ISBN)
{
DBTool dbTool = new DBTool();
SqlConnection conn = dbTool.GetConnection();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_GetBookDetailByISBN";
command.Parameters.Add("@isbn", SqlDbType.NVarChar).Value = ISBN;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "sp_GetBookDetail");
conn.Close();
return ds;
}
public DataSet GetBookDetailByID(string bookid)
{
DBTool dbTool = new DBTool();
SqlConnection conn = dbTool.GetConnection();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_GetBookDetailByID";
command.Parameters.Add("@bookid", SqlDbType.Int).Value = Convert.ToInt32(bookid);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "sp_GetBookDetail");
conn.Close();
return ds;
}
private void AddCounter(string bookid, string Userid)
{
DBTool dbTool = new DBTool();
Utility Util = new Utility();
string SQL = string.Format("INSERT INTO tb_counter(userid,bookid,load_date) VALUES ({0},{1},'{2}')",
Userid, bookid, Util.GetToday());
dbTool.RunCommand(SQL);
}
public DataSet ListInteriorBooks()
{
DBTool dbTool = new DBTool();
SqlConnection conn = dbTool.GetConnection();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_Book_GetThisMonthInteriorBook";
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "sp_Book_GetThisMonthInteriorBook");
conn.Close();
return ds;
}
public string GetSubcategoryByID(string bookid)
{
string SQL = "SELECT subcategoryid from tb_book where bookid='" + bookid + "'";
DBTool dbTool = new DBTool();
try
{
return dbTool.GetOneString(SQL);
}
catch (Exception)
{
return "";
}
}
public string GetLastestBookId()
{
DBTool dbTool = new DBTool();
SqlConnection conn = dbTool.GetConnection();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_GetLastestBookId";
string bookid = Convert.ToString(command.ExecuteScalar());
conn.Close();
return bookid;
}
public DataSet GetBookListByKeyword_MB(string Keyword)
{
DBTool dbTool = new DBTool();
SqlConnection conn = dbTool.GetConnection();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_GetBookListByKeyword_MB";
command.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = Keyword;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "sp_GetBookListByKeyword_MB");
conn.Close();
return ds;
}
public Boolean Update(string name, string remark, string status, string subcategoryid)
{
string SQL = string.Format("UPDATE mst_subcategory SET name = '{0}' , remark = '{1}', status = '{2}' WHERE"
+ " subcategoryid = '{3}'", name, remark, status, subcategoryid);
DBTool dbTool = new DBTool();
if (dbTool.RunCommand(SQL))
{
return true;
}
else
{
return false;
}
}
public string GetName(string subcategoryid)
{
string SQL = "SELECT name FROM mst_subcategory WHERE subcategoryid =" + subcategoryid;
DBTool dbTool = new DBTool();
try
{
return dbTool.GetOneString(SQL);
}
catch (Exception)
{
return "";
}
}
