CODEWITHSUNDEEP
c#sqlsql-serverdatabase
Innovit
Innovit

Reputation: 103

is public static sql connection secure

I am working on a windows service in c#, and perhaps I might use the same methods in my web application if it works. The main service is using a sqlconnection that is used for itself, retrieved from app.config using ConfigurationManager, and it creates a few threads of different types, to run several tasks. Each of these threads has its own connection (stored in class property).

public partial class MainService : ServiceBase{
    private static string SQLConnStr;
    protected static SqlConnection SQLConn;
}

class OtherClass{
    private sqlconnection sqlconn;
    private string connstr;

}

The part that I am stuck with, is that, when I create another object type, I need that object to be able to access the DB using OtherClass' connection. This new object is created and called in OtherClass for processing later on.

Is it possible to re-use the sql connection from OtherClass? Is it safe and secure to make the sql connection string as public static? Is the design I am using not good?

Upvotes: 0

Views: 771

Answers (4)

Innovit
Innovit

Reputation: 103

I like your answers, but mine is the case of bad design, after looking into it a while. So I decided to create a public static query function in the MainService:

public static void qQuery(string querystr){
    using(sqlconnection conn = new sqlconnection(sqlconnstr){
        .....
    }
}

sqlconnstr is a private string in MainService which is initialized when the service starts.

I hope it is safe and secure way of doing it...

Upvotes: -1

cnd
cnd

Reputation: 33714

pass it to OtherClass constructor ?

details with example:

public partial class MainService : ServiceBase{
    private string SQLConnStr;
}

class OtherClass{
    private string connstr;
    public OtherClass(string _connstr) { this.connstr = _connstr; }
    public foo() { using(var conn = new SqlConnection(connstr) { ... } }
}

Creating new SqlConnection each time is fine in .NET since there is connection pool.

Upvotes: 2

Hans Kesting
Hans Kesting

Reputation: 39255

There is a distinction between connection*string* and the connection itself. For the connectionstring (describing where you should connect to), you can store that in a config file so youu can easily change it.

The connection itself that is used to communicate with the database must be opened and closed. When you use datareaders, you can't usually use the same connection for simultaneous commands. It is best to open a new connection as late as possible and close it as soon as possible. Let the built-in connectionpooling do it's thing to optimize the real connections.

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You should use the respective configuration mechanism. For applications/windows services this would bee the app.config file, for web applications it would be the web.config file and for DLLs, the settings should be copied from the DLL's app.config to the application's app.config/web.config as described here many times (DLLs don't read their config files, so the settings need to go into the application's config file).

That being said: Use the settings designer in Visual Studio to simply add a new connection string setting. If necessary, add it to other projects, too.

To use the connection string from class within the same project, you can then simply use Settings.Default.MyConnectionString assuming that the setting is named MyConnectionString.

Another exception for DLLs: Some DLLs don't need their own settings, but they need to access a database. In that case you could pass the connection string or even an SqlConnection to the function that requires database access.

Upvotes: 1

Related Questions