DB.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography.X509Certificates;
namespace test
{
internal class DB
{
//连接字符串
public static string connstr = @"server=LAPTOP-FP07RJI9;database=2015;integrated security=true;";
//连接实例
public static SqlConnection conn = null;
//增删改
public static void TableChange(string sql, SqlParameter[] para = null)
{
try
{
SqlCommand cmd = new SqlCommand(sql, DB.conn);
if (para != null)
cmd.Parameters.AddRange(para);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("成功");
}
else
{
MessageBox.Show("失败");
}
}
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, DB.conn);//用于执行SQL语句和存储过程
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;
}
}
}
}
Program.cs
namespace test
{
internal static class Program
{
static void Main()
{
try
{
DB.conn=new System.Data.SqlClient.SqlConnection(DB.connstr);
DB.conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Application.Run(new FormMain());
DB.conn.Close();
}
}
}
维护表
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;
using System.Data.SqlClient;
namespace Demo2015
{
public partial class FormEmployee : Form
{
public FormEmployee()
{
InitializeComponent();
}
string sex;
private void btnAdd_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 btnDelete_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 btnSelect_Click(object sender, EventArgs e)
{
MyShow();
}
private void btnUpdate_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 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);
}
}
}
查询表
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;
using System.Data.SqlClient;
namespace Demo2015
{
public partial class FormSearch : Form
{
public FormSearch()
{
InitializeComponent();
}
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 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);
}
}
}