ADO.NET-轉

最新推荐文章于 2025-01-07 21:33:58 发布

原创最新推荐文章于 2025-01-07 21:33:58 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#security #dataset #command #sql server #string #null

所谓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>

引用这个定义则用如下语句

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 Main()

{

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

{

conn.Open();

// 参数

string updateString = @"update Categories set CategoryName = 'Other' where CategoryName = 'Miscellaneous'";

// 1.初始化

SqlCommand cmd = new SqlCommand(updateString);

// 2. 连接conn

cmd.Connection = conn;

// 3.执行

cmd.ExecuteNonQuery();

}

finally

{

// 关闭

if (conn != null)

{

conn.Close();

}

public void DeleteData() //删除

{

try

{

conn.Open();

//准备参数

string deleteString = @"delete from Categories where CategoryName = 'Other'";

// 1. command

SqlCommand cmd = new SqlCommand();

// 2. 绑定

cmd.CommandText = deleteString;

// 3. 连接

cmd.Connection = conn;

// 4. 执行

cmd.ExecuteNonQuery();

}

finally

{

// 关闭

if (conn != null)

{

conn.Close();

}

public int GetNumberOfRecords() //统计

{

int count = -1;

try

{

conn.Open();

// 1. 初始化一个command

SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

// 2. 执行

count = (int)cmd.ExecuteScalar();

}

finally

{

// 关闭

if (conn != null)

{

conn.Close();

}

return count;

}

采用SqlDataAdapter()的SqlCommandBuilder操作增,删,改,读,C#代码如下

private void UseCommandBuilder()

{

string connectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=LAP800";

SqlConnection connection = new SqlConnection(connectionString);

connection.Open();

try {

SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection);

SqlDataAdapter adapter = new SqlDataAdapter(command);

SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

Console.WriteLine(builder.GetInsertCommand().CommandText);

Console.WriteLine(builder.GetUpdateCommand().CommandText);

Console.WriteLine(builder.GetDeleteCommand().CommandText);

DataTable table = new DataTable();

adapter.Fill(table); //读出数据,平常用时会Fill给一个dataset,

DataRow row = table.NewRow;

row("CustomerID") = "PAULK";

row("CompanyName") = "Pauly's Edibles";

row("ContactName") = "Paul Kimmel";

row("ContactTitle") = "The Fat Man";

table.Rows.Add(row); //插入上面生成的一条数据

DataRow[] results = table.Select("CustomerID = 'ALFKI'");

results(0)("Region") = "DE"; //修改数据

DataRow[] deleteThese = table.Select("CustomerID = 'FARLA'");

foreach (int row in deleteThese) {

row.Delete(); //删除数据

}

adapter.Update(table);

table.AcceptChanges();

} catch (Exception ex) {

Debug.WriteLine(ex.Message);

throw;

} finally {

connection.Close();

}

收集

System.Data.SqlClient.SqlConnection 常用的一些连接字符串(C#代码)：

SqlConnection conn = new SqlConnection("Server=(local);Integrated Security=SSPI;database=Pubs");

SqlConnection conn = new SqlConnection("server=(local)//NetSDK;database=pubs;Integrated Security=SSPI");

SqlConnection conn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");

SqlConnection conn = new SqlConnection(" data source=(local);initial catalog=xr;integrated security=SSPI;persist security info=False;workstation id=XURUI;packet size=4096; ");

SqlConnection conn = new System.Data.SqlClient.SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer");

SqlConnection conn = new SqlConnection( " uid=sa;pwd=passwords;initial catalog=pubs;data source=127.0.0.1;Connect Timeout=900");

System.Data.OleDb.OleDbConnection常用的一些连接字符串(C#代码)：

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/MyWeb/81/05/GrocerToGo.mdb");

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=grocertogo.mdb;");

OleDbConnection conn = new OleDbConnection("Provider=MSDAORA; Data Source=ORACLE8i7;Persist Security Info=False;Integrated Security=yes");

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:/bin/LocalAccess40.mdb");

OleDbConnection conn = new OleDbConnection("Provider=SQLOLEDB;Data Source=MySQLServer;Integrated Security=SSPI");