dboperate.cs

随着我国市场经济的快速发展

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Drawing;
namespace PMS.PMSClass
{
    class DBOperate
    {
        SqlConnection conn = DBConnection.MyConnection();
        public int OperateData(string strSql)//操作数据库，执行各种SQL语句
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql,conn);
            int i=(int)cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public void BindDataGridView(DataGridView dgv,string sql)//绑定DataGridView控件
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            dgv.DataSource = ds.Tables[0];
            ds.Dispose();
        }
        public int HumanNum(string strsql)//查找指定数据表的人数
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(strsql,conn);
            int i =(int)cmd.ExecuteScalar();
            conn.Close();
            return i;
        }
        public void Read_Image(OpenFileDialog openF, PictureBox MyImage)  //显示选择的图片
        {
            openF.Filter = "*.jpg|*.jpg|*.bmp|*.bmp";   //指定OpenFileDialog控件打开的文件格式
            if (openF.ShowDialog() == DialogResult.OK)  //如果打开了图片文件
            {
                try
                {
                    MyImage.Image = System.Drawing.Image.FromFile(openF.FileName);  //将图片文件存入到PictureBox控件中
                }
                catch
                {
                    MessageBox.Show("您选择的图片不能被读取或文件类型不对！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }
        public void SaveImage(string MID, OpenFileDialog openF)//将图片以二进制存入数据库中
        {
            string strimg = openF.FileName.ToString();  //记录图片的所在路径
            FileStream fs = new FileStream(strimg, FileMode.Open, FileAccess.Read); //将图片以文件流的形式进行保存
            BinaryReader br = new BinaryReader(fs);
            byte[] imgBytesIn= br.ReadBytes((int)fs.Length);  //将流读入到字节数组中
            conn.Open();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tb_employee Set employeePhoto=@Photo where employeeID=" + MID);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
            cmd.Parameters.Add("@Photo", SqlDbType.Binary).Value = imgBytesIn;
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public void Get_Image(string ygname,PictureBox pb)//将图片从数据库中取出
        {
            byte[] imagebytes = null;
            conn.Open();
            SqlCommand com = new SqlCommand("select * from tb_employee where employeeID='" + ygname + "'", conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                imagebytes = (byte[])dr.GetValue(11);
            }
            dr.Close();
            conn.Close();
            MemoryStream ms = new MemoryStream(imagebytes);
            Bitmap bmpt = new Bitmap(ms);
            pb.Image = bmpt;
        }
        public DataSet GetTable(string sql)//返回dataset
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            ds.Dispose();
            return ds;
        }
        public void BindDropdownlist(string strTable,ComboBox cb,int i)//绑定下拉列表,参数“表”，“控件”，“列”
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from "+strTable,conn);
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                cb.Items.Add(sdr[i].ToString());
            }
            conn.Close();
        }
        public decimal GYSD(int pay)//个人所得税计算
        {
            decimal tax=0;//个人所得税
            int Y = pay - 1600;//计税工资=每月工资-1600
            if (pay <= 1600)
            {
                tax = 0;
            }
            else
            {
                if (Y >= 0 || Y <= 500)
                {
                    tax = (decimal)(Y * 0.05);
                }
                else
                {
                    if (Y > 500 || Y <= 2000)
                    {
                        tax = (decimal)(Y * 0.1 - 25);
                    }
                    else
                    {
                        if (Y > 2000 || Y <= 5000)
                        {
                            tax = (decimal)(Y * 0.15 - 125);
                        }
                        else
                        {
                            if (Y > 5000 || Y <= 20000)
                            {
                                tax = (decimal)(Y * 0.2 - 375);
                            }
                            else
                            {
                                if (Y > 20000 || Y <= 40000)
                                {
                                    tax = (decimal)(Y * 0.25 - 1375);
                                }
                                else
                                {
                                    if (Y > 40000 || Y <= 60000)
                                    {
                                        tax = (decimal)(Y * 0.3 - 3375);
                                    }
                                    else
                                    {
                                        if (Y > 60000 || Y <= 80000)
                                        {
                                            tax = (decimal)(Y * 0.35 - 6375);
                                        }
                                        else
                                        {
                                            if (Y > 80000 || Y <= 100000)
                                            {
                                                tax = (decimal)(Y * 0.4 - 10375);
                                            }
                                            else
                                            {
                                                if (Y > 100000)
                                                {
                                                    tax = (decimal)(Y * 0.45 - 15375);
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return tax;
        }
        public void DeleUserInfo(string id)
        {
            string str1 = "delete from tb_check where PID='"+id+"'";
            string str2 = "delete from tb_pay where YID='" + id + "'";
            string str3 = "delete from tb_prize where UserID='" + id + "'";
            string str4 = "delete from tb_redeploy where UID='" + id + "'";
            OperateData(str1);
            OperateData(str2);
            OperateData(str3);
            OperateData(str4);
        }
    }
}