public void Connect(ConnectionParam param)
{
ValidParam(param);
this.ServerType = param.ServerType;
try
{
switch (param.ServerType)
{
case ServerType.MSSqlServer:
var sqlConn = new SqlConnection(param.ToString());
sqlConn.Open();
this.DbConnectionSql = sqlConn;
Connected = true;
break;
case ServerType.PostGres:
var pgConnection = new NpgsqlConnection(param.ToString());
pgConnection.Open();
this.DbConnectionSql = pgConnection;
Connected = true;
break;
default:
break;
}
}
catch (Exception)
{
Connected = false;
throw;
}
}
public bool Validate(ConnectionParam param)
{
if (param.ValidateType == 1)
{
try
{
using (var conn = DapperFactory.GetConnection(param.ConnectionString))
{
if (conn.State == ConnectionState.Open)
{
return(true);
}
return(false);
}
}
catch (Exception)
{
return(false);
}
}
try
{
var query = $"select count(1) from {param.CurenetTableName}";
using (var conn = DapperFactory.GetConnection(param.ConnectionString))
{
var result = conn.Query <int>(query).First();
return(result >= 0);
}
}
catch (Exception)
{
return(false);
}
}
private async Task ProcessingNonquery()
{
this.Start();
string sql = @"
DECLARE @i int = 0;
DECLARE @msg VARCHAR(50);
WHILE @i < 100000
BEGIN
SET @msg = 'i = '+ Convert(VARCHAR(10), @i);
IF ((@i % 100 ) = 0)
RAISERROR (@msg, 10, 0);
SET @i = @i+1;
END
";
int n = 0;
using (ConnectionParam cnn = new ConnectionParam(ConnectionString))
{
cnn.OnConnectionInfoMessage += this.Cnn_OnConnectionInfoMessage;
n = await cnn.ExecuteNonQueryAsync(sql);
cnn.OnConnectionInfoMessage -= this.Cnn_OnConnectionInfoMessage;
}
this.Stop(n);
}
private async Task ProcessingDataSet()
{
this.Start();
string sql = @"
DECLARE @i int = 0;
DECLARE @msg VARCHAR(50);
WHILE @i < 1000000
BEGIN
SET @msg = 'i = '+ Convert(VARCHAR(10), @i);
IF ((@i % 100 ) = 0)
RAISERROR (@msg, 10, 0);
SET @i = @i+1;
END
;
SELECT TOP 10 t.object_id, t.name FROM sys.tables t
;
SELECT TOp 20 c.object_id, c.name, c.column_id FROm sys.columns c
;";
DataSet dst = null;
using (ConnectionParam cnn = new ConnectionParam(ConnectionString))
{
dst = await cnn.ExecuteDataSetAsync(sql);
}
this.Stop(dst);
}
public void RouteRequests(ConnectionParam connection)
{
var request = Encoding.UTF8.GetString(connection.Context);
TransactionProtocol message = JsonConvert.DeserializeObject <TransactionProtocol>(request);
if (message.Type_message == TypeMessage.add)
{
AddHandler(message, connection);
}
else
if (message.Type_message == TypeMessage.give)
{
GiveHandler(message, connection);
}
else
if (message.Type_message == TypeMessage.response)
{
ResponseHandler(message, connection);
}
else
{
ErrorHandler(message, connection);
}
}
private void buttonConnect_Click(object sender, EventArgs e)
{
var sqlServer = this.comboBoxSQLServer.Text;
var dataBase = this.comboBoxDB.Text;
try
{
TableUtil.Connect(sqlServer, dataBase);
this.comboBoxTable.Items.Clear();
this.comboBoxTable.Items.AddRange(TableUtil.Tables);
ConnectionParam options = new ConnectionParam()
{
SqlServer = sqlServer,
Database = dataBase,
};
saveConnectionParamSettings(options);
}
catch (System.Data.SqlClient.SqlException ex)
{
MessageBox.Show(
string.Format("Не удалось соединиться с базой данных {0} на сервере {1} и получить список таблиц.\n{2}"
, dataBase
, sqlServer
, ex.Message
)
, "Ошибка соединения");
}
}
public void TestAsyncDataSet()
{
string sql = @"
SELECT 3 AS id
SELECT 4 as id, 'toto' as name
";
DataSet dst = null;
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, timeOut: 200))
{
dst = Task.Run(() => cnn.ExecuteDataSetAsync(sql)).Result;
}
Assert.IsNotNull(dst, "Resultat ExecuteDataSetAsync ?");
Assert.AreEqual(dst.Tables.Count, 2, "Resultat ExecuteDataSetAsync Nombre de table KO");
Assert.AreEqual(dst.Tables[0].Rows.Count, 1, "Resultat ExecuteDataSetAsync T[0] nbRows KO");
int n = SqlConvert.To(dst.Tables[0].Rows[0], "id", 0);
Assert.AreEqual(n, 3, "Resultat ExecuteDataSetAsync Table[0].Rows[0][id] == 3 KO");
Assert.AreEqual(dst.Tables[1].Rows.Count, 1, "Resultat ExecuteDataSetAsync T[1] nbRows KO");
n = SqlConvert.To(dst.Tables[1].Rows[0], "id", 0);
Assert.AreEqual(n, 4, "Resultat ExecuteDataSetAsync Table[1].Rows[0][id] == 4 KO");
string nom = SqlConvert.To(dst.Tables[1].Rows[0], "name", string.Empty);
Assert.AreEqual(nom, "toto", "Resultat ExecuteDataSetAsync Table[1].Rows[0][name] == toto KO");
}
internal void ErrorHandler(TransactionProtocol message, ConnectionParam connection)
{
message.Type_message = API_Transaction.Enums.TypeMessage.error;
var byte_message = ConvertToByteArray(message);
connection.Socket.Send(byte_message);
}
private void ValidParam(ConnectionParam param)
{
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
}
public FormCreateUserFields()
{
InitializeComponent();
metroGridTables.AutoGenerateColumns = false;
metroTabControl1.SelectedIndex = 0;
FillServerTypes();
FormatFiedType();
FormatFieldSubType();
metroTabPageCreateUserField.Hide();
_tableRepository = new Infra.SAPTableRepository(DataConnection.Instance);
_connectionParamRepository = new Infra.ConnectionParamRepository(DataConnection.Instance);
_settingRepository = new SettingRepository(DataConnection.Instance);
_connectinoParam = _connectionParamRepository.GetConnectionParam();
_SAPFieldRepository = new SAPFieldRepository(_company);
if (_connectinoParam == null)
{
_connectinoParam = new ConnectionParam();
}
metroComboBoxLanguage.SelectedValueChanged += MetroComboBoxLanguage_SelectedValueChanged;
metroGridTables.CellMouseClick += MetroGridTables_CellMouseClick;
SetSetting();
FillControls();
metroToolTip2.SetToolTip(metroLinkContact, "*****@*****.**");
}
public void TestNonQueryScalar()
{
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, timeOut: 200))
{
AssertAreEqual(cnn.Connection, null, "La connexion");
AssertAreEqual(cnn.Transaction, null, "La transaction");
AssertAreEqual(cnn.TimeOut, 200, "Le timeOut");
cnn.ExecuteNonQuery(@"
IF (EXISTS (SELECT 1 FROM sys.tables WHERE name = '__test_unitaire__ConnectionParam'))
DROP TABLE __test_unitaire__ConnectionParam
");
AssertAreEqual(cnn.Connection, null, "La connexion");
AssertAreEqual(cnn.Transaction, null, "La transaction");
}
// connexion maintenue
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, true))
{
AssertAreEqual(cnn.Connection, null, "La connexion");
AssertAreEqual(cnn.Transaction, null, "La transaction");
cnn.ExecuteNonQuery("CREATE TABLE __test_unitaire__ConnectionParam (id INT IDENTITY, nom VARCHAR(50))");
Assert.IsNotNull(cnn.Connection, "La connexion est perdue");
AssertAreEqual(cnn.Transaction, null, "La transaction");
List <SqlParameter> parameters = new List <SqlParameter>
{
new SqlParameter("nom", "Test1"),
new SqlParameter("toto", null),
new SqlParameter("x", SqlDbType.Int, 32, ParameterDirection.InputOutput, true, 1, 1, "s", DataRowVersion.Default, null),
new SqlParameter("y", SqlDbType.Int, 32, ParameterDirection.Input, true, 1, 1, "s", DataRowVersion.Default, null),
new SqlParameter("z", SqlDbType.Int, 32, ParameterDirection.Input, true, 1, 1, "s", DataRowVersion.Default, 2),
};
cnn.OnConnectionInfoMessage += Cnn_OnConnectionInfoMessageInsert;
cnn.ExecuteNonQuery("INSERT INTO __test_unitaire__ConnectionParam (nom) VALUES (@nom);", parameters: parameters);
cnn.OnConnectionInfoMessage -= Cnn_OnConnectionInfoMessageInsert;
Assert.IsNotNull(cnn.Connection, "La connexion est perdue");
AssertAreEqual(cnn.Transaction, null, "La transaction");
int id = cnn.ExecuteInsert("INSERT INTO __test_unitaire__ConnectionParam (nom) VALUES ('TEST2');");
AssertAreEqual(id, 2, "Id autoincrementé");
parameters = new List <SqlParameter>
{
new SqlParameter("id", 1)
};
int nb = cnn.ExecuteGetId("SELECT COUNT(*) + 3 FROM __test_unitaire__ConnectionParam WHERE id = @id", -1, System.Data.CommandType.Text, parameters);
Assert.IsNotNull(cnn.Connection, "La connexion est perdue");
AssertAreEqual(nb, 4, "Nombre de lignes + 3 remontées");
AssertAreEqual(cnn.Transaction, null, "La transaction");
}
}
public IHttpActionResult ConnectionString(ConnectionParam connectionParam)
{
var result = _validateDbService.Validate(connectionParam);
return(Json(new ReturnResult <object>()
{
Success = result
}));
}
public void Save(ConnectionParam connectionParam)
{
if (connectionParam.Id == 0)
{
_dataConnection.DbConnection.Insert <ConnectionParam>(connectionParam);
}
else
{
_dataConnection.DbConnection.Update <ConnectionParam>(connectionParam);
}
}
internal void GiveHandler(TransactionProtocol message, ConnectionParam connection)
{
if (string.IsNullOrWhiteSpace(message.Transaction) == false)
{
ErrorHandler(message, connection);
}
var task = RequestStorage.GetInstance().GetTransaction();
var buffer = ConvertToByteArray(task);
connection.Socket.Send(buffer);
}
public void ExcludeConnection(ConnectionParam connection)
{
var request_id = connections.Where(m => m.Value.Address == connection.Address)
.Select(m => m.Key).FirstOrDefault();
bool status = false;
ConnectionParam discard;
while (status)
{
status = connections.TryRemove(request_id, out discard);
}
}
public void TestConnectionOk()
{
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
AssertAreEqual(cnn.ConnexionString, myconnectionString, "La chaine de connexion");
AssertAreEqual(cnn.TimeOut, 0, "Le timeOut");
AssertAreEqual(cnn.Connection, null, "La connexion");
AssertAreEqual(cnn.Transaction, null, "La transaction");
ConnectionStringInfo infos = new ConnectionStringInfo(myconnectionString);
AssertAreEqual(cnn.Infos().ToString(), infos.ToString(), "COnnexion info");
}
}
public void TestAsyncScalar()
{
string sql = @"
SELECT 3 AS id
";
int n = 0;
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, timeOut: 200))
{
n = SqlConvert.To(Task.Run(() => cnn.ExecuteScalarAsync(sql)).Result, 0);
}
Assert.AreEqual(n, 3, "Resultat ExecuteScalarAsync ?");
}
public void TestGetId()
{
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
int nb = cnn.ExecuteGetId(@" SELECT NULL", -66);
AssertAreEqual(nb, -66, "Get ID Not Null");
}
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
int nb = cnn.ExecuteGetId(@" SELECT 'Robert'", -7);
AssertAreEqual(nb, -7, "Get ID Not Null");
}
}
internal void AddHandler(TransactionProtocol message, ConnectionParam connection)
{
if (string.IsNullOrWhiteSpace(message.Request_id) || string.IsNullOrWhiteSpace(message.Sender_id) ||
string.IsNullOrWhiteSpace(message.Transaction))
{
ErrorHandler(message, connection);
return;
}
var request_storage = RequestStorage.GetInstance();
request_storage.AddTransaction(message);
var connection_storage = ConnectionStorage.GetInstance();
connection_storage.AddConnection(connection, message.Request_id);
}
private void loadConnectionParamSettings()
{
ConnectionParameters = new List <ConnectionParam>();
string appDataPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
string settingsFolderPath = Path.Combine(appDataPath, SettingsFolderName);
string settingsFilePath = Path.Combine(settingsFolderPath, SettingsFileName);
if (File.Exists(settingsFilePath))
{
string[] settings = File.ReadAllLines(settingsFilePath);
foreach (string connectionParam in settings)
{
ConnectionParam param = new ConnectionParam();
param.Parse(connectionParam);
ConnectionParameters.Add(param);
}
}
}
public bool Connect(ConnectionParam param)
{
bool bRet = false;
if (param != null)
{
MyLog4Net.Container.Instance.Log.InfoFormat("登录站点 {0} ... ", param.IPAddress);
m_funcOnStarted = OnStarted;
m_funcOnStopped = OnStopped;
NETSDK_LOGIN_PARAM loginParam = param.ToLoginParam();
bRet = InteropService.Connect(loginParam, m_funcOnStarted, m_funcOnStopped);
m_IsConnected = bRet;
MyLog4Net.Container.Instance.Log.InfoFormat("登录站点 {0}, {1}", param.IPAddress, bRet ? "成功" : "失败");
}
return(bRet);
}
public void TestExcecuteXml()
{
string sql = "SELECT 'leon' AS Nom, 3 as Id FOR XML RAW('Crug')";
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, timeOut: 200))
{
XElement result = cnn.ExecuteXml(sql);
Assert.IsNotNull(result, "Noueud XML non trouvé");
Assert.AreEqual(result.Name, "Crug", "Mauvais Nom");
Assert.IsNotNull(result.Attribute("Id"), "id not found");
Assert.AreEqual(result.Attribute("Id").Value, "3", "Bad id");
Assert.IsNotNull(result.Attribute("Nom"), "nom not found");
Assert.AreEqual(result.Attribute("Nom").Value, "leon", "Bad nom");
sql = "SELECT 'leon' AS Nom, 3 as Id WHERE 1=0 FOR XML RAW('Crug')";
result = cnn.ExecuteXml(sql, timeOut: 250);
Assert.IsNull(result, "Noueud XML trouvé");
}
}
internal void ResponseHandler(TransactionProtocol message, ConnectionParam connection)
{
if (string.IsNullOrWhiteSpace(message.Request_id) || string.IsNullOrWhiteSpace(message.Sender_id) ||
string.IsNullOrWhiteSpace(message.Message))
{
ErrorHandler(message, connection);
return;
}
var sender_connection = ConnectionStorage.GetInstance().GetConnection(message.Request_id);
if (sender_connection is null)
{
ErrorHandler(message, connection);
return;
}
var message_buffer = ConvertToByteArray(message);
sender_connection.Socket.Send(message_buffer);
}
public void TestInfoMessage()
{
string sql = "RAISERROR('coucou', 0, 0)";
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, timeOut: 200))
{
cnn.OnConnectionInfoMessage += Cnn_OnConnectionInfoMessage;
using (DataTable tbl = cnn.ExecuteDataTable(sql))
{
}
cnn.OnConnectionInfoMessage -= Cnn_OnConnectionInfoMessage;
}
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, stayConnectionOpen: true, timeOut: 200))
{
using (DataTable tbl = cnn.ExecuteDataTable(sql))
{
}
cnn.OnConnectionInfoMessage += Cnn_OnConnectionInfoMessage;
using (DataTable tbl = cnn.ExecuteDataTable(sql))
{
}
cnn.OnConnectionInfoMessage -= Cnn_OnConnectionInfoMessage;
}
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, stayConnectionOpen: true, timeOut: 200))
{
using (DataTable tbl = cnn.ExecuteDataTable(sql))
{
}
cnn.OnConnectionInfoMessage += Cnn_OnConnectionInfoMessage;
using (DataTable tbl = cnn.ExecuteDataTable(sql))
{
}
cnn.OnConnectionInfoMessage -= Cnn_OnConnectionInfoMessage;
}
}
private void saveConnectionParamSettings(ConnectionParam connection)
{
string appDataPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
string settingsFolderPath = Path.Combine(appDataPath, SettingsFolderName);
if (!Directory.Exists(settingsFolderPath))
{
Directory.CreateDirectory(settingsFolderPath);
}
string settingsFilePath = Path.Combine(settingsFolderPath, SettingsFileName);
if (!File.Exists(settingsFilePath))
{
StreamWriter writer = File.CreateText(settingsFilePath);
writer.Close();
}
string[] settings = File.ReadAllLines(settingsFilePath);
if (!settings.Contains(connection.ConnectionString, StringComparer.InvariantCultureIgnoreCase))
{
File.AppendAllText(settingsFilePath, connection.ConnectionString);
}
}
public void TestAsyncNonQuery()
{
string sql = @"
DECLARE @i int = 0;
DECLARE @msg VARCHAR(50);
WHILE @i < 100000
BEGIN
SET @msg = 'i = '+ Convert(VARCHAR(10), @i);
IF ((@i % 100 ) = 0)
RAISERROR (@msg, 10, 0);
SET @i = @i+1;
END
";
int n = 0;
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, timeOut: 200))
{
n = Task.Run(() => cnn.ExecuteNonQueryAsync(sql)).Result;
}
Assert.AreEqual(n, -1, "Resultat ExecuteNonQueryAsync ?");
}
public bool AddConnection(ConnectionParam connection, string request_id)
{
var status = connections.TryAdd(request_id, connection);
return(status);
}
public override string BuildConnectionString(ConnectionParam pconparam)
{
return "override";
}
/// <summary> /// @author = Nguyen The Hung /// @description : It is an abstract method with 1 parameters /// @description : use to assign all connection parameters to an connection string /// @Create Date = 25/07/2007 /// @param name= pConnectionParam (an object of class ConnectionParam) : contains all connection parameters /// @LastModify = 30/07/2007 /// </summary> /// <returns>an object of class IDbConnection</returns> public abstract string BuildConnectionString(ConnectionParam pConnectionParam);
/// <summary>
/// @author = Nguyen The Hung
/// @description : It is a static method with 2 parameters
/// @description : use to get connection object of an specific connectin type ex : SQL/ORACLE/ACCESS/...
/// @description : and assign an object of class ConnectionParam which contains all connection paramters
/// @Create Date = 25/07/2007
/// @param name= pConnectionParam (an object of class ConnectionParam ) : contain all connection parameters like user,databse......
/// @LastModify = 30/07/2007
/// </summary>
/// <returns>an object of class ConnectionFactory</returns>
public static ConnectionFactory NewConnectionFactory(ConnectionParam pConnectionParam)
{
return NewConnectionFactory(pConnectionParam.getDriverType(), pConnectionParam); ;
}
public override string BuildConnectionString(ConnectionParam pconparam)
{
// with OdbcConnectionString la` 1 trong cac chuoi~ duoi day
// @"Driver={SQL Server};Server=.;Trusted_Connection=yes;Database=pubs;"; // --> ket noi csdl SQL bang che do authentication cua windows
// @"Driver={SQL Server};server=.;uid=sa;pwd=12345;database=pubs;"; // --> ket noi csdl SQL bang che do username va password cua SQL
// @"Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Trusted_Connection=yes"
// @"Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\bin\nwind.mdb"
// @"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls"
// @"Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin"
// @"DSN= dsnname" ==>voi dsnname duoc cau hinh nhu sau : vao administrator Tools/Data Sources (ODBC)->chon tab System (DSN) ->bam nut Add -> chon driver do Microsoft Access(*.mdb)->o muc Data Source Name danh ten DSN bat ky(dsnname) ,vi du la odbcTest->nhan nut Select de chon database Access,vi du D:\test.mdb ->bam nut ok de ket thuc
if (DbType == "ODBC_SQL")
{
keysize = Aes.KeySize.Bits128;
byte[] cipherText = new byte[16];
byte[] decipheredText = new byte[16];
FileInfo inf = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "settingsODBC_SQL.dat");
if (inf.Exists)
{
StreamReader reader = inf.OpenText();
//base.Server = reader.ReadLine();
pconparam.setServer(reader.ReadLine());
//base.UserName = reader.ReadLine();
pconparam.setUserName(reader.ReadLine());
cipherText = Encoding.Unicode.GetBytes(reader.ReadLine());
AesLib.Aes a = new Aes(keysize, new byte[16]);
a.InvCipher(cipherText, decipheredText);
//Password = Encoding.Unicode.GetString(decipheredText);
pconparam.setPassword(Encoding.Unicode.GetString(decipheredText));
//base.Database = reader.ReadLine();
pconparam.setDatabase(reader.ReadLine());
strconn = reader.ReadLine();
if (strconn == "sa")
{
//string OdbcConnectionString = @"Driver={SQL Server};server=.;uid=sa;pwd=12345;database=pubs;"; // --> ket noi csdl SQL bang che do username va password cua SQL
//strconn = @"Driver={SQL Server};server="+ base.Server + ";uid="+base.UserName +";pwd="+base.Password +";database=" + base.Database;
strconn = @"Driver={SQL Server}" + ";Connect Timeout=60" + ";MultipleActiveResultSets=True; server=" + pconparam.getServer() + ";uid=" + pconparam.getUserName() + ";pwd=" + pconparam.getPassword() + ";database=" + pconparam.getDatabase();
}
else if (strconn == "rbNTSecurity")
{
//string OdbcConnectionString = @"Driver={SQL Server};Server=.;Trusted_Connection=yes;Database=pubs;"; // --> ket noi csdl SQL bang che do authentication cua windows
//strconn = @"Driver={SQL Server};Server=" + base.Server + ";Trusted_Connection=yes;database="+base.Database;
strconn = @"Driver={SQL Server}" + ";Connect Timeout=60" + ";MultipleActiveResultSets=True; Server=" + pconparam.getServer() + ";Trusted_Connection=yes;database=" + pconparam.getDatabase();
}
reader.Close();
}
else
{
StreamWriter writer = inf.CreateText();
writer.Close();
}
base.ConnectionString = strconn;
}
else if (DbType == "ODBC_Access")
{
keysize = Aes.KeySize.Bits128;
byte[] cipherText = new byte[16];
byte[] decipheredText = new byte[16];
FileInfo inf = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "settingsOLEDB_Access.dat");
if (inf.Exists)
{
StreamReader reader = inf.OpenText();
//base.Server = reader.ReadLine();
pconparam.setServer(reader.ReadLine());
//base.UserName = reader.ReadLine();
pconparam.setUserName(reader.ReadLine());
cipherText = Encoding.Unicode.GetBytes(reader.ReadLine());
AesLib.Aes a = new Aes(keysize, new byte[16]);
a.InvCipher(cipherText, decipheredText);
//Password = Encoding.Unicode.GetString(decipheredText);
pconparam.setPassword(Encoding.Unicode.GetString(decipheredText));
//base.Database = reader.ReadLine();
pconparam.setDatabase(reader.ReadLine());
strconn = reader.ReadLine();
string accessDBpath = pconparam.getDatabase(); //duong dan chua file csdl Access
//set duong` dan cho file csdl Access neu no ko nam` trong thu muc ung dung \bin\Debug
//hoac string OdbcConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\test.mdb";
strconn = @"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + accessDBpath;
//example 1 : accessDBpath = test.mdb (neu file test.mdb nam` trong thu muc bin\Debug cua ung dung)
//example 2 : accessDBpath = D:\test.mdb trong truong` hop nay` phai set duong dan den file access de ung dung co the truy cap vao file access
reader.Close();
}
else
{
StreamWriter writer = inf.CreateText();
writer.Close();
}
base.ConnectionString = strconn;
}
else if (DbType == "ODBC_Oracle")
{
//@"Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Trusted_Connection=yes"
//code later
}
else if (DbType == "ODBC_Excel")
{
//@"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls"
//code later
}
else if (DbType == "ODBC_TextFile")
{
//@"Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin"
//code later
}
else if (DbType == "ODBC_DSN")
{
string DSN_ODBC_ACCESS = "tenDSN"; //ten DSN
keysize = Aes.KeySize.Bits128;
byte[] cipherText = new byte[16];
byte[] decipheredText = new byte[16];
FileInfo inf = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "settingsOLEDB_DSN.dat");
if (inf.Exists)
{
StreamReader reader = inf.OpenText();
//base.Server = reader.ReadLine(); //ko dung`
//base.UserName = reader.ReadLine(); //ko dung`
cipherText = Encoding.Unicode.GetBytes(reader.ReadLine());
AesLib.Aes a = new Aes(keysize, new byte[16]);
a.InvCipher(cipherText, decipheredText);
//Password = Encoding.Unicode.GetString(decipheredText);
DSN_ODBC_ACCESS = Encoding.Unicode.GetString(decipheredText);
//base.Database = reader.ReadLine();
//strconn = reader.ReadLine();
//hoac string OdbcConnectionString = @"DSN=tenDSN";
strconn = @"DSN=" + DSN_ODBC_ACCESS;
reader.Close();
}
else
{
StreamWriter writer = inf.CreateText();
writer.Close();
}
base.ConnectionString = strconn;
//@"DSN= dsnname" ==>voi dsnname duoc cau hinh nhu sau :
//vao administrator Tools/Data Sources (ODBC)->chon tab System (DSN) ->bam nut Add -> chon driver do Microsoft Access(*.mdb)
//->o muc Data Source Name danh ten DSN bat ky(dsnname) ,vi du la odbcTest
//->nhan nut Select de chon database Access,vi du D:\test.mdb ->bam nut ok de ket thuc
//code later
}
else throw new Exception("Invalid database type");
return base.ConnectionString;
}
/// <summary>
/// @author = Nguyen The Hung
/// @description : It is a constructor with 1 parameters,use to init object
/// @Create Date = 25/07/2007
/// @param name= pConnectionParam (an object of class ConnectionParam ) : contain all connection parameters like user,databse......
/// @LastModify = 30/07/2007
/// </summary>
/// <returns>not have return type</returns>
public ConnectionFactory(ConnectionParam pConnectionParam)
: this()
{
propConnectionParam = pConnectionParam; //assign all connection parameters
}
public override string BuildConnectionString(ConnectionParam pconparam)
{
String strconn = pconparam.getLoginType();
if (strconn == null || (strconn.Equals("")) || (!strconn.Equals("WINDOWS_NT")))
{
//strconn = "server=" + base.Server + "; database=" + base.Database + " ; user id=" + base.UserName + "; password="******"; Connection Reset=false";
strconn = " MultipleActiveResultSets=True; server=" + pconparam.getServer() + ";Connect Timeout=60" + "; database=" + pconparam.getDatabase() + " ; user id=" + pconparam.getUserName() + "; password="******"; Connection Reset=false";
}
else
{
//strconn = "server=" + base.Server + "; database= " + base.Database + " ; Trusted_Connection=true; Connection Reset=false";
strconn = "MultipleActiveResultSets=True; server=" + pconparam.getServer() + ";Connect Timeout=60" + "; database= " + pconparam.getDatabase() + " ; Trusted_Connection=true; Connection Reset=false";
}
pconparam.setConnString(strconn);
this.ConIdbConnection.ConnectionString = strconn;
this.ConnectionString = strconn;
return strconn;
//keysize = Aes.KeySize.Bits128;
//byte[] cipherText = new byte[16];
//byte[] decipheredText = new byte[16];
//FileInfo inf = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "settings.dat");
//if (inf.Exists)
//{
// StreamReader reader = inf.OpenText();
// pconparam.setServer(reader.ReadLine());
// pconparam.setUserName(reader.ReadLine());
// //base.Server = reader.ReadLine();
// //base.UserName = reader.ReadLine();
// cipherText = Encoding.Unicode.GetBytes(reader.ReadLine());
// AesLib.Aes a = new Aes(keysize, new byte[16]);
// a.InvCipher(cipherText, decipheredText);
// //Password = Encoding.Unicode.GetString(decipheredText);
// pconparam.setPassword(Encoding.Unicode.GetString(decipheredText));
// //base.Database = reader.ReadLine();
// pconparam.setDatabase(reader.ReadLine());
// strconn = reader.ReadLine();
// if (strconn == "sa")
// {
// //strconn = "server=" + base.Server + "; database=" + base.Database + " ; user id=" + base.UserName + "; password="******"; Connection Reset=false";
// strconn = "server=" + pconparam.getServer() + "; database=" + pconparam.getDatabase() + " ; user id=" + pconparam.getUserName() + "; password="******"; Connection Reset=false";
// }
// else if (strconn == "rbNTSecurity")
// {
// //strconn = "server=" + base.Server + "; database= " + base.Database + " ; Trusted_Connection=true; Connection Reset=false";
// strconn = "server=" + pconparam.getServer() + "; database= " + pconparam.getDatabase() + " ; Trusted_Connection=true; Connection Reset=false";
// }
// reader.Close();
//}
//else
//{
// StreamWriter writer = inf.CreateText();
// writer.Close();
//}
//base.ConnectionString = strconn;
////base.ConnectionString = "server = "+ base.Server +";uid= "+ UserName+";pwd = " + Password +";database ="+ Database+";";
//return base.ConnectionString;
}
private void LoopReconnect()
{
while (!m_IsConnected)
{
if (m_MREReconnect.WaitOne())
{
while (!m_IsConnected)
{
if (m_Connected)
{
break;
}
if (m_IsConnected)
{
break;
}
if (!m_Connected)
{
Thread.Sleep(INTERVAL_RECONNECT * 1000);
}
if (Message != null)
{
Message("开始重连 ...");
}
if (ReConnectStart != null)
{
ReConnectStart(this, EventArgs.Empty);
}
ConnectionParam ip = IPList.GetNext();
if (ip != null)
{
if (Connect(ip))
{
ConnectedServer = true; // m_ImageAnalysisService.Start(true);
}
}
if (ConnectedServer)
{
if (Message != null)
{
Message("重连成功");
}
if (ReConnectFinished != null)
{
ReConnectFinished(true);
}
}
else
{
if (Message != null)
{
Message("重连失败");
}
if (ReConnectFinished != null)
{
ReConnectFinished(false);
}
}
}
m_MREReconnect.Reset();
}
}
}
/// <summary>
/// @author = Nguyen The Hung
/// @description : It is a static method with 2 parameters
/// @description : use to get connection object of an specific connectin type ex : SQL/ORACLE/ACCESS/...
/// @description : and assign an object of class ConnectionParam which contains all connection paramters
/// @Create Date = 25/07/2007
/// @param name= pstrConnectionType (string) : type of connection (SQL/ODBC/OLEDB/ORACLE)
/// @param name= pConnectionParam (an object of class ConnectionParam ) : contain all connection parameters like user,databse......
/// @LastModify = 30/07/2007
/// </summary>
/// <returns>an object of class ConnectionFactory</returns>
public static ConnectionFactory NewConnectionFactory(string pstrConnectionType, ConnectionParam pConnectionParam)
{
ConnectionFactory pconfac = NewConnectionFactory(pstrConnectionType);
pconfac.propConnectionParam = pConnectionParam;
return pconfac;
}
public SqlConnection sqlCon()
{
string strCustId = System.Configuration.ConfigurationManager.AppSettings["CustId"];
string strURL = "customer.staffpanel.in";// System.Configuration.ConfigurationManager.AppSettings["URL"];
ConnectionParam obj = null;
string strMasterCon = "";
string strUserId = "";
string strPassword = "";
string strServerName = "";
string strDBName = "";
try
{
string strDBDetails = HttpContext.Current.Application["DBDetails"].ToString();
strDBDetails = System.Configuration.ConfigurationManager.AppSettings["db"];
// strDBDetails = @"AMRUTA-PC\SQLEXPRESS|sa|mnbv@1234|EmpPayroll";
// strDBDetails = @"103.27.86.197|sa|admin^it1008|StaffPan_AadityaAnagha";
// strDBDetails = @"103.27.86.197|sa|admin^it1008|StaffPan_NirmalUjjwal";
// strDBDetails = @"103.27.86.197|sa|admin^it1008|StaffPan_YavatmalUrban";
if (strDBDetails == "")
{
string jsonString = GET("http://" + strURL + "/RestJSONService.svc/GetJsonData/" + strCustId);
jsonString = jsonString.Replace('[', ' ').Replace(']', ' ');
DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(ConnectionParam));
MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(jsonString));
obj = (ConnectionParam)ser.ReadObject(stream);
HttpContext.Current.Application["DBDetails"] = obj.ServerIP + "|" + obj.UserID + "|" + obj.Password + "|" + obj.DatabaseName;
strUserId = obj.UserID;
strPassword = obj.Password;
strServerName = obj.ServerIP;
strDBName = obj.DatabaseName;
}
else
{
String[] strArr = strDBDetails.Split('|');
strServerName = strArr[0];
strUserId = strArr[1];
strPassword = strArr[2];
strDBName = strArr[3];
HttpContext.Current.Application["DBDetails"] = strServerName + "|" + strUserId + "|" + strPassword + "|" + strDBName;
}
}
catch
{
string strDBDetails = HttpContext.Current.Application["DBDetails"].ToString();
if (strDBDetails == "")
{
String[] strPathArr = System.Web.HttpContext.Current.Request.RawUrl.Split('/');
string strFilePath = "";
for (int i = 0; i < strPathArr.Length - 3; i++)
{
strFilePath = strFilePath + "../";
}
System.Data.DataTable dt = null;
string strPath = System.Web.HttpContext.Current.Server.MapPath(strFilePath + "XML/connection.xml");
XmlReader xmlFile = null;
xmlFile = XmlReader.Create(strPath, new XmlReaderSettings());
DataSet ds = new DataSet();
ds.ReadXml(xmlFile, XmlReadMode.InferTypedSchema);
//dv = new DataView(ds.Tables[0]);
dt = ds.Tables[0];
String[] strArr = strCustId.Split('_');
DataRow[] dataRows = dt.Select(" sdcid = " + strArr[0]);
if (dataRows.Length > 0)
{
foreach (DataRow rows in dataRows)
{
strUserId = rows["SqlUserID"].ToString();
strPassword = rows["SqlPassword"].ToString();
strServerName = rows["ServerName"].ToString();
strDBName = rows["SqlDBName"].ToString();
HttpContext.Current.Application["DBDetails"] = strServerName + "|" + strUserId + "|" + strPassword + "|" + strDBName;
}
}
}
else
{
String[] strArr = strDBDetails.Split('|');
strServerName = strArr[0];
strUserId = strArr[1];
strPassword = strArr[2];
strDBName = strArr[3];
HttpContext.Current.Application["DBDetails"] = strServerName + "|" + strUserId + "|" + strPassword + "|" + strDBName;
}
}
strMasterCon = "User ID=" + strUserId;
strMasterCon = strMasterCon + ";password="******";data source=" + strServerName;
strMasterCon = strMasterCon + ";persist security info=False";
strMasterCon = strMasterCon + ";initial catalog=" + strDBName;
strMasterCon = strMasterCon + ";Connect Timeout=1200";
return(new SqlConnection(strMasterCon));
}
public void TestTransaction()
{
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
cnn.BeginTransaction();
Assert.IsNotNull(cnn.Connection, "connexion nulle après begin trans");
AssertAreEqual(cnn.TransactionCount, 1, "Niveau de profondeur des transactions après begin trans");
cnn.ExecuteNonQuery(@"
IF (NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = '__test_unitaire__ConnectionParam_TRANSACTION'))
CREATE TABLE __test_unitaire__ConnectionParam_TRANSACTION (id int identity, nom varchar(50) NOT NULL);
");
Assert.IsNotNull(cnn.Connection, "connexion nulle après SQL");
AssertAreEqual(cnn.TransactionCount, 1, "Niveau de profondeur des transactions après sql");
cnn.CommitTransaction();
Assert.IsNull(cnn.Connection, "connexion nulle après commit");
AssertAreEqual(cnn.TransactionCount, 0, "Niveau de profondeur des transactions après commit");
}
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
int nbrows = cnn.ExecuteGetId("SELECT count(*) FROM __test_unitaire__ConnectionParam_TRANSACTION", 0);
cnn.BeginTransaction();
Assert.IsNotNull(cnn.Connection, "connexion nulle après begin trans N°2");
AssertAreEqual(cnn.TransactionCount, 1, "Niveau de profondeur des transactions après begin transN°2");
cnn.ExecuteNonQuery(@"INSERT INTO __test_unitaire__ConnectionParam_TRANSACTION (nom) VALUES ('toto')");
Assert.IsNotNull(cnn.Connection, "connexion nulle après SQL");
cnn.BeginTransaction();
AssertAreEqual(cnn.TransactionCount, 2, "Niveau de profondeur des transactions après begin trans Imbriqué");
cnn.ExecuteNonQuery(@"INSERT INTO __test_unitaire__ConnectionParam_TRANSACTION (nom) VALUES ('tata')");
int id = cnn.ExecuteGetId(@"SELECT SCOPE_IDENTITY()", -99);
Assert.AreNotEqual(id, -99, "SCOPE identity fail");
int nbrows2 = cnn.ExecuteGetId("SELECT count(*) FROM __test_unitaire__ConnectionParam_TRANSACTION", 0);
AssertAreEqual(nbrows + 2, nbrows2, "Nombre de lignes incohérent avant rollback");
cnn.CommitTransaction();
AssertAreEqual(cnn.TransactionCount, 1, "Niveau de profondeur des transactions après premier commit Imbriqué");
cnn.RollBackTransaction();
Assert.IsNull(cnn.Connection, "connexion nulle après roolback");
AssertAreEqual(cnn.TransactionCount, 0, "Niveau de profondeur des transactions après commit");
int nbrows3 = cnn.ExecuteGetId("SELECT count(*) FROM __test_unitaire__ConnectionParam_TRANSACTION", 0);
AssertAreEqual(nbrows, nbrows3, "Nombre de lignes incohérent après rollback");
}
// Test auto rollback (plus pour la couverture de code qu'autre chose !!
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, true))
{
cnn.BeginTransaction();
}
}
