ADO.NET常用操作

ADO.NET基本操作

1.       事务处理

cnNorthwind.Open()

 

Dim tranNorthwind as SqlTransaction=cnNorthwind.BeginTransaction()

Dim cmdDel as New SqlCommand()

 

CmdDel.Connection=cnNorthwind

CmdDel.Transaction=tranNorthwind

 

Try

CmdDel.CommandText=”Delete From 类别 where 类别id=1”

CmdDel.ExecuteNonQuery()

CmdDel.CommandText=”Delete From 产品 where 类别id=1”

CmdDel.ExecuteNonQuery()

TranNorthwind.Commit()

Catch e as Exception

TranNorthwind.Rollback()

Finally

CnNorthwind.Close()

End Try

 

2.       构建DataSet

DataSet用对象描述数据库

Dim dsNorthwind as New DataSet(“Northwind”)

添加一个表格对象到dsNorthwind

Dim dtLeibie as new DataTable(“类别”)

DsNorthiwind.Tables.Add(dtLeibie)

向表格中增加列

Dim colLeibieID as DataColumn=dtLeibie.Columns.Add(“类别ID”,GetType(System.Int32))  //C# typeof(Int32)

ColLeibieID.AllowDBNull=False

ColLeibieID.Unique=True

 

向表格中添加行

Dim drNewLeibie as DataRow=dsNorthwind.Tables(“类别”).NewRow()

DtNewLeibie(“类别id”)=1

DtNewLeibie(“类别名称”)=”计算机”

DsNorthwind.Tables(“类别”).Rows.Add(drNewLeibie)

 

邦定游标

private cmLeibie as CurrencyManager

 

cmLeibie=Ctype(me.BindingContext(dsNorthwind.Tables(“类别”),CurrencyManager)

//C#  cmLeibie=(CurrencyManager)this.BindingContext(dsNorthwind.Tables[“类别”]);

cmLeibie.Position=0

cmLiebie.Position=cmLeibie.Count-1

 

更新当前行

Dim drLeibie As DataRow=dsNorthwind.Tables(“类别”).Rows(cmLeibie.Position)

 

DsNorthwind.Tables(“类别”).BeginEdit()

DrLeibie(“类别名称”)=”新名称”

DsNorthwind.Tables(“类别”).EndEdit()

 

删除当前行

Dim drLeibie as DabaRow=dsNorthwind.Tables(“类别”).Rows(cmLeibie.Position)

DsNorthwind.Tables(“类别”).Rows.Remove(drLeibie)

 

加载XSD架构,可以提高Fill速度

dsNorthwind.ReadXmlSchema(“XSD文件位置”)

 

建立InsertCommand

dim cmInsert as New SqlCommand(“Insert Into 类别 Values(@类别ID,@类别名称)”,cnNorthwind)

 

cmInsert.Parameters.Add(New SqlParameter(“@类别ID”,SqlDbType.Nchar,5,ParameterDirection.Input,false,0,0,”LeiBieID”,DataRowVersion,Current,null))

…..

daLeibie.InsertCommand=cmInsert

 

更新参数设置 成功通过

           System.Data.OleDb.OleDbCommand comUpdate = new System.Data.OleDb.OleDbCommand();

            comUpdate.CommandText = "Update 类别 set 类别名称=@类别名称 where 类别id=@类别id";

 

            comUpdate.Parameters.Add(new System.Data.OleDb.OleDbParameter("@类别名称", System.Data.OleDb.OleDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "类别名称", DataRowVersion.Current, null));

            comUpdate.Parameters.Add(new System.Data.OleDb.OleDbParameter("@类别ID", System.Data.OleDb.OleDbType.SmallInt, 4, ParameterDirection.Input, false, 0, 0, "类别id", DataRowVersion.Current, null));

 

            comUpdate.Connection = m_conDb;

           

            SqlStr="Select * from 类别";

          

            m_dsLb = new DataSet();

            m_daLb=new System.Data.OleDb.OleDbDataAdapter(SqlStr,m_conDb);

m_daLb.UpdateCommand=comUpdate;

 

把更新提交到表格

            if (m_dsLb.HasChanges())

            {

                m_daLb.Update(m_dsLb.Tables["类别"].GetChanges());

                MessageBox.Show("保存成功");

                m_dsLb.Tables["类别"].AcceptChanges();

            }