Register now or log in to join your professional community.
I have2 clients with same reuirements but the only difference is1 of then want to have MS ACCESS and the other want to use MS SQL SERVER. I want to develop it at once and connect database DBMS dynamically
Change the connection string at runtime.
TIP: You can also economize code by using a database driver that works on many databases such as OLE DB.
Easly with bridge like ODBC and make a DSN file for each database so you can connect swaply
I do the following:
ConnexionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & "C:\\Ophtalmo\\Ophtalmo.accdb" My.Settings("OphtalmoConnectionString") = ConnexionString
I build the connexionstring at runtime depending on MS-ACCESS or SQL-Server.
But keep in mind that th connexion is not the only issue that you will be facing !
Some querries will not work on both DBMS at the same time, for exemple :
You need to handle the compatibility for " and ' string delimiters
Dates values are not calculated the same way
. . . . .
you need to create new table in sql server will be better and name it "user_db" with fields (username , db_type , connnections string), in the code select * from user_db where username = @username
if (str_db_type = "sql")
{
sql_con sqlconnection = new sqlconnection(connection_string);
}
else
{
acc_con oledbconnection = new oledbconnection(connection_string );
}
then pass this connection to the function of subroutine like
ExecuteScalarExample(sql_con);or you mayExecuteScalarExample(acc_con);
public static void ExecuteScalarExample(IDbConnection con) { IDbCommand com = con.CreateCommand(); com.CommandType = CommandType.Text; com.CommandText = "SELECT COUNT(*) FROM Employees";
int result = (int)com.ExecuteScalar(); Console.WriteLine("Employee count = " + result); }
