none
What is the best third party product for rapid C# programming with MS-ACCESS and Winforms? RRS feed

  • Question

  • Hello,

    My dilemma is a unique one, I think.  I have gained a certain level of C# proficiency, and wish to do pragmatic, rapid development in C# with MS-ACCESS as my database engine.  I have a strictly single-user application, no need for SQL or mutl-user.

    My app is also extremely simple:  2 flat tables, and a 3rd file/table consisting of transactions based on the first 2 tables.

    The  volume of data is medium:  The 2 flat tables are about 1,000 records Max. (each), and the record size (fixed) at about <300 bytes.

    The 3rd file (transactions) average about 20MB (< 30,000 records) per year.  Nothing "heavy" to speak of.

    Given these characteristics, I am looking for a third-party SDK or API that can, from a C# developer standpoint:

    1 - simplify my need to deal with Winforms details -- create simple screen layouts, fields, buttons, drop downs, etc..

    2 - simplify my need to "delve into" MS-ACCESS database calls/interfaces -- my app just needs to read/write records,

         do simple, repeatable queries that have only 2 selection criteria at most (a date range, and a single field), and output data to EXCEL file format.

    I am told one such company is ComponentOne, http://www.grapecity.com  but I've been unsuccessful so far in trying to contact them.

    Would be highly grateful for any useful tips and suggestions.

    Thanks in advance.


    R.Tan

    • Moved by Dave PatrickMVP Sunday, October 15, 2017 9:15 PM
    • Moved by CoolDadTx Monday, October 16, 2017 2:12 PM Winforms related
    Sunday, October 15, 2017 8:55 PM

All replies

  • For working with MS-Access it sounds like OleDb data provider would suit your needs. For exporting data to Excel if using .xlsx format consider the library SpreadSheetLight which is free and does not require Excel to be installed.

    Below is a very simple class showing reading and inserting data in ms-access where the database is in the same folder as the application. Updates and removal of record should be easy to grasp from the shown code.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.Diagnostics;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace DataAccess
    {
        /// <summary>
        /// Responsible for all database loading and inserting
        /// </summary>
        /// <remarks>
        /// LoadSingleRow and AddNewRow(ByVal CompanyName As String, ByVal ContactName As String...)
        /// were added in for assisting with a online question where the person was using VS2008
        /// so there are differences with object initialization to fit into VS2008 syntax
        /// </remarks>
        public class Operations
        {
    
            /// <summary>
            /// Creates our connection string to the database which is easy to follow
            /// and there is no string concatenation done here
            /// </summary>
            /// <remarks></remarks>
            private OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder { Provider = "Microsoft.ACE.OLEDB.12.0", DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb") };
    
            /// <summary>
            /// Used to get all customers at program startup
            /// </summary>
            /// <remarks></remarks>
            private string SelectStatement = "SELECT Identifier, CompanyName, ContactName, ContactTitle FROM Customer;";
    
            /// <summary>
            /// Responsible for inserting rows into the customer table
            /// </summary>
            /// <remarks></remarks>
            private string InsertStatement = "INSERT INTO Customer (CompanyName, ContactName, ContactTitle) Values (@CompanyName, @ContactName, @ContactTitle)";
    
            /// <summary>
            /// Used to open the database in Windows Explorer
            /// </summary>
            /// <remarks></remarks>
            public void ViewDatabase()
            {
                Process.Start(Builder.DataSource);
            }
    
            /// <summary>
            /// Load existing customers into a BindingSource which becomes
            /// the DataSource for a DataGridView
            /// </summary>
            /// <returns></returns>
            /// <remarks></remarks>
            public DataTable LoadCustomers()
            {
                using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
                {
                    using (OleDbCommand cmd = new OleDbCommand { Connection = cn })
                    {
                        cmd.CommandText = "SELECT Identifier, CompanyName, ContactName, ContactTitle FROM Customer;";
    
                        DataTable dt = new DataTable { TableName = "Customer" };
    
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
    
                        return dt;
    
                    }
                }
            }
            public string LoadSingleRow(int Identifier)
            {
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                using (OleDbConnection cn = new OleDbConnection(Builder.ConnectionString))
                {
                    using (OleDbCommand cmd = new OleDbCommand("", cn))
                    {
                        cmd.CommandText = "SELECT CompanyName, ContactName, ContactTitle FROM Customer WHERE Identifier =" + Identifier.ToString();
                        cn.Open();
                        OleDbDataReader Reader = cmd.ExecuteReader();
                        if (Reader.HasRows)
                        {
                            Reader.Read();
    
                            sb.AppendLine("Company name [" + Reader.GetString(0) + "]");
                            sb.AppendLine("Contact [" + Reader.GetString(1) + "]");
                            sb.AppendLine("Contact name [" + Reader.GetString(2) + "]");
                        }
                        else
                        {
                            sb.AppendLine("Operation failed");
                        }
                    }
                }
    
                return sb.ToString();
            }
            /// <summary>
            /// Used to add one row to the Customer table and on success
            /// return the new primary key.
            /// 
            /// Here we pass in a customer but could have very well passed in
            /// an object array or one parameter for each field.
            /// 
            /// All fields are of type string but you can add other types
            /// too.
            /// 
            /// AddWithValue is used here but we could also use Add and
            /// control the parameters.
            /// 
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="Identfier"></param>
            /// <returns></returns>
            /// <remarks></remarks>
            public bool AddNewRow(Customer sender, ref int Identfier)
            {
                bool Success = true;
    
                try
                {
                    using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
                    {
                        using (OleDbCommand cmd = new OleDbCommand { Connection = cn })
                        {
    
                            cmd.CommandText = InsertStatement;
    
                            cmd.Parameters.AddWithValue("@CompanyName", sender.CompanyName);
                            cmd.Parameters.AddWithValue("@ContactName", sender.ContactName);
                            cmd.Parameters.AddWithValue("@ContactTitle", sender.ContactTitle);
    
                            cn.Open();
    
                            cmd.ExecuteNonQuery();
    
                            cmd.CommandText = "Select @@Identity";
                            Identfier = Convert.ToInt32(cmd.ExecuteScalar());
    
                        }
                    }
    
                }
                catch (Exception ex)
                {
                    Success = false;
                }
    
                return Success;
    
            }
            public bool AddNewRow(string CompanyName, string ContactName, string ContactTitle, ref int Identfier)
            {
                bool Success = true;
    
                try
                {
                    using (OleDbConnection cn = new OleDbConnection(Builder.ConnectionString))
                    {
                        using (OleDbCommand cmd = new OleDbCommand("", cn))
                        {
    
                            cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,ContactTitle) Values (@CompanyName,@ContactName,@ContactTitle)";
    
                            cmd.Parameters.AddWithValue("@CompanyName", CompanyName.Trim());
                            cmd.Parameters.AddWithValue("@ContactName", ContactName.Trim());
                            cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle.Trim());
    
                            cn.Open();
    
                            cmd.ExecuteNonQuery();
    
                            cmd.CommandText = "Select @@Identity";
                            Identfier = Convert.ToInt32(cmd.ExecuteScalar());
    
                        }
                    }
    
                }
                catch (Exception ex)
                {
                    Success = false;
                }
    
                return Success;
    
            }
            public Operations()
            {
            }
    
            public override string ToString()
            {
                return "Demo adding rows";
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, October 16, 2017 12:52 AM
    Moderator
  • What is best for you might be the worst for someone else and what is best for them might be the worst for you. Words like "best" in questions like this are often unproductive.

    As for making MS-ACCESS database calls/interfaces easier, there is a company called Microsoft that offers something called Entity Framework that does that.

    Note that if you are not locked into Access then consider SQLite. It is part of current versions of Windows 10.



    Sam Hobbs
    SimpleSamples.Info

    Monday, October 16, 2017 3:37 AM
  • I would start with the Data Binding tools in Visual Studio if your solutions are that simple:

    Bind Windows Forms controls to data in Visual Studio

    If you want Windows Forms controls with more features then I would recommend either DevExpress or Telerik. Just be prepared to spend a good chunk of change for their component packages. Since you are single developer you may also want to look at Syncfusion, which offers a free license.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, October 16, 2017 1:19 PM