C# 数据库连接

Program

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace demo2015
{
static class Program
{
/// D:\Cute’s\CS\导师调剂\华理\研究生复试\曾思铭\SOURCE\temp2015\demo2015\Program.cs
/// 应用程序的主入口点。
///
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
try
{
DBHelperSQL.conn = new System.Data.SqlClient.SqlConnection(DBHelperSQL.connStr);
DBHelperSQL.conn.Open();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
Application.Run(new FormMain());
DBHelperSQL.conn.Close();
}
}
}

DBHelperSQL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace demo2015
{
public class DBHelperSQL
{
///
/// 连接字符串
///
public static string connStr = @“server=(local);database=test2015;integrated security=true;”;
///
/// 连接实例初始为空
///
public static SqlConnection conn = null;
///
/// 增删改
///
///
///
public static void TableChange(string sql, SqlParameter[] para = null)
{
try
{
SqlCommand cmd = new SqlCommand(sql, DBHelperSQL.conn);
if (para != null)
cmd.Parameters.AddRange(para);//存储参数过程
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show(“操作成功！”);
}
else
{
MessageBox.Show(“操作失败！\n请检查数据库是否有该条数据！”);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
///
/// 增删改
///
///
///
///

    public static DataTable TableSelect(string sql, SqlParameter[] para = null)
    {
        try
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(sql, DBHelperSQL.conn);
            if (para != null)
                cmd.Parameters.AddRange(para);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return null;
        }
        
    }

}

}

FormMain
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace demo2015
{
public partial class FormMain : Form
{
public Formmain()
{
InitializeComponent();
}

    private void 信息查询ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        FormSearch fs = new FormSearch();
        fs.MdiParent = this;
        fs.Show();
    }

    private void 信息统计ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        FormStatistics fss = new FormStatistics();
        fss.MdiParent = this;
        fss.Show();
    }

    private void 员工关系表维护ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        FormEmployee fe = new FormEmployee();
        fe.MdiParent = this;
        fe.Show();
    }

    private void 退出程序ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        Application.Exit();
    }
}

}

FormEmployee

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

namespace demo2015
{
public partial class FormEmployee : Form
{
public FormEmployee()
{
InitializeComponent();
}
string sex;
private void label1_Click(object sender, EventArgs e)
{

    }

    private void button1_Click(object sender, EventArgs e)
    {
        string sql = @"INSERT INTO [test2015].[dbo].[EMPLOYEE]
                       ([EmpNo]
                       ,[EmpName]
                       ,[EmpSex]
                       ,[EmpAge])
                 VALUES(@EmpNo,@EmpName,@EmpSex,@EmpAge)";
        SqlParameter[] para = { new SqlParameter("@EmpNo", txtEmpNo.Text),
            new SqlParameter("@EmpName", txtEmpName.Text),
            new SqlParameter("@EmpSex", sex),
            new SqlParameter("@EmpAge", txtEmpAge.Text) };
        DBHelperSQL.TableChange(sql, para);
        MyShow();
    }
    
    private void textBox1_TextChanged(object sender, EventArgs e)
    {
        string sql = @"INSERT INTO [test2015].[dbo].[EMPLOYEE]
                       ([EmpNo]
                       ,[EmpName]
                       ,[EmpSex]
                       ,[EmpAge])
                 VALUES(@EmpNo,@EmpName,@EmpSex,@EmpAge)";
        SqlParameter[] para = { new SqlParameter("@EmpNo", txtEmpNo.Text),
            new SqlParameter("@EmpName", txtEmpName.Text),
            new SqlParameter("@EmpSex", sex),
            new SqlParameter("@EmpAge", txtEmpAge.Text) };
        DBHelperSQL.TableChange(sql, para);
        MyShow();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        string sql = @"DELETE FROM [test2015].[dbo].[EMPLOYEE]
                            WHERE [EmpNo] = @EmpNo";
        SqlParameter[] para = { new SqlParameter("@EmpNo", txtEmpNo.Text) };
        DBHelperSQL.TableChange(sql, para);
        MyShow();
    }

    private void button3_Click(object sender, EventArgs e)
    {
        string sql = @"UPDATE [test2015].[dbo].[EMPLOYEE]
                           SET [EmpName] = @EmpName
                              ,[EmpSex] = @EmpSex
                              ,[EmpAge] = @EmpAge
                         WHERE [EmpNo] = @EmpNo";
        SqlParameter[] para = { new SqlParameter("@EmpNo", txtEmpNo.Text),
            new SqlParameter("@EmpName", txtEmpName.Text),
            new SqlParameter("@EmpSex", sex),
            new SqlParameter("@EmpAge", txtEmpAge.Text) };
        DBHelperSQL.TableChange(sql, para);
        MyShow();
    }

    private void button4_Click(object sender, EventArgs e)
    {
        
        MyShow();
    }

    private void rdbtnM_CheckedChanged(object sender, EventArgs e)
    {
        RadioButton rb=(RadioButton)sender;
        sex = rb.Text;
    }

    private void rdbtnF_CheckedChanged(object sender, EventArgs e)
    {
        RadioButton rb = (RadioButton)sender;
        sex = rb.Text;
    }
    public void MyShow()
    {
        string sql = @"SELECT [EmpNo]
                              ,[EmpName]
                              ,[EmpSex]
                              ,[EmpAge]
                          FROM [test2015].[dbo].[EMPLOYEE]";
        dv.DataSource = DBHelperSQL.TableSelect(sql);
    }
}

}

FormSearch
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace demo2015
{
public partial class FormSearch : Form
{
public FormSearch()
{
InitializeComponent();
}

    private void label1_Click(object sender, EventArgs e)
    {

    }

    private void cboEmpNo_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    private void FormSearch_Load(object sender, EventArgs e)
    {
        string sql = @"select * From EMPLOYEE";
        DataTable dt = DBHelperSQL.TableSelect(sql);
        cboEmpNo.DataSource = dt;
        cboEmpNo.DisplayMember = dt.Columns["EmpNo"].ToString();
        cboEmpNo.ValueMember = dt.Columns["EmpNo"].ToString();
        cboEmpName.DataSource = dt;
        cboEmpName.DisplayMember = dt.Columns["EmpName"].ToString();
        cboEmpName.ValueMember = dt.Columns["EmpName"].ToString();
        cboEmpNo.SelectedIndex = 0;
    }

    private void label2_Click(object sender, EventArgs e)
    {

    }

    private void cboEmpName_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sql = @"select c.EmpName 员工姓名,b.CmpName 公司名,a.Salary 薪水
                        from WORKS a,COMPANY b,EMPLOYEE c
                        where a.CmpNo=b.CmpNo and c.EmpNo=a.EmpNo and c.EmpNo=@EmpNo";
        SqlParameter[] para = { new SqlParameter("@EmpNo",cboEmpNo.SelectedValue.ToString()) };
        dv.DataSource = DBHelperSQL.TableSelect(sql, para);
    }
}

}

FormStatistics
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace demo2015
{
public partial class FormStatistics : Form
{
public FormStatistics()
{
InitializeComponent();
}

    private void FormStatistics_Load(object sender, EventArgs e)
    {

    }

    private void button2_Click(object sender, EventArgs e)
    {
        string sql = @"select b.EmpName,SUM(Salary) 薪水
                        from WORKS a,EMPLOYEE b
                        where a.EmpNo=b.EmpNo and b.EmpAge>=40
                        group by b.EmpNo ,b.EmpName
                        order by 薪水 desc";
       
        dv.DataSource = DBHelperSQL.TableSelect(sql);
    }

    private void button1_Click(object sender, EventArgs e)
    {
        string sql = @"select c.EmpName 员工姓名,b.CmpName 公司名
                        from WORKS a,COMPANY b,EMPLOYEE c
                        where a.CmpNo=b.CmpNo and c.EmpNo=a.EmpNo and c.EmpNo in(select EmpNo
														                            from WORKS
														                            group by EmpNo
														                            having COUNT(*)>1)";
        dv.DataSource = DBHelperSQL.TableSelect(sql);
    }
}

}