class_sql.cs

实现企业的人事管理

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;


namespace renshiguanli
{
    public class Class_sql
    {
        private SqlConnection mycon = new SqlConnection(@"data source=NUAA\SQLEXPRESS;integrated security=SSPI;initial catalog=aaa");

        public void set_combobox(ComboBox combobox_department)
        {
            if (mycon.State == ConnectionState.Closed)
            {
                mycon.Open();
            }
            SqlDataAdapter myAdapter = new SqlDataAdapter("select * from tblDepartment", mycon);
            SqlCommandBuilder myBuilder = new SqlCommandBuilder(myAdapter);
            DataSet myDataset = new DataSet();
            myAdapter.Fill(myDataset, "tblDepartment");
            combobox_department.DataSource = myDataset.Tables["tblDepartment"];
            combobox_department.DisplayMember = "deptname";
            combobox_department.ValueMember = "deptid";
            mycon.Close();
        }

        public void add_employee(Class_employee employee)
        {
            mycon.Open();
            SqlDataAdapter myAdapter = new SqlDataAdapter("select * from tblEmployee",mycon);
            SqlCommandBuilder myBuilder = new SqlCommandBuilder(myAdapter);
            DataSet myDataset = new DataSet();
            myAdapter.Fill(myDataset, "tblEmployee");
            DataRow newRow = myDataset.Tables["tblEmployee"].NewRow();
            newRow["name"] = employee.Name;
            newRow["loginname"] = employee.LoginName;
            newRow["password"] = employee.Password;
            newRow["email"] = employee.Email;
            newRow["deptid"] = employee.DeptID;
            newRow["basicsalary"] = employee.BasicSalary;
            newRow["title"] = employee.Title;
            newRow["telephone"] = employee.Telephone;
            newRow["onboarddate"] = employee.OnbordTime;
            newRow["employeelevel"] = employee.EmployeeLevel;
            newRow["photoimage"] = employee.Image;
            myDataset.Tables["tblEmployee"].Rows.Add(newRow);
            myAdapter.Update(myDataset, "tblEmployee");
            mycon.Close();
        }

        public string convert_dept(int deptid)
        {
            string deptname;
            SqlDataAdapter deptAdapter = new SqlDataAdapter("select * from tbldepartment", mycon);
            DataSet deptDataset = new DataSet();
            deptAdapter.Fill(deptDataset, "tbldepartment");
            for (int k = 0; k < deptDataset.Tables["tbldepartment"].Rows.Count; k++)
            {
                if ((int)deptDataset.Tables["tbldepartment"].Rows[k]["deptid"] == deptid)
                {
                    deptname = deptDataset.Tables["tbldepartment"].Rows[k]["deptname"].ToString();
                    return deptname;
                }
            }
            return null;
        }

        public int convert_dept(string deptname)
        {
            int deptid;
            SqlDataAdapter deptAdapter = new SqlDataAdapter("select * from tbldepartment", mycon);
            DataSet deptDataset = new DataSet();
            deptAdapter.Fill(deptDataset, "tbldepartment");
            for (int k = 0; k < deptDataset.Tables["tbldepartment"].Rows.Count; k++)
            {
                if (deptDataset.Tables["tbldepartment"].Rows[k]["deptname"].ToString() == deptname)
                {
                    deptid = (int)deptDataset.Tables["tbldepartment"].Rows[k]["deptid"];
                    return deptid;
                }
            }
            return 0;
        }

        public void selected_employee(int cell_selected)
        {
            if (mycon.State == ConnectionState.Closed)
            {
                mycon.Open();
            }
            SqlDataAdapter myAdapter = new SqlDataAdapter("select * from tblEmployee", mycon);
            SqlCommandBuilder myBuilder = new SqlCommandBuilder(myAdapter);
            DataSet myDataset = new DataSet();
            myAdapter.Fill(myDataset, "tblEmployee");
            for (int k = 0; k < myDataset.Tables["tblemployee"].Rows.Count; k++)
            {
                if ((int)myDataset.Tables["tblemployee"].Rows[k]["employeeid"] == Information.cell_selected)
                {
                    Information.employee.LoginName = myDataset.Tables["tblEmployee"].Rows[k]["loginname"].ToString();
                    Information.employee.Email = myDataset.Tables["tblEmployee"].Rows[k]["email"].ToString();
                    Information.employee.OnbordTime = (DateTime)myDataset.Tables["tblEmployee"].Rows[k]["onboarddate"];
                    Information.employee.Name = myDataset.Tables["tblEmployee"].Rows[k]["name"].ToString();
                    Information.employee.DeptID = (int)myDataset.Tables["tblEmployee"].Rows[k]["deptid"];
                    Information.employee.Telephone = myDataset.Tables["tblEmployee"].Rows[k]["Telephone"].ToString();
                    Information.employee.BasicSalary = (int)myDataset.Tables["tblEmployee"].Rows[k]["BasicSalary"];
                    Information.employee.Title = myDataset.Tables["tblEmployee"].Rows[k]["Title"].ToString();
                    Information.employee.EmployeeLevel = (int)myDataset.Tables["tblEmployee"].Rows[k]["EmployeeLevel"];
                    Information.employee.Image = (byte[])myDataset.Tables["tblEmployee"].Rows[k]["photoimage"];
                    break;
                }
            }
        }

        public void modify_employee(Class_employee employee)
        {
            mycon.Open();
            SqlDataAdapter myAdapter = new SqlDataAdapter("select * from tblEmployee", mycon);
            SqlCommandBuilder myBuilder = new SqlCommandBuilder(myAdapter);
            DataSet myDataset = new DataSet();
            myAdapter.Fill(myDataset, "tblEmployee");
            for (int k = 0; k < myDataset.Tables["tblemployee"].Rows.Count; k++)
            {
                if ((int)myDataset.Tables["tblemployee"].Rows[k]["employeeid"] == Information.cell_selected)
                {
                    myDataset.Tables["tblEmployee"].Rows[k]["loginname"] = Information.employee.LoginName;
                    myDataset.Tables["tblEmployee"].Rows[k]["email"] = Information.employee.Email;
                    myDataset.Tables["tblEmployee"].Rows[k]["onboarddate"] = Information.employee.OnbordTime;
                    myDataset.Tables["tblEmployee"].Rows[k]["name"] = Information.employee.Name;
                    myDataset.Tables["tblEmployee"].Rows[k]["deptid"] = Information.employee.DeptID;
                    myDataset.Tables["tblEmployee"].Rows[k]["Telephone"] = Information.employee.Telephone;
                    myDataset.Tables["tblEmployee"].Rows[k]["BasicSalary"] = Information.employee.BasicSalary;
                    myDataset.Tables["tblEmployee"].Rows[k]["Title"] = Information.employee.Title;
                    myDataset.Tables["tblEmployee"].Rows[k]["EmployeeLevel"] = Information.employee.EmployeeLevel;
                    myDataset.Tables["tblEmployee"].Rows[k]["photoimage"] = Information.employee.Image;
                    myAdapter.Update(myDataset, "tblemployee");
                    break;
                }
            }
            mycon.Close();
        }

        public ArrayList search_employee(string name_search, string email_search, int deptid_search)
        {
            ArrayList found_employee=new ArrayList();
            mycon.Open();
            SqlDataAdapter myAdapter = new SqlDataAdapter("select * from tblEmployee", mycon);
            SqlCommandBuilder myBuilder = new SqlCommandBuilder(myAdapter);
            DataSet myDataset = new DataSet();
            myAdapter.Fill(myDataset, "tblEmployee");
            if (name_search != "")
            {
                for (int k = 0; k < myDataset.Tables["tblEmployee"].Rows.Count; k++)
                {
                    if (myDataset.Tables["tblEmployee"].Rows[k]["name"].ToString() == name_search)
                    {
                        if ((email_search != "" && email_search != myDataset.Tables["tblEmployee"].Rows[k]["email"].ToString()) || (deptid_search != -1 && deptid_search != (int)myDataset.Tables["tblEmployee"].Rows[k]["deptid"]))
                        {
                        }
                        else
                        {
                            found_employee.Add (k);
                        }
                    }
                }
            }
            else if (email_search != "")
            {
                for (int k = 0; k < myDataset.Tables["tblEmployee"].Rows.Count; k++)
                {
                    if (myDataset.Tables["tblEmployee"].Rows[k]["email"].ToString() == email_search)
                    {
                        if (deptid_search != -1 && deptid_search != (int)myDataset.Tables["tblEmployee"].Rows[k]["deptid"])
                        {
                        }
                        else
                        {
                            found_employee.Add(k) ;
                        }
                    }
                }
            }
            else
            {
                for (int k = 0; k < myDataset.Tables["tblEmployee"].Rows.Count; k++)
                {
                    if (deptid_search == (int)myDataset.Tables["tblEmployee"].Rows[k]["deptid"])
                    {
                        found_employee.Add(k);
                    }
                }
            }
            mycon.Close();
            return found_employee;
        }
    }
}