private void button1_Click(object sender, EventArgs e)
{
string a = title.Text;
string t= dateTimePicker1.Text;
string b = content.Text;
MySqlConnection conn = null;
MySqlCommand cmd = null;
string sql = "insert into note()";
MySQLDataAdapter mda = new MySQLDataAdapter(sql,conn);
try
{
conn = base.GetConn();
conn.Open();
cmd = new MySqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
return i;
conn.Close();
}
catch (Exception)
{
throw;
}
}
static void Main(string[] args)
{
conn = new MySQLConnection(new MySQLConnectionString("10.125.103.139", "heartlink", "qwe", "123").AsString);
cmd = new MySQLCommand();
da = new MySQLDataAdapter();
String pass = "******";
String sql = "select ID from students where password="******"ID = {0}", DBReader.GetString(0));
}
}
finally
{
DBReader.Close();
conn.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 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_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 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 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();
}
//获得数据库数据
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;
}
//获得数据库数据
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 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;
}
}
//获得数据库数据
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);
}
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();
}
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);
}
//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();
}
//获得数据库数据
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;
}
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 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;
}
}
//获得数据库数据
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;
}
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();
}
}
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_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 static MySQLDataReader ExecuteReader(string strSQL)
{
MySQLConnection connection = new MySQLConnection(connectionString);
MySQLCommand cmd = new MySQLCommand(strSQL, connection);
MySQLDataReader myReader = null;
try
{
connection.Open();
MySQLDataAdapter command = new MySQLDataAdapter(strSQL, connection);
MySQLCommand commn = new MySQLCommand("set names gbk", connection);
myReader = cmd.ExecuteReaderEx();
return myReader;
}
catch (MySQLException e)
{
throw e;
}
finally
{
myReader.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 Program()
{
conn = new MySQLConnection(new MySQLConnectionString("10.125.103.139", "heartlink", "qwe", "123").AsString);
cmd = new MySQLCommand();
da = new MySQLDataAdapter();
}
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();
}
}
