Ado type updates in Ado.net

[Byron Hopp]

Is there a similar way to append, delete, and update records via an object
in Ado.net similar to a Adodb.Recordset in Ado?

i.e. in ado I have done these tasks this way in Ado...

*** not tested Append ***

Dim oRs as new Adodb.Recordset
oRs:CursorLocation = adUseClient
oRs:LockType = adLockOptimistic
oRs:CursorType = adOpenDynamic
oRs.ActiveConnection = cConnStr
oRs.Source = "Select * from Names where 1=0"
oRs.Open()
oRs.AddNew()
oRs.Fields("LastName").Value = "Hopp"
oRs.Fields("FirstName").Value = "Byron"
oRs.Update()

*** not tested Delete ***

Dim oRs as new Adodb.Recordset
oRs:CursorLocation = adUseClient
oRs:LockType = adLockOptimistic
oRs:CursorType = adOpenDynamic
oRs.ActiveConnection = cConnStr
oRs.Source = "Select * from Names where LastName='Hopp'"
oRs.Open()
oRs.Delete()
oRs.Update()

*** not tested Update ***

Dim oRs as new Adodb.Recordset
oRs:CursorLocation = adUseClient
oRs:LockType = adLockOptimistic
oRs:CursorType = adOpenDynamic
oRs.ActiveConnection = cConnStr
oRs.Source = "Select * from Names where LastName='Hopp'"
oRs.Open()
oRs.Fields("LastName").Value = "Hopp"
oRs.Fields("FirstName").Value = "B. L."
oRs.Update()

What would be a similar way of doing this in Ado.Net?

Thanks in advance.

Byron...

 

[Guest]

The closest, in ADO.NET, is filling a DataSet with a DataAdapter, chaging
data and then calling Update() on the DataAdapter.

C#
---
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlString, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet("NameOfDataSet");

//need mapping for friendly names
da.TableMappings.Add("Table", "Customers");

conn.Open;
da.Fill(ds);

//some updates
ds.Tables["Customers"].Rows[0]["LastName"] = "NewLastName";

da.Update();

This is stubbed code (partially pseudocode), but it gets the basic idea
across.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Mythran]

The closest, in ADO.NET, is filling a DataSet with a DataAdapter, chaging
data and then calling Update() on the DataAdapter.

C#
---
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlString, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet("NameOfDataSet");

//need mapping for friendly names
da.TableMappings.Add("Table", "Customers");

conn.Open;
da.Fill(ds);

//some updates
ds.Tables["Customers"].Rows[0]["LastName"] = "NewLastName";

da.Update();

This is stubbed code (partially pseudocode), but it gets the basic idea
across.

---

An alternate way using the same method would be to create a typed dataset
and access the columns that way.

Example:

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlString, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
MyDataSet.tblMyTableDataTable dt = (new MyDataSet()).tblMyTable;

conn.Open();
da.Fill(dt);

//some updates
dt.LastName = "NewLastName";

da.Update();

// yadda yadda yadda

In the long run, it allows for easier recognition of column names and
compile-time errors if, for example, you spelled a column name wrong. This
would work best if you used drag-n-drop to create the typed dataset (in the
Visual Studio .Net IDE).

Hope this helps...

Mythran

 

[Byron Hopp]

Is there a way to get this to work without using the 'drag and drop' method.
I would like to attempt to make a reusable way to wrap this in a function.

Byron...

"Cowboy (Gregory A. Beamer) - MVP" <[email protected]>
wrote in message

The closest, in ADO.NET, is filling a DataSet with a DataAdapter, chaging
data and then calling Update() on the DataAdapter.

C#
---
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlString, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet("NameOfDataSet");

//need mapping for friendly names
da.TableMappings.Add("Table", "Customers");

conn.Open;
da.Fill(ds);

//some updates
ds.Tables["Customers"].Rows[0]["LastName"] = "NewLastName";

da.Update();

This is stubbed code (partially pseudocode), but it gets the basic idea
across.

---

An alternate way using the same method would be to create a typed dataset
and access the columns that way.

Example:

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlString, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
MyDataSet.tblMyTableDataTable dt = (new MyDataSet()).tblMyTable;

conn.Open();
da.Fill(dt);

//some updates
dt.LastName = "NewLastName";

da.Update();

// yadda yadda yadda

In the long run, it allows for easier recognition of column names and
compile-time errors if, for example, you spelled a column name wrong.
This would work best if you used drag-n-drop to create the typed dataset
(in the Visual Studio .Net IDE).

Hope this helps...

Mythran

 

[Mythran]

Is there a way to get this to work without using the 'drag and drop'
method. I would like to attempt to make a reusable way to wrap this in a
function.

Byron...

"Cowboy (Gregory A. Beamer) - MVP" <[email protected]>
wrote in message

The closest, in ADO.NET, is filling a DataSet with a DataAdapter,
chaging
data and then calling Update() on the DataAdapter.

C#
---
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlString, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet("NameOfDataSet");

//need mapping for friendly names
da.TableMappings.Add("Table", "Customers");

conn.Open;
da.Fill(ds);

//some updates
ds.Tables["Customers"].Rows[0]["LastName"] = "NewLastName";

da.Update();

This is stubbed code (partially pseudocode), but it gets the basic idea
across.

---

An alternate way using the same method would be to create a typed dataset
and access the columns that way.

Example:

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlString, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
MyDataSet.tblMyTableDataTable dt = (new MyDataSet()).tblMyTable;

conn.Open();
da.Fill(dt);

//some updates
dt.LastName = "NewLastName";

da.Update();

// yadda yadda yadda

In the long run, it allows for easier recognition of column names and
compile-time errors if, for example, you spelled a column name wrong.
This would work best if you used drag-n-drop to create the typed dataset
(in the Visual Studio .Net IDE).

Hope this helps...

Mythran

You can take a look at the Enterprise - Patterns and Practices libraries,
specifically the Data Access portion..it's available on the Microsoft
Patterns and Practices website.

Mythran