What .NET / C# equivalent to ADO.AddNew(), ADO.Update(), etc-CodePudding

Remember these from ADO days?

rs.movenext()
rs.addnew()
rs.update()

Is there an equivalent .Open, .MoveNext, .AddNew and .Update class or component in .NET? What's the current standard way for opening, reading and updating SQL Server database tables row-by-row?

Currently, I use Microsoft.Data.SqlClient with SqlCommand.ExecuteNonQuery() to issue INSERT/UPDATE and DELETE statements.

Is that just the way to go, or is there a more up-to-date way of doing it?

CodePudding user response：

Concur with all the comments. To answer your question the closest is perhaps DataReader and manual update ;

using var c = new SqlCommand("SELECT * FROM person", "connstr here");
using var u = new SqlCommand("UPDATE person SET Name = @n WHERE ID = @i", "connstr here");
u.Parameters.Add("@n", SqlDbType.Int);
u.Parameters.Add("@i", SqlDbType.VarChar, 30);
c.Connection.Open();
u.Connection.Open();
var r = c.ExecuteReader();
while(r.Read()){
  var id = r.GetInt32(0));
  var name = r.GetString(1);
  u.Parameters["@n"] = name "Hello";
  u.Parameters["@i"] = id;
  u.ExecuteNonQuery();
}

Ugh.

It got better with DataTables:

var da = new SqlDataAdapter("SELECT * FROM Person");
var cb = new SqlCommandBuilder(da);
var dt = new DataTable();
da.Fill(dt);
foreach(DataRow r in dt.Rows)
  r["Name"] = r["Name"].ToString() "Hello";
da.UpdateCommand = cb.GetUpdateCommand();
da.Update(dt);

Ugh.

And that got better with strongly typed datatables:

var t = new PersonTableAdapter();
var dt = t.GetData(); //the sql is hidden inside the tabkeadapter and is configured in a nice visual designer 
foreach(var person in dt)
  person.Name ="Hello";
t.Update(dt);

But even these are ancient tech now, though they do still work nicely with WinForms, but not in netcore

Fast forward one iteration of EF and other ORMs like it and it's probably fair to say that most people who create a project in netcore use entity framework core, the simple approach for which (if you have an existing db) can be:

install EF core power tools extension (no affiliation)
reverse engineer your existing db to get a context and a set of classes representing the db tables (instances represent the rows) with navigation properties between them representing relationships
write:


    var people = dbContext.People; //you'd use LINQ to do where clauses etc here
    foreach(var person in people)
      person.Name ="Hello";
    
    dbContext.SaveChanges();

(actually you'd probably use the async versions but I've left it out for brevity)

Also worth mentioning that Sam Saffron and Marc Gravell got weary of EF being too heavyweight to meet the needs of supporting stackoverflow's data access requirements and created Dapper, which is kinda like the object mapping part of EF but more light weight and gives you hands on SQL (but you have to manage relationships between data yourself); it runs queries and reads/creates instances of objects for you:

//class Person with int Id, string Name properties somewhere 

    ...

    using var c = new SqlConnection("conn str here");
    var people = c.Query<Person>("SELECT * FROM people"); //the property names of Person match the table column names exactly
    foreach(var p in people){
      p.Name  = "Hello";
      c.Execute("UPDATE person SET Name = @Name WHERE ID = @Id", p); //the parameter names exactly match the c# property names
    }

People like it as a blend of "gives me acces to the raw sql so I can accurately control exactly what the db does" but "relieves me of the tedium of adding parameters to commands, or pulling data out of readers and putting it into object properties" - a sort of halfway house between a full ORM and creating DbCommand instances

There are plenty of other libraries that aim to do similar things to EFC/Dapper; I won't list them all, nor do I endorse or recommend anything here over anything else you might find. A Google search for "x alternative" will start you off if you want to explore alternatives to these popular pins mentioned.

This answer also isn't intended to be a "this is exactly and only how you do" nor a "this is best practice" (though hopefully mostly reasonable; I'd async where possible) it's just an illustration of the progression of "how we did things then, after ADORS", to "how we might do things now"

^{I have no affiliation with any software mentioned}