If say I need to run two separate SQL statements against two separate databases. Right now I do (pseudocode):
Try{
declare variable connectionA to DatabaseA
declare variable connectionB to DatabaseB
connectionA.open()
connectionB.open()
declare variable SQLCmdA with ConnectionA and one SQL statement
declare variable SQLCmdB with ConnectionB and another SQL statement
SQLCmdA.executeNonQuery()
SQLCmdB.executeNonQuery()
}
Catch ()
{
print error message
}
Finally(){
connectionA.close()
connectionB.close()
SQLCmdA.Dispose()
SQLCmdB.Dispose()
}
The above seems very clumsy. And if I have three different sql statements, i would need three different SQLCmd variables.
Is there a "standard" way of doing such things, especially in terms of efficiency, performance? if anyone can provide a simple improved pseudocode, that'd be great.
In addition, do I need to worry about implementing Connection Pooling, to conserve resource and speed up the program? If so, how do I implement it in this case?
Thanks!
Step-1) Open the Mysql ConnectionUse the mysqli_connect() method to open connection to database server without specifying the name of the database. <? php $hostname = 'localhost'; $username = 'my_username'; $password = 'my_password'; $con = mysqli_connect($hostname, $username, $password); if(!$ con){ die('Error ' .
A database can be available to multiple connections in several situations. Multi-user database access is possible if Derby is running inside a server framework. A single application can work with multiple Connections to the same database and assign them to different threads.
By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance.
Instead of adding variables, why not make a class?
public class MyDatabaseConnection {
public MyDatabaseConnection(string connectionString) {
this.connectionString = connectionString;
// create a database connection perhaps
}
// some methods for querying a database
public void execute(string query) { }
}
In this case it's easy to add a third database connection
MyDatabaseConnection con1 = new MyDatabaseConnection("Server=localhost");
MyDatabaseConnection con2 = new MyDatabaseConnection("Server=other_server");
MyDatabaseConnection con3 = new MyDatabaseConnection("Server=third_one");
And execute an sql query on each
MyDatabaseConnection[] cons = new MyDatabaseConnection[]{ con1, con2, con3 };
foreach (MyDatabaseConnection con in cons) {
con.execute(someSqlCommandText);
}
If you're going to be doing low-level database access, this seems fine to me. Of course, if you only need one database connection open at any time, you could abstract most of the code into a method (that takes an SQL command/text as a parameter and returns the result), but this may not be the case in your situation.
You could also make things slightly neater by making use of using
statements, as such:
using(var sqlConnectionA = new ...)
using(var sqlConnectionB = new ...)
{
try
{
// Perform queries here.
}
catch (SqlException exSql)
{
// SQL error
}
}
If you need all two (or three, or...) connetions open at the same time and need to retain the SqlCommand for each of them, then yes, you're probably going to have to do it the way you're doing it.
However, if you only need one connection open at a time, you could use a single connection and single command, then change things as needed.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With