Use of OleDbDataAdapter and OleDbCommandBuilder to gret and update DataTables in C#

Question

Use of OleDbDataAdapter and OleDbCommandBuilder to gret and update DataTables in C#

Owen Ransen 546

I'm really inexpert in database and OLE stuff, but I've managed to find (after a ton of searchng) enough explanation to write GetDataTable and UpdateDataTable functions.

My question is: Have I missed something here below which will bite me in the future?

    public static void UpdateDataTable (DataTable dt)  
    {  
        try  
        {  
            OpenDbConnections();  
  
            string strSQL = "SELECT * FROM " + dt.TableName;  
  
            OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL, m_DbDataConnection);  
  
            OleDbCommandBuilder myCmdBuilder = new OleDbCommandBuilder(myCmd);  
  
            myCmdBuilder.GetInsertCommand(); // So dt.Add works  
            myCmdBuilder.GetDeleteCommand(); // So a dt row.Delete works  
            myCmdBuilder.GetUpdateCommand(); // so a dt row.BeginEdit and row.EndEdit works  
  
            myCmd.Update(dt);  
        }  
        catch (Exception Ex)  
        {  
            ABSup.Log.WriteLn(CLog.Type_e.ekErr, "UpdateTable failed <" + dt.TableName + ">\n" +  
                                                  Ex.Message, "\n", ABSup.m_sTableDBFullFilename,  
                                                 "\nHRESULT:", Ex.HResult.ToString());  
        }  
    }  
  
  
  
    // Get a named table from the database...  
    // Tables are normally in DB_CreaColl.mdb  
    public static DataTable GetDataTable (string sTableName)  
    {  
        try  
        {  
            OpenDbConnections();  
  
            string strSQL = "SELECT * FROM " + sTableName;  
  
            OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL, m_DbDataConnection);  
            DataSet dtSet = new DataSet();  
            myCmd.Fill(dtSet, sTableName);  
            DataTable tabData = dtSet.Tables[0];  
            return tabData;  
        }  
        catch (Exception Ex)  
        {  
            ABSup.Log.WriteLn(CLog.Type_e.ekErr, "GetDataTable could not open <" + sTableName + ">\n" +   
                                                  Ex.Message, "\n", ABSup.m_sTableDBFullFilename,  
                                                 "\nHRESULT:", Ex.HResult.ToString());  
            return null;  
        }  
    }

PS: I've also tried to add the above code in the 101010 code window, but it does not work...

Jack J Jun 25,296 Reputation points

2022-06-29T08:53:23.667+00:00

@Owen Ransen , Welcome to Microsoft Q&A, you could refer to the following link to use OleDbDataAdapter to update a DataTable in c#.
Using OleDbDataAdapter to update a DataTable C#

Accepted answer

2 additional answers

Your answer

Jack J Jun 25,296 Reputation points

2022-06-29T08:53:23.667+00:00

@Owen Ransen , Welcome to Microsoft Q&A, you could refer to the following link to use OleDbDataAdapter to update a DataTable in c#.
Using OleDbDataAdapter to update a DataTable C#

Answer 1

AgaveJoe 30,126

My question is: Have I missed something here below which will bite me in the future?

The DataTable is/was a ASP.NET generic data container that's not found much in modern C# programming. Strong types and Generics are preferred. After all C# is a strongly typed language. Also, the DataTable is an ASP.NET construct that does not translate well to other platforms/clients. For example, if you decided to share you data access layer over REST services (Web API), only ASP.NET clients will understand what a DataTable is. Even then the ASP.NET client might not know if they have a Widget result set or a FooBar result set.

In my opinion you're better off using the OleDbDataReader to populate a List<T> (C# Generics).

Owen Ransen 546 Reputation points

2022-06-30T05:56:12.95+00:00

Thanks for the reply. The CAD application is unlikely to move from anything other than Access so I'll stick with DataTable what I've got for the moment, encapsulating the database stuff to make it easier for a port in some distant future.
Lamia Elshahat Eldesoky 76 Reputation points

2022-07-01T01:43:40.917+00:00

Good idea
Lamia Elshahat Eldesoky 76 Reputation points

2022-07-01T15:25:26.043+00:00

That's right

Answer 2

Karen Payne MVP 35,586 Volunteer Moderator

GetDataTable should use assertion rather than a try/catch, something like the following which requires the connection string to be setup by you.

using System;  
using System.Data;  
using System.Data.OleDb;  
using System.Linq;  
  
namespace YourNamespaceGoesHere  
{  
    public class Utilities  
    {  
        private static string _connectionString =   
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TODO";  
  
        public static bool TableExists(string tableName)  
        {  
            using (var cn = new OleDbConnection() { ConnectionString = _connectionString })  
            {  
                var schema = cn.GetOleDbSchemaTable(  
                    OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });  
  
                return schema.Rows.OfType<DataRow>().Any(r =>   
                    string.Equals(r.ItemArray[2].ToString(), tableName,   
                        StringComparison.CurrentCultureIgnoreCase));  
            }  
        }  
    }  
}

Also, its a bad idea to have a method such as OpenDbConnections(); which appears to open database connection(s). Best to create a new connection when possible, meaning in some cases it's not possible while in other cases a connection is private to a method.

Owen Ransen 546 Reputation points

2022-06-29T16:49:06.353+00:00

Thanks for the reply...

If I've understood you what you mean is that I should open and close connections to a database as and when I need them. i.e not keep a connection open for the whole run of the program.

That "using" will do the open and close, right?

Have I understood?
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-06-29T17:52:27.593+00:00

Generally speaking, a connection should reside in each method required to interact with a database operation. The connection string is scoped appropriately e.g. working with a single database and several classes need the connection string make it public static, read-only. If one class needs it, private static, read-only. Even better if there is no password, place the connection string in appsettings.json and read from that file. When working with a SqlDataAdapter place all code in a class and call from a form.

I started refactoring an old project to show what I mean as per above but is not finish so read the comments. Will finish it tonight after work, way too busy right now.

Project on GitHub done with .NET Framework 4.8, will work fine in .NET Core also.

Karen Payne MVP 35,586 Volunteer Moderator

Here is the updated code, all data operations moved out of the form.

public partial class Form1 : Form  
{  
    public Form1()  
    {  
        InitializeComponent();  
        Shown += OnShown;  
    }  
  
    private void OnShown(object sender, EventArgs e)  
    {  
        var (success, exception) = EmployeeOperations.Load();  
  
        if (success)  
        {  
            dataGridView1.DataSource = EmployeeOperations.BindingSource;  
            dataGridView1.Columns[0].ReadOnly = true;  
        }  
        else  
        {  
            SaveChangeButton.Enabled = false;  
            MessageBox.Show($@"Failed to load data\n{exception.Message}");  
        }  
  
    }  
    private void SaveChangeButton_Click(object sender, EventArgs e)  
    {  
        var (affected, exception) = EmployeeOperations.SaveChanges();  
        if (exception != null)  
        {  
            MessageBox.Show($@"Save failed\n{exception.Message}");  
        }  
        else  
        {  
            MessageBox.Show($@"Affected records {affected}");  
        }  
    }  
}

Owen Ransen 546 Reputation points

2022-06-30T05:57:06.823+00:00

Thanks for the reply. I'll follow your advice, or try to!

Answer 3

Lamia Elshahat Eldesoky 76

That's great

Share via

Use of OleDbDataAdapter and OleDbCommandBuilder to gret and update DataTables in C#

2 additional answers

Your answer