Clash Royale CLAN TAG#URR8PPP

Handling Database Connection in C#

I am Creating WinForm application using C# and SqlServer. I have to handle many database CRUD Queries on it. And also there are so many forms and so many controllers.

Now I want to know is, If i create common class for handle database connectivity with many methods for open connection, close connection, execute Sql command or do any other data retrievals. This method is good or bad?

or below method for run every query is good or bad?

using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=MYDB"))

connection.Open();
// Pool A is created.



which method is better for performance and security?

DbContext

Database

3 Answers
3

Here are some points to think about when using a connection.

1) Dispose the connection object as soon as you no longer need it by using the using statement:

using (var conn = new SqlConnection(connectionstring))

// your sql magic goes here



2) If you're not disposing the object immediately, you can make sure the connection is closed using a try-finally statement:

var conn = new SqlConnection(connectionstring);
try

// do sql shizzle

finally

conn.Close();



3) To prevent SQL injection, use parameterized queries, never concatenated strings

using (var conn = new SqlConnection(connectionstring))

conn.Open();
using(var comm = new SqlCommand("select * from FooBar where foo = @foo", conn))

comm.Parameters.Add(new SqlParameter("@foo", "bar"));
using(var reader = comm.ExecuteReader())

// Do stuff with the reader;





4) If you're performing multiple update, insert or delete statements, and they all need to be succesful at once, use a transaction:

using (var conn = new SqlConnection(connectionstring))

conn.Open();
using(var trans = conn.BeginTransaction())

try

using(var comm = new SqlCommand("delete from FooBar where fooId = @foo", conn, trans))

comm.Parameters.Add(new SqlParameter ParameterName = "@foo", DbType = System.Data.DbType.Int32 );
for(int i = 0; i < 10 ; i++)

comm.Parameters["@foo"].Value = i;
comm.ExecuteNonQuery();


trans.Commit();

catch (Exception exe)

trans.Rollback();
// do some logging





5) Stored procedures are used similarly:

using (var conn = new SqlConnection(connectionstring))

conn.Open();
using (var comm = new SqlCommand("FooBarProcedure", conn) CommandType = CommandType.StoredProcedure )

comm.Parameters.Add(new SqlParameter("@FooBar", "shizzle"));
comm.ExecuteNonQuery();




(Source stored procedures: this Answer)

Multi threading: The safest way to use multi threading and SQL connections is to always close and dispose your connection object. It's the behavior the SqlConnection was designed for. (Source: Answer John Skeet)

Best practice is make a common DBHelper class and create CRUD methods into that class.
I am adding code snippet.This may help you.

web.config

<connectionStrings>
<add name="mssqltips"
connectionString="data source=localhost;initial catalog=mssqltips;Integrated Security=SSPI;"
providerName="System.Data.SqlClient" />
</connectionStrings>


DBHelper.cs

//Opening Connection
public SqlConnection GetConnection(string connectionName)

string cnstr = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
SqlConnection cn = new SqlConnection(cnstr);
cn.Open();
return cn;

//for select
public DataSet ExecuteQuery(
string connectionName,
string storedProcName,
Dictionary<string, sqlparameter=""> procParameters
)

DataSet ds = new DataSet();
using(SqlConnection cn = GetConnection(connectionName))

using(SqlCommand cmd = cn.CreateCommand())

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcName;
// assign parameters passed in to the command
foreach (var procParameter in procParameters)

cmd.Parameters.Add(procParameter.Value);

using (SqlDataAdapter da = new SqlDataAdapter(cmd))

da.Fill(ds);



return ds;


//for insert,update,delete

public int ExecuteCommand(
string connectionName,
string storedProcName,
Dictionary<string, SqlParameter> procParameters
)

int rc;
using (SqlConnection cn = GetConnection(connectionName))

// create a SQL command to execute the stored procedure
using (SqlCommand cmd = cn.CreateCommand())

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcName;
// assign parameters passed in to the command
foreach (var procParameter in procParameters)

cmd.Parameters.Add(procParameter.Value);

rc = cmd.ExecuteNonQuery();


return rc;



If you do not want to dispose context every time you can create repository class and inject SqlConnection inside.

using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=MYDB"))

repository.SetConnection(connection);
var values = repository.GetSomething();



And Create Class:

public Class Repository

private SqlConnection _connection get; set;

public void SetConnection(SetConnection connection)

_connection = connection;


public string GetSomething()

_connection.Open();
//do stuff with _connection
_connection.Close();




Anyway I recommend you to read about ORM's (Entity Framework or Dapper) and SQL injection attack.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.