locked
Database Multiple Users / Remote Access RRS feed

  • Question

  • Hello,

    I'm currently working on rebuilding the database system for the company I work for.  I'm using VS C# 2010, the stores are connected by a private server network (Novell if that's relevant).  I was told to ask in the architecture forums about this particular problem.

    One reason (of several) that I'm trying to replace the current 20 year old DOS based system is that it's not very reliable for the multitude of users it has.  I haven't been able to decipher the relatively ancient code used to create it, but it we presently use Remote Desktop to access the program's .exe file stored on one of the machines.  The drawback being that if I log into one store, whoever was already on it gets booted.

    I've already built most of the individual forms needed to replace the system, my problem is that I'm only used to making small standalone programs, not server-based or multi user so I'm not at all sure what to do with them at this point.  Our set up appears to have separate databases for each store, and they all just call the prez and he syncs the records up at the end of every day.  My goal is to have a program I can just lodge in the server or something so that each store gets a separate table in one database.  I've already built a log in page with generic ID's for each store and that will open the form for that store, the form having the appropriate database table loaded.  Now I have no idea whatsoever as to where I go from there.  I've been told that just planting the DB on the server and having users access/update it remotely via the new program is insanely bad (and had to scrap the use of Access databases as well in favor of SQL for better multi-user).  This leaves me with more confusion, because if I just make an all in one and give it to each store, they still have separate databases, and I've been told that if I just build the database into the program itself and leave it on the server with shortcut icons to activate it, it will cause software corruption.  How should I set this up?  I have almost all the forms, I can switch my current prototypes to SQL easily enough, how do I connect them?

    Relative newb.  I've got an Associates in software application programming but it didn't cover a whole lot of networking.  Any advice or constructive abuse is welcome.  You can call me an idiot, but only if you offer advice toward changing that status. :)


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!
    Thursday, December 23, 2010 7:11 PM

Answers

  •  

    Please do understand the thoroughly understand the existing system in terms of functionality . This process is time taking process.  Then decide how much screens are provide for the software. When you are handling software development , please do try to follow the agile software development methods   like scrum.  Please do create very short term goals for your software project with respect to short timed milestones in terms software deliverable s.

     

    For your software application please do create the N tier software architecture for your project, For example Graphical user interface <===> Business logic <===> Data Access layer <==> Database layer (Sql server 2008 or Sql server 2005).

     

    For your software application you need to have a strong database application. After thoroughly understanding the functionalities ( their are times where the functionalities change time) , please do create the a simple but strong database  for software application.

    When the database is ready which depicts the your current software functionality, then decide to start the designing the screen for the graphical user interface.

     

    You can very  graphical user interface after the database is done.  You can install the Graphical user interface in each workstation . The software application running in each work station can communicate with Business logic by using the window communication foundation (WCF). The simple application flow is given below.

     

    Graphical user interface(GUI). will be communication with WCF which is turn business logic which is in turn communicating with Data Access layer and data access layer is communicating with SqlServer 2008.  You can host the WCF layer in IIS 7.0 where you need to have window server 2008 R2.  This server will also have the main database for your software application.

     

    The simple application follow

     

    GUI ç è WCF ç è BUSINESS LOGIC LAYER ç è DATA ACCESS LAYER ç è DATABASE LAYER(SQL SERVER 2008)

     

    If you have business logic for your software application , you can add the business logic layer other wise you can remove the business logic layer. If the business logic layer is removed , the follow diagram is given below

     

    GUI ç è WCF ç è DATA ACCESS LAYER ç è DATABASE LAYER(SQL SERVER 2008)

     

    For the database layer can use Ado.net , linqtoSQL or EntityFramework 3.5 or 4.0. Please ensure you have a very strong database design before your start the software application design or development. As the mentioned earlier , please do follow agile design methods  ie scrum for the software project. Please do remember the short goals for the software  goals and also give importance to software testing.

     

    For the graphical user interface please do follow in PRISM framework  and also looking into WCF materials from Microsoft. 

     

    Some very important technical tips regarding the WCF

     

    • How to design the wcf service ?
    • The Kind of binding needs to used in wcf service.
    • Hosting the wcf service.
    • Performing the loading testing on the WCF service.
    • Debugging the WCF service.

     

    The need of proof of concept. Please of Proof of Concept (POC) for your software application  and thoroughly test software application.

     

    Basic WCF Programming

    http://msdn.microsoft.com/en-us/library/ms731067.aspx

     

    Windows Communication Foundation Bindings

    http://msdn.microsoft.com/en-us/library/ms733027.aspx

     

    System-Provided Bindings

    http://msdn.microsoft.com/en-us/library/ms733027.aspx

     

    WCF Binding Comparison

    http://www.pluralsight-training.net/community/blogs/aaron/archive/2007/03/22/46560.aspx

     

    How to: Host a WCF Service in IIS

    http://msdn.microsoft.com/en-us/library/ms733766.aspx

     

    Hosting and Consuming WCF Services

    http://msdn.microsoft.com/en-us/library/bb332338.aspx

     

     

    Graphical User Interface - PRISM

    Welcome to the Prism Community

    http://compositewpf.codeplex.com/

     

     

     


    PHIJO MP
    • Marked as answer by psifreak Monday, December 27, 2010 1:27 PM
    Friday, December 24, 2010 8:36 AM
  • Tutorial 1: Creating a Data Access Layer

    http://msdn.microsoft.com/en-us/library/aa581778.aspx

      Chapter 12: Data Access Layer Guidelines

    http://apparchguide.codeplex.com/wikipage?title=Chapter%2012%20-%20Data%20Access%20Layer%20Guidelines&referringTitle=Home


    http://www.labor.state.ny.us/cioshares/pdf/Data%20Access%20Layer%20Design.pdf

    In an N-Tier architecture, the data access layer consists of components that aid one in the process of accessing the database. When it is used correctly, the data access layer serves as an abstract level for the structures of the database. Simple changes that are made to the database and to tables and other components will not effect the rest of the application – the data access layer will not allow this to happen! The various layers of the application send their data requests to this layer, and this layer responds.

     

    Only the data access layer may access the database. As a result, this is where field names and table names are hard coded – they are not hard coded anywhere else in the system. The data access layer is also able to access many services that it can then provide with data – these might include Services and Active Directory.

     

    Importantly, the data access layer serves as an extra layer for database security. This is because the other layers do not know database credentials, connect strings, or other sensitive information – with the data access layer, there is simply no reason for them to have access to this knowledge!

     

    On the data access layer, you can write generic methods, which will then interface, with your data. For instance, you might want to write a method for the creation and subsequent opening of a Connection object (internal), plus one more for the creation and use of a Command object, as well as a stored procedure with or without a return value…

     

    The possibilities are endless. It can also contain specific methods, like “saveProduct”, so that in the event that the Product object calls on it with the appropriate data, it can then persist it along to the Data Tier. Obviously, the data layer should contain no business rules or data manipulation / transformation logic – that is for other layers. The data access layer just serves as a reusable interface to the database – no more, no less.


    PHIJO MP
    • Marked as answer by psifreak Monday, December 27, 2010 1:27 PM
    Friday, December 24, 2010 9:11 AM
  • <!-- [if gte mso 10]> <mce:style>

    Business logic, or domain logic, is a non-technical term generally used to describe the functional algorithms that handle information exchange between a database and a user interface.

     Business logic:

     

    1. dels real life business objects (such as accounts, loans, itineraries, and inventories)
    2. prescribes how business objects interact with one another
    3. enforces the routes and the methods by which business objects are accessed and updated

     

    In practice, the business logic is often interwoven in the other two tiers (the user services tier and the database services tier), such as by encoding business logic in stored procedures and in decisions about input validation and display formatting. However there is not a well defined rule.

     

    A Task Management System using Three Layer Architecture

    http://www.codeproject.com/KB/aspnet/task_mgt_sys_threelayer.aspx

     

    Tutorial 2: Creating a Business Logic Layer

    http://msdn.microsoft.com/en-us/library/aa581779.aspx

     


    PHIJO MP
    • Marked as answer by psifreak Monday, December 27, 2010 1:27 PM
    Friday, December 24, 2010 9:20 AM
  • Explained in simple steps.

     

    Step 1:

     Please do study the existing software functionality and come up with a database design to meet  the software project  requirement.

     Step 2:

     Please do create an assembly which is Data Access Layer other wise know as DAL. In this DAL layer you can use linqtosql or entity frame work or ado.net. The choice is your all your. Please do use more stored procedure for the database operation.

      Step 3:

     If you need the Business logic layer other wise known as BLL , you can create the assembly for the BLL project. You can  call DAL methods here by adding the reference of the DAL assembly in the BLL project. The BLL layer needs to contain the business logic for your software project other wise , please do remove this layer.

      Step 4:

     This layer is wcf  layer , You  need to create the WCF service and host the wcf service in IIS 7.0. Please do use only IIS 7.0 only. Inside the WCF service methods , you call the BLL methods or the DAL methods.  For the software development , you wcf console hosting for development. Later you need to change the wcf service hosting to IIS 7.0. Never use the use Console WCF service for the actual development of your software application.

      Step 5 :

     Here we need to focus to design the Screens based on the database. Please do not create a screen which does not meet database functionality. End result will be double mayhem, a small advice. After the Design and implement the Screen, please do call the proxy methods of wcf service inside the Graphical user interface and debugging the software application from the graphical user interface to the database and vice versa.  For the graphical user interface , please do follow the prism framework. Software standards needs to maintained at any cost..

     Please do create a Proof of Concept from Step 1 to step 5 and debug the software application from Step 5 in the reverse order.

     Deploy the software application in your various workstations and test your application in various work station where you are able get and send data to and from the database.

     Please do agile software developments like scrum for software projects.

      


    PHIJO MP
    • Marked as answer by psifreak Monday, December 27, 2010 1:27 PM
    Saturday, December 25, 2010 5:15 AM

All replies

  • Addendum:  I've been told to look into ADO.Net.  If anyone can offer advice or links pertaining to reading material on the subject, it would be greatly appreciated.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!
    Thursday, December 23, 2010 7:31 PM
  • You should definitely be using SQL Server.

    You don't necesssarily have to have all new forms and tables for each store. You can instead add a StoreId into your tables and then retrieve by StoreId to get the set of data for a particular store.

    If you can put your SQL Server onto a computer that everyone can access via a connection, then you can all share the same database.

    It sounds like you may need some professional assistance to get you started in the right direction. Do you have a .NET user group in your area? That is a good way to find local consultants. Otherwise several people here are consultants, including me: (www.insteptech.com )

    Hope this helps.


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Thursday, December 23, 2010 8:11 PM
  • I was just using the separate tables because I can import them as tables where they sit when it goes live, admittedly a lazy solution because one neat feature is that the current system can export them as almost any DB format.  They're currently on different databases altogether (for understandable reasons concerning the age of this technology, it's about all they could manage).  Basically, the log in determines which form to load, and which dataset to put in it (so far the prototypes are all working fine, but it's all on my own computer).  The program doesn't have to be of extreme quality as long as it can handle inventory / sales. So far it can do both acceptably well on local testing, but the networking thing is where i get sketchy.

    However, I am extremely curious about that link (partly for work, partly for my own sake) but I can't find anything to do with contacting them in any way, just links to seminars in CA.  Am I missing a link somewhere that I can contact InStep in any way, and if so, there's also no mention of whether or not there is a charge for the service.  Also checked out the blog link in your signature, awesome code samples!

    Addendum:  Never mind, found the contact link.


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!
    Thursday, December 23, 2010 8:54 PM
  •  

    Please do understand the thoroughly understand the existing system in terms of functionality . This process is time taking process.  Then decide how much screens are provide for the software. When you are handling software development , please do try to follow the agile software development methods   like scrum.  Please do create very short term goals for your software project with respect to short timed milestones in terms software deliverable s.

     

    For your software application please do create the N tier software architecture for your project, For example Graphical user interface <===> Business logic <===> Data Access layer <==> Database layer (Sql server 2008 or Sql server 2005).

     

    For your software application you need to have a strong database application. After thoroughly understanding the functionalities ( their are times where the functionalities change time) , please do create the a simple but strong database  for software application.

    When the database is ready which depicts the your current software functionality, then decide to start the designing the screen for the graphical user interface.

     

    You can very  graphical user interface after the database is done.  You can install the Graphical user interface in each workstation . The software application running in each work station can communicate with Business logic by using the window communication foundation (WCF). The simple application flow is given below.

     

    Graphical user interface(GUI). will be communication with WCF which is turn business logic which is in turn communicating with Data Access layer and data access layer is communicating with SqlServer 2008.  You can host the WCF layer in IIS 7.0 where you need to have window server 2008 R2.  This server will also have the main database for your software application.

     

    The simple application follow

     

    GUI ç è WCF ç è BUSINESS LOGIC LAYER ç è DATA ACCESS LAYER ç è DATABASE LAYER(SQL SERVER 2008)

     

    If you have business logic for your software application , you can add the business logic layer other wise you can remove the business logic layer. If the business logic layer is removed , the follow diagram is given below

     

    GUI ç è WCF ç è DATA ACCESS LAYER ç è DATABASE LAYER(SQL SERVER 2008)

     

    For the database layer can use Ado.net , linqtoSQL or EntityFramework 3.5 or 4.0. Please ensure you have a very strong database design before your start the software application design or development. As the mentioned earlier , please do follow agile design methods  ie scrum for the software project. Please do remember the short goals for the software  goals and also give importance to software testing.

     

    For the graphical user interface please do follow in PRISM framework  and also looking into WCF materials from Microsoft. 

     

    Some very important technical tips regarding the WCF

     

    • How to design the wcf service ?
    • The Kind of binding needs to used in wcf service.
    • Hosting the wcf service.
    • Performing the loading testing on the WCF service.
    • Debugging the WCF service.

     

    The need of proof of concept. Please of Proof of Concept (POC) for your software application  and thoroughly test software application.

     

    Basic WCF Programming

    http://msdn.microsoft.com/en-us/library/ms731067.aspx

     

    Windows Communication Foundation Bindings

    http://msdn.microsoft.com/en-us/library/ms733027.aspx

     

    System-Provided Bindings

    http://msdn.microsoft.com/en-us/library/ms733027.aspx

     

    WCF Binding Comparison

    http://www.pluralsight-training.net/community/blogs/aaron/archive/2007/03/22/46560.aspx

     

    How to: Host a WCF Service in IIS

    http://msdn.microsoft.com/en-us/library/ms733766.aspx

     

    Hosting and Consuming WCF Services

    http://msdn.microsoft.com/en-us/library/bb332338.aspx

     

     

    Graphical User Interface - PRISM

    Welcome to the Prism Community

    http://compositewpf.codeplex.com/

     

     

     


    PHIJO MP
    • Marked as answer by psifreak Monday, December 27, 2010 1:27 PM
    Friday, December 24, 2010 8:36 AM
  • Tutorial 1: Creating a Data Access Layer

    http://msdn.microsoft.com/en-us/library/aa581778.aspx

      Chapter 12: Data Access Layer Guidelines

    http://apparchguide.codeplex.com/wikipage?title=Chapter%2012%20-%20Data%20Access%20Layer%20Guidelines&referringTitle=Home


    http://www.labor.state.ny.us/cioshares/pdf/Data%20Access%20Layer%20Design.pdf

    In an N-Tier architecture, the data access layer consists of components that aid one in the process of accessing the database. When it is used correctly, the data access layer serves as an abstract level for the structures of the database. Simple changes that are made to the database and to tables and other components will not effect the rest of the application – the data access layer will not allow this to happen! The various layers of the application send their data requests to this layer, and this layer responds.

     

    Only the data access layer may access the database. As a result, this is where field names and table names are hard coded – they are not hard coded anywhere else in the system. The data access layer is also able to access many services that it can then provide with data – these might include Services and Active Directory.

     

    Importantly, the data access layer serves as an extra layer for database security. This is because the other layers do not know database credentials, connect strings, or other sensitive information – with the data access layer, there is simply no reason for them to have access to this knowledge!

     

    On the data access layer, you can write generic methods, which will then interface, with your data. For instance, you might want to write a method for the creation and subsequent opening of a Connection object (internal), plus one more for the creation and use of a Command object, as well as a stored procedure with or without a return value…

     

    The possibilities are endless. It can also contain specific methods, like “saveProduct”, so that in the event that the Product object calls on it with the appropriate data, it can then persist it along to the Data Tier. Obviously, the data layer should contain no business rules or data manipulation / transformation logic – that is for other layers. The data access layer just serves as a reusable interface to the database – no more, no less.


    PHIJO MP
    • Marked as answer by psifreak Monday, December 27, 2010 1:27 PM
    Friday, December 24, 2010 9:11 AM
  • <!-- [if gte mso 10]> <mce:style>

    Business logic, or domain logic, is a non-technical term generally used to describe the functional algorithms that handle information exchange between a database and a user interface.

     Business logic:

     

    1. dels real life business objects (such as accounts, loans, itineraries, and inventories)
    2. prescribes how business objects interact with one another
    3. enforces the routes and the methods by which business objects are accessed and updated

     

    In practice, the business logic is often interwoven in the other two tiers (the user services tier and the database services tier), such as by encoding business logic in stored procedures and in decisions about input validation and display formatting. However there is not a well defined rule.

     

    A Task Management System using Three Layer Architecture

    http://www.codeproject.com/KB/aspnet/task_mgt_sys_threelayer.aspx

     

    Tutorial 2: Creating a Business Logic Layer

    http://msdn.microsoft.com/en-us/library/aa581779.aspx

     


    PHIJO MP
    • Marked as answer by psifreak Monday, December 27, 2010 1:27 PM
    Friday, December 24, 2010 9:20 AM
  • Explained in simple steps.

     

    Step 1:

     Please do study the existing software functionality and come up with a database design to meet  the software project  requirement.

     Step 2:

     Please do create an assembly which is Data Access Layer other wise know as DAL. In this DAL layer you can use linqtosql or entity frame work or ado.net. The choice is your all your. Please do use more stored procedure for the database operation.

      Step 3:

     If you need the Business logic layer other wise known as BLL , you can create the assembly for the BLL project. You can  call DAL methods here by adding the reference of the DAL assembly in the BLL project. The BLL layer needs to contain the business logic for your software project other wise , please do remove this layer.

      Step 4:

     This layer is wcf  layer , You  need to create the WCF service and host the wcf service in IIS 7.0. Please do use only IIS 7.0 only. Inside the WCF service methods , you call the BLL methods or the DAL methods.  For the software development , you wcf console hosting for development. Later you need to change the wcf service hosting to IIS 7.0. Never use the use Console WCF service for the actual development of your software application.

      Step 5 :

     Here we need to focus to design the Screens based on the database. Please do not create a screen which does not meet database functionality. End result will be double mayhem, a small advice. After the Design and implement the Screen, please do call the proxy methods of wcf service inside the Graphical user interface and debugging the software application from the graphical user interface to the database and vice versa.  For the graphical user interface , please do follow the prism framework. Software standards needs to maintained at any cost..

     Please do create a Proof of Concept from Step 1 to step 5 and debug the software application from Step 5 in the reverse order.

     Deploy the software application in your various workstations and test your application in various work station where you are able get and send data to and from the database.

     Please do agile software developments like scrum for software projects.

      


    PHIJO MP
    • Marked as answer by psifreak Monday, December 27, 2010 1:27 PM
    Saturday, December 25, 2010 5:15 AM
  • Thanks a ton Phijo, looks like the reading material you sent me should keep me pointed in the right direction for this.  Much appreciated!!

    Last question, I know Microsoft IIS7 is recommended, unfortunately it's not an available option at this time.  Would Novell have too many issues with this kind of program?  I can't see why it would be unable to function, but I also wouldn't be surprised if that was the case.


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!
    Monday, December 27, 2010 1:28 PM
  • You can use window service instead of IIS 7.0. Debugging a windows service is very tricky.

     


    PHIJO MP
    Monday, December 27, 2010 2:27 PM
  • imple Windows Service Sample

    http://www.codeproject.com/KB/dotnet/simplewindowsservice.aspx

      How to: Host a WCF Service in a Managed Windows Service

    http://msdn.microsoft.com/en-us/library/ms733069.aspx

      Debugging Windows Services under Visual Studio .NET

    http://www.codeproject.com/KB/dotnet/DebugWinServices.aspx

     How to debug Windows services

    http://support.microsoft.com/kb/824344

     Five ways debugging .NET Windows service

    http://blogs.msdn.com/b/yaleeyangmsblog/archive/2007/05/02/three-ways-debugging-net-windows-service.aspx

     


    PHIJO MP
    Monday, December 27, 2010 2:42 PM