CODEWITHSUNDEEP
c#sql-serverms-access
user1451111
user1451111

Reputation: 1943

replacing data access strategy from SQL Server to Access MDB file in C#

I created a small accounting application a while back. It uses SQL Server 2008 as backend data store. Now I realize that SQL Server is too much for such a

small program PLUS it is not very much portable. I mean I want to have this application on my USB stick for easy access anywhere, and SQL Server will not be

available everywhere. So now I want to replace the data store from SQL Server to something portable i.e. MS Access MDB file.

1- Is it a good option or should I use SQL Server express edition? 2- I don't have experience using SQL Express edition. If I use it, would it be needed on any machine where I intend to run my application? 2- What changes should I make in the code to make it compatioble with MDF files (or SQL Express)?

As I said it is quite simple program, it uses connected model to fetch and insert data currently. For example 

void bindGrid()
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    DataSet set = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter();
                    SqlCommand selectCommand = new SqlCommand("SELECT * FROM Categories ORDER BY name", con);
                    da.SelectCommand = selectCommand;
                    da.Fill(set);
                    this.grdCategories.DataSource = set.Tables[0];
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Upvotes: 0

Views: 496

Answers (2)

Frobzig
Frobzig

Reputation: 324

I would recommend SQL CE for small projects.

LocalDB vs SQL Server Compact

There are significant differences between LocalDB and SQL Server Compact:
Execution mode: SQL Server Compact is an in-proc DLL, while LocalDB runs as a separate process.
Disk usage: all SQL Server Compact binaries amount to some 4MBs, while LocalDB installation takes 140MBs.
Features: SQL Server Compact offers core RDBMS functionality like querying, while LocalDB provides a much richer set of features, including Stored Procedures, Geometry and Geography data types, etc.

Upvotes: 1

Eric J.
Eric J.

Reputation: 150108

Rather than using Access, I would use LocalDB. It should require very few if any changes to your code other than the connection string.

http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx

LocalDB is created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. In effect the developers that target SQL Server no longer have to install and manage a full instance of SQL Server Express on their laptops and other development machines. Moreover, if the simplicity (and limitations) of LocalDB fit the needs of the target application environment, developers can continue using it in production, as LocalDB makes a pretty good embedded database too.

Upvotes: 1

Related Questions