所谓ADO.NET是一组用于和数据源进行交互的面向对象类库,就是访问数据库SQL Access 或XML Excel等数据源而已。
访问数据首先就要建立到操作对象的连接,这就是connection对象,通常用的访问SQL Server的叫SqlConnection类,访问Access的叫OleDbConnection.
连接上了,自然就要执行Sql语句来操作表,这时就需要Command对象了,同样的也分SqlCommand(访问SQLServer),OleDbCommand(访问Access或其它).
执行Sql Command的成功后有结果返回,此时需要装结果的容器了,如果是数据表则需要,一是DataReader另一个是DataSet.
DataReader来自于Command.ExecuteReader()返回,而DataSet来自于DataAdapter.Fill(ds, "dstablename")的Fill.同是存放表的.
为何要分两种呢,因为DataSet经过Fill操作后是将表拷贝至内存了,并关闭了连接,而DataReader只是打开联接,数据需要由前至后的一条条读出.在不同的应用环境需要时各有所长,特别是dataset在大量请求负载处理时将会显现出超强的性能优势,采用SqlDataAdapter.Update操作的SqlCommandBuilder来更新删除,修改一些大批量数据,或用SQL语句实现修改多列多项有困难的需求时.
使用Sql server数据库时常会用到的在webconfig中定义connectionStrings 如下
<connectionStrings>
<add name="Northwind" connectionString="Server=localhost;Integrated Security=True;Database=Northwind;Persist Security Info=True" providerName="System.Data.SqlClient" />
</connectionStrings>
引用这个定义则用如下语句
string connectionString =ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
通常使用SqlDataAdapter(),读取数据用GridView显示如下:
string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connStr);
myConnection.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection); //注意两个表读
DataSet ds = new DataSet();
da.Fill(ds, "Customers");
CustomersGridView.DataSource = ds.Tables[0];//ds.Tables["Customers"]
CustomersGridView.DataBind();
OrdersGridView.DataSource = ds.Tables[0];//ds.Tables["Customers"]
OrdersGridView.DataBind();
myConnection.Close();
通常使用SqlDataReader(),读取数据用GridView显示时如下:
string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connStr);
myConnection.Open();
SqlCommand command = new SqlCommand("Select * FROM Customers", myConnection);
SqlDataReader dr=command.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();
myConnection.Close();
一个基本的Sql server 采用DataReader()操作增,删,改,读的类,C#代码如下
using System;
using System.Data;
using System.Data.SqlClient;
class SqlCommandDemo
{
SqlConnection conn;
public SqlCommandDemo() //构造
{
// 建立联接方法
conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
}
// 主程序
static void
{
SqlCommandDemo scd = new SqlCommandDemo();
Console.WriteLine();
Console.WriteLine("Categories Before Insert");
Console.WriteLine("------------------------");
//ExecuteReader 读出显示
scd.ReadData();
// 用ExecuteNonQuery 方法新建插入一条数据
scd.InsertData();
Console.WriteLine();
Console.WriteLine("Categories After Insert");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteNonQuery 更新
scd.UpdateData();
Console.WriteLine();
Console.WriteLine("Categories After Update");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteNonQuery 删除方法
scd.DeleteData();
Console.WriteLine();
Console.WriteLine("Categories After Delete");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteScalar 统计方法
int numberOfRecords = scd.GetNumberOfRecords();
Console.WriteLine();
Console.WriteLine("Number of Records: {0}", numberOfRecords);
}
//以下各子方法
public void ReadData() //读出
{
SqlDataReader rdr = null;
try
{
conn.Open();
// 1. 初始化一个SqlCommand对象
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
// 2. 读出
rdr = cmd.ExecuteReader();
// 遍历所有数据输出
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// 关闭
if (rdr != null)
{
rdr.Close();
}
// 且关闭联接
if (conn != null)
{
conn.Close();
}
}
}
public void InsertData() //插入
{
try
{
conn.Open();
// 准备参数
string insertString = @"insert into Categories (CategoryName, Description)values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
// 1. 初始化SqlCommand,并连接conn
SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. 执行
cmd.ExecuteNonQuery();
}
finally
{
// 关闭
if (conn != null)
{
conn.Close();
}
}
}
public void UpdateData() //更新
{
try
{