.net窗体程序要点（速记）

SqlManage.cs

using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;

//public static string cnn_string = @"server=DESKTOP-48M8EJT\MSSQLSERVER1;database=tmp2015;integrated security=SSPI";
public static string cnnstring = @"Server=AN2XOC6HS03APKT;Database=tmp2019;Uid=sa;Pwd=liwei666;";
public static SqlConnection cnn = null;

/// <summary>
/// 数据库查询操作
/// </summary>
/// <param name="sql">数据库语句</param>
public static void ExcuteTable(string sql)
{
    try
    {
        SqlCommand cmd = new SqlCommand(sql, SqlManage.cnn);
        if (cmd.ExecuteNonQuery() > 0)
        {
            MessageBox.Show("操作成功");
        }
        else
        {
            MessageBox.Show("操作失败!\n检查数据库是否有该记录");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

/// <summary>
/// 数据库更新操作(包括插入、修改、删除)
/// </summary>
/// <param name="sql">数据库语句</param>
/// <returns>返回查询结果</returns>
public static DataTable SelectTable(string sql)
{
    try
    {
        DataTable table = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(sql, SqlManage.cnn);
        sda.Fill(table);
        return table;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        return null;
    }
}

/// <summary>
/// 设置DataGridView控件的属性
/// </summary>
/// <param name="d">控件名</param>
public static void SetDataGridView(DataGridView d)
{
    d.ReadOnly = true;
    d.AllowUserToAddRows = false;
    d.RowHeadersVisible = false;
    d.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}

/// <summary>
/// 判断TextBox控件是否输入了内容
/// </summary>
/// <param name="t">控件名</param>
/// <returns>输入了内容发挥true</returns>
public static bool JudTextBox(TextBox t)
{
    return t.Text.Trim().Length > 0 ? true : false;
}

 

Program.cs

using System.Data.SqlClient;

Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
try
{
    SqlManage.cnn = new SqlConnection(SqlManage.cnnstring);
    SqlManage.cnn.Open();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
    Application.Exit();
    return;
}
Application.Run(new FormMain());

SqlManage.cnn.Close();

 

插入数据：

if (!SqlManage.JudTextBox(this.textBox1))
{
    MessageBox.Show("输入员工编号");
    return;
}
if (!SqlManage.JudTextBox(this.textBox2))
{
    MessageBox.Show("输入员工姓名");
    return;
}
if (!SqlManage.JudTextBox(this.textBox3))
{
    MessageBox.Show("输入员工年龄");
    return;
}
int age;
if (!int.TryParse(this.textBox3.Text, out age))
{
    MessageBox.Show("年龄必须是整数");
    return;
}
string sex = this.radioButton1.Checked ? "男" : "女";
string sql = string.Format("insert into EMPLOYEE values('{0}','{1}','{2}','{3}')",
    this.textBox1.Text.Trim(), this.textBox2.Text.Trim(), sex, age);
SqlManage.ExcuteTable(sql);

 

修改数据：

if (!SqlManage.JudTextBox(this.textBox1))
{
    MessageBox.Show("输入员工编号");
    return;
}
if (!SqlManage.JudTextBox(this.textBox2))
{
    MessageBox.Show("输入员工姓名");
    return;
}
if (!SqlManage.JudTextBox(this.textBox3))
{
    MessageBox.Show("输入员工年龄");
    return;
}
int age;
if (!int.TryParse(this.textBox3.Text, out age))
{
    MessageBox.Show("年龄必须是整数");
    return;
}
string sex = this.radioButton1.Checked ? "男" : "女";
string sql = string.Format("update EMPLOYEE set  EmpName='{0}',EmpSex='{1}',EmpAge='{2}' where EmpNo='{3}'",
     this.textBox2.Text.Trim(), sex, age, this.textBox1.Text.Trim());
SqlManage.ExcuteTable(sql);

 

删除数据：

if (!SqlManage.JudTextBox(this.textBox1))
{
    MessageBox.Show("输入员工编号");
    return;
}
string sql = string.Format("delete from EMPLOYEE where EmpNo='{0}'", this.textBox1.Text.Trim());
SqlManage.ExcuteTable(sql);

 

显示网格：

SqlManage.SetDataGridView(this.dataGridView1);
string sql = "select EmpNO as 员工编号,EmpName as 员工姓名,EmpSex as 员工表性别,EmpAge as 员工年龄 from EMPLOYEE";
DataTable table = SqlManage.SelectTable(sql);
this.dataGridView1.DataSource = table;

 

下拉框查找：

SqlManage.SetDataGridView(this.dataGridView1);

//初始化教师编号
string sql = "select EmpNo from EMPLOYEE";
DataTable table = SqlManage.SelectTable(sql);
string empno;
foreach (DataRow row in table.Rows)
{
    empno = row["EmpNo"].ToString();
    this.comboBox1.Items.Add(empno);
}
if (table.Rows.Count > 0)
{
    this.comboBox1.SelectedIndex = 0;
}

//初始化教师姓名
string sql_name = "select EmpName from EMPLOYEE";
table.Clear();
table = SqlManage.SelectTable(sql_name);
string empname;
foreach (DataRow row in table.Rows)
{
    empname = row["EmpName"].ToString();
    this.comboBox2.Items.Add(empname);
}
if (table.Rows.Count > 0)
{
    this.comboBox2.SelectedIndex = 0;
}

 

string sql = "";
if (this.radioButton1.Checked)
{
    sql = string.Format("select CmpName as 公司名,Salary as 工资 from EMPLOYEE,COMPANY,WORKS where EMPLOYEE.EmpNo=WORKS.EmpNo and COMPANY.CmpNo=WORKS.CmpNo and WORKS.EmpNo = '{0}'",
        this.comboBox1.Text);
}
else if (this.radioButton2.Checked)
{
    sql = string.Format("select CmpName as 公司名,Salary as 工资 from EMPLOYEE,COMPANY,WORKS where EMPLOYEE.EmpNo=WORKS.EmpNo and COMPANY.CmpNo=WORKS.CmpNo and EmpName = '{0}'",
        this.comboBox2.Text);
}
DataTable table = SqlManage.SelectTable(sql);
if (table.Rows.Count > 0)
{
    this.dataGridView1.DataSource = table;
}
else
{
    MessageBox.Show("没有相关内容");
}

 

having子句查询：

SqlManage.SetDataGridView(this.dataGridView1);
string sql = "select EmpName as 员工姓名, CmpName as 公司名 from EMPLOYEE,COMPANY,WORKS where EMPLOYEE.EmpNo=WORKS.EmpNo and COMPANY.CmpNo=WORKS.CmpNo and" +
    " WORKS.EmpNo in (select EmpNo from WORKS group by EmpNo having count(*)>1)";
DataTable table = SqlManage.SelectTable(sql);
this.dataGridView1.DataSource = table;

 

readme.txt

1.附加数据库文件test.mdf
3 2.backinfo.bak为数据库的备份文件
3.建表.sql为所有数据库操作的sql语句
4.Info.doc为说明文件
5.连接字符串:"Server=<你的计算机全名>;Database=test;Uid=sa;Pwd=123456;"

 

Info.doc

数据库用户名:sa;
登录密码:123456;
连接字符串:Server=AN2XOC6HS03APKT;Database=tmp2019;Uid=sa;Pwd=123456;