C#与SQL server 的连接

_winds

　　数据库的服务器

　　管理员信息表

　　学生信息表

　　

　　一、登陆窗口

　　源代码：
　　

　　

　　源代码：
　　using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;       //此处的应用是必要的




namespace 数据库
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();

}


private void button1_Click(object sender, EventArgs e)
{

SqlConnection thisConn = new SqlConnection("Server=szpt-ceacc3d660\\SQLEXPRESS;Integrated Security=true;Database=数据库"); //创建与数据库的连接
SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from 学生信息", thisConn);                    //创建SQL适配器
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);                             //创建SQL命令生成器
DataSet thisDataSet = new DataSet();                                            //创建数据集
thisAdapter.Fill(thisDataSet, "Student");                                          //将查询结果填充到数据集中


listBox1.Items.Clear();
listBox1.Items.Add(" 学号  姓名  性别  年龄  成绩");
foreach(DataRow row in thisDataSet.Tables["Student"].Rows)
{
listBox1.Items.Add(row["学号"].ToString() + "  " + row["姓名"].ToString() + "  " + row["性别"].ToString() + "   " + row["年龄"].ToString() + "   " + row["成绩"].ToString());
}
}


private void button2_Click(object sender, EventArgs e)
{
SqlConnection thisConn = new SqlConnection("Server=szpt-ceacc3d660\\SQLEXPRESS;Integrated Security=true;Database=数据库"); //创建与数据库的连接
SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from 学生信息", thisConn);                    //创建SQL适配器
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);                             //创建SQL命令生成器
DataSet thisDataSet = new DataSet();      //创建数据集
thisAdapter.Fill(thisDataSet, "Student"); //将查询结果填充到数据集中


DataRow newRow = thisDataSet.Tables["Student"].NewRow();  //创建新行
newRow["学号"] = textBox1.Text.ToString();
newRow["姓名"] = textBox2.Text.ToString();
newRow["性别"] = textBox3.Text.ToString();
newRow["年龄"] = textBox4.Text.ToString();
newRow["成绩"] = textBox5.Text.ToString();
thisDataSet.Tables["Student"].Rows.Add(newRow);   //增加到数据集里
thisAdapter.Update(thisDataSet,"Student");     //写到数据库中
MessageBox.Show("插入数据成功！");
listBox2.Items.Clear();
listBox2.Items.Add(" 学号  姓名  性别  年龄  成绩");
foreach(DataRow row in thisDataSet.Tables["Student"].Rows)
{
listBox2.Items.Add(row["学号"].ToString() + "  " + row["姓名"].ToString() + "  " + row["性别"].ToString() + "   " + row["年龄"].ToString() + "   " + row["成绩"].ToString());

}
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
}


private void button3_Click(object sender, EventArgs e)
{
SqlConnection thisConn = new SqlConnection("Server=szpt-ceacc3d660\\SQLEXPRESS;Integrated Security=true;Database=数据库"); //创建与数据库的连接
SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from 学生信息", thisConn);    //创建SQL适配器
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);  //创建SQL命令生成器
DataSet thisDataSet = new DataSet();      //创建数据集
thisAdapter.Fill(thisDataSet, "Student"); //将查询结果填充到数据集中


thisDataSet.Tables["Student"].Rows[Convert.ToInt16(textBox6.Text)].Delete();
thisAdapter.Update(thisDataSet, "Student");


listBox3.Items.Clear();
listBox3.Items.Add(" 学号  姓名  性别  年龄  成绩");
foreach (DataRow row in thisDataSet.Tables["Student"].Rows)
{
listBox3.Items.Add(row["学号"].ToString() + "  " + row["姓名"].ToString() + "  " + row["性别"].ToString() + "   " + row["年龄"].ToString() + "   " + row["成绩"].ToString());


}
textBox6.Text = "";
}


private void button4_Click(object sender, EventArgs e)
{
SqlConnection thisConn = new SqlConnection("Server=szpt-ceacc3d660\\SQLEXPRESS;Integrated Security=true;Database=数据库"); //创建与数据库的连接
SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from 学生信息", thisConn);    //创建SQL适配器
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);  //创建SQL命令生成器
DataSet thisDataSet = new DataSet();      //创建数据集
thisAdapter.Fill(thisDataSet, "Student"); //将查询结果填充到数据集中


thisDataSet.Tables["Student"].Rows[Convert.ToInt16(textBox7.Text)]["成绩"] = textBox8.Text;
thisAdapter.Update(thisDataSet, "Student");


listBox4.Items.Clear();
listBox4.Items.Add(" 学号  姓名  性别  年龄  成绩");
foreach (DataRow row in thisDataSet.Tables["Student"].Rows)
{
listBox4.Items.Add(row["学号"].ToString() + "  " + row["姓名"].ToString() + "  " + row["性别"].ToString() + "   " + row["年龄"].ToString() + "   " + row["成绩"].ToString());


}
textBox7.Text = "";
textBox8.Text = "";
}




private void button5_Click(object sender, EventArgs e)
{

string command1 ="select * from 学生信息 where 性别='男'";
SqlConnection thisConn = new SqlConnection("Server=szpt-ceacc3d660\\SQLEXPRESS;Integrated Security=true;Database=数据库"); //创建与数据库的连接
SqlDataAdapter thisAdapter = new SqlDataAdapter(command1, thisConn);    //创建SQL适配器
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);  //创建SQL命令生成器
DataSet thisDataSet = new DataSet();      //创建数据集
thisAdapter.Fill(thisDataSet, "Student"); //将查询结果填充到数据集中



listBox5.Items.Clear();
listBox5.Items.Add(" 学号  姓名  性别  年龄  成绩");
foreach (DataRow row in thisDataSet.Tables["Student"].Rows)
{
listBox5.Items.Add(row["学号"].ToString() + "  " + row["姓名"].ToString() + "  " + row["性别"].ToString() + "   " + row["年龄"].ToString() + "   " + row["成绩"].ToString());


}
}


private void button6_Click(object sender, EventArgs e)
{
int i = 60;
string command2 = "select * from 学生信息 where 成绩<"+i;
SqlConnection thisConn = new SqlConnection("Server=szpt-ceacc3d660\\SQLEXPRESS;Integrated Security=true;Database=数据库"); //创建与数据库的连接
SqlDataAdapter thisAdapter = new SqlDataAdapter(command2, thisConn);    //创建SQL适配器
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);  //创建SQL命令生成器
DataSet thisDataSet = new DataSet();      //创建数据集
thisAdapter.Fill(thisDataSet, "Student"); //将查询结果填充到数据集中




listBox5.Items.Clear();
listBox5.Items.Add(" 学号  姓名  性别  年龄  成绩");
foreach (DataRow row in thisDataSet.Tables["Student"].Rows)
{
listBox5.Items.Add(row["学号"].ToString() + "  " + row["姓名"].ToString() + "  " + row["性别"].ToString() + "   " + row["年龄"].ToString() + "   " + row["成绩"].ToString());


}
}


private void button7_Click(object sender, EventArgs e)
{
int age=17;
string command3 = "select * from 学生信息 where 年龄>"+age+"and 性别='男'";
SqlConnection thisConn = new SqlConnection("Server=szpt-ceacc3d660\\SQLEXPRESS;Integrated Security=true;Database=数据库"); //创建与数据库的连接
SqlDataAdapter thisAdapter = new SqlDataAdapter(command3, thisConn);    //创建SQL适配器
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);  //创建SQL命令生成器
DataSet thisDataSet = new DataSet();      //创建数据集
thisAdapter.Fill(thisDataSet, "Student"); //将查询结果填充到数据集中




listBox5.Items.Clear();
listBox5.Items.Add(" 学号  姓名  性别  年龄  成绩");
foreach (DataRow row in thisDataSet.Tables["Student"].Rows)
{
listBox5.Items.Add(row["学号"].ToString() + "  " + row["姓名"].ToString() + "  " + row["性别"].ToString() + "   " + row["年龄"].ToString() + "   " + row["成绩"].ToString());


}
}


private void button8_Click(object sender, EventArgs e)
{
string command4 = "select * from 学生信息 where 性别='女'";
SqlConnection thisConn = new SqlConnection("Server=szpt-ceacc3d660\\SQLEXPRESS;Integrated Security=true;Database=数据库"); //创建与数据库的连接
SqlDataAdapter thisAdapter = new SqlDataAdapter(command4, thisConn);    //创建SQL适配器
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);  //创建SQL命令生成器
DataSet thisDataSet = new DataSet();      //创建数据集
thisAdapter.Fill(thisDataSet, "Student"); //将查询结果填充到数据集中




listBox5.Items.Clear();
listBox5.Items.Add(" 学号  姓名  性别  年龄  成绩");
foreach (DataRow row in thisDataSet.Tables["Student"].Rows)
{
listBox5.Items.Add(row["学号"].ToString() + "  " + row["姓名"].ToString() + "  " + row["性别"].ToString() + "   " + row["年龄"].ToString() + "   " + row["成绩"].ToString());


}


}
}
}