ADO.net
Command 对象
ExecuteNonQuery 执行没有返回值得命令
ExecuteScalar 返回一个单独的值
ExecuteReader 通过DataReader对象返回一个结果集
CommandType属性,用于说明CommandText属性类型
Text:SQL文本命令
StoredProcedure 存储过程
TableDirect 一个表或多个表
参数
OleDbCommand1.Parameters[0].Value = "Hello World";
OleDbCommand1.Parameters["myParameter"].Value = "Good";
在设为Text,参数用?表示
DataAdapter对象
中介
SelectCommand
InsertCommand
DeleteCommand
UpdateCommand
用代码创建连接
OleDbConnection myConnection = new OleDbConnection();
my Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\\Northwind\\Northwind.mdb";
使用DataReader
System.Data.OleDb.OleDbDataReader myOleDbReader;
System.Data.SqlClient.SqlDataReader mySqlReader;
myOleDbReader = myOleDbCommand.ExecuteReader();
mySqlReader = mySqlCommand.ExecuteReader();
while (myDataReader.Read())
{
object myObject = myDataReader[3];
object myOtherObject = myDataReader["CustomerID"];
}
myDataReader.Close();
用DataReader获取类型化数据
myDataReader.GetOridinal("CustomerID");//列序号
myDataReader.GetBoolean(3); //布尔值
使用多个结果集
do
{
while (myReader.Read())
{
//.....
}
}while (myReader.NextResult());
创建和配置DataAdapter
1.在Server Explorer中创建
选择表或列,拖到设计器
2.用Data Adapter Configuration向导创建
Data选项卡 DataAdapter组件
用DataAdapter获取数据
DataSet myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet);
预览数据
Data菜单 Preview Data
类型化的DataSet对象
Data菜单 Generate Dataset 生成xsd文件
代码方式创建Dataset对象
DataSet myDataSet = new DataSet();
DataTable myTable = new DataTable();
myDataSet.Tables.Add(myTable);
DataColumn AccountsColumn = new DataColumn("Accounts");
myDataSet.Tables[0].Columns.Add(AccountsColumn);
DataRow myRow;
myRow = myDataSet.Tables[0].NewRow();
for (int i=0; i<StringCollection.Count;i++)
{
myRow.Item[Counter] = StringCollection[i];
}
myDataSet.Tables[0].Rows.Add(myRow);
从文本文件读取数据
System.IO.StreamReader myReader = new System.IO.StreamReader("c:\\myFile.txt");
string myString;
while (myReader.Peek()!=-1) //非流尾
{
myString = myReader.ReadLine()k;
myDataSet.Tables["table1"].Rows.Add(myString.Split(char.Parse(",")));
}
DataRelation对象
DataRelation myRelation = new DataRelation("Data Relation 1",column1,column2);
myDataSet.Relations.Add(myRelation);
相关记录
DataRow[] ChildRows;
DataRow ParentRow;
ChildRows = myDataSet.Tables["Customers"].Rows[1].GetChildRows(CustomersOrders);
ParentRow = myDataSet.Tables["Orders"].Rows[5].GetParentRow(CustomersOrers);
约束:UniqueConstraint ForeignKeyConstraint
myDataColumn.Unique = true;
//或显式添加
UniqueConstraint myConstraint = new UnigueConstraint(myDataColumn);
myDataTable.Constraints.Add(myConstraint);
指定多个列
DataColumn[] myColumns = new DataColumn[2];
myColumn[0] = EmployeesTable.Columns["firstname"];
myColumn[1] = EmployeesTable.Columns["LastName"];
UniqueConstraint myConstraint = new UniqueConstraint(myColumns);
EmployeesTable.Constraints.Add(myConstraint);
外键约束
ForeignKeyConstraint myConstraint = new ForeignKeyConstraint(CustomersTbl.Columns["CustomerID"],OrdersTbl.Columns["CustomerID"]);
CustomersTbl.Constraints.Add(MyConstraint);
ForeignKeyConstraint包含3项规则
UpdateRule 无论何时更新一个父行,UpdateRule都被执行
DeleteRule
AcceptRejectRule 当所属DataTable的AcceptChange方法被调用时
约束属性值设置
Cascade 父行中的改变被级联到子行中
None 改变父行时对子行没有影响
SetDefault 在相关子记录的外键设置为默认值(即DefaultValue)
SetNull 子表中的外键设为DBNull
编辑和更新数据
myDataRow[2] = "aaa";
myDataRow["Customers"] = "bbb";
//对行数据内容回滚
myDataRow.RejectChanges();
//提交数据 需在DataAdapter.Update方法之后调用,否则会覆盖原始版本
myDataRow.AcceptChanges();
RowState属性
Unchanged
Modified
Added
Deleted
Detached //已创建,不属于任何DataRowCollection
DataView.RowState
Unchanged
Added
Deleted
OriginalRows
CurrentRows
ModifiedCurrent
ModifiedOriginal
更新
myDataAdapter.Update();
myDataAdapter.Update(myDataSet);
myDataAdapter.Update(myDataTable);
myDataAdapter.Update(myDataRows);
处理更新错误
DataAdapter RowUpdated事件
事件参数 RowUpdatedEventArgs
Command 当运行更新时执行的命令
Errors 返回Data Provider产生的任何错误
RecordsAffected 返回所影响记录数量
Row 被更新的行
Status 命令的UpdateStatus
Continue 没有错误
ErrorsOccurred 更新时,发生错误
SkipAllRemainingRows 当前及剩余行更新被忽略
SkipCurrentRow 当前行的更新应该被忽略
例:
{
if (e.Status == UpdateStatus.ErrorsOccurred)
{
e.Status = UpdateStatus.SkipCurrentRow;
}
}
事务处理
System.Data.OleDb.OleDbTransaction myTransaction = null;
try
{
myConnection.Open();
myTransaction = myConnection.BeginTransaction();
Update1.Transaction = myTransaction;
Update2.Transaction = myTransaction;
Update1.ExecuteNonQuery();
Update2.ExecuteNonQuery();
myTransaction.Commit();
}
catch (Exception ex)
{
myTransaction.Rollback();
}
finally
{
myConnection.Close();
}