• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • FREE workshop: Preparing contractors for Autumn : Weds 29th Sep at 7.15pm. More details here.

Embedded Database for Windows Forms App

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Embedded Database for Windows Forms App

    I've a small Windows Form (.net) app - basically a glorified calendar/organiser customised for a particular industry. I've been using SQL Server Compact Edition quite happily for the backend but this seems now to have been deprecated by MS and does not play nicely with the latest Visual Studio. I've also had requests to make it multi-user.

    So, looking for a replacement. The ideal candidate will integrate well with VS, install easily on remote workstations, and support concurrent connections, preferably without going full monty client server.

    So far considered

    Access (Jet): file-based but prone to corruption.
    SQLLite unknown quantity but apparently recommended by MS.
    SQL server LocalDB: not sure if this is genuinely multiuser, and
    SQl Server Express or MySql if there's nothing more suitable.

    Experience/Recommendations? When I say multi-user we are probably talking 5 users tops, a few Gb data max and light usage.
    My subconscious is annoying. It's got a mind of its own.

    #2
    So you have per-workstation DB which is used purely to store local data to user(s) on that workstation?
    Originally posted by MaryPoppins
    I'd still not breastfeed a nazi
    Originally posted by vetran
    Urine is quite nourishing

    Comment


      #3
      This is the raison d'être of SQLite (one L). You don't need to install anything; it's just an application reading/writing a data file in a format it knows about.

      It supports concurrent access, but only one can write at a time so it's not be the best if you're likely to have lots of simultaneous writes going on. I've never tried VS integration, but I believe there is some, and the SQL is not as fully featured as you'd get with a proper database server (I've had issues with the lack of UPSERT). I haven't tried it with .NET either, but presumably that's just some mappings to the C library. Otherwise it works very well in my experience.
      Will work inside IR35. Or for food.

      Comment


        #4
        Originally posted by VectraMan View Post
        This is the raison d'être of SQLite (one L). You don't need to install anything; it's just an application reading/writing a data file in a format it knows about.

        It supports concurrent access, but only one can write at a time so it's not be the best if you're likely to have lots of simultaneous writes going on. I've never tried VS integration, but I believe there is some, and the SQL is not as fully featured as you'd get with a proper database server (I've had issues with the lack of UPSERT). I haven't tried it with .NET either, but presumably that's just some mappings to the C library. Otherwise it works very well in my experience.
        I've used SQLite with no problems using .NET

        Just use the SQLite ADO.NET provider here:

        System.Data.SQLite: Home

        As VectraMan says, multiple concurrent reads is fine, each writer locks the database until the write is committed.

        Doesn't work properly if the DB file is on a network share, has to be a local drive to the application.

        Apart from that, is very fast and efficient DB, I used it with an ASP.NET application for years, never let me down.

        PS. If you want multi-user a browser front end with ASP.NET is a much better solution. You can use SQLite like I did on the webserver in that case no problem.

        PPS. If you want to stick with a WinForms GUI and multi-user access, you should move to a client-server database. For small amounts of data just use SQL Server Express as the DB server.

        https://www.microsoft.com/en-gb/serv...r-express.aspx
        Last edited by DimPrawn; 24 November 2015, 11:59.

        Comment


          #5
          Originally posted by pjclarke View Post
          SQLLite unknown quantity but apparently recommended by MS.
          If the db is entirely kept on the user workstation then SQLite is an obvious candidate. It compiles into your application as a bunch of C files (actually there is a single-file version too) so you may need to do some cross-language development if you are using another language for your application.

          Boo

          Comment


            #6
            Originally posted by Boo View Post
            If the db is entirely kept on the user workstation then SQLite is an obvious candidate. It compiles into your application as a bunch of C files (actually there is a single-file version too) so you may need to do some cross-language development if you are using another language for your application.

            Boo
            If you are using .NET, the ADO.NET provider takes care of everything, as simple as opening a connection, read and write, manage transactions etc. It talks to the SQLite.dll using P/Invoke internally.

            Comment


              #7
              PS. If you want multi-user a browser front end with ASP.NET is a much better solution. You can use SQLite like I did on the webserver in that case no problem.

              PPS. If you want to stick with a WinForms GUI and multi-user access, you should move to a client-server database. For small amounts of data just use SQL Server Express as the DB server.
              As a first cut, I've ported to SQL Server and that seems to work well, though I'm not sure that it qualifies as lightweight. Been looking at the LocalDb option but cannot seem to get a straight answer a to whether it supports multiuser/concurrency, as I understand the term.

              The target market is mainly very small orgs with maybe 1/2 PCs in an office plus users who may want to use the app on a laptop. Little or no tech support. My skillset does not include webserver in any depth, so I'm sticking with what I know for now.

              Thanks for all the answers, food for thought. So nobody has experience of using Jet for this kinda thing? It used to be the default database for .net not so long ago ISTR.
              My subconscious is annoying. It's got a mind of its own.

              Comment


                #8
                Originally posted by pjclarke View Post
                So nobody has experience of using Jet for this kinda thing? It used to be the default database for .net not so long ago ISTR.
                I have in the dim and distant past, but via ODBC and C++. It's essentially the same as SQLite being based on a single file and with no server process, but with ODBC you had to faff around creating a DSN and have the driver installed so a bit more difficult to set up than SQLite. But I remember it working well enough.
                Will work inside IR35. Or for food.

                Comment


                  #9
                  The more I read around the topic, the more I like SQLite. MS want to push people onto SQL Server but have deprecated the Compact Edition, so I think if I wanted an MS multi-user solution would mean SQL Server Express which just feels like overkill for this app.

                  The Jet db seems to have been deprecated at v4, which is 32-bit only, which puzzled me as Access seems to be going strong as part of Office. However it seems the core RDBMS has been frozen and MS are just maintaining the driver (ACE) which enables 64 bit systems to access the db engine. Not sure I want to tie myself to a deprecated tech.
                  My subconscious is annoying. It's got a mind of its own.

                  Comment


                    #10
                    .Net CMS like Umbraco still use Windows CE heavily so it may not have a long future but it is still being used, and good enough for clients too tight to pay out for a proper license

                    I've not encountered issues using Visual Studio but most of my back end code is just ADO.Net hand written code, maybe all the gui stuff is starting to hate it ?
                    Socialism is inseparably interwoven with totalitarianism and the abject worship of the state.

                    No Socialist Government conducting the entire life and industry of the country could afford to allow free, sharp, or violently-worded expressions of public discontent.

                    Comment

                    Working...
                    X