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 <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 <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);
}
//功能函数，执行数据库更新语句
private void UpdateByGoodsID()
{
if (!InfoCheck())
{
MessageBox.Show("信息错误:信息空缺或错误！");
return;
}
SqlConnection sqlConnection = new GetSqlConnection().GetCon();//连接数据库
string sql = "update dbo.tb_JhGoodsInfo set ";
sql += "EmpID ='" + txtEmpID.Text + "' ,DepotName='" + txtDepotName.Text + "',JhCompName='" + txtJhCompName.Text;
sql += "',GoodsName='" + txtGoodsName.Text + "',GoodsNum=" + txtGoodsNum.Text + ",GoodsUnit='" + txtGoodsUnit.Text;
sql += "',GoodsJhPrice='" + txtGoodsJhPrice.Text + "',GoodsSellPrice='" + txtGoodsSellPrice.Text;
sql += "',GoodsNeedPrice='" + txtGoodsNeedPrice.Text + "',GoodsNoPrice='" + txtGoodsNoPrice.Text;
sql += "',GoodsRemark='" + txtGoodsRemark.Text + "',GoodsTime='" + txtGoodsTime.Value + "',Flag=0";
sql += " where GoodsID='" + txtGoodsID.Text + "'";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
try
{ int r = sqlCommand.ExecuteNonQuery(); }
catch (SqlException)
{
MessageBox.Show("插入信息错误，请检查是否商品编号重复");
//打开查询界面
}
sqlConnection.Close();
}
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 <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);
}
private void BackupButton_Click(object sender, EventArgs e)
{
try
{
if (filePathBox.Text != "" && fileNameBox.Text != "")
{
SqlConnection con = new GetSqlConnection().GetCon();//连接数据库
string strBacl = "backup database db_CSManage to disk='" + filePathBox.Text.Trim() + "\\" + fileNameBox.Text.Trim() + ".bak'";
SqlCommand cmd = new SqlCommand(strBacl, con);
if (cmd.ExecuteNonQuery() != 0)
{
MessageBox.Show("数据备份成功！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show("数据备份失败！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("请填写备份的正确位置及文件名！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
private void buttonQuery_Click(object sender, EventArgs e)
{
listView1.Items.Clear();
SqlConnection sqlConnection = new GetSqlConnection().GetCon();
string sql = "select * from dbo.tb_JhGoodsInfo where ";
switch (txtQueryKind.SelectedIndex)
{
case 0: { sql += "GoodsID LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
case 1: { sql += "GoodsName LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
case 2: { sql += "JhCompName LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
case 3: { sql += "EmpID LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
case 4: { sql += "DepotName LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
default: { break; }
}
try
{
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
SqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
ListViewItem item = new ListViewItem((string)dataReader["GoodsID"]);
item.SubItems.Add((string)dataReader["GoodsName"]);
item.SubItems.Add((string)dataReader["JhCompName"]);
item.SubItems.Add((string)dataReader["EmpID"]);
item.SubItems.Add((string)dataReader["DepotName"]);
/*item.SubItems.Add(dataReader["GoodsNum"].ToString());
* item.SubItems.Add((string)dataReader["GoodsUnit"]);
* item.SubItems.Add((string)dataReader["GoodsJhPrice"]);
* item.SubItems.Add((string)dataReader["GoodsSellPrice"]);
* item.SubItems.Add((string)dataReader["GoodsNeedPrice"]);
* item.SubItems.Add((string)dataReader["GoodsNoPrice"]);*/
listView1.Items.Add(item);
}
if (!dataReader.HasRows)
{
ListViewItem item = new ListViewItem("无查询结果");
listView1.Items.Add(item);
}
dataReader.Close();
sqlConnection.Close();
}
catch (SqlException)
{
MessageBox.Show("错误！\n输入正确类型！");
}
}
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);
}
//功能函数，执行数据库删除语句
private void DeleteByGoodsID()
{
try
{
SqlConnection sqlConnection = new GetSqlConnection().GetCon();//连接数据库
string sql = "delete from dbo.tb_JhGoodsInfo where GoodsID=" + txtGoodsID.Text;
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.Prepare();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
catch (SqlException)
{
MessageBox.Show("数据库连接失败！");
}
}
//功能函数，随着数据库语句的执行，刷新DataGridView
private void UpdateDataGridView()
{
//增加对当前行的锁定。
int currentRow = -1;
if (dataGridView1.SelectedRows.Count != 0)
{
currentRow = dataGridView1.CurrentRow.Index;
}
try
{
SqlConnection sqlConnection = new GetSqlConnection().GetCon();//连接数据库
SqlCommand cmd = new SqlCommand("select * from dbo.tb_JhGoodsInfo order by GoodsID asc", sqlConnection);
SqlDataReader dataReader = cmd.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataReader;
this.dataGridView1.DataSource = bindingSource;
dataGridView1.Columns[0].HeaderCell.Value = "商品ID";
dataGridView1.Columns[1].HeaderCell.Value = "员工";
dataGridView1.Columns[2].HeaderCell.Value = "进货公司";
dataGridView1.Columns[3].HeaderCell.Value = "仓库";
dataGridView1.Columns[4].HeaderCell.Value = "商品名称";
dataGridView1.Columns[5].HeaderCell.Value = "数量";
dataGridView1.Columns[6].HeaderCell.Value = "计量单位";
dataGridView1.Columns[7].HeaderCell.Value = "进价";
dataGridView1.Columns[8].HeaderCell.Value = "售价";
dataGridView1.Columns[9].HeaderCell.Value = "应付价格";
dataGridView1.Columns[10].HeaderCell.Value = "实付价格";
dataGridView1.Columns[11].HeaderCell.Value = "备注";
dataGridView1.Columns[12].HeaderCell.Value = "时间";
dataReader.Close();
sqlConnection.Close();
}
catch (SqlException)
{
MessageBox.Show("数据库连接失败");
}
if (currentRow != -1)
{
dataGridView1.Rows[0].Selected = false;
dataGridView1.Rows[currentRow].Selected = true;
dataGridView1.FirstDisplayedScrollingRowIndex = currentRow;
}
}
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);
}
//查询按钮
private void queryButton_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection = new GetSqlConnection().GetCon();
string sql = "select * from dbo.tb_JhGoodsInfo where ";
switch (keyType.SelectedIndex)
{
case 0: { sql += "GoodsID LIKE \'%" + keyText.Text + "%\'"; break; }
case 1: { sql += "GoodsName LIKE \'%" + keyText.Text + "%\'"; break; }
case 2: { sql += "JhCompName LIKE \'%" + keyText.Text + "%\'"; break; }
case 3: { sql += "EmpID LIKE \'%" + keyText.Text + "%\'"; break; }
case 4: { sql += "DepotName LIKE \'%" + keyText.Text + "%\'"; break; }
default: { break; }
}
try
{
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
SqlDataReader dataReader = cmd.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataReader;
this.dataGridView1.DataSource = bindingSource;
if (!dataReader.HasRows)
{
MessageBox.Show("无查询结果");
}
dataReader.Close();
sqlConnection.Close();
}
catch (SqlException)
{
MessageBox.Show("错误！\n输入正确类型！");
}
}
//功能函数，执行数据库插入语句，同时会检查输入是否正确。
private void InsertIntoSql()
{
if (!InfoCheck())
{
MessageBox.Show("信息错误:信息空缺或错误！");
return;
}
SqlConnection sqlConnection = new GetSqlConnection().GetCon();//连接数据库
string sql = "Insert into dbo.tb_JhGoodsInfo(GoodsID,EmpID,JhCompName,DepotName,GoodsName,GoodsNum,GoodsUnit,GoodsJhPrice,GoodsSellPrice," +
"GoodsNeedPrice,GoodsNoPrice,GoodsRemark,GoodsTime,Flag)values(@GoodsID,@EmpID,@JhCompName,@DepotName,@GoodsName,@GoodsNum,@GoodsUnit," +
"@GoodsJhPrice,@GoodsSellPrice,@GoodsNeedPrice,@GoodsNoPrice,@GoodsRemark,@GoodsTime,@Flag)";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.Prepare();
sqlCommand.Parameters.AddWithValue("@GoodsID", txtGoodsID.Text);
sqlCommand.Parameters.AddWithValue("@EmpID", txtEmpID.Text);
sqlCommand.Parameters.AddWithValue("@JhCompName", txtJhCompName.Text);
sqlCommand.Parameters.AddWithValue("@DepotName", txtDepotName.Text);
sqlCommand.Parameters.AddWithValue("@GoodsName", txtGoodsName.Text);
sqlCommand.Parameters.AddWithValue("@GoodsNum", StringToInt(txtGoodsNum.Text));
sqlCommand.Parameters.AddWithValue("@GoodsUnit", txtGoodsUnit.Text);
sqlCommand.Parameters.AddWithValue("@GoodsJhPrice", txtGoodsJhPrice.Text);
sqlCommand.Parameters.AddWithValue("@GoodsSellPrice", txtGoodsSellPrice.Text);
sqlCommand.Parameters.AddWithValue("@GoodsNeedPrice", txtGoodsNeedPrice.Text);
sqlCommand.Parameters.AddWithValue("@GoodsNoPrice", txtGoodsNoPrice.Text);
sqlCommand.Parameters.AddWithValue("@GoodsRemark", txtGoodsRemark.Text);
sqlCommand.Parameters.AddWithValue("@GoodsTime", txtGoodsTime.Value);
sqlCommand.Parameters.AddWithValue("@Flag", 0);
try
{ int r = sqlCommand.ExecuteNonQuery(); }
catch (SqlException)
{
MessageBox.Show("插入信息错误，请检查是否商品编号重复");
//打开查询界面
}
sqlConnection.Close();
}
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);
}
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);
}
