ADO.Net连接SQl Server

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 t1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“hem09DataSet.Employee”中。您可以根据需要移动或删除它。
this.employeeTableAdapter.Fill(this.hem09DataSet.Employee);
}

private void button1_Click(object sender, EventArgs e)
{
//1.1构造连接字符串
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();
connStr.DataSource = "(local)";
connStr.InitialCatalog = "hem09";
connStr.UserID = "sa";
connStr.Password = "123456";
//connStr.IntegratedSecurity = true;//可以通过这个设置使用windows方式登录
//Data Source=(local);Initial Catalog=hem09;User ID=sa;Password=123456

//1.2构造连接对象
SqlConnection conn = new SqlConnection();
//conn.ConnectionString = connStr.ConnectionString;
conn.ConnectionString = "Data Source=(local);Initial Catalog=hem09;User ID=sa;Password=123456";
//conn.State==ConnectionState.Open

//1.3打开连接
conn.Open();

//2执行命令
SqlCommand cmd = new SqlCommand();//2.1构造命令对象
cmd.Connection = conn;//2.2指定连接对象
cmd.CommandText = "insert into department values('"+txtName.Text+"')";//2.3构造命令
//cmd.CommandType = CommandType.Text;//默认就是文本，可以省略不写
int row = cmd.ExecuteNonQuery();//2.4执行命令，可以用于执行DDL，insert update delete

//1.4关闭连接
conn.Close();
//1.5释放资源
conn.Dispose();

if (row == 1)
{
MessageBox.Show("OK");
}
else
{
MessageBox.Show("No");
}

}

private void button2_Click(object sender, EventArgs e)
{
//1构造sql语句
string sql = "select * from department";// where did="+txtid.Text;

//2构造连接对象
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=hem09;User ID=sa;Password=123456");

//2.1打开连接
conn.Open();

//3执行命令
SqlCommand cmd = new SqlCommand(sql, conn);
string dname = cmd.ExecuteScalar().ToString();//这个方法返回结果集中的第一行第一列的值

//2.2关闭连接,释放资源
conn.Close();
conn.Dispose();

MessageBox.Show(dname);
}

private void button3_Click(object sender, EventArgs e)
{
string sql = "select dname,did from department";

SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=hem09;User ID=sa;Password=123456");
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader reader= cmd.ExecuteReader();

//将数据绑定到列表框中
cboList.Items.Clear();
//通过循环读取每一行的数据
while (reader.Read())//延迟执行
{
//使用索引器读取数据
//reader[1]
//reader["dname"]
//使用方法的方式读取，不需要进行类型转换
string dname=reader.GetString(0);//参数为列的索引
//注意：这里的索引，不是根据表的结构来确定的，而是根据select子句的结果集决定的
cboList.Items.Add(dname);
}
reader.Close();
reader.Dispose();
cmd.Dispose();

//cmd.CommandText = "";
}
catch
{
//...
}
finally
{
conn.Close();
conn.Dispose();
}
}

private void button4_Click(object sender, EventArgs e)
{
string sql = "select dname,did from department";
using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=hem09;user id=sa;Password=123456"))
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();//尽量晚的打开连接，尽量早的关闭连接
SqlDataReader reader = cmd.ExecuteReader();

//将数据绑定到列表
cboList.Items.Clear();
while (reader.Read())
{
Department d1 = new Department() {Did=reader.GetInt32(1),Dname=reader.GetString(0)};
cboList.Items.Add(d1);
cboList.DisplayMember = "Dname";//指定对象的某个属性用于显示
}
reader.Close();
reader.Dispose();
}
}

private void button5_Click(object sender, EventArgs e)
{
//1、构造sql语句
string sql = "select * from department";
//2建立连接
using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=hem09;User id=sa;Password=123456"))
{
//3.1构造命令对象
SqlCommand cmd = new SqlCommand(sql, conn);
//2.1打开连接
conn.Open();
//3.2执行命令
using (SqlDataReader reader = cmd.ExecuteReader())
{
//清空原有数据
gvList.Rows.Clear();
gvList.Columns.Clear();
//新建列
gvList.Columns.Add("did","编号");
gvList.Columns.Add("dname", "名称");
//遍历查询结果集，将数据添加到列表中
while (reader.Read())
{
Department d1 = new Department() { Did = reader.GetInt32(0), Dname = reader.GetString(1) };
gvList.Rows.Add(d1.Did, d1.Dname);
}
}
cmd.Dispose();
}
}

private void gvList_CellClick(object sender, DataGridViewCellEventArgs e)
{

}
}
}