locked
Databases & Projects RRS feed

  • Question

  • Hey

    Can someone explain is simple terms, different methods of implementing databases in a project because it seems that the though process just doesnt match what is required...

    in conjunction with a previous thread, i need to add a database solution to my project that doesnt require SQL server and an SQL server install. This limits me obviously but there must be a way.

    1. LocalDB. i downloaded and installed this. It reads that a local db can be created and used however, it didnt work for me. There were 2 instances in my browser pane but they were not available when i tried to add a datasource.
    2. SQL Compact 4.0 Edition. This was looking promising. I was able to create the database .sdf file but couldnt find a way to add it or access it from my project.
    3. MS Access. I currently have my data in an Access Database as it was the easiest to set up but i believe using this with a vb.net project is a little dated now. However its the only method that is working....
    4. XML. This was suggested in my other thread but im not sure its the way i want to go. Seeing as i need to query the data, i feel that its datatables of datatables seeing as the ReadXML is a direct read stream into the datatable and a straight save back. Maybe im wrong, hopefully i am but i feel the documentation sends me round in circles.

    I have been trying to find a way to extract the Access data tables from Access to either the sdf or mdf files but its not happening. Here are my questions:

    When look at adding a new item / Data, there are a few options for me:

    1. DataSet. this is what i used to connect the access database too which needed a download and install of an Access 2007 runtime file. This works good in the sense of the TableAdapter code that makes it simple to get / post data to / from the database, just dont want it to be an Access database. Can this be dome with other database types?
    2. Service-based Database. This appears to be created locally but its unclear whether i need to install SQL server during the install of the application once compiled. I am not permitted to install SQL server on any of the company machines as IT dont want it, they say its a risk.

    Apart from XML, there arent any other container type objects for me to add.

    What are my options and how are would they be implemented? I am ok with the use of databases, queries, stored procedures but trying to add something outside of SQL Server is somewhat difficult. My last project was SQL server based using BLL & DLL methods that worked great but im looking for a simplified way here, something that doesnt require external installations as they are not permitted.

    Any help graciously received

    Thanks


    Im a self taught VB.Net guy who writes code for Autodesk Inventor. I may not know the terminology but i try so please be patient. Im not a kid so please dont treat me like one :)

    Thursday, May 14, 2020 6:09 AM

All replies

  • Service-based Database. This appears to be created locally but its unclear whether i need to install SQL server during the install of the application once compiled. I am not permitted to install SQL server on any of the company machines as IT dont want it, they say its a risk.

    IT personnel do not know what they are talking about when it comes to a standalone MS SQL Server Express MDF file that is using Localdb being used locally on a computer, which VS Service  Based Database is using.

    LocalDB.i downloaded and installed this. It reads that a local db can be created and used however, it didnt work for me. There were 2 instances in my browser pane but they were not available when i tried to add a datasource.

    I don't know what you are talking about concerning some browser pane and using the Localdb engine to access a MS SQL Server Express standalone MDF file. You can access a MDF file using lLocalDB through VS' Server Explorer or via SSMS for DBA purposes. 

    A Service Based Database is a MS SQL Server Express standalone MDF file that uses Localdb. Localdb is installed with Visual Stuido.

    What is Localdb?

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15

    great but im looking for a simplified way here, something that doesnt require external installations as they are not permitted.

    I find the IT directive questionable and kind of total nonsense. The XML database is total nonsense too. You need to install database software on the workstation to use a database locally. 

    You can install a database engine on the workstation that has a light footprint.

    https://www.sqlite.org/index.html

    https://www.codeproject.com/Articles/1210189/Using-SQLite-in-Csharp-VB-Net


    • Edited by DA924x Thursday, May 14, 2020 10:34 AM
    Thursday, May 14, 2020 10:23 AM
  • SQL Compact 4.0 Edition. This was looking promising. I was able to create the database .sdf file but couldnt find a way to add it or access it from my project.

    You just use System.Data.SqlServerCe.dll

    You can find samples from Google, like :

    Easy Visual Studio C# database example using a local dynamically created database (Microsoft Sql Server Compact)

    (~same thing in VB.NET)



    • Edited by Castorix31 Thursday, May 14, 2020 10:55 AM
    Thursday, May 14, 2020 10:50 AM
  • Thanks for your reply. My reply back below

    IT personnel do not know what they are talking about when it comes to a standalone MS SQL Server Express MDF file that is using Localdb being used locally on a computer, which VS Service  Based Database is using.

    It doesnt matter if they do or do not know what they are talking about, There are strict company policies in place that prevent the installation any program that may introduce a 'way in' to the network. In one of our other offices there was some sort of DoS attack and because the port range fell within SQL server, it got added to a list of applications not to be installed. This is just a parameter i have to work with...

    I don't know what you are talking about concerning some browser pane and using the Localdb engine to access a MS SQL Server Express standalone MDF file. You can access a MDF file using LocalDB through VS' Server Explorer or via SSMS for DBA purposes. 



    Like i said, i installed it and i could see them in the browser pane / explorer window but couldnt access them when attempting to add a datasource. They can open whatever tehy like but not much good if they arent accessible through my project.

    I find the IT directive questionable and kind of total nonsense. The XML database is total nonsense too. You need to install database software on the workstation to use a database locally. 

    This may be the case but people in charge of IT (Not all) seem to run this power trip about what we can and cant install. There is no way they will install it on a server or let us install it on our workstations. Im not a fan of XML

    Thanks


    Im a self taught VB.Net guy who writes code for Autodesk Inventor. I may not know the terminology but i try so please be patient. Im not a kid so please dont treat me like one :)

    Thursday, May 14, 2020 3:00 PM
  • It doesnt matter if they do or do not know what they are talking about, There are strict company policies in place that prevent the installation any program that may introduce a 'way in' to the network. In one of our other offices there was some sort of DoS attack and because the port range fell within SQL server, it got added to a list of applications not to be installed. This is just a parameter i have to work with...

    Localdb is local to the machine it is installed on  and its environment using inter process communication between two programs running at the machine level, which is Localdb and the program the is logged on the MDF database using Localdb engine that is also running at machine level. Localdb is not a network database solution.  Only one user at any given time  can have the MDF file open, and it can be configured to only use Windows autentication and NT file permission access based on user credentials.

    And what you are talking about in accessing Localdb and,  a standalone MDF file, is Visual Studio's Server Explorer that is not a browser in the traditional sense.

    This may be the case but people in charge of IT (Not all) seem to run this power trip about what we can and cant install. There is no way they will install it on a server or let us install it on our workstations. Im not a fan of XML

    Well it looks that you're damned if you do and damned if you don't due to IT ignorance.  And without a database solution installed on the computer locally, then I don't see a successful path to glory. 

     

    Thursday, May 14, 2020 7:44 PM
  • Hey

    Thanks for the reply. I was under the impression that there was a sort of SQL engine pack that you could use when compiling and installing the project on the workstation. As long as i dont install a full SQL server, i can get around the wording of IT.. As for connections, i connect using code BLL & DAL which only connected for millisecond to get the data then closes the connection. I have this set up in a different project with multi users and it works great.

    I dont think LocalDB is what im looking for. If i use a SQL database mdf file, is there a SQL engine that can be installed to manage the database? Ive installed other packages before that work with databases but never installed SQL serve so it must exist somewhere. The database file will be placed in a local network folder like j:/data

    Thanks


    Im a self taught VB.Net guy who writes code for Autodesk Inventor. I may not know the terminology but i try so please be patient. Im not a kid so please dont treat me like one :)

    Thursday, May 14, 2020 8:03 PM
  • Thanks for the reply. I was under the impression that there was a sort of SQL engine pack that you could use when compiling and installing the project on the workstation.

    There is a Localdb MSI file that can be included in the install package and executed during the installation of the software solution and Localdb can be installed during this process. 

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15

    <copied>

    Install LocalDB through the installation wizard or by using the SqlLocalDB.msi program. LocalDB is an option when installing SQL Server Express Localdb.

    <end>As long as i dont install a full SQL server, i can get around the wording of IT.. As for connections, i connect using code BLL & DAL which only connected for millisecond to get the data then closes the connection. I have this set up in a different project with multi users and it works great.

    Localdb is a single user instance database solution using a standalone MDF file, meaning that only one user can have the MDF file open at any given time. A second user trying to run the program and trying to open the MDF file that is already open will result in an exception being thrown in the second program. Localdb is not a multi user database solution. It is a single user instance database solution and not suitable for multi user database usage concurrently.

    I dont think LocalDB is what im looking for. If i use a SQL database mdf file, is there a SQL engine that can be installed to manage the database?

    You can have a master copy of the MDF file that is accessible by Visual Studio's Server Explorer or MS SQL Server Management Stuido that you can do database administration on it for the master MDF file. 

    But the master MDF file must be installed on the local workstation with the program so that the program can access the MDF file for CRUD operations using Localdb that is also installed on the workstation.

    Take note if using Localdb. If the MDF file was created using MSSQL Server Express 2014, then the Localdb must be for MS SQL  Sever Express 2014. Capiche?

    Visual Stuido if using Service Based Database applies to the same rule above, VS2012 uses MS SQL Server Express 2012 MDF file so Localdb must be for MS SQL Server 2012.

    Thursday, May 14, 2020 8:39 PM
  • OK

    Probably a daft question here then.....

    The project i am writing isnt reliant on a database, its not database driven. The only need for the database is to hold 4 tables of lookup data with an initial setup phase of boolean choices for visibility etc.

    Am i able to access the mdf &  get / post data without the need for an SQL engine? Im thinking not. I am accessing the database on the fly with a class Connection

    Imports System.Data.SqlClient
    
    Public Class ClassConn
    
        '## CREATE THE CLASS VARIABLES
        Private Shared _Instance As ClassConn
        Private _Connections As New Hashtable
    
        '## SET UP ALL THE VARIABLES
        Public DataSource As String = "ipaddress here"
        Public InitialCatalog As String = "DatabaseName"
        Public PersistSecurityInfo As Boolean = True
        Public UserID As String = "username"
        Public Password As String = "password"
        Public MultipleActiveResultSets As Boolean = True
        Public App As String = "EntityFramework"
    
        Public ConnString As String = _
            "data source=" & DataSource & ";" & _
            "Initial Catalog=" & InitialCatalog & ";" & _
            "Persist Security Info=" & PersistSecurityInfo & ";" & _
            "user id=" & UserID & ";" & _
            "password=" & Password & ";" & _
            "MultipleActiveResultSets=" & MultipleActiveResultSets & ";" & _
            "app=" & App
    
        Public Sub Reset()
            Dim connection As SqlConnection = Nothing
            Try
                connection = CType(_Connections(ConnString), SqlConnection)
                connection.Dispose()
                connection = Nothing
            Catch ex As Exception
                'Do Nothing
            End Try
        End Sub
    
        Public Function GetConnection() As SqlConnection
            Dim Connection As SqlConnection = Nothing
            Dim bNeedAdd As Boolean = False
            Try
                Connection = CType(_Connections(ConnString), SqlConnection)
            Catch ex As Exception
            End Try
            If Connection Is Nothing Then
                bNeedAdd = True
            End If
            If Connection Is Nothing OrElse Connection.State = ConnectionState.Broken OrElse Connection.State = ConnectionState.Closed Then
                Try
                    Connection.Dispose()
                    Connection = Nothing
                Catch ex As Exception
                End Try
                Connection = New SqlConnection
            End If
    
            'Always return an open connection
            If Connection.State = ConnectionState.Closed Then
                Connection.ConnectionString = ConnString
                ' Connection.Open()
            End If
            If bNeedAdd Then
                _Connections.Add(ConnString, Connection)
            End If
            Return Connection
        End Function
    End Class

    Then as i need it, i use BLL & DLL Classes which call the connection on the fly, gets the data then closes the connection. the transaction process is milliseconds. In theory, would this work seeing as the database would not be exclusively connected all the time?



    Im a self taught VB.Net guy who writes code for Autodesk Inventor. I may not know the terminology but i try so please be patient. Im not a kid so please dont treat me like one :)

    Thursday, May 14, 2020 9:39 PM
  • Am i able to access the mdf &  get / post data without the need for an SQL engine? Im thinking not. I am accessing the database on the fly with a class Connection

    You cannot access a MS SQL Server MDF file programwise without some version of the database engine installed on a machine whether or not the full database engine is being used that the MS SQL Server Express or Server version uses,  or it is modified and limited version of the database engine called Localdb is being used.

    Then as i need it, i use BLL & DLL Classes which call the connection on the fly, gets the data then closes the connection. the transaction process is milliseconds. In theory, would this work seeing as the database would not be exclusively connected all the time?

    If you're thinking that you can use Localdb  and the standalone MDF file is some kind of file share environment, you will always run the risk of the second user trying to open the MDF file with the file already in an open state by the first user causing the second user using the program to abort. Milliseconds usage is not going to prevent the scenario, which is a timing issue. 

    Friday, May 15, 2020 12:47 AM
  • Hey

    Thanks for your input, much appreciated. Ive decided to go against the IT as this application is really important to the drafting team. I put a case together for the man in charge and will move forward with the SQL server version using the BLL & DAL methods i mentioned before. Im using these methods as it does maintain a closed database until its needed. The SQL server requirement is much needed in the multi user environment.

    Thanks again


    Im a self taught VB.Net guy who writes code for Autodesk Inventor. I may not know the terminology but i try so please be patient. Im not a kid so please dont treat me like one :)

    Friday, May 15, 2020 4:58 AM
  • You should be installing MS SQL Server Server edition on a server using a Windows server O/S, or you install MS SQL Server Express Edition on a Windowsw workstation or server O/S if MS SQL Server is expected to be used in multiuser environment. You install Localdb and deploy the standalone MDF to each computer, becuase Localdb is a single user instance of using MS SQL Server locally on a computer for an individual user using the computer that Localdb and the MDF file have been deployed to.
    Friday, May 15, 2020 6:36 AM