ADO.NET 对象模型 :
net数据提供程序:sql client and oledb or sql xml
联机对象:Connection,Command,DataReader,Transaction,Parameter,DataAdapter
脱机对象:DataTable,DataColumn,Consraint,DataRow,dataset,dataview,datarelation
元数据
强类型dataset对象
封装也是面向对象的重要特征之一
private:私有成员,在类的内部才可以访问;
protected: 保护成员,该类内部和继承类才可以访问;
public:公共成员,无访问限制
internal:在同一个命名空间内可以访问;
protected internal:访问仅限于从包含类派生的当前程序集或类型.
一个类可以实现多个接口;
接口的成员包括从基接口继承的成员和由接口本身声明的成员(包括方法,属性,事件或索引器)
接口不支持 虚方法;
接口不能被实例化;
客户端可以通过调用 dataset 的getChanges方法来将包含已修改行的dataset发送回中间层.
连接字符串
conection的state属性 ado.net p70 stateChange事件 p80
sql 2000 :provider=SQLOLEDB;Data Source=MyServer\MyInstance;Initial Catalog=MyDatabase;Integrated Security=SSPI;(或者Trusted_Connection=Yes;)
access:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\PATH\MyDatabase.MDB;
关闭连接池:
oledb 在字符串中添加:OLE DB Services=-4;
sql 添加:Pooling=False;
连接命令command:
一.获取多个结果
strConn="#%#$%$%@$";
OleDbConnection cn =new OleDbConnection(strConn);
cn.Open();
string strSQL="SELECT A,B FROM TABLE;" + "SELECT C,D FROM TABLE1;" +"SELECT E,F FROM TABLE2";
strSql="select语句";
OleDbDataReader dr=cmd.ExecuteReader();
do
{
while(dr.Read())
Console.Writeline(dr[0] + "-" + dr[1]);
Console.WriteLine();
}while (dr.NextResult());
二.只返回一个值
string strConn="@#$%$#%@#$%";
OleDbConnection cn = new OleDbConnection(strConn);
cn.Open();
OleDbCommand cmd = cn.CreateCommand();
cmd.CommandText ="select count(*) FROM TABLE";
int count=Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText="select name from table where id = '123'";
string strname = Convert.ToString(cmd.ExecuteScalar());
三.调用存储过程
//存储过程通过结果集返回信息
/* CREATE PROCEDURE Getinfo(@id int) AS
SELECT id,name,info,addr FROM TABLE WHERE id=@id
RETURN */
string strConn="@#$%$%@#$%";
OleDbConnection cn = new OleDbConnection(strConn);
cn.open();
OleDbCommand cmd = cn.CreateCommand();
cmd.CommandText ="Getinfo";
cmd.CommandType =CommandType.StoreProcedure;
//CommandType还有两个值:Text,TableDirect
//TableDirect是为CommandText属性预先加入"SELECT * FROM"
cmd.Parameters.Add("@id",OleDbType.integer);
cmd.Parameters [0].Value="key";
OleDbDataReader dr = cmd.ExecuteReader();
if { dr.Read())
Console.WriteLine(dr["info"]);
else
Console.WriteLine("No info found");
dr.Close();//貌似新版本不要关闭.
cn.Close();
另一种调用存储过程
{返回值=CALL MyStoreProc(?,?,?)}
eg:
cmd.CommandText ="{CALL Getinfo(?)}"
cmd.CommandType =CommandType.Text
//存储过程通过输出参数返回信息
/* CREATE PROCEDURE Getinfo (@id int,@name nvarchar(40) OUTPUT,
@info nvarchar(500)OUTPUT,
@addr nvarchar(100) OUTPUT)AS
SELECT @name = name,@info=information,@addr=address FROM TABLE WHERE id=@id
IF @@ROWCOUNT =1
RETURN 0
ELSE
RETURN -1 */
eg:
string strConn ="2#$@#$@#%#@$";
OleDbConnection cn = new OleDbConnection(strConn);
cn.Open();
OleDbCommand cmd =cn.CreateCommand();
cmd.CommandText ="{?=CALL Getinfo(?,?,?,?)};
cmd.Parameters.Add("@RetVal",OleDbType.integer);//返回参数
cmd.Parameters.Add("@id",OleDbType.integer);
cmd.Parameters.Add("@name",OleDbType.varWChar,40);//varWChar?另查资料
cmd.Parameters.Add("@info",OleDbType.varWChar,500);/同上
cmd.Parameters.Add("@addr",OleDbType.varWChar,100)
cmd.Parameters["@RetVal"].Direction=ParameterDirection.ReturnValue;
cmd.Parameters["@id"].Value="123";
cmd.Parameters["@name"].Direction =ParameterDirection.Output;
cmd.Parameters["@info"].Direction =ParameterDirection.Output;
cmd.Parameters["@addr"].Direction =ParameterDirection.Output;
cmd.ExecuteNonQuery();
if (Convert.ToInt32(cmd.Parameters["@RetVal"].Value)==0}
console.WriteLine(cmd.Parameters["@name"].Value);
else
console.WriteLine("name not found");
在Transaction(事务)中执行查询
eg:
cn.open();
OleDbTransaction txn=cn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(strSQL,cn,txn);
int intRecordsAffected=cmd.ExecuteNonQuery();
if( intRecordsAffected==1)
{
Console.WriteLine("Update succeeded");
txn.Commit();
}
else
{
Console.WriteLine("Update failed");
txn.Rollback();
}
insert table_name
select column_list
from table_list where search_conditions 插入查询到的数据
