Web or application front end for SQL database

iamuser
iamuser used Ask the Experts™
on
I have created a new SQL database with all the required data and tables. The DB houses PDF's and other data. Using SSMS i can run query's and pull the data I need but it's not good for the staff.

What I want to do is to created maybe a simple web page (think google search) or front end where they can type in a key word, phrase, or combination of both and get a result list with a download option for the pdf/documents if any.

How would I start on creating such a front end? I have never made any front end's for SQL DB before
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
If this is primarily for documents, you are probably better off exporting the documents to something like Office365/One Drive/Sharepoint and letting your users access the built in search there. Another option of course is Google GSuite using TeamDrive.

Trying to start something like this fresh is harder than it seems. At the core is a simple web form and a results page.  It's how you get the results, security, learning html, css, javascript (front end) and then learning .net. It is not something you can pull off in a month.  

There are also third party tools that connect to your database such as https://www.tableau.com/ or https://www.periscopedata.com/. Those are mainly for allowing non programmers to mine your data. I don't know how they would be for searching files.
ste5anSenior Developer

Commented:
Depending on the kind of users: In integrated AD, using Access is a simple solution.

Author

Commented:
we looked at a few software (not the one mentioned here), Management would prefer that we develop something in house as that would be more tailored to what they want and no $$$. I'll go back and show them the other software mentioned here.

While I understand that it requires more effort, "It's how you get the results, security, learning html, css, javascript (front end) and then learning .net."

can you elaborate more on the steps needs to be down, this way I can demonstrate to management the effort needed to accomplish something in house the time it would take.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
Not including the web server and all that goes with it, there are three parts. One is the database and you have that already. The other two parts are the front end (html, css, javascript) and the back end (.NET, Node, Java, PHP etc).  Actually, the other part is the secret sauce, your search engine.  If all you are doing is searching for a specific number using a number field in a database, it is not as difficult as say searching for a text string, part of a word, sentence or getting results of what you meant and not way you said..... Google has spent how much to figure all of that out?  

The searching, the part from when  the form data is submitted to the time when results are returned could be the hardest part. Look what EE had to come up with for advance search

Also take into consideration your own data. It may make sense to you and your team, but to the non-tech users that just want to type something in a search box and get results, it becomes more complex.  The way I have tackled this is to determine what types of reports/queries clients want and make separate reports for each where they have a little input such as a date range. But whenever somebody asks, "I just need to search anything at any time" and offered a similar 'advanced search' such as what EE has done, it fails.  Too complex and the results are not always as expected.

Getting back to the front end and back end. If you do some self learning, you can make a form in short order.

<form method="post" action="/somepage">
      <label>Search Term</label>
      <button type="submit">Submit</button>
</form>

Open in new window

 
Then results
<table>
     <tr>
         <td>data</td>
          <td>data</td>
          <td>data</td>
     </tr>
     <tr>
         <td>data</td>
          <td>data</td>
          <td>data</td>
     </tr>
     <tr>
         <td>data</td>
          <td>data</td>
          <td>data</td>
     </tr>

</table>

Open in new window


Somewhere between the input and output you are going to connect to your database, submit an sql statement, get data that will be returned to the results page.

As you learn, you start with simple html.  Now you may want to make it look good and some of the simplest looking designs are actually complex on the inside. You will start learning css and then javascript.  Inside of a week of immersive study you could start getting comfortable.  You will realize though that you may have the gift for tech, the right half of your brain is lacking.  You can compensate by using an admin panel template https://coreui.io/, https://adminlte.io/, https://themeforest.net/category/site-templates/admin-templates, https://www.telerik.com/kendo-ui

Those products can make you look good. There will be a learning curve especially when you are starting out.  

The other part of front end as you get more complex will be learning javascript and the different options be it jquery, angular, react or vue.  Another week or two of immersive self study and you can start piecing things together.  

After about three weeks you could start making some static pages with fake static data to represent what you want to do. This is an important step. If you can make these pages look and act the way you want, it will be easier to learn the back end and know exactly what you need to generate to make that happen.  And if you stick to a web api format, you can just concentrate on using the back end only for accepting form data to send to the server and return json data that can be feed to the pages you just created.

.net tutorials https://dotnet.microsoft.com/learn/web.  Spend a couple of weeks to get comfortable here and start integrating with what you created in the previous section with just html/css/js.  You are a long way from feeling comfortable with knowing "everything" but can ask questions and figure out what you need for the step you are currently on.

From here you will want to learn about logging users in and adding security to your pages. Adding different levels of security and applying that to your pages. You will also want to create an admin area to make it easy for you to manage users, levels, passwords and other things that come up.  You will also want to research on keeping files safe and away from the www except when needed.

Now let's go back to the search engine. if you are going to only allow people to search something very specific like a file name and the data is very clean, it can be done. From experience, data is not clean and it this part is  going to be a pain point if people can't get what they are looking for. You could spend a month putting all of this together only to find that your users are expecting google -- searching with bad spelling or not really knowing what you are supposed to use for search terms but the results happen to just work.

I would say within a month you could start putting something together using one of the front end templates I pointed out.  Another couple of weeks working with some select co workers to test things out, work out major bugs that are show stoppers and tracking the small ones for later. Let's say in month three you go live. Now your job just gets going.  You have to train users, write documentation (start a help blog or use a third party cloud help desk), capture bugs and requests.  Within the year you will have things going with less effort....until something breaks because of some type of update or technology changes. You will also want to start on a version 2 that will be sleeker, more user friendly, keep up with the latest tech and more importantly you will learn a lot within that time to understand all the things you did that could be a lot more efficient. You need to keep up with this or users will start finding work arounds they don't tell you about until it becomes out of control.

I do think the hard part is going to be the search and returning results people expect.  For that reason, I would check out the third party connectors like google https://cloud.google.com/products/search/ or using OneDrive.   Worlddox https://www.worldox.com/ is another option too.

There are going to be good reasons to keep things in house though. If your management is in support of this, and they know understand the difference between spending money up front and having a proven solution vs spending a year to come up with something that is tailored to your specific needs. And they are willing to support you to and perhaps somebody else to continually support this, then it is worth thinking about.  If you make $100,000 per year than that is how much your program is going to cost plus some extras. Compare that to spending a fraction of that on a product plus support and only a fraction of your salary for being the liaison from your company to the vendor, it may just come out cheaper in the long run.  On the other hand, if management does push this through for you to go forward, you will have some good paid training that will add to your skills.

Author

Commented:
Thanks for all the info, it is a lot. I will present this to management and see what they say. So essentially what they want is a staff knowledge base. Example of what they want to do below

  • page similar google search. Blank white page with search box
  • Type in some keyboards, "Excel merging" or "document margin"
  • Result list of items that contain the search words.  
  • user clicks on any of the returned result, (triggers store procedure in SQL to retrieve the file)
  • file downloads  to the person's computer

Sounds simple but i know it's not.
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
Result list of items that contain the search words.  

Like I said that will be the hard part and where a third party service like Google Search Cloud comes in. Otherwise, you just have this

<form method="post" action="/somepage">
      <label>Search Term</label>
      <button type="submit">Submit</button>
</form>

Open in new window

<table>
     <tr>
         <td><a href="linktofile">file</a></td>
          <td>description</a></td>
          <td>other data</td>
     </tr>
     <tr>
         <td><a href="linktofile">file</a></td>
          <td>description</a></td>
          <td>other data</td>
     </tr>
     <tr>
         <td><a href="linktofile">file</a></td>
          <td>description</a></td>
          <td>other data</td>
     </tr>

</table>

Open in new window


If you can practice making some searches using SSMS and it is working for you, then it is just a matter of using server side code to present the parameters to a SP or view and you are good to go.

Author

Commented:
If you can practice making some searches using SSMS and it is working for you, then it is just a matter of using server side code to present the parameters to a SP or view and you are good to go.

I was thinking about doing this but I wasn't sure how to tie in a view with something external
Developer & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
On that aspect, I would open a new question after reading up on it.  Look through these examples:

https://docs.microsoft.com/en-us/aspnet/web-pages/overview/data/5-working-with-data

https://www.w3schools.com/asp/webpages_database.asp

https://www.c-sharpcorner.com/article/how-to-connect-sql-database-in-asp-net-using-c-sharp-and-insert-and-view-the-data-usi/ 

See how far you can get on your own.   The steps I suggest is to get one of these working exactly as shown. The reason is you know they are proven and if you are coming up with errors, you know it is not the code (except maybe some typos).   Work through it to completion.

When you are done with that, create a table with some fake data that you can share for the purposes of learning that may somewhat resemble your own with just a few fields based on the view you create.  Then reconfigure your form and results page to match your data and see how it goes. If you run into issues, start a new question on EE and include your table/view structure, some sample data, your code and exact errors or issues you are running into.

Author

Commented:
thanks all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today