Post reply

How to build an app with GUI that works with SQL Server?

  • September 30, 2018 at 7:24 am

    #379676

    I have to create a project which should have a GUI because people who gonna use it don't know TSQL. It's purpose it to let people choose options with checkboxes than run a index maintenance script on the server with specified options. I assume that SQL Server is already started on the machine. Basically I want an app which will be built in a high level language, have it's own GUI, I should give it access to SQL Server instance in order for it to do operations on the server. Options are like whether how to maintain indexes, rebuilt or reorganize, online or offline...
    I don't know how to implement this. I know TSQL, but I don't know which programming language would be the best for this. And should I use Windows app interface or can I do this project using web interface?
    I am a begginner so maybe I have some misunderstanding. If needed I can provide more information.
    I need an advice from more experienced people how to build, which language will the the best, and what interface to use(web or not).
    Thanks for your time!!

  • October 1, 2018 at 8:09 am

    #2007507

    Index maintenance is typically a DBA role, and should NEVER be relegated to users.   Just doesn't sound like a good idea to me at all.   Determining which method of dealing with the index is not a decision a user or an app is likely to ever be qualified to do.   That's the reason you pay a DBA to do it.   The DBA has the knowledge and experience to perform that task.   The user most likely does not and could easily do far more harm than good.   Really bad idea to even consider making an app and letting users run such things.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 1, 2018 at 8:32 am

    #2007513

    you can also setup a maintenance plan to rebuild/reorg indexes automatically at night.  I agree with Steve that this should not be an app that allows users to do this whenever they want.  Also, allowing users to do this will wreak havoc on existing apps that use the db. Since users can do it whenever they want, it will lock/block user access to the tables.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • October 1, 2018 at 8:52 am

    #2007521

    elea.grig - Sunday, September 30, 2018 7:24 AM

    I have to create a project which should have a GUI because people who gonna use it don't know TSQL. It's purpose it to let people choose options with checkboxes than run a index maintenance script on the server with specified options. I assume that SQL Server is already started on the machine. Basically I want an app which will be built in a high level language, have it's own GUI, I should give it access to SQL Server instance in order for it to do operations on the server. Options are like whether how to maintain indexes, rebuilt or reorganize, online or offline...
    I don't know how to implement this. I know TSQL, but I don't know which programming language would be the best for this. And should I use Windows app interface or can I do this project using web interface?
    I am a begginner so maybe I have some misunderstanding. If needed I can provide more information.
    I need an advice from more experienced people how to build, which language will the the best, and what interface to use(web or not).
    Thanks for your time!!

    I'd use one of the Microsoft .NET languages, either C# or VB.NET. These have very simple access to all the SQL libraries and drivers.

  • October 1, 2018 at 12:41 pm

    #2007573

    sgmunson - Monday, October 1, 2018 8:09 AM

    Index maintenance is typically a DBA role, and should NEVER be relegated to users.   Just doesn't sound like a good idea to me at all.   Determining which method of dealing with the index is not a decision a user or an app is likely to ever be qualified to do.   That's the reason you pay a DBA to do it.   The DBA has the knowledge and experience to perform that task.   The user most likely does not and could easily do far more harm than good.   Really bad idea to even consider making an app and letting users run such things.

    Thanks for the answer! Actually this is a university project for studying, and basically this program will be used by other students. And maintenance plan does not let me choose all the options I will provide on my script

  • October 1, 2018 at 1:05 pm

    #2007582

    elea.grig - Monday, October 1, 2018 12:41 PM

    sgmunson - Monday, October 1, 2018 8:09 AM

    Index maintenance is typically a DBA role, and should NEVER be relegated to users.   Just doesn't sound like a good idea to me at all.   Determining which method of dealing with the index is not a decision a user or an app is likely to ever be qualified to do.   That's the reason you pay a DBA to do it.   The DBA has the knowledge and experience to perform that task.   The user most likely does not and could easily do far more harm than good.   Really bad idea to even consider making an app and letting users run such things.

    Thanks for the answer! Actually this is a university project for studying, and basically this program will be used by other students. And maintenance plan does not let me choose all the options I will provide on my script

    I fail to see how that matters.   If your teachers are going to show you a bad idea, then they are not doing you any favors.  Besides, whatever database they have it pointed to will quickly get messed up by all that index maintenance, and quickly lose any real value precisely because it will be in a degraded state.   Somebody on the faculty isn't too bright a bulb....

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 2, 2018 at 3:14 pm

    #2007800

  • October 2, 2018 at 7:32 pm

    #2007817

    elea.grig - Monday, October 1, 2018 12:41 PM

    sgmunson - Monday, October 1, 2018 8:09 AM

    Index maintenance is typically a DBA role, and should NEVER be relegated to users.   Just doesn't sound like a good idea to me at all.   Determining which method of dealing with the index is not a decision a user or an app is likely to ever be qualified to do.   That's the reason you pay a DBA to do it.   The DBA has the knowledge and experience to perform that task.   The user most likely does not and could easily do far more harm than good.   Really bad idea to even consider making an app and letting users run such things.

    Thanks for the answer! Actually this is a university project for studying, and basically this program will be used by other students. And maintenance plan does not let me choose all the options I will provide on my script

    Until someone actually knows if the index is  evenly distributed, static, append only, or silo distributed (two types there), has solved the "ExpAnsive Update" problem that nearly all indexes suffer, and have determined the correct FILL Factor for the index AND you've made the realization the REORGANIZE isn't just useless but actually perpetuates really bad page splits, you shouldn't do any index maintenance at all because it'll do more harm than good.

    I'll also tell you that many (maybe even most) DBAs haven't figured all of that out never mind users that don't know enough about T-SQL to do an index rebuild.

    Besides, a user that's allowed (and I'd never allow it) can use the built in GUI in the Explorer window to rebuild an index.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 3, 2018 at 2:27 am

    #2007827

    Putting aside for now the (important) issue of whether end-users should carry out index maintenance, this question is a good one. I understand it is asking: what's the easiest way of building a GUI to run some TSQL code? In the "old days" the classic model was to build an app in C#, VB or some other language to serve as the front end, and to make the app run SQL or SPs in the database. What's the modern equivalent of doing this?

    For small scale apps where the clients and the database are on the same network, I have seen an interesting approach using HTA and VB script. The HTA front end reads the metadata and dynamically builds dialog boxes for executing SPs. The parameters of the SP would control what entry fields were displayed on the form, and there would be more entry fields for output parameters and a results grid for any results set. This idea could be extended to having a dynamically created app framework, there could be menus showing all the SPs that could be called and even a reporting menu for running views and table-valued functions and their parameters. It could handle permissions for different groups of users by creating database roles and putting the exec permissions for the SPs and the select permissions for the views / TVFs into the different roles.

    Anyway this is just an idea, I don't have access to the HTA / VBA code any more.

  • October 3, 2018 at 6:25 am

    #2007853

    Jeff Moden - Tuesday, October 2, 2018 7:32 PM

    elea.grig - Monday, October 1, 2018 12:41 PM

    sgmunson - Monday, October 1, 2018 8:09 AM

    Index maintenance is typically a DBA role, and should NEVER be relegated to users.   Just doesn't sound like a good idea to me at all.   Determining which method of dealing with the index is not a decision a user or an app is likely to ever be qualified to do.   That's the reason you pay a DBA to do it.   The DBA has the knowledge and experience to perform that task.   The user most likely does not and could easily do far more harm than good.   Really bad idea to even consider making an app and letting users run such things.

    Thanks for the answer! Actually this is a university project for studying, and basically this program will be used by other students. And maintenance plan does not let me choose all the options I will provide on my script

    Until someone actually knows if the index is  evenly distributed, static, append only, or silo distributed (two types there), has solved the "ExpAnsive Update" problem that nearly all indexes suffer, and have determined the correct FILL Factor for the index AND you've made the realization the REORGANIZE isn't just useless but actually perpetuates really bad page splits, you shouldn't do any index maintenance at all because it'll do more harm than good.

    I'll also tell you that many (maybe even most) DBAs haven't figured all of that out never mind users that don't know enough about T-SQL to do an index rebuild.

    Besides, a user that's allowed (and I'd never allow it) can use the built in GUI in the Explorer window to rebuild an index.

    And if the professor or teacher in this particular case wants their training DB messed up thoroughly in fairly short order, sure go ahead and let some students mess with it, and before long, the remaining students who didn't get in line fast enough won't have anything useful to learn...  because the db is so messed up, there's not enough left to do any useful learning with.   And let's not forget the waste of time in teaching your students to do something no one would actually do in the real world.

    Seems to me they should be building an application to actually accomplish something rather than just do something that for the most part, if you set things up right in the first place, doesn't usually need to be done..

    Jeff, ... I'm with you 100%.  + a googolplex to the googolplex power, cubed !!!

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 3, 2018 at 6:29 am

    #2007855

    timwell - Tuesday, October 2, 2018 3:14 PM

    sgmunson - Monday, October 1, 2018 1:05 PM

    elea.grig - Monday, October 1, 2018 12:41 PM

    sgmunson - Monday, October 1, 2018 8:09 AM

    Index maintenance is typically a DBA role, and should NEVER be relegated to users.   Just doesn't sound like a good idea to me at all.   Determining which method of dealing with the index is not a decision a user or an app is likely to ever be qualified to do.   That's the reason you pay a DBA to do it.   The DBA has the knowledge and experience to perform that task.   The user most likely does not and could easily do far more harm than good.   Really bad idea to even consider making an app and letting users run such things.

    Thanks for the answer! Actually this is a university project for studying, and basically this program will be used by other students. And maintenance plan does not let me choose all the options I will provide on my script

    I fail to see how that matters.   If your teachers are going to show you a bad idea, then they are not doing you any favors.  Besides, whatever database they have it pointed to will quickly get messed up by all that index maintenance, and quickly lose any real value precisely because it will be in a degraded state.   Somebody on the faculty isn't too bright a bulb....

    Considering that this is a university project and a learning exercise it seems like you are making assumptions about what it is for and who will actually be using it (and saying things about their faculty that could be considered  insulting).
    It doesn't sound like this is a production database that will be brought down by this app.
    Maybe part of the learning exercise it to see what happens when you mess with indexes..... 

    If this were my assignment I would do this as a Windows application in .Net.
    (I did some introductory articles that might help with getting started on connecting etc.)

    But my question at that point is:  What on earth are they learning?  How to do something incredibly foolish?  If you call that "learning", remind me to stay far away.   No wonder we can't get decent IT people out of college these days.   Their learning keeps getting short-circuited by some loony prof that doesn't seem to know how to teach...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 3, 2018 at 8:35 am

    #2007875

    As a C# developer I would say that I could write a routine like this in short order.  I will also add that it would be a huge mistake on my part to give such functionality to a general group of users.  DBA's are hired and retained for their expertise, and part of that knowledge is knowing how and when to rebuild indexes.  SQL is not a toy and should be treated with care.

  • October 4, 2018 at 5:48 pm

    #2008235

    Keith Oliver - Wednesday, October 3, 2018 8:35 AM

    As a C# developer I would say that I could write a routine like this in short order.  I will also add that it would be a huge mistake on my part to give such functionality to a general group of users.  DBA's are hired and retained for their expertise, and part of that knowledge is knowing how and when to rebuild indexes.  SQL is not a toy and should be treated with care.

    Someone once described SSMS as handing a shotgun to a baby. Could not agree more with that sentiment.
    pcd

  • October 16, 2018 at 12:04 pm

    #2009934

    sgmunson - Monday, October 1, 2018 1:05 PM

    elea.grig - Monday, October 1, 2018 12:41 PM

    sgmunson - Monday, October 1, 2018 8:09 AM

    Index maintenance is typically a DBA role, and should NEVER be relegated to users.   Just doesn't sound like a good idea to me at all.   Determining which method of dealing with the index is not a decision a user or an app is likely to ever be qualified to do.   That's the reason you pay a DBA to do it.   The DBA has the knowledge and experience to perform that task.   The user most likely does not and could easily do far more harm than good.   Really bad idea to even consider making an app and letting users run such things.

    Thanks for the answer! Actually this is a university project for studying, and basically this program will be used by other students. And maintenance plan does not let me choose all the options I will provide on my script

    I fail to see how that matters.   If your teachers are going to show you a bad idea, then they are not doing you any favors.  Besides, whatever database they have it pointed to will quickly get messed up by all that index maintenance, and quickly lose any real value precisely because it will be in a degraded state.   Somebody on the faculty isn't too bright a bulb....

    I've explained everything that this project is basically useless but in developing countries universities are like this.

  • October 18, 2018 at 6:16 am

    #2010211

    elea.grig - Tuesday, October 16, 2018 12:04 PM

    sgmunson - Monday, October 1, 2018 1:05 PM

    elea.grig - Monday, October 1, 2018 12:41 PM

    sgmunson - Monday, October 1, 2018 8:09 AM

    Index maintenance is typically a DBA role, and should NEVER be relegated to users.   Just doesn't sound like a good idea to me at all.   Determining which method of dealing with the index is not a decision a user or an app is likely to ever be qualified to do.   That's the reason you pay a DBA to do it.   The DBA has the knowledge and experience to perform that task.   The user most likely does not and could easily do far more harm than good.   Really bad idea to even consider making an app and letting users run such things.

    Thanks for the answer! Actually this is a university project for studying, and basically this program will be used by other students. And maintenance plan does not let me choose all the options I will provide on my script

    I fail to see how that matters.   If your teachers are going to show you a bad idea, then they are not doing you any favors.  Besides, whatever database they have it pointed to will quickly get messed up by all that index maintenance, and quickly lose any real value precisely because it will be in a degraded state.   Somebody on the faculty isn't too bright a bulb....

    I've explained everything that this project is basically useless but in developing countries universities are like this.

    So be it.   And "in developing countries universities", if that is how their educators teach SQL, watch out world, will there ever be a glut of even less skilled database folks than there already are....   That might just be why those countries are referred to as "developing"...  Let's hope that such foolishness is not reflective of the overall state of IT education, because if it is, ...  again,  WATCH OUT WORLD...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 15 posts - 1 through 15 (of 56 total)

You must be logged in to reply to this topic. Login to reply