110,499
社区成员
发帖
与我相关
我的任务
分享
OleDbDataAdapter da2 = null; //建立个OleDbDataAdapter
da2 = new OleDbDataAdapter(cmd); //用定义好的cmd来配置OleDbDataAdapter
DataSet() ds = new DataSet(); //建立个DataSet()
OleDbCommandBuilder cmdb = new OleDbCommandBuilder(da2); //OleDbCommandBuilder 具体的事情,你百度下吧
da2.Fill(ds); //填充DataSet()
da2.Update(ds); //ds的数据更改后,调用OleDbDataAdapter的Update()方法
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace EmployeeManage
{
public partial class EmployeeEdit : Form
{
public EmployeeEdit()
{
InitializeComponent();
}
public EmployeeEdit(bool _isEdit, int _Id,bool isAdmin,bool _isSuper)
{
InitializeComponent();
Id = _Id;
isEdit = _isEdit;
if (!_isEdit)
this.Text = "添加员工信息";
if (!isAdmin)
ckbIsMng.Enabled = false;
if (!_isSuper)
{
ckbSuperMng.Visible = false;
}
if (_isSuper)
{
ckbIsMng.Enabled = true;
}
}
private int Id;
private bool isEdit;
public byte[] photo;
public EmployeeModel emp = new EmployeeModel();
private void bindInfo()
{
if (isEdit)
{
txtWorkNo.ReadOnly = true;
string sql = "Select * from [Employee] where [Id]=" + Id;
DataTable dt = DBHelper.GetTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
txtWorkNo.Text = dt.Rows[0]["WorkNo"].ToString();
txtName.Text = dt.Rows[0]["Name"].ToString();
txtTelephone.Text = dt.Rows[0]["Telephone"].ToString();
txtAddress.Text = dt.Rows[0]["Address"].ToString();
txtNowAddress.Text = dt.Rows[0]["NowAddress"].ToString();
txtIDNumber.Text = dt.Rows[0]["IDNumber"].ToString();
txtLevel.Text = dt.Rows[0]["Level"].ToString();
if (dt.Rows[0]["photo"] != null && dt.Rows[0]["photo"].ToString() != "")
{
pictureBox1.Image = PictureOpreate.ByteArrayToImage((byte[])dt.Rows[0]["photo"], 1);
}
if (dt.Rows[0]["IsMng"].ToString() == "True")
ckbIsMng.Checked = true;
else
ckbIsMng.Checked = false;
if (dt.Rows[0]["IsSuperMng"].ToString() == "True")
ckbSuperMng.Checked = true;
else
ckbSuperMng.Checked = false;
txtDepart.Text = dt.Rows[0]["DepartmentName"].ToString();
rtbDemo.Text = dt.Rows[0]["Demo"].ToString();
if (dt.Rows[0]["Sex"].ToString() == "True")
rbtMan.Checked = true;
else
rbtWoman.Checked = true;
}
}
}
private void EmployeeEdit_Load(object sender, EventArgs e)
{
this.Icon = new System.Drawing.Icon(AppDomain.CurrentDomain.BaseDirectory + @"Logo/Logo.ico");
bindInfo();
}
private void BtnUpdate_Click(object sender, EventArgs e)
{
if (txtWorkNo.Text.Trim() == "")
{
FormMessageBox.Show(LoadMode.Prompt,"员工工号不能为空!");
txtWorkNo.Focus();
return;
}
if (txtName.Text.Trim() == "")
{
FormMessageBox.Show(LoadMode.Prompt, "员工姓名不能为空!");
txtName.Focus();
return;
}
emp.WorkNo = txtWorkNo.Text;
emp.Name = txtName.Text.Trim();
emp.Age = 0;
emp.Telephone = txtTelephone.Text.Trim();
emp.Address = txtNowAddress.Text.Trim();
emp.NowAddress = txtNowAddress.Text.Trim();
emp.IDNumber = txtIDNumber.Text.Trim();
emp.Level = txtLevel.Text.Trim();
emp.IsMng = ckbIsMng.Checked;
emp.IsSuperMng = ckbSuperMng.Checked;
emp.Photo = PictureOpreate.ImageToBytes(pictureBox1.Image);
emp.Demo = rtbDemo.Text.Trim();
emp.DepartmentName = txtDepart.Text.Trim();
if (rbtMan.Checked)
emp.Sex = true;
else
emp.Sex = false;
if (isEdit)
{
emp.Id = Id;
}
else
{
string sql = "Select WorkNo from [Employee] where [WorkNo]='" + txtWorkNo.Text.Trim() + "'";
object exit = DBHelper.GetScalar(sql);
if (exit != null)
{
FormMessageBox.Show(LoadMode.Warning, "该员工工号已存在!");
// MessageBox.Show("该员工工号已存在");
return;
}
emp.Passwrod = DESEncryption.DesEncrypt("123456");
}
this.DialogResult =DialogResult.OK;
}
private void BtnColse_Click(object sender, EventArgs e)
{
this.Close();
}
private void btnChoose_Click(object sender, EventArgs e)
{
OpenFileDialog open = new OpenFileDialog();
if (open.ShowDialog() == DialogResult.OK)
{
photo = PictureOpreate.ReadPictureBytes(open.FileName);
pictureBox1.Image = PictureOpreate.GetPicBySize(PictureOpreate.ReadPicture(open.FileName), 114, 156);
}
}
/// <summary>
/// 限制输入数字以外的字符
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void textbox_KeyPress(object sender, KeyPressEventArgs e)
{
if (((int)e.KeyChar < 48 || (int)e.KeyChar > 57) && e.KeyChar != 8)
{
e.Handled = true;
}
}
private void ckbSuperMng_CheckedChanged(object sender, EventArgs e)
{
if (ckbSuperMng.Checked)
{
ckbIsMng.Checked = true;
ckbIsMng.Enabled = false;
}
else
{
ckbIsMng.Enabled = true;
}
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace EmployeeManage
{
public partial class EmployeeList : Form
{
public EmployeeList()
{
InitializeComponent();
}
public static EmployeeList form = null;
public static EmployeeList GetInstance()
{
if (form == null || form.IsDisposed)
form = new EmployeeList();
return form;
}
public EmployeeList(bool isMng, string _workNo, bool _isSuperMng)
{
InitializeComponent();
}
private bool isMng;
public bool IsMng
{
get { return isMng; }
set { isMng = value; }
}
private string workNo;
public string WorkNo1
{
get { return workNo; }
set { workNo = value; }
}
private bool isSuperMng;
public bool IsSuperMng
{
get { return isSuperMng; }
set { isSuperMng = value; }
}
private void EmployeeList_Load(object sender, EventArgs e)
{
this.Icon = new System.Drawing.Icon(AppDomain.CurrentDomain.BaseDirectory + @"Logo/Logo.ico");
if (!IsMng)
{
txtWorkNo.Text = WorkNo1;
tsmEdit.Visible = false;
btnAdd.Visible = false;
tsmDelete.Visible = false;
//tsmMission.Visible = false;
tsmAttend.Visible = false;
}
bindDgv();
}
private void btnSearch_Click(object sender, EventArgs e)
{
bindDgv();
}
private void bindDgv()
{
string sql = "SELECT b.Name, b.WorkNo, b.Age, b.Sex, b.Telephone, b.DepartmentId, b.[Level], b.IDNumber, b.ID,b.DepartmentName FROM [Employee] b where (1=1) ";
if (txtName.Text.Trim() != "")
{
sql += " and ( [Name] like '%" + txtName.Text.Trim() + "%' )";
}
if (txtWorkNo.Text.Trim() != "")
{
sql += " and ([WorkNo] like '%" + txtWorkNo.Text.Trim() + "%') ";
}
sql += " order by b.[ID] desc";
DataTable dt = DBHelper.GetTable(sql);
dgvList.AutoGenerateColumns = false;
dgvList.DataSource = dt;
}
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
EmployeeEdit add = new EmployeeEdit(false, 0, groupBox1.Visible, IsSuperMng);
if (add.ShowDialog() == DialogResult.OK)
{
EmployeeModel emp = add.emp;
string sql = string.Format("Insert into [Employee]([WorkNo],[Name],[Age],[Telephone],[Address],[Demo],[Sex],[DepartmentName],[IDNumber],[IsMng],[Level],[NowAddress],[Password],[isSuperMng])values('{0}','{1}',{2},'{3}','{4}','{5}',{6},'{7}','{8}',{9},'{10}','{11}','{12}',{13})", emp.WorkNo, emp.Name, emp.Age, emp.Telephone, emp.Address, emp.Demo, emp.Sex, emp.DepartmentName, emp.IDNumber, emp.IsMng, emp.Level, emp.NowAddress, emp.Passwrod, emp.IsSuperMng);
if (DBHelper.Execute(sql))
{
string sql2 = "Update [Employee] set photo=@photo where workNo='" + emp.WorkNo + "'";
DBHelper.Execute(sql2, add.emp.Photo);
bindDgv();
FormMessageBox.Show(LoadMode.Prompt, "员工添加成功!");
this.Focus();
// MessageBox.Show("员工添加成功!");
}
else
{
// MessageBox.Show("员工添加失败!");
FormMessageBox.Show(LoadMode.Error, "员工添加失败!");
return;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void tsmEdit_Click(object sender, EventArgs e)
{
try
{
int index = dgvList.SelectedCells[0].RowIndex;
int Id = Convert.ToInt32(dgvList.SelectedRows[0].Cells["ID"].Value.ToString());
EmployeeEdit edit = new EmployeeEdit(true, Id, tsmDelete.Visible, isSuperMng);
if (edit.ShowDialog() == DialogResult.OK)
{
EmployeeModel emp = edit.emp;
string sql = string.Format("Update [Employee] set [WorkNo]='{0}',[Name]='{1}',[Age]={2},[Telephone]='{3}',[Address]='{4}',[Demo]='{5}',[Sex]={6},[DepartmentName]='{8}',[IDNumber]='{9}',[IsMng]={10}, [Level]='{11}',[NowAddress]='{12}',[IsSuperMng]={13} where [ID]={7}", emp.WorkNo, emp.Name, emp.Age, emp.Telephone, emp.Address, emp.Demo, emp.Sex, emp.Id, emp.DepartmentName, emp.IDNumber, emp.IsMng, emp.Level, emp.NowAddress, emp.IsSuperMng);
if (DBHelper.Execute(sql))
{
string sql2 = "Update [Employee] set photo=@photo where workNo='" + emp.WorkNo + "'";
DBHelper.Execute(sql2, edit.emp.Photo);
bindDgv();
if (dgvList.Rows.Count > 0)
dgvList.Rows[index].Selected = true;
this.Focus();
this.Focus();
// MessageBox.Show("员工添加成功!");
}
else
{
// MessageBox.Show("员工添加失败!");
return;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void tsmDelete_Click(object sender, EventArgs e)
{
try
{
string Id = dgvList.SelectedRows[0].Cells["ID"].Value.ToString();
string workNo = dgvList.SelectedRows[0].Cells["workNo"].Value.ToString();
if (workNo == WorkNo1)
{
FormMessageBox.Show(LoadMode.Prompt, "自己无法删除自己!");
this.Focus();
return;
}
if (IsMng)
{
object obj = DBHelper.GetScalar("Select Id from [Employee] where IssuperMng=True and Id=" + Id);
if (obj != null)
{
FormMessageBox.Show(LoadMode.Prompt, "你无权删除该超级管理员!");
this.Focus();
return;
}
}
string name = dgvList.SelectedRows[0].Cells["Name"].Value.ToString();
if (MessageBox.Show("确定删除员工[" + name + "]、工号[" + workNo + "]?", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
{
string sql = "Delete from [Employee] Where [ID]=" + Id;
if (DBHelper.Execute(sql))
{
bindDgv();
FormMessageBox.Show(LoadMode.Prompt, "员工删除成功!");
this.Focus();
}
else
{
FormMessageBox.Show(LoadMode.Error, "员工删除失败!");
return;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void dgvList_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.ColumnIndex == 2)
{
DataGridViewRow row = this.dgvList.Rows[e.RowIndex];
if (row != null)
{
if (row.Cells["Sex"].Value != null && row.Cells["Sex"].Value.ToString() != "")
{
if (row.Cells["Sex"].Value.ToString() == "True")
e.Value = "男";
else
e.Value = "女";
}
}
}
}
private void dgvList_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.Button == MouseButtons.Right)
{
if (e.RowIndex >= 0)
{
if (dgvList.Rows[e.RowIndex].Selected == false)
{
dgvList.ClearSelection();
dgvList.Rows[e.RowIndex].Selected = true;
}
if (dgvList.SelectedRows.Count == 1 && e.ColumnIndex != -1)
{
dgvList.CurrentCell = dgvList.Rows[e.RowIndex].Cells[e.ColumnIndex];
}
dgvList.ContextMenuStrip = contextMenuStrip1;
}
else
{
dgvList.ContextMenuStrip = null;
return;
}
}
}
private void dgvList_DoubleClick(object sender, EventArgs e)
{
tsmEdit_Click(null, null);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Configuration;
namespace EmployeeManage
{
public class DBHelper
{
//属性:数据库链接对象
private static OleDbConnection conn;
public static OleDbConnection Conn
{
get
{
try
{
string connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" + "" + Application.StartupPath + @"\Database.mdb;Jet OLEDB:Database Password=qaz123plm";
// string connstr = ConfigurationManager.ConnectionStrings["DatabaseConnStr"].ConnectionString.ToString();
if (conn == null)
conn = new OleDbConnection(connstr);
if (conn.State == ConnectionState.Closed)
conn.Open();
if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
return conn;
}
catch (Exception ex)
{
throw;
}
}
}
//方法:查询,DataReader
public static OleDbDataReader GetReader(string SqlStr)
{
OleDbCommand cmd = new OleDbCommand(SqlStr, Conn);
return cmd.ExecuteReader();
}
public static OleDbDataReader GetReader(string SqlStr, OleDbParameter[] paras)
{
OleDbCommand cmd = new OleDbCommand(SqlStr, Conn);
cmd.Parameters.AddRange(paras);
return cmd.ExecuteReader();
}
//查询:DataTable
public static DataTable GetTable(string SqlStr)
{
try
{
OleDbDataAdapter dap = new OleDbDataAdapter(SqlStr, Conn);
DataSet ds = new DataSet();
dap.Fill(ds);
conn.Close();
return ds.Tables[0];
}
catch (Exception ex)
{
throw;
}
}
//增删改
public static bool Execute(string SqlStr)
{
OleDbCommand cmd = new OleDbCommand(SqlStr, Conn);
int result = cmd.ExecuteNonQuery();
conn.Close();
return result > 0;
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int Execute(string SqlStr, byte[] photo)
{
OleDbCommand cmd = new OleDbCommand(SqlStr, Conn);
OleDbParameter myParameter = new OleDbParameter("@photo", SqlDbType.Image);
myParameter.Value = photo;
cmd.Parameters.Add(myParameter);
int result = cmd.ExecuteNonQuery();
return result;
}
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <returns>影响的记录数</returns>
public static bool Execute(string SqlStr, string pwd)
{
OleDbCommand cmd = new OleDbCommand(SqlStr, Conn);
OleDbParameter myParameter = new OleDbParameter("@password", SqlDbType.NChar);
myParameter.Value = pwd;
cmd.Parameters.Add(myParameter);
int result = cmd.ExecuteNonQuery();
return result>0;
}
//返回首行首列
public static object GetScalar(string SqlStr)
{
OleDbCommand cmd = new OleDbCommand(SqlStr, Conn);
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(List<string> SQLStringList)
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = Conn;
OleDbTransaction tx = Conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OleDb.OleDbException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
}