今天总结了一下ADO.NET编程中DataReader和dataSet两个比较重要的对象的使用，完成了combobox，listbox，以及fpSpread动态添加数据的测试，对使用sqlCommand来添加SQL语句参数也有了一定的认识，希望分朋友们分享一下经验，当然也有不足，工作不忙的时候再好好修改~
1.首先MS SQL Server 2012 Management在本机创建的数据库test，另外两个表，部门表和员工表
2.winform编写，form1添加4个控件：button2，listBox1，comboBox1，fpSpread_yuangong然后进行编程
<没用定义新的类，有兴趣的朋友可以定义DBconnection这样的类，DB连接，字段等属性封装进去>最不能容忍的是公司限网连截图都不能上传，有时间再编辑
代码如下：
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.Collections;
using System.Data.SqlClient;
using FarPoint.Win.Spread;namespace WindowsFormsApp_DB
{
public partial class Form1 : Form
{ public static string dbconnstring = "server=Q1502LP0003\\SQLEXPRESS;database=test;uid=ammy;pwd=ammy714926;";
//SqlConnection dbc = new SqlConnection("server=Q1502LP0003\\SQLEXPRESS;database=test;uid=ammy;pwd=ammy714926;"); public SqlDbType sqlDbType { get; set; } public Form1()
{
InitializeComponent();
} private void button2_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();//每次清空
SqlConnection dbc = new SqlConnection(dbconnstring);
string sQuery = "";
sQuery = "SELECT ";
sQuery = sQuery + "* ";
sQuery = sQuery + "FROM test.dbo.部门表 WITH(NOLOCK)";
SqlCommand dbm = new SqlCommand(sQuery, dbc);
dbc.Open();//连接到数据源
SqlDataReader dbReader = dbm.ExecuteReader(CommandBehavior.CloseConnection);
while(dbReader.Read())
{
//必须和DB表列名一致，或者用索引
listBox1.Items.Add("部门编号是:"+dbReader["部门编号"].ToString().Trim());
listBox1.Items.Add("部门名称是:" + dbReader["部门名称"].ToString().Trim());
listBox1.Items.Add("部门职责是:" + dbReader["部门职责"].ToString().Trim());
listBox1.Items.Add("-----------------------------"); }
dbReader.Close();
dbc.Dispose();
dbc.Close();//关闭数据源连接，因为SqlDataReader始终占有SqlConnection，需要在用完关闭
dbc = null;
} private void Form1_Load(object sender, EventArgs e)
{
initial(fpSpread_yuangong);
setcombox(comboBox1);
//属性设计器里：this.comboBox1.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
} private void initial(FpSpread spread)
{
spread.Sheets[].ColumnCount = ;
spread.Sheets[].ColumnHeader.Rows[].Height = ;
spread.Sheets[].ColumnHeader.Cells[, ].Text = "员工编号";
spread.Sheets[].ColumnHeader.Cells[, ].Text = "员工姓名";
spread.Sheets[].ColumnHeader.Cells[, ].Text = "联系电话";
spread.Sheets[].ColumnHeader.Cells[, ].Text = "email";
spread.Sheets[].Columns[].Width = ;
spread.Sheets[].Columns[].Width = ;
spread.Sheets[].Columns[].Width = ;
spread.Sheets[].Columns[].Width = ; } private void setcombox(ComboBox comboBox)
{
SqlConnection dbc = new SqlConnection(dbconnstring);
string sQuery1 = "";
sQuery1 = "select * from test.dbo.部门表";
//sQuery1 = "select top 2 * from test.dbo.部门表";由此可见加载到dataset的数据不是整张表，而是由select决定的数据集
SqlCommand dbm1 = new SqlCommand(sQuery1,dbc);
SqlDataAdapter adapter = new SqlDataAdapter();
dbc.Open();
adapter.SelectCommand = dbm1;
DataSet ds = new DataSet();
DataTable bumen = ds.Tables.Add("内存部门表");
adapter.Fill(bumen); dbc.Dispose();
dbc.Close();//关闭数据源连接
dbc = null; DataRow dr = bumen.NewRow();
dr["部门编号"] = "";
dr["部门名称"] = "全体";
bumen.Rows.InsertAt(dr, ); comboBox.ValueMember = "部门编号";
comboBox.DisplayMember = "部门名称";
comboBox.DataSource = bumen; } private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
searchStuff();
} private void searchStuff()
{
string paraziduan = comboBox1.SelectedValue.ToString();
string sQuery2 = "";
if (paraziduan == "")//这里不是paraziduan == "全体"！这是显示的数据！所以调试了好久才发现！
{
sQuery2 = "select * from test.dbo.员工通讯录表";
}
else
{
sQuery2 = sQuery2 + "select * from test.dbo.员工通讯录表 where 所属部门 = @dbpara";
//sQuery2 = sQuery2 + "select * from test.dbo.员工通讯录表where 所属部门"+para+"";这样写的话总是错，这种格式不知道错在哪里
} SqlConnection dbc = new SqlConnection(dbconnstring);
if (dbc.State.Equals(ConnectionState.Closed))
{
dbc.Open();
} //以下是利用SqlCommand传参数的方法，很好用！
SqlCommand dbm2 = new SqlCommand(sQuery2,dbc);
dbm2.CommandText = sQuery2;
SqlParameter para = new SqlParameter("@dbpara", SqlDbType.VarChar, );//生成一个名字为@dbpara的参数，字段数据类型和长度要和DB表字段设置一致
para.Value = paraziduan;//给参数赋值
dbm2.Parameters.Add(para); SqlDataAdapter adapter2 = new SqlDataAdapter();
adapter2.SelectCommand = dbm2;
DataSet ds2 = new DataSet();
DataTable yuangong = ds2.Tables.Add("员工按部门选择表");
adapter2.Fill(yuangong); dbc.Dispose();
dbc.Close();//关闭数据源连接
dbc = null; fpSpread_yuangong.DataSource = yuangong;
for (int i = ; i < yuangong.Rows.Count; i++)
{
fpSpread_yuangong.Sheets[].ColumnCount = ;//如果注释这行，奇怪的是会把dataTable的所以字段无差别全部显示？
fpSpread_yuangong.Sheets[].Cells[i, ].Text = yuangong.Rows[i]["员工编号"].ToString();
fpSpread_yuangong.Sheets[].Cells[i, ].Text = yuangong.Rows[i]["姓名"].ToString();
fpSpread_yuangong.Sheets[].Cells[i, ].Text = yuangong.Rows[i]["电话号码"].ToString();
//搞不清电子邮件一列为什么显示不出来？
fpSpread_yuangong.Sheets[].Cells[i, ].Text = yuangong.Rows[i]["电子邮件"].ToString().Trim();
} } }
}
效果图：
是今天业余学习的小结。对于DataReader和dataSet的区别，网上一搜就是一大堆，关键还是要理解和运用！
加油！
