Custom Database Connection Object: Should i use a general one (singleton) or one per object instance?

What is the best practice: To create just one single static class (Singleton) that provide all needed connection to the database or create one object per DAO instance?
Note that my project access more than one database simultaneously, so i created a class AcessoBanco that receives a .INI configuration file e returns me all the connections i need.
I was using a single static class approach but i was receiving sporadic exceptions about concurrency because the system do some multi-threaded tasks. I solved it by creating locks in the AcessoBanco class, but, it is really a good idea?
Maybe, if i put one instance of AcessoBanco per dao object the concurrency problem can be solved more elegantly, am i right? Some examples:

Using the Singleton Approach

public class Repository1

{

    public Repository1(string iniFilePath)

    {

        AcessoBanco.Configure(iniFilePath); // Singleton that creates all the connections (concurrency excepction solved using locks)



        // After configured, just call AcessoBanco.GetConnections() in any point of the code to get the connections 

    }

}

Using one instance per object

public class Repository2

{

    public AcessoBanco Conexoes { get; set; }

    public Repository2(string iniFilePath)

    {

        Conexoes = new AcessoBanco(iniFilePath); // Using one instance of AcessoBanco in each DAO. I will need to do it in every DAO.

    }

}

asked Nov 23 '18 at 19:48

Ewerton

1,67712146

Maybe helpful: stackoverflow.com/a/45029588/5779732

– Amit Joshi
Nov 24 '18 at 12:23

add a comment |

Using the Singleton Approach

public class Repository1

{

    public Repository1(string iniFilePath)

    {

        AcessoBanco.Configure(iniFilePath); // Singleton that creates all the connections (concurrency excepction solved using locks)



        // After configured, just call AcessoBanco.GetConnections() in any point of the code to get the connections 

    }

}

Using one instance per object

public class Repository2

{

    public AcessoBanco Conexoes { get; set; }

    public Repository2(string iniFilePath)

    {

        Conexoes = new AcessoBanco(iniFilePath); // Using one instance of AcessoBanco in each DAO. I will need to do it in every DAO.

    }

}

asked Nov 23 '18 at 19:48

Ewerton

1,67712146

Maybe helpful: stackoverflow.com/a/45029588/5779732

– Amit Joshi
Nov 24 '18 at 12:23

add a comment |

Using the Singleton Approach

public class Repository1

{

    public Repository1(string iniFilePath)

    {

        AcessoBanco.Configure(iniFilePath); // Singleton that creates all the connections (concurrency excepction solved using locks)



        // After configured, just call AcessoBanco.GetConnections() in any point of the code to get the connections 

    }

}

Using one instance per object

public class Repository2

{

    public AcessoBanco Conexoes { get; set; }

    public Repository2(string iniFilePath)

    {

        Conexoes = new AcessoBanco(iniFilePath); // Using one instance of AcessoBanco in each DAO. I will need to do it in every DAO.

    }

}

asked Nov 23 '18 at 19:48

Ewerton

1,67712146

Using the Singleton Approach

public class Repository1

{

    public Repository1(string iniFilePath)

    {

        AcessoBanco.Configure(iniFilePath); // Singleton that creates all the connections (concurrency excepction solved using locks)



        // After configured, just call AcessoBanco.GetConnections() in any point of the code to get the connections 

    }

}

Using one instance per object

public class Repository2

{

    public AcessoBanco Conexoes { get; set; }

    public Repository2(string iniFilePath)

    {

        Conexoes = new AcessoBanco(iniFilePath); // Using one instance of AcessoBanco in each DAO. I will need to do it in every DAO.

    }

}

c# repository-pattern dao data-access-layer data-access

asked Nov 23 '18 at 19:48

Ewerton

1,67712146

asked Nov 23 '18 at 19:48

Ewerton

1,67712146

asked Nov 23 '18 at 19:48

Ewerton

1,67712146

asked Nov 23 '18 at 19:48

Ewerton

1,67712146

asked Nov 23 '18 at 19:48

Ewerton

1,67712146

Maybe helpful: stackoverflow.com/a/45029588/5779732

– Amit Joshi
Nov 24 '18 at 12:23

add a comment |

Maybe helpful: stackoverflow.com/a/45029588/5779732

– Amit Joshi
Nov 24 '18 at 12:23

Maybe helpful: stackoverflow.com/a/45029588/5779732

– Amit Joshi
Nov 24 '18 at 12:23

add a comment |

2 Answers
2

active

oldest

votes

The details you provided do not indicate that using a Singleton pattern is a good idea; on the contrary, as you already discovered, it will likely cause issues with multi-threaded apps. Most database engines support connection pools and the overhead of opening/closing connections should be minimal. Do not open the connection in advance either. Just open / close the connection for the time you strictly need it. You are creating more headaches than you need to.

There's nothing wrong with a pattern like this or something similar:

using (var connection = new Connection()){



}

answered Nov 23 '18 at 20:09

Icarus

54.2k1174106

Yes, i agree, but my application user a variable number of database and i need to know in advance what is the list of available databases. The AcessoBanco class do it for me evaluating a .ini file. My question is: I need to create the list of available databases just one time (eg.: in the application start) or every time i need a connection.

– Ewerton
Nov 23 '18 at 20:44

add a comment |

It highly depends on what kind of a db you use.

Just 2 examples:

CosmosDB - its recommended to use singleton instance of the client since it uses (most often) http calls which likes HttpClient singleton instance

ADO.NET (for sql server) - its extremely bad idea to have singleton instances since your app might want to use connection from different threads and you'll run into a whole bunch of different issues. That's exactly why it uses connection pool under the hood.

answered Nov 23 '18 at 20:00

dee zg

4,67231434

I use SQL Server. so, one connection per instance, right?

– Ewerton
Nov 23 '18 at 20:45

per instance of what?

– dee zg
Nov 23 '18 at 20:56

per instance of the object that needs to access de database (my dao's), each object that needs do access the database will have its own instance of AcessoBanco. We are talking about the same thing?

– Ewerton
Nov 23 '18 at 21:43

no. you want to open connection as late as possible and close it as early as possible. which means either using open() and close() or as @Icarus mentioned, with using block.

– dee zg
Nov 23 '18 at 23:04

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53452253%2fcustom-database-connection-object-should-i-use-a-general-one-singleton-or-one%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

There's nothing wrong with a pattern like this or something similar:

using (var connection = new Connection()){



}

answered Nov 23 '18 at 20:09

Icarus

54.2k1174106

Yes, i agree, but my application user a variable number of database and i need to know in advance what is the list of available databases. The AcessoBanco class do it for me evaluating a .ini file. My question is: I need to create the list of available databases just one time (eg.: in the application start) or every time i need a connection.

– Ewerton
Nov 23 '18 at 20:44

add a comment |

There's nothing wrong with a pattern like this or something similar:

using (var connection = new Connection()){



}

answered Nov 23 '18 at 20:09

Icarus

54.2k1174106

Yes, i agree, but my application user a variable number of database and i need to know in advance what is the list of available databases. The AcessoBanco class do it for me evaluating a .ini file. My question is: I need to create the list of available databases just one time (eg.: in the application start) or every time i need a connection.

– Ewerton
Nov 23 '18 at 20:44

add a comment |

There's nothing wrong with a pattern like this or something similar:

using (var connection = new Connection()){



}

answered Nov 23 '18 at 20:09

Icarus

54.2k1174106

There's nothing wrong with a pattern like this or something similar:

using (var connection = new Connection()){



}

answered Nov 23 '18 at 20:09

Icarus

54.2k1174106

answered Nov 23 '18 at 20:09

Icarus

54.2k1174106

answered Nov 23 '18 at 20:09

Icarus

54.2k1174106

answered Nov 23 '18 at 20:09

Icarus

54.2k1174106

Yes, i agree, but my application user a variable number of database and i need to know in advance what is the list of available databases. The AcessoBanco class do it for me evaluating a .ini file. My question is: I need to create the list of available databases just one time (eg.: in the application start) or every time i need a connection.

– Ewerton
Nov 23 '18 at 20:44

add a comment |

Yes, i agree, but my application user a variable number of database and i need to know in advance what is the list of available databases. The AcessoBanco class do it for me evaluating a .ini file. My question is: I need to create the list of available databases just one time (eg.: in the application start) or every time i need a connection.

– Ewerton
Nov 23 '18 at 20:44

Yes, i agree, but my application user a variable number of database and i need to know in advance what is the list of available databases. The AcessoBanco class do it for me evaluating a .ini file. My question is: I need to create the list of available databases just one time (eg.: in the application start) or every time i need a connection.

– Ewerton
Nov 23 '18 at 20:44

add a comment |

It highly depends on what kind of a db you use.

Just 2 examples:

CosmosDB - its recommended to use singleton instance of the client since it uses (most often) http calls which likes HttpClient singleton instance

ADO.NET (for sql server) - its extremely bad idea to have singleton instances since your app might want to use connection from different threads and you'll run into a whole bunch of different issues. That's exactly why it uses connection pool under the hood.

answered Nov 23 '18 at 20:00

dee zg

4,67231434

I use SQL Server. so, one connection per instance, right?

– Ewerton
Nov 23 '18 at 20:45

per instance of what?

– dee zg
Nov 23 '18 at 20:56

per instance of the object that needs to access de database (my dao's), each object that needs do access the database will have its own instance of AcessoBanco. We are talking about the same thing?

– Ewerton
Nov 23 '18 at 21:43

no. you want to open connection as late as possible and close it as early as possible. which means either using open() and close() or as @Icarus mentioned, with using block.

– dee zg
Nov 23 '18 at 23:04

add a comment |

It highly depends on what kind of a db you use.

Just 2 examples:

CosmosDB - its recommended to use singleton instance of the client since it uses (most often) http calls which likes HttpClient singleton instance

ADO.NET (for sql server) - its extremely bad idea to have singleton instances since your app might want to use connection from different threads and you'll run into a whole bunch of different issues. That's exactly why it uses connection pool under the hood.

answered Nov 23 '18 at 20:00

dee zg

4,67231434

I use SQL Server. so, one connection per instance, right?

– Ewerton
Nov 23 '18 at 20:45

per instance of what?

– dee zg
Nov 23 '18 at 20:56

per instance of the object that needs to access de database (my dao's), each object that needs do access the database will have its own instance of AcessoBanco. We are talking about the same thing?

– Ewerton
Nov 23 '18 at 21:43

no. you want to open connection as late as possible and close it as early as possible. which means either using open() and close() or as @Icarus mentioned, with using block.

– dee zg
Nov 23 '18 at 23:04

add a comment |

It highly depends on what kind of a db you use.

Just 2 examples:

CosmosDB - its recommended to use singleton instance of the client since it uses (most often) http calls which likes HttpClient singleton instance

ADO.NET (for sql server) - its extremely bad idea to have singleton instances since your app might want to use connection from different threads and you'll run into a whole bunch of different issues. That's exactly why it uses connection pool under the hood.

answered Nov 23 '18 at 20:00

dee zg

4,67231434

It highly depends on what kind of a db you use.

Just 2 examples:

CosmosDB - its recommended to use singleton instance of the client since it uses (most often) http calls which likes HttpClient singleton instance

ADO.NET (for sql server) - its extremely bad idea to have singleton instances since your app might want to use connection from different threads and you'll run into a whole bunch of different issues. That's exactly why it uses connection pool under the hood.

answered Nov 23 '18 at 20:00

dee zg

4,67231434

answered Nov 23 '18 at 20:00

dee zg

4,67231434

answered Nov 23 '18 at 20:00

dee zg

4,67231434

answered Nov 23 '18 at 20:00

dee zg

4,67231434

I use SQL Server. so, one connection per instance, right?

– Ewerton
Nov 23 '18 at 20:45

per instance of what?

– dee zg
Nov 23 '18 at 20:56

per instance of the object that needs to access de database (my dao's), each object that needs do access the database will have its own instance of AcessoBanco. We are talking about the same thing?

– Ewerton
Nov 23 '18 at 21:43

no. you want to open connection as late as possible and close it as early as possible. which means either using open() and close() or as @Icarus mentioned, with using block.

– dee zg
Nov 23 '18 at 23:04

add a comment |

I use SQL Server. so, one connection per instance, right?

– Ewerton
Nov 23 '18 at 20:45

per instance of what?

– dee zg
Nov 23 '18 at 20:56

per instance of the object that needs to access de database (my dao's), each object that needs do access the database will have its own instance of AcessoBanco. We are talking about the same thing?

– Ewerton
Nov 23 '18 at 21:43

no. you want to open connection as late as possible and close it as early as possible. which means either using open() and close() or as @Icarus mentioned, with using block.

– dee zg
Nov 23 '18 at 23:04

I use SQL Server. so, one connection per instance, right?

– Ewerton
Nov 23 '18 at 20:45

per instance of what?

– dee zg
Nov 23 '18 at 20:56

per instance of the object that needs to access de database (my dao's), each object that needs do access the database will have its own instance of AcessoBanco. We are talking about the same thing?

– Ewerton
Nov 23 '18 at 21:43

no. you want to open connection as late as possible and close it as early as possible. which means either using open() and close() or as @Icarus mentioned, with using block.

– dee zg
Nov 23 '18 at 23:04

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nsryjdtyk