Use ADODB.RecordSet in C#

Usually one can use System.Data.SqlClient to deal with operations on MSSQL server. The corresponding objects are SqlConnection, SqlCommand, SqlDataReader etc.

There is one scenario like this: the user needs to read something from each row in a table, and use the obtained information to update that row. One needs to cycle through all the records, and then use "UPDATE" query for the purpose. If SqlDataReader is used, the efficiency will be slow. SqlDataReader is read only, and uses the current connection exclusively as well. The user needs to open a second connection, and use the second connection to do the update based on a key.

In ADODB.RecordSet, one can use RecordSet.Update() function to directly update the current row and write back to database. No second connection is needed, and no key-based query is needed. This is much more efficient. A similar mechanism in SqlClient may exist, but I don't know yet at this time.

The following is an example using ADODB.RecordSet in C#. Note one needs to add ADODB to the references list of the current C# project first.

Parameters used by RecordSet can be found at http://www.w3schools.com/ADO/met_rs_open.asp.

private void updateTbl_ADODB() {
    ADODB.Connection conn = new ADODB.ConnectionClass();
    ADODB.Recordset rs = new ADODB.RecordsetClass();

    try {
        string strConn = 
            "Provider=SQLOLEDB;Initial Catalog=[database];Data Source=[server];";
        conn.Open(strConn, [user], [pwd], 0);

        string sql = "SELECT * FROM tbl";
        rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, 
                ADODB.LockTypeEnum.adLockOptimistic, -1);

        while (rs.EOF == false)  {
            rs.Update("field_name", [new_value]);
            rs.MoveNext();
        }

    } catch (Exception e) {
        MessageBox.Show(this, "Error" + e.Message);
    } finally {
        conn.Close();
    }
}