//vs与数据库建立连接的过程:
//与数据库建立起连接(建立Connection类的实例对象)
//打开连接(Connection实例对象的Open()方法)
//执行操作数据库命令(对应SqlCommand)
//关闭数据库连接
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;
using System.Runtime.CompilerServices;
namespace 学生页面选课系统
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string strcon = DataConn.con;
//通过类名直接调用静态成员变量
SqlConnection conn = new SqlConnection(strcon);
//给数据库建立连接
conn.Open();
//查询用sq1语句,数据库连接
string strsq1 = " select Snum as 学生学号,Sname as 学生姓名,Sage as 学生年龄,Ssex as 学生性别 ,Sdept as 所属院系 from Table_student";
//填充数据集
//创建SqlDataAdapter对象
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(strsq1, conn);
//填充DataSet
DataSet dataset = new DataSet();
sqlDataAdapter.Fill(dataset, "Table_student");
//将数据表格在dataGridView中展示
dataGridView1.DataSource = dataset.Tables["Table_student"];
conn.Close();
}
catch (Exception mess)
{
MessageBox.Show("出现异常!", mess.Message);
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
{
MessageBox.Show("查询学号不能为空!");
}
else
{
try
{
string strcon = DataConn.con;
//通过类名直接调用静态成员变量
SqlConnection conn = new SqlConnection(strcon);
//给数据库建立连接
conn.Open();
//查询用sq1语句,数据库连接
string strsq1 = " select Snum as 学生学号,Sname as 学生姓名,Sage as 学生年龄,Ssex as 学生性别 ,Sdept as 所属院系 from Table_student where Snum='" + textBox1.Text + "'";
MessageBox.Show(strcon);
//填充数据集
//创建SqlDataAdapter对象
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(strsq1, conn);
//填充DataSet
DataSet dataset = new DataSet();
sqlDataAdapter.Fill(dataset, "Table_student");
//将数据表格在dataGridView中展示
if (dataset.Tables["Table_student"].Rows.Count == 0)
{
MessageBox.Show("您输入的学号有误!");
}
else
{
dataGridView1.DataSource = dataset.Tables["Table_student"];
}
conn.Close();
}
catch (Exception mess)
{
MessageBox.Show("出现异常!", mess.Message);
}
}
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || comboBox1.SelectedItem.ToString () == "")
{
MessageBox.Show("学生信息不能有空选项!");
}
else
{
try
{
string strcon = DataConn.con;
//通过类名直接调用静态成员变量
SqlConnection conn = new SqlConnection(strcon);
//给数据库建立连接
conn.Open();
//查询用sq1语句,数据库连接
string strsq1 = "insert into Table_student values ('" + textBox1.Text.Trim() + "','" + textBox2.Text .Trim ()+ "','" + textBox3.Text.Trim() + "','" + comboBox1.Text.Trim() + "','" + textBox4.Text .Trim ()+ "')";
MessageBox.Show(strcon);
//创建一个可以与数据源直接进行通信的command对象
SqlCommand sqlCommand = new SqlCommand(strsq1 ,conn);
//返回insert delete updata 所影响的数据行数
int result= sqlCommand.ExecuteNonQuery();
if(result>0)
{
MessageBox.Show("成功添加数据!");
}
else
{
MessageBox.Show("添加数据失败!");
}
button1_Click(this,e);
conn.Close();
}
catch (Exception mess)
{
MessageBox.Show(mess.Message,"出现异常!");
}
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
int n = dataGridView1.CurrentCell.RowIndex;
textBox1.Text = dataGridView1[0, n].Value.ToString();
textBox2.Text = dataGridView1[1, n].Value.ToString();
textBox3.Text = dataGridView1[2, n].Value.ToString();
comboBox1.Text = dataGridView1[3, n].Value.ToString();
textBox4.Text = dataGridView1[4, n].Value.ToString();
}
private void button4_Click(object sender, EventArgs e)
{
try
{
string strcon = DataConn.con;
//通过类名直接调用静态成员变量
SqlConnection conn = new SqlConnection(strcon);
//给数据库建立连接
conn.Open();
//查询用sq1语句,数据库连接
string strsq1 = "update Table_student set Sname='" + textBox2.Text.Trim() + "',Sage=" + textBox3.Text.Trim() + ",Ssex='" + comboBox1.Text.Trim() + "',Sdept='" + textBox4.Text.Trim() + "' where Snum='"+textBox1 .Text .Trim () +"' " ;
MessageBox.Show(strcon);
//创建一个可以与数据源直接进行通信的command对象
SqlCommand sqlCommand = new SqlCommand(strsq1, conn);
//返回insert delete updata 所影响的数据行数
int result = sqlCommand.ExecuteNonQuery();
if (result > 0)
{
MessageBox.Show("成功修改数据!");
}
else
{
MessageBox.Show("修改数据失败!");
}
button1_Click(this, e);
conn.Close();
}
catch (Exception mess)
{
MessageBox.Show(mess.Message, "出现异常!");
}
}
private void button5_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
{
MessageBox.Show("查询学号不能为空!");
}
else
{
try
{
string strcon = DataConn.con;
//通过类名直接调用静态成员变量
SqlConnection conn = new SqlConnection(strcon);
//给数据库建立连接
conn.Open();
//弹出是否确认删除的对话框
DialogResult result = MessageBox.Show("确认退出吗?", "操作提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result == DialogResult.OK)
{
//查询用sq1语句,数据库连接
string strsq1 = "delete from Table_student where Snum='" + textBox1.Text.Trim() + "' ";
MessageBox.Show(strcon);
//创建一个可以与数据源直接进行通信的command对象
SqlCommand sqlCommand = new SqlCommand(strsq1, conn);
//返回insert delete updata 所影响的数据行数
int resultnum = sqlCommand.ExecuteNonQuery();
if (resultnum > 0)
{
MessageBox.Show("成功删除数据!");
}
else
{
MessageBox.Show("删除数据失败!");
}
button1_Click(this, e);
conn.Close();
}
}
catch (Exception mess)
{
MessageBox.Show(mess.Message, "出现异常!");
}
}
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace 学生页面选课系统
{
class DataConn
{
//创建一个公共类,在类中通过静态变量或者静态方法的形式设置连接字符串
//定义静态成员变量
public static string con = "server=.;database=master;integrated security=true;";
}
}