//获得数据库数据 private void GetUserData() { MySQLConnection DBConn = null; DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "template", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); DBConn.Open(); MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn); setformat.ExecuteNonQuery(); setformat.Dispose(); string type = MainWindow.wfmodel.WFModel_Type; string sql = "select name, description,type from template where type = '"+type+"'"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); foreach (DataRow dr in ds.Rows) { TemplateModel um = new TemplateModel(); um.name = dr["name"].ToString(); um.desc = dr["description"].ToString(); um.type = dr["type"].ToString(); models.Add(um); } view.Source = models; this.listView1.DataContext = view; }
private void skinButton_DishIncrease_Click(object sender, EventArgs e) { conn1.Open(); string dishid = dataGridView2.SelectedRows[0].Cells[0].Value.ToString(); string dishquantity = dataGridView2.SelectedRows[0].Cells[1].Value.ToString(); string dishinformation = dataGridView2.SelectedRows[0].Cells[2].Value.ToString(); string dishstatus = dataGridView2.SelectedRows[0].Cells[3].Value.ToString(); string sql = "insert into dish values('" + dishid + "','" + dishquantity + "','" + dishinformation + "','" + dishstatus + "')"; MySQLCommand mys = new MySQLCommand(sql, conn1); if (mys.ExecuteNonQuery() != -1) { MessageBox.Show("新增成功!", "恭喜"); sql = "select * from dish"; MySQLDataAdapter ada2 = new MySQLDataAdapter(sql, conn1); DataSet ds2 = new DataSet(); ada2.Fill(ds2); dataGridView2.DataSource = ds2.Tables[0]; textBox_DishID.Clear(); textBox_DishQuantity.Clear(); textBox_DishInformation.Clear(); textBox_DishStatus.Clear(); } conn1.Close(); }
//获得数据库数据 private void GetUserData() { MySQLConnection DBConn = null; DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "template", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); DBConn.Open(); MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn); setformat.ExecuteNonQuery(); setformat.Dispose(); string type = MainWindow.wfmodel.WFModel_Type; string sql = "select name, description,type from template where type = '" + type + "'"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); foreach (DataRow dr in ds.Rows) { TemplateModel um = new TemplateModel(); um.name = dr["name"].ToString(); um.desc = dr["description"].ToString(); um.type = dr["type"].ToString(); models.Add(um); } view.Source = models; this.listView1.DataContext = view; }
public DataSet Query(string sql, params MySQLParameter[] cmdParms) { DataSet ds = new DataSet(); MySQLCommand cmd = new MySQLCommand(); MyDbConnection3.PrepareCommand(cmd, this.DbConn, null, sql, cmdParms); DataSet result; using (MySQLDataAdapter da = new MySQLDataAdapter(cmd)) { try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); this.LogSql(sql); } catch (MySQLException ex) { throw new Exception(ex.Message); } finally { if (cmd != null) { cmd.Dispose(); } } result = ds; } return(result); }
private void skinButton_DishEdit_Click(object sender, EventArgs e) { conn1.Open(); if (MessageBox.Show("确定要从数据库中修改这一行数据吗?", "数据无法恢复请慎重选择!", MessageBoxButtons.OKCancel) == DialogResult.OK) { string dishid = dataGridView2.SelectedRows[0].Cells[0].Value.ToString(); string dishquantity = dataGridView2.SelectedRows[0].Cells[1].Value.ToString(); string dishinformation = dataGridView2.SelectedRows[0].Cells[2].Value.ToString(); string dishstatus = dataGridView2.SelectedRows[0].Cells[3].Value.ToString(); string sql = "update dish set dishid=" + dishid + ",dishquantity=" + dishquantity + ",dishinformation=" + dishinformation + ",dishstatus=" + dishstatus + " where dishid=" + dishid; MySQLCommand mys = new MySQLCommand(sql, conn1); if (mys.ExecuteNonQuery() != -1) { MessageBox.Show("修改成功!", "恭喜"); sql = "select * from dish"; MySQLDataAdapter ada2 = new MySQLDataAdapter(sql, conn1); DataSet ds2 = new DataSet(); ada2.Fill(ds2); dataGridView2.DataSource = ds2.Tables[0]; } else { MessageBox.Show("修改失败!", "提示"); } } conn1.Close(); }
//string table_src = "ResourceDB"; public void FilterName(string to_filter) { db_cn.Open(); da = new MySQLDataAdapter("SELECT * FROM ResourceDB WHERE ResourceN LIKE '%" + to_filter + "%'", db_cn); da.Fill(dt); db_cn.Close(); }
public DataSet Query(string sql, int Times = 0) { DataSet ds = new DataSet(); MySQLDataAdapter mySQLDataAdapter; MySQLDataAdapter command = mySQLDataAdapter = new MySQLDataAdapter(sql, this.DbConn); try { if (Times > 0) { command.SelectCommand.CommandTimeout = Times; } command.Fill(ds, "ds"); this.LogSql(sql); } catch (MySQLException ex) { LogManager.WriteLog(LogTypes.Exception, string.Format("执行SQL异常: {0}\r\n{1}", sql, ex.ToString()), null, true); LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", sql), null, true); } finally { if (mySQLDataAdapter != null) { ((IDisposable)mySQLDataAdapter).Dispose(); } } return(ds); }
//获得数据库数据 private void GetUserData() { MySQLConnection DBConn = null; DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); DBConn.Open(); MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn); setformat.ExecuteNonQuery(); setformat.Dispose(); string sql = "select distinct User_Dept from tb_user"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); foreach (DataRow dr in ds.Rows) { UserModel um = new UserModel(); um.Department = dr["User_Dept"].ToString(); models.Add(um); } view.Source = models; this.listView1.DataContext = view; }
public LateWtEl(string flag) { InitializeComponent(); string SQLstr = "\0"; MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString ("localhost", "DormitoryManage", "root", "123456").AsString); if (flag == "0") { this.Text = "晚归信息"; SQLstr = "SELECT studentNumber as '学号',lateReturnTime as '晚归时间' FROM late_return WHERE studentNumber = " + PublicValue.STUNUM; } else if (flag == "1") { this.Text = "水电信息"; SQLstr = "SELECT studentNumber as '学号', dormitoryNumber as '公寓号',roomNumber as '寝室号',checkMonth as '日期',electricityConsumption as '用电量',electricityBill as '电费',waterConsumption" + " as '用水量',waterBill as '水费' FROM Water_Electricity WHERE studentNumber = " + PublicValue.STUNUM; } SQLconnection.Open(); MySQLCommand SQLcommand = new MySQLCommand("SET NAMES GB2312", SQLconnection); SQLcommand.ExecuteNonQuery(); //执行设置字符集的语句 MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); }
//获得数据库数据 private void GetUserData() { MySQLConnection DBConn = null; string connectStr = new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString; //System.Windows.Forms.MessageBox.Show(connectStr); DBConn = new MySQLConnection(connectStr); DBConn.Open(); MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn); setformat.ExecuteNonQuery(); setformat.Dispose(); string sql = "select User_Id,User_Name,User_Dept,User_Job,User_Mail,User_Cell from tb_user"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); foreach (DataRow dr in ds.Rows) { UserModel um = new UserModel(); um.ID = dr["User_Id"].ToString(); um.Name = dr["User_Name"].ToString(); um.Department = dr["User_Dept"].ToString(); um.Email = dr["User_Mail"].ToString(); um.PersonPosition = dr["User_Job"].ToString(); um.Telephone = dr["User_Cell"].ToString(); models.Add(um); } view.Source = models; this.listView1.DataContext = view; }
private void skinButton_MealCardEdit_Click(object sender, EventArgs e) { conn1.Open(); if (MessageBox.Show("确定要从数据库中修改这一行数据吗?", "数据无法恢复请慎重选择!", MessageBoxButtons.OKCancel) == DialogResult.OK) { string mealcardid = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string mealcardinformation = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); string mealcardstatus = dataGridView1.SelectedRows[0].Cells[2].Value.ToString(); string sql = "update mealcard set mealcardid=" + mealcardid + ",mealcardinformation=" + mealcardinformation + ",mealcardstatus=" + mealcardstatus + " where mealcardid=" + mealcardid; MySQLCommand mys = new MySQLCommand(sql, conn1); if (mys.ExecuteNonQuery() != -1) { MessageBox.Show("修改成功!", "恭喜"); sql = "select * from mealcard"; MySQLDataAdapter ada1 = new MySQLDataAdapter(sql, conn1); DataSet ds1 = new DataSet(); ada1.Fill(ds1); dataGridView1.DataSource = ds1.Tables[0]; } else { MessageBox.Show("修改失败!", "提示"); } } conn1.Close(); }
private void User_Load(object sender, EventArgs e) { conn1.Open(); string sql = "select mealcardid as '饭卡ID号',mealcardinformation as '饭卡余额', mealcardstatus as '饭卡状态' from mealcard"; MySQLDataAdapter ada1 = new MySQLDataAdapter(sql, conn1); DataSet ds1 = new DataSet(); ada1.Fill(ds1); dataGridView1.DataSource = ds1.Tables[0]; sql = "select dishid as '菜品ID号', dishquantity as '菜品数量', dishinformation as '菜品单价',dishstatus as '菜品状态' from Dish"; MySQLDataAdapter ada2 = new MySQLDataAdapter(sql, conn1); DataSet ds2 = new DataSet(); ada2.Fill(ds2); dataGridView2.DataSource = ds2.Tables[0]; conn1.Close(); this.timer1.Start(); switch (DateTime.Now.DayOfWeek.ToString()) { case "Monday": weekstr = "星期一"; break; case "Tuesday": weekstr = "星期二"; break; case "Wednesday": weekstr = "星期三"; break; case "Thursday": weekstr = "星期四"; break; case "Friday": weekstr = "星期五"; break; case "Saturday": weekstr = "星期六"; break; case "Sunday": weekstr = "星期日"; break; } }
public void MySqlProcedure() { //打开连接 Open(); // CommitCommand("CALL consume_db.new_procedure(167178)"); OpenCommand(); Command = "new_procedure3"; cmd.CommandType = System.Data.CommandType.StoredProcedure; MySQLParameter par1 = new MySQLParameter("item_no", DbType.String, "167178"); MySQLParameter[] parameters = { new MySQLParameter("?item_no", System.Data.DbType.Int32, "This value is ignored") }; parameters[0].Value = "167178"; cmd.Parameters.AddRange(parameters); MySQLDataAdapter ad = new MySQLDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); /* * StringBuilder abc = new StringBuilder(20); * MySQLParameter par3 = new MySQLParameter("?file_name", abc); * par3.Direction = ParameterDirection.Output; * * par3.Scale = 100; * par3.Size = 100; * * cmd.Parameters.Add(par3);*/ }
private void skinButton_Refreash2_Click(object sender, EventArgs e) { conn1.Open(); string sql = "select * from dish"; MySQLDataAdapter ada2 = new MySQLDataAdapter(sql, conn1); DataSet ds2 = new DataSet(); ada2.Fill(ds2); dataGridView2.DataSource = ds2.Tables[0]; MessageBox.Show("已刷新", "提示"); conn1.Close(); }
/// <summary> /// MySQL查询调用 /// </summary> /// <param name="procedureName">存储过程名称</param> /// <param name="dp">存储过程参数</param> /// <returns>查询结果数据集合</returns> public DataSet ExecuteQuery(string procedureName, MySQLParameter[] dp) { Connect(); MySQLCommand cmd = CreateProcedureCommand(procedureName, dp); //MySQLDataReader mdr = cmd.ExecuteReaderEx(); MySQLDataAdapter mda = new MySQLDataAdapter(); mda.SelectCommand = cmd; try { mda.Fill(ds); } catch (Exception ex) { throw ex; } finally { Terminate(); } return(ds); }
//获得数据库数据 void GetTemplateData() { models.Clear(); MySQLConnection DBConn = null; DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); try { DBConn.Open(); MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn); setformat.ExecuteNonQuery(); setformat.Dispose(); string sql = "select model_name,owner,model_content,lastedit_time,model_disc,create_time from wf_model"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); foreach (DataRow dr in ds.Rows) { WFModel wfm = new WFModel(); wfm.WFModel_CreateTime = dr["create_time"].ToString(); wfm.WFModel_LasteditTime = dr["lastedit_time"].ToString(); wfm.WFModel_Name = dr["model_name"].ToString(); wfm.WFModel_Owner = dr["owner"].ToString(); string test = dr["model_content"].ToString(); if (dr["model_content"] == null || dr["model_content"].ToString().Length <= 0) { wfm.WFModel_Content = ""; } else { wfm.WFModel_Content = Encoding.Default.GetString((Byte[])dr["model_content"]); } models.Add(wfm); } view.Source = models; this.listView1.DataContext = view; } catch (Exception e) { MessageBox.Show("数据库连接失败,请检查网络连接或者数据库配置"); return; } }
public static ToolboxCategoryItems loadUserbox() { MySQLConnection DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); if (DBConn != null) { try { DBConn.Open(); string sql1 = "set names gb2312"; MySQLCommand DBComm = new MySQLCommand(sql1, DBConn); //設定下達 command DBComm.ExecuteNonQuery(); DBComm.Dispose(); string sql = "select * from tb_user"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable UserDataTable = new DataTable(); mda.Fill(UserDataTable); DBConn.Close(); loadSystemIcon(); ToolboxCategoryItems toolboxCategoryItems = new ToolboxCategoryItems(); ToolboxCategory users = new System.Activities.Presentation.Toolbox.ToolboxCategory("系统工作人员"); foreach (DataRow dr in UserDataTable.Rows) { //byte[] temp = Encoding.Default.GetBytes(dr["User_Name"].ToString()); //temp = System.Text.Encoding.Convert(Encoding.GetEncoding("utf8"), Encoding.GetEncoding("gb2312"), temp); //string username = Encoding.Default.GetString(temp); //ToolboxItemWrapper User = new ToolboxItemWrapper(typeof(Wxwinter.BPM.WFDesigner.User), username); ToolboxItemWrapper User = new ToolboxItemWrapper(typeof(Wxwinter.BPM.WFDesigner.User), dr["User_Name"].ToString()); users.Add(User); } toolboxCategoryItems.Add(users); parentWindow.statusInfo.Text = ""; return(toolboxCategoryItems); } catch (Exception e) { parentWindow.statusInfo.Text = "数据库连接失败,请检查网络设置和数据库连接配置"; return(null); } } else { return(null); } }
//获得数据库数据 void GetTemplateData() { models.Clear(); MySQLConnection DBConn = null; DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); try { DBConn.Open(); MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn); setformat.ExecuteNonQuery(); setformat.Dispose(); string sql = "select model_name,owner,model_content,lastedit_time,model_disc,create_time from wf_model"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); foreach (DataRow dr in ds.Rows) { WFModel wfm = new WFModel(); wfm.WFModel_CreateTime = dr["create_time"].ToString(); wfm.WFModel_LasteditTime = dr["lastedit_time"].ToString(); wfm.WFModel_Name = dr["model_name"].ToString(); wfm.WFModel_Owner = dr["owner"].ToString(); string test = dr["model_content"].ToString(); if (dr["model_content"] == null || dr["model_content"].ToString().Length<=0) { wfm.WFModel_Content = ""; }else wfm.WFModel_Content = Encoding.Default.GetString((Byte[])dr["model_content"]); models.Add(wfm); } view.Source = models; this.listView1.DataContext = view; } catch (Exception e) { MessageBox.Show("数据库连接失败,请检查网络连接或者数据库配置"); return; } }
public void perform_search(string search_type, string search_string, string grade_level) { db_cn.Open(); if (grade_level == "") { switch (search_type) { case "ID": da = new MySQLDataAdapter("SELECT * FROM [ResourceDB] where ID like '%" + search_string + "%'", db_cn); da.Fill(dt); break; case "Author": da = new MySQLDataAdapter("SELECT * FROM [ResourceDB] where Author like '%" + search_string + "%'", db_cn); da.Fill(dt); break; case "Tags": da = new MySQLDataAdapter("SELECT * FROM [ResourceDB] where [w_tags] like '%" + search_string + "%'", db_cn); da.Fill(dt); break; } } else { switch (grade_level) { case "G7": grade_level_search("Grade 7", search_type, search_string); break; case "G8": grade_level_search("Grade 8", search_type, search_string); break; case "G9": grade_level_search("Grade 9", search_type, search_string); break; case "G10": grade_level_search("Grade 10", search_type, search_string); break; } } }
//获得数据库数据 DataTable GeteData() { MySQLConnection DBConn = null; DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "template", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); DBConn.Open(); //MySQLCommand DBCmd = new MySQLCommand("set names gb2312", DBConn); // DBCmd.ExecuteNonQuery(); string sql = "select * from template"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); return ds; }
private void button4_Click(object sender, EventArgs e) { MySQLConnection DBConn; DBConn = new MySQLConnection(new MySQLConnectionString("127.0.0.1", "test", "root", "root", 3306).AsString); try { DBConn.Open(); string sql = "insert into testTable(id,name) values('54','542')"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataSet ds = new DataSet(); mda.Fill(ds, "testtable"); } catch (Exception ex) { MessageBox.Show(ex.Message); } DBConn.Close(); }
//获得数据库数据 DataTable GeteData() { MySQLConnection DBConn = null; DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "template", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); DBConn.Open(); //MySQLCommand DBCmd = new MySQLCommand("set names gb2312", DBConn); // DBCmd.ExecuteNonQuery(); string sql = "select * from template"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); return(ds); }
public static ToolboxCategoryItems loadTemplatebox() { MySQLConnection DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "template", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); if (DBConn != null) { try { DBConn.Open(); string sql = "select * from template"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); MySQLCommand mcd = new MySQLCommand(sql, DBConn); mcd.ExecuteNonQuery(); DataTable TemplateDataTable = new DataTable(); mda.Fill(TemplateDataTable); DBConn.Close(); loadSystemIcon(); ToolboxCategoryItems toolboxCategoryItems = new ToolboxCategoryItems(); ToolboxCategory templates = new System.Activities.Presentation.Toolbox.ToolboxCategory("表单模板"); foreach (DataRow dr in TemplateDataTable.Rows) { ToolboxItemWrapper Template = new ToolboxItemWrapper(typeof(Wxwinter.BPM.WFDesigner.Template), dr["NAME"].ToString()); templates.Add(Template); } toolboxCategoryItems.Add(templates); parentWindow.statusInfo.Text = ""; return(toolboxCategoryItems); } catch (Exception e) { parentWindow.statusInfo.Text = "数据库连接失败,请检查网络设置和数据库连接配置"; return(null); } } else { return(null); } }
public static ToolboxCategoryItems loadTemplatebox() { MySQLConnection DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "template", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); if (DBConn != null) { try { DBConn.Open(); string sql = "select * from template"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); MySQLCommand mcd = new MySQLCommand(sql, DBConn); mcd.ExecuteNonQuery(); DataTable TemplateDataTable = new DataTable(); mda.Fill(TemplateDataTable); DBConn.Close(); loadSystemIcon(); ToolboxCategoryItems toolboxCategoryItems = new ToolboxCategoryItems(); ToolboxCategory templates = new System.Activities.Presentation.Toolbox.ToolboxCategory("表单模板"); foreach (DataRow dr in TemplateDataTable.Rows) { ToolboxItemWrapper Template = new ToolboxItemWrapper(typeof(Wxwinter.BPM.WFDesigner.Template), dr["NAME"].ToString()); templates.Add(Template); } toolboxCategoryItems.Add(templates); parentWindow.statusInfo.Text = ""; return toolboxCategoryItems; } catch (Exception e) { parentWindow.statusInfo.Text = "数据库连接失败,请检查网络设置和数据库连接配置"; return null; } } else { return null; } }
public static DataTable ExecuteDataTable(string SQLString) { using (MySQLConnection connection = new MySQLConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); MySQLDataAdapter command = new MySQLDataAdapter(SQLString, connection); MySQLCommand commn = new MySQLCommand("set names gbk", connection); commn.ExecuteNonQuery(); command.Fill(ds, "ds"); connection.Close(); } catch (MySQLException ex) { throw new Exception(ex.Message); } return ds.Tables[0]; } }
public static DataTable ExecuteDataTable(string SQLString, params MySQLParameter[] cmdParms) { using (MySQLConnection connection = new MySQLConnection(connectionString)) { MySQLCommand cmd = new MySQLCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (MySQLDataAdapter da = new MySQLDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (MySQLException ex) { throw new Exception(ex.Message); } return ds.Tables[0]; } } }
public DataTable QeuryTable(string sqlTxt) { DataTable dt = new DataTable(); try { conn.Open(); cmd.CommandText = sqlTxt; cmd.Connection = conn; da.SelectCommand = cmd; da.Fill(dt); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } return(dt); }
public void grade_level_search(string glvl, string what_to, string search_what) { switch (what_to) { case "ID": da = new MySQLDataAdapter("SELECT * FROM [ResourceDB] WHERE [GradeLevel]= '" + glvl + "'", db_cn); da.Fill(dt); using (dt) { DataView dv = dt.DefaultView; dv.RowFilter = "ID like '%" + search_what + "&'"; dt2 = dv.ToTable(); } break; case "Author": da = new MySQLDataAdapter("SELECT * FROM [ResourceDB] WHERE [GradeLevel]= '" + glvl + "'", db_cn); da.Fill(dt); using (dt) { DataView dv = dt.DefaultView; dv.RowFilter = "Author like '%" + search_what + "&'"; dt2 = dv.ToTable(); } break; case "Tags": da = new MySQLDataAdapter("SELECT * FROM [ResourceDB] WHERE [GradeLevel]= '" + glvl + "'", db_cn); da.Fill(dt); using (dt) { DataView dv = dt.DefaultView; dv.RowFilter = "[w_tags] like '%" + search_what + "&'"; dt2 = dv.ToTable(); } break; } }
public void GetPeopleDate(string str) { models.Clear(); MySQLConnection DBConn = null; DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); try { DBConn.Open(); MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn); setformat.ExecuteNonQuery(); setformat.Dispose(); string sql = "select tb_per.User_Name , tb_user.User_Mail from tb_user, tb_per where tb_per.user_name = tb_user.User_Name and tb_per.user_authority = "; sql += "'" + str + "'"; //MessageBox.Show(sql); MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable ds = new DataTable(); mda.Fill(ds); DBConn.Close(); foreach (DataRow dr in ds.Rows) { peopleInfo wfm = new peopleInfo(); wfm.peopleName = dr["user_name"].ToString(); wfm.peopleEmail = dr["User_Mail"].ToString(); models.Add(wfm); } view.Source = models; this.peopleListView.DataContext = view; } catch (System.Exception ex) { MessageBox.Show("数据库连接失败,请检查网络连接或者数据库配置"); return; } }
private void skinButton_MealCardIncrease_Click(object sender, EventArgs e) { conn1.Open(); string mealcardid = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string mealcardinformation = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); string mealcardstatus = dataGridView1.SelectedRows[0].Cells[2].Value.ToString(); string sql = "insert into mealcard values('" + mealcardid + "','" + mealcardinformation + "','" + mealcardstatus + "')"; MySQLCommand mys = new MySQLCommand(sql, conn1); if (mys.ExecuteNonQuery() != -1) { MessageBox.Show("新增成功!", "恭喜"); sql = "select * from mealcard"; MySQLDataAdapter ada1 = new MySQLDataAdapter(sql, conn1); DataSet ds1 = new DataSet(); ada1.Fill(ds1); dataGridView1.DataSource = ds1.Tables[0]; textBox_MealCardID.Clear(); textBox_MealCardInformation.Clear(); textBox_MealCardStatus.Clear(); } }
private void skinButton_DishDelete_Click(object sender, EventArgs e) { conn1.Open(); if (MessageBox.Show("确定要从数据库中删除这一行数据吗?", "数据无法恢复请慎重选择!", MessageBoxButtons.OKCancel) == DialogResult.OK) { int dishid = int.Parse(dataGridView2.SelectedRows[0].Cells[0].Value.ToString()); string sql = "delete from dish where dishid=" + dishid; MySQLCommand mys = new MySQLCommand(sql, conn1); if (mys.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); sql = "select * from dish"; MySQLDataAdapter ada2 = new MySQLDataAdapter(sql, conn1); DataSet ds2 = new DataSet(); ada2.Fill(ds2); dataGridView2.DataSource = ds2.Tables[0]; } else { MessageBox.Show("删除失败!", "提示"); } } conn1.Close(); }
//获取起始页码和结束页码 public static DataTable ExecuteDataTable(string cmdText, int startResord, int maxRecord) { using (MySQLConnection connection = new MySQLConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); MySQLDataAdapter command = new MySQLDataAdapter(cmdText, connection); command.Fill(ds, startResord, maxRecord, "ds"); connection.Close(); } catch (MySQLException ex) { throw new Exception(ex.Message); } return ds.Tables[0]; } }
private void ButtonOK_Click(object sender, EventArgs e) { string mode = ComboBox.SelectedItem.ToString(); MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString ("localhost", "DormitoryManage", "root", "123456").AsString); SQLconnection.Open(); MySQLCommand SQLcommand = new MySQLCommand("SET NAMES GB2312", SQLconnection); SQLcommand.ExecuteNonQuery(); //执行设置字符集的语句 switch (mode) { case "查询": { if (TextBox1.Text != "") { string SQLstr = "SELECT studentNumber as '学号', dormitoryNumber as '公寓号',roomNumber as '寝室号'," + "studentName as '姓名',studentGender as '性别',studentAge as '年龄',studentMajor as '专业'," + "studentPhone as '电话',studentPassword as '密码' FROM student_info WHERE studentNumber = '" + TextBox1.Text + "'"; MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); MessageBox.Show("查询完成!", "提示"); } else { string SQLstr = "SELECT studentNumber as '学号', dormitoryNumber as '公寓号',roomNumber as '寝室号'," + "studentName as '姓名',studentGender as '性别',studentAge as '年龄',studentMajor as '专业'," + "studentPhone as '电话',studentPassword as '密码' FROM student_info"; MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); MessageBox.Show("查询完成!", "提示"); } break; } case "添加": { if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox4.Text == "" || TextBox5.Text == "" || TextBox6.Text == "" || TextBox7.Text == "" || TextBox8.Text == "" || TextBox9.Text == "") { if (TextBox1.Text == "") { labelN1.Text = "输入不能为空"; } else { labelN1.Text = " "; } if (TextBox2.Text == "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; } if (TextBox3.Text == "") { labelN3.Text = "输入不能为空"; } else { labelN3.Text = " "; } if (TextBox4.Text == "") { labelN4.Text = "输入不能为空"; } else { labelN4.Text = " "; } if (TextBox5.Text == "") { labelN5.Text = "输入不能为空"; } else { labelN5.Text = " "; } if (TextBox6.Text == "") { labelN6.Text = "输入不能为空"; } else { labelN6.Text = " "; } if (TextBox7.Text == "") { labelN7.Text = "输入不能为空"; } else { labelN7.Text = " "; } if (TextBox8.Text == "") { labelN8.Text = "输入不能为空"; } else { labelN8.Text = " "; } if (TextBox9.Text == "") { labelN9.Text = "输入不能为空"; } else { labelN9.Text = " "; } } else { labelN1.Text = " "; labelN2.Text = " "; labelN3.Text = " "; labelN4.Text = " "; labelN5.Text = " "; labelN6.Text = " "; labelN7.Text = " "; labelN8.Text = " "; labelN9.Text = " "; string SQLstr = "INSERT student_info VALUES('" + TextBox1.Text + "','" + TextBox5.Text + "','" + TextBox6.Text + "'," + "'" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox7.Text + "','" + TextBox8.Text + "'," + "'" + TextBox9.Text + "')"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("添加成功!", "提示"); } else { MessageBox.Show("添加失败!", "提示"); } } break; } case "修改": { if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox4.Text == "" || TextBox5.Text == "" || TextBox6.Text == "" || TextBox7.Text == "" || TextBox8.Text == "" || TextBox9.Text == "") { if (TextBox1.Text == "") { labelN1.Text = "输入不能为空"; } else { labelN1.Text = " "; } if (TextBox2.Text == "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; } if (TextBox3.Text == "") { labelN3.Text = "输入不能为空"; } else { labelN3.Text = " "; } if (TextBox4.Text == "") { labelN4.Text = "输入不能为空"; } else { labelN4.Text = " "; } if (TextBox5.Text == "") { labelN5.Text = "输入不能为空"; } else { labelN5.Text = " "; } if (TextBox6.Text == "") { labelN6.Text = "输入不能为空"; } else { labelN6.Text = " "; } if (TextBox7.Text == "") { labelN7.Text = "输入不能为空"; } else { labelN7.Text = " "; } if (TextBox8.Text == "") { labelN8.Text = "输入不能为空"; } else { labelN8.Text = " "; } if (TextBox9.Text == "") { labelN9.Text = "输入不能为空"; } else { labelN9.Text = " "; } } else { labelN1.Text = " "; labelN2.Text = " "; labelN3.Text = " "; labelN4.Text = " "; labelN5.Text = " "; labelN6.Text = " "; labelN7.Text = " "; labelN8.Text = " "; labelN9.Text = " "; int studentnumber = int.Parse(DataGridView.SelectedRows[0].Cells[0].Value.ToString()); string SQLstr = "UPDATE student_info SET studentNumber = '{0}', dormitoryNumber = '{1}',roomNumber = '{2}'," + "studentName = '{3}',studentGender = '{4}',studentAge = '{5}',studentMajor = '{6}'," + "studentPhone = '{7}',studentPassword = '******'WHERE studentNumber = " + studentnumber; SQLstr = string.Format(SQLstr, TextBox1.Text, TextBox5.Text, TextBox6.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox7.Text, TextBox8.Text, TextBox9.Text); MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("修改成功!", "提示"); } else { MessageBox.Show("修改失败!", "提示"); } } break; } case "删除": { if (TextBox1.Text != "") { string SQLstr = "DELETE FROM student_info WHERE studentNumber = '" + TextBox1.Text + "'"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); } else { MessageBox.Show("删除失败!", "提示"); } } else { string SQLstr = "TRUNCATE TABLE student_info"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); } else { MessageBox.Show("删除失败!", "提示"); } } break; } } SQLconnection.Close(); }
public ArrayList SearchAccount(SelectAccount selAccount) { try { //AccountInfo accountInfo = new AccountInfo(); //accountInfo.Sex = short.Parse(reader["user_sex"].ToString()); //accountInfo.Birthday = DateTime.Parse(reader["user_birthday"].ToString()); //accountInfo.Phone = reader["uesr_phome"].ToString(); //return accountInfo; con.Open(); sql = "INNER JOIN users_information ON users_information.user_name LIKE '%" + selAccount.Sel_info + "%'"; MySQLCommand com = new MySQLCommand(sql, con); MySQLDataAdapter adp = new MySQLDataAdapter(com); DataSet ds = new DataSet(); adp.Fill(ds); ArrayList arr = new ArrayList(); foreach (DataRow dr in ds.Tables[0].Rows) { AccountInfo accountInfo = new AccountInfo(); accountInfo.User_id = Int32.Parse(dr["user_id"].ToString()); accountInfo.User_name = dr["user_name"].ToString(); accountInfo.Sex = short.Parse(dr["user_sex"].ToString()); accountInfo.Birthday = DateTime.Parse(dr["user_birthday"].ToString()); accountInfo.Phone = dr["uesr_phome"].ToString(); arr.Add(accountInfo); } try { sql = "SELECT * FROM users INNER JOIN users_information ON users.user_id = " + Int32.Parse(selAccount.Sel_info); com = new MySQLCommand(sql, con); DbDataReader reader = com.ExecuteReader(); if(reader.Read()) { AccountInfo accountInfo = new AccountInfo(); accountInfo.User_id = Int32.Parse(reader["user_id"].ToString()); accountInfo.User_name = reader["user_name"].ToString(); accountInfo.Sex = short.Parse(reader["user_sex"].ToString()); accountInfo.Birthday = DateTime.Parse(reader["user_birthday"].ToString()); accountInfo.Phone = reader["uesr_phome"].ToString(); arr.Add(accountInfo); } } catch { } return arr; } catch(Exception ex) { throw ex; } finally { con.Close(); } }
public static ToolboxCategoryItems loadUserbox() { MySQLConnection DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString); if (DBConn != null) { try { DBConn.Open(); string sql1 = "set names gb2312"; MySQLCommand DBComm = new MySQLCommand(sql1, DBConn); //設定下達 command DBComm.ExecuteNonQuery(); DBComm.Dispose(); string sql = "select * from tb_user"; MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn); DataTable UserDataTable = new DataTable(); mda.Fill(UserDataTable); DBConn.Close(); loadSystemIcon(); ToolboxCategoryItems toolboxCategoryItems = new ToolboxCategoryItems(); ToolboxCategory users = new System.Activities.Presentation.Toolbox.ToolboxCategory("系统工作人员"); foreach (DataRow dr in UserDataTable.Rows) { //byte[] temp = Encoding.Default.GetBytes(dr["User_Name"].ToString()); //temp = System.Text.Encoding.Convert(Encoding.GetEncoding("utf8"), Encoding.GetEncoding("gb2312"), temp); //string username = Encoding.Default.GetString(temp); //ToolboxItemWrapper User = new ToolboxItemWrapper(typeof(Wxwinter.BPM.WFDesigner.User), username); ToolboxItemWrapper User = new ToolboxItemWrapper(typeof(Wxwinter.BPM.WFDesigner.User), dr["User_Name"].ToString()); users.Add(User); } toolboxCategoryItems.Add(users); parentWindow.statusInfo.Text = ""; return toolboxCategoryItems; } catch (Exception e) { parentWindow.statusInfo.Text = "数据库连接失败,请检查网络设置和数据库连接配置"; return null; } } else { return null; } }
public void MySqlProcedure() { //打开连接 Open(); // CommitCommand("CALL consume_db.new_procedure(167178)"); OpenCommand(); Command = "new_procedure3"; cmd.CommandType = System.Data.CommandType.StoredProcedure; MySQLParameter par1 = new MySQLParameter("item_no", DbType.String, "167178"); MySQLParameter[] parameters = { new MySQLParameter("?item_no",System.Data.DbType.Int32, "This value is ignored") }; parameters[0].Value = "167178"; cmd.Parameters.AddRange(parameters); MySQLDataAdapter ad = new MySQLDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); /* StringBuilder abc = new StringBuilder(20); MySQLParameter par3 = new MySQLParameter("?file_name", abc); par3.Direction = ParameterDirection.Output; par3.Scale = 100; par3.Size = 100; cmd.Parameters.Add(par3);*/ }
public void Query(string tableName, string where, IDictionary<string, string> item) { MySQLCommand commn = null; MySQLDataReader mySqlReader = null; MySQLDataAdapter mySqlAdapter = null; try { conn.Open(); Common.Log("Query columns from table: " + tableName); // Query table column name string[] columns = null; commn = new MySQLCommand(); string queryString = "SELECT column_name FROM information_schema.columns WHERE table_schema = 'DB_NAME' AND table_name = 'TABLE_NAME'"; queryString = queryString.Replace("DB_NAME", databaseName); queryString = queryString.Replace("TABLE_NAME", tableName); commn.CommandText = queryString; commn.CommandType = CommandType.Text; commn.Connection = conn; mySqlAdapter = new MySQLDataAdapter(commn); DataSet myDataSet = new DataSet(); mySqlAdapter.Fill(myDataSet); columns = new string[myDataSet.Tables[0].Rows.Count]; for (int rowIndex = 0; rowIndex < myDataSet.Tables[0].Rows.Count; rowIndex++) { columns[rowIndex] = myDataSet.Tables[0].Rows[rowIndex][0].ToString(); } myDataSet.Clear(); // Query table by where condition commn.Dispose(); commn = new MySQLCommand(); string queryString2 = "SELECT * FROM TABLE_NAME WHERE CONDITION"; queryString2 = queryString2.Replace("TABLE_NAME", tableName); queryString2 = queryString2.Replace("CONDITION", where); commn.CommandText = queryString2; commn.CommandType = CommandType.Text; commn.Connection = conn; Common.Log(queryString2); mySqlAdapter = new MySQLDataAdapter(commn); myDataSet = new DataSet(); mySqlAdapter.Fill(myDataSet); for (int columnIndex = 0; columnIndex < myDataSet.Tables[0].Columns.Count; columnIndex++) { item[columns[columnIndex]] = myDataSet.Tables[0].Rows[0][columnIndex].ToString(); } } catch (MySQLException mySqlException) { Common.Log(mySqlException.ToString()); } finally { if (mySqlAdapter != null) { mySqlAdapter.Dispose(); } if (mySqlReader != null) { mySqlReader.Close(); mySqlReader.Dispose(); } if (commn != null) { commn.Dispose(); } if (conn != null) { conn.Close(); conn.Dispose(); } } }
private void ButtonOK_Click(object sender, EventArgs e) { string mode = ComboBox.SelectedItem.ToString(); MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString ("localhost", "DormitoryManage", "root", "123456").AsString); SQLconnection.Open(); MySQLCommand SQLcommand = new MySQLCommand("SET NAMES GB2312", SQLconnection); SQLcommand.ExecuteNonQuery(); //执行设置字符集的语句 switch (mode) { case "入住信息 查询": { if (TextBox1.Text != "") { string SQLstr = "SELECT studentNumber as '学号', dormitoryNumber as '公寓号',roomNumber as '寝室号'," + "checkInTime as '入住时间',isChangeRoom as '换寝情况' FROM check_in WHERE studentNumber = '" + TextBox1.Text + "'"; MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); MessageBox.Show("查询完成!", "提示"); } else { string SQLstr = "SELECT studentNumber as '学号', dormitoryNumber as '公寓号',roomNumber as '寝室号'," + "checkInTime as '入住时间',isChangeRoom as '换寝情况' FROM check_in"; MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); MessageBox.Show("查询完成!", "提示"); } break; } case "入住信息 添加": { if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox4.Text == "" || TextBox5.Text == "") { if (TextBox1.Text == "") { labelN1.Text = "输入不能为空"; } else { labelN1.Text = " "; } if (TextBox2.Text == "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; } if (TextBox3.Text == "") { labelN3.Text = "输入不能为空"; } else { labelN3.Text = " "; } if (TextBox4.Text == "") { labelN4.Text = "输入不能为空"; } else { labelN4.Text = " "; } if (TextBox5.Text == "") { labelN5.Text = "输入不能为空"; } else { labelN5.Text = " "; } } else { labelN1.Text = " "; labelN2.Text = " "; labelN3.Text = " "; labelN4.Text = " "; labelN5.Text = " "; string SQLstr = "INSERT check_in VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "'," + "'" + TextBox4.Text + "','" + TextBox5.Text + "')"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("添加成功!", "提示"); } else { MessageBox.Show("添加失败!", "提示"); } } break; } case "入住信息 修改": { if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox4.Text == "" || TextBox5.Text == "") { if (TextBox1.Text == "") { labelN1.Text = "输入不能为空"; } else { labelN1.Text = " "; } if (TextBox2.Text == "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; } if (TextBox3.Text == "") { labelN3.Text = "输入不能为空"; } else { labelN3.Text = " "; } if (TextBox4.Text == "") { labelN4.Text = "输入不能为空"; } else { labelN4.Text = " "; } if (TextBox5.Text == "") { labelN5.Text = "输入不能为空"; } else { labelN5.Text = " "; } } else { labelN1.Text = " "; labelN2.Text = " "; labelN3.Text = " "; labelN4.Text = " "; labelN5.Text = " "; labelN6.Text = " "; labelN7.Text = " "; labelN8.Text = " "; labelN9.Text = " "; labelN10.Text = " "; labelN11.Text = " "; int studentnumber = int.Parse(DataGridView.SelectedRows[0].Cells[0].Value.ToString()); string SQLstr = "UPDATE check_in SET studentNumber = '{0}', dormitoryNumber = '{1}',roomNumber = '{2}'," + "checkInTime = '{3}',isChangeRoom = '{4}' WHERE studentNumber = " + studentnumber; SQLstr = string.Format(SQLstr, TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text); MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("修改成功!", "提示"); } else { MessageBox.Show("修改失败!", "提示"); } } break; } case "入住信息 删除": { if (TextBox1.Text != "") { string SQLstr = "DELETE FROM check_in WHERE studentNumber = '" + TextBox1.Text + "'"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); } else { MessageBox.Show("删除失败!", "提示"); } } else { string SQLstr = "TRUNCATE TABLE check_in"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); } else { MessageBox.Show("删除失败!", "提示"); } } break; } case "晚归信息 查询": { if (TextBox1.Text != "") { string SQLstr = "SELECT studentNumber as '学号', dormitoryNumber as '公寓号',roomNumber as '寝室号'," + "lateReturnTime as '晚归时间' FROM late_return WHERE studentNumber = '" + TextBox1.Text + "'"; MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); MessageBox.Show("查询完成!", "提示"); } else { string SQLstr = "SELECT studentNumber as '学号', dormitoryNumber as '公寓号',roomNumber as '寝室号'," + "lateReturnTime as '晚归时间' FROM late_return"; MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); MessageBox.Show("查询完成!", "提示"); } break; } case "晚归信息 添加": { if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox7.Text == "") { if (TextBox1.Text == "") { labelN1.Text = "输入不能为空"; } else { labelN1.Text = " "; } if (TextBox2.Text == "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; } if (TextBox3.Text == "") { labelN3.Text = "输入不能为空"; } else { labelN3.Text = " "; } if (TextBox7.Text == "") { labelN7.Text = "输入不能为空"; } else { labelN7.Text = " "; } } else { labelN1.Text = " "; labelN2.Text = " "; labelN3.Text = " "; labelN4.Text = " "; labelN5.Text = " "; labelN6.Text = " "; labelN7.Text = " "; labelN8.Text = " "; labelN9.Text = " "; labelN10.Text = " "; labelN11.Text = " "; string SQLstr = "INSERT late_return VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "'," + "'" + TextBox7.Text + "')"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("添加成功!", "提示"); } else { MessageBox.Show("添加失败!", "提示"); } } break; } case "晚归信息 修改": { if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox7.Text == "") { if (TextBox1.Text == "") { labelN1.Text = "输入不能为空"; } else { labelN1.Text = " "; } if (TextBox2.Text == "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; } if (TextBox3.Text == "") { labelN3.Text = "输入不能为空"; } else { labelN3.Text = " "; } if (TextBox7.Text == "") { labelN7.Text = "输入不能为空"; } else { labelN7.Text = " "; } } else { labelN1.Text = " "; labelN2.Text = " "; labelN3.Text = " "; labelN4.Text = " "; labelN5.Text = " "; labelN6.Text = " "; labelN7.Text = " "; labelN8.Text = " "; labelN9.Text = " "; labelN10.Text = " "; labelN11.Text = " "; string SQLstr = "UPDATE late_return SET studentNumber = '{0}', dormitoryNumber = '{1}',roomNumber = '{2}'," + "lateReturnTime = '{3}' WHERE studentNumber = '" + TextBox1.Text + "' and lateReturnTime = '" + TextBox7.Text + "'"; SQLstr = string.Format(SQLstr, TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox7.Text); MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("修改成功!", "提示"); } else { MessageBox.Show("修改失败!", "提示"); } } break; } case "晚归信息 删除": { if (TextBox1.Text != "") { string SQLstr = "DELETE FROM late_return WHERE studentNumber = '" + TextBox1.Text + "'"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); } else { MessageBox.Show("删除失败!", "提示"); } } else { string SQLstr = "TRUNCATE TABLE late_return"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); } else { MessageBox.Show("删除失败!", "提示"); } } break; } case "水电信息 查询": { if (TextBox2.Text != "" && TextBox3.Text != "") { labelN2.Text = " "; labelN3.Text = " "; string SQLstr = "SELECT dormitoryNumber as '公寓号',roomNumber as '寝室号',studentNumber as '学号'," + "checkMonth as '日期',electricityConsumption as '用电量',electricityBill as '电费',waterConsumption" + " as '用水量',waterBill as '水费' FROM water_electricity WHERE dormitoryNumber = '" + TextBox2.Text + "'" + "and roomNumber = '" + TextBox3.Text + "'"; MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); MessageBox.Show("查询完成!", "提示"); } else if (TextBox2.Text != "") { labelN3.Text = "输入不能为空"; } else if (TextBox3.Text != "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; labelN3.Text = " "; string SQLstr = "SELECT dormitoryNumber as '公寓号',roomNumber as '寝室号',studentNumber as '学号'," + "checkMonth as '日期',electricityConsumption as '用电量',electricityBill as '电费',waterConsumption" + " as '用水量',waterBill as '水费' FROM water_electricity"; MySQLDataAdapter SQLadapter = new MySQLDataAdapter(SQLstr, SQLconnection); DataSet set = new DataSet(); SQLadapter.Fill(set); DataGridView.DataSource = set.Tables[0]; SQLconnection.Close(); MessageBox.Show("查询完成!", "提示"); } break; } case "水电信息 添加": { if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox6.Text == "" || TextBox8.Text == "" || TextBox9.Text == "" || TextBox10.Text == "" || TextBox11.Text == "") { if (TextBox1.Text == "") { labelN1.Text = "输入不能为空"; } else { labelN1.Text = " "; } if (TextBox2.Text == "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; } if (TextBox3.Text == "") { labelN3.Text = "输入不能为空"; } else { labelN3.Text = " "; } if (TextBox6.Text == "") { labelN6.Text = "输入不能为空"; } else { labelN6.Text = " "; } if (TextBox8.Text == "") { labelN8.Text = "输入不能为空"; } else { labelN8.Text = " "; } if (TextBox9.Text == "") { labelN9.Text = "输入不能为空"; } else { labelN9.Text = " "; } if (TextBox10.Text == "") { labelN10.Text = "输入不能为空"; } else { labelN10.Text = " "; } if (TextBox11.Text == "") { labelN11.Text = "输入不能为空"; } else { labelN11.Text = " "; } } else { labelN1.Text = " "; labelN2.Text = " "; labelN3.Text = " "; labelN4.Text = " "; labelN5.Text = " "; labelN6.Text = " "; labelN7.Text = " "; labelN8.Text = " "; labelN9.Text = " "; labelN10.Text = " "; labelN11.Text = " "; string SQLstr = "INSERT water_electricity VALUES('" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox1.Text + "'," + "'" + TextBox6.Text + "','" + TextBox8.Text + "','" + TextBox9.Text + "','" + TextBox10.Text + "','" + TextBox11.Text + "')"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("添加成功!", "提示"); } else { MessageBox.Show("添加失败!", "提示"); } } break; } case "水电信息 修改": { if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox6.Text == "" || TextBox8.Text == "" || TextBox9.Text == "" || TextBox10.Text == "" || TextBox11.Text == "") { if (TextBox1.Text == "") { labelN1.Text = "输入不能为空"; } else { labelN1.Text = " "; } if (TextBox2.Text == "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; } if (TextBox3.Text == "") { labelN3.Text = "输入不能为空"; } else { labelN3.Text = " "; } if (TextBox6.Text == "") { labelN6.Text = "输入不能为空"; } else { labelN6.Text = " "; } if (TextBox8.Text == "") { labelN8.Text = "输入不能为空"; } else { labelN8.Text = " "; } if (TextBox9.Text == "") { labelN9.Text = "输入不能为空"; } else { labelN9.Text = " "; } if (TextBox10.Text == "") { labelN10.Text = "输入不能为空"; } else { labelN10.Text = " "; } if (TextBox11.Text == "") { labelN11.Text = "输入不能为空"; } else { labelN11.Text = " "; } } else { labelN1.Text = " "; labelN2.Text = " "; labelN3.Text = " "; labelN4.Text = " "; labelN5.Text = " "; labelN6.Text = " "; labelN7.Text = " "; labelN8.Text = " "; labelN9.Text = " "; labelN10.Text = " "; labelN11.Text = " "; string SQLstr = "UPDATE water_electricity SET dormitoryNumber = '{0}', roomNumber = '{1}',studentNumber = '{2}'," + "checkMonth = '{3}',electricityConsumption = '{4}',electricityBill = '{5}',waterConsumption = '{6}'," + "waterBill = '{7}' WHERE dormitoryNumber = '" + TextBox2.Text + "'" + "and roomNumber = '" + TextBox3.Text + "'"; SQLstr = string.Format(SQLstr, TextBox2.Text, TextBox3.Text, TextBox1.Text, TextBox6.Text, TextBox8.Text, TextBox9.Text, TextBox10.Text, TextBox11.Text); MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("修改成功!", "提示"); } else { MessageBox.Show("修改失败!", "提示"); } } break; } case "水电信息 删除": { if (TextBox2.Text != "" && TextBox3.Text != "") { labelN2.Text = " "; labelN3.Text = " "; string SQLstr = "DELETE FROM water_electricity WHERE dormitoryNumber = '" + TextBox2.Text + "'" + "and roomNumber = '" + TextBox3.Text + "'"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); } else { MessageBox.Show("删除失败!", "提示"); } } else if (TextBox2.Text != "") { labelN3.Text = "输入不能为空"; } else if (TextBox3.Text != "") { labelN2.Text = "输入不能为空"; } else { labelN2.Text = " "; labelN3.Text = " "; string SQLstr = "TRUNCATE TABLE water_electricity"; MySQLCommand sqlcommand = new MySQLCommand(SQLstr, SQLconnection); if (sqlcommand.ExecuteNonQuery() != -1) { MessageBox.Show("删除成功!", "提示"); } else { MessageBox.Show("删除失败!", "提示"); } } break; } } SQLconnection.Close(); }
public ArrayList SelOverMessages(int user_id) { try { con.Open(); DataSet ds = new DataSet(); sql = "SELECT * FROM over_messages where receiver_id=" + user_id; MySQLCommand com = new MySQLCommand(sql, con); MySQLDataAdapter adp = new MySQLDataAdapter(com); adp.Fill(ds); ArrayList messages = new ArrayList(); foreach (DataRow dr in ds.Tables[0].Rows) { MessageData msg = new MessageData(); msg.User_id = (int)dr["sender_id"]; msg.Receiver_id = (int)dr["receiver_id"]; msg.Time = (DateTime)dr["time"]; msg.Message = dr["message"].ToString(); messages.Add(msg); } sql = "DELETE user_message where receiver_id=" + user_id; com = new MySQLCommand(sql, con); com.ExecuteNonQuery(); return messages; } catch(Exception ex) { throw ex; } finally { con.Close(); } }