Improve efficiency of database connection



  • Hi I have a class called "SqlConnect" that allows me to connect to an MS SQL Server. The class works well, however this class is called from dozens of other classes, and hence I need to instanciate a new object of the SqlConnect class in each of those classes.

    Here is how the SqlConnect class looks like:
    //Constructor:
    @
    SqlConnect::SqlConnect(QString dbName, QString alias){
    database = QSqlDatabase::addDatabase("QODBC", alias);
    database.setDatabaseName("Driver={SQL Server};Server=myServer;Database=" + dbName + ";");
    database.setUserName("myUsername");
    database.setPassword("myPassword");

    database.open();
    

    }
    @

    //Main function (to request the DB):
    @
    QSqlQuery SqlConnect::GetResultsFromQuery(QString command){
    query = QSqlQuery(database);
    query.setForwardOnly(true);
    query.prepare(command);
    query.exec();
    return query;
    }
    @

    Each class using the SqlConnect object is as follow:
    .h:
    @SqlConnect *sql;@
    .cpp:
    @sql = new SqlConnect("databaseName", "callingClassName");@

    Since I'm connecting to only 2 different database in the whole program, I believe I could do much more efficient by having only two instances that can be used at a global scope by all the classes. I have never used global variables since it's known to be a bad practice. Could you please help me and tell me how can I use only one instance (across all my classes) of SqlConnect for each database I want to connect to.

    Thanks much


  • Lifetime Qt Champion

    Hi,

    At startup create your two connections with different names and when you want to execute a query on a given database just use:

    QSqlDatabase db = QSqlDatabase::database("my connection name");
    QSqlQuery query(db);
    ...//rest of your code
    


  • Hi SGaist

    Thanks for your help (again).
    What do you mean at startup? In which class?


  • Lifetime Qt Champion

    Your application startup, it can be done in the main function or in a class that you see fit for that.



  • Let's say I put it in the main function (which is alone in the main class in my case), then 'db' will exists only in the main function ? I have others useful function in SqlConnect like bulk insert, etc. so I really would like to keep using it..


  • Lifetime Qt Champion

    The db variable yes but the related connection itself no. You can retrieve it where you want using the method I mentioned above.



  • Oh! My mistake I read QSqlDatabase::addDatabase instead of QSqlDatabase::database. Ok I'm trying it now.
    Thanks.



  • Works like a charm. Thanks much for your help.


  • Lifetime Qt Champion

    You're welcome !

    No need to modify the title anymore, just use the "Topic Tool" button to mark the thread as solved :)



  • Done :)


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.