public override IDbConnection Open(out string errorMessage)
{
string connStr = null;
try {
if (settings.UseConnectionString)
{
connStr = settings.ConnectionString;
}
else
{
//Data Source='myASEserver';Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
connStr = String.Format("Data Source='{0}';Port={1};Database={2};Uid={3};Pwd={4};",
settings.Server, settings.Port, settings.Database, settings.Username, settings.Password);
}
SetConnectionStringParameter(connStr, String.Empty, "pooling", settings.EnablePooling.ToString());
if (settings.EnablePooling)
{
SetConnectionStringParameter(connStr, String.Empty, "MinPoolSize", settings.MinPoolSize.ToString());
SetConnectionStringParameter(connStr, String.Empty, "MaxPoolSize", settings.MaxPoolSize.ToString());
}
connection = new SybaseConnection(connStr);
connection.Open();
errorMessage = String.Empty;
isConnectionError = false;
return(connection);
} catch {
isConnectionError = true;
errorMessage = String.Format("Unable to connect. (CS={0})", connStr == null ? "NULL" : connStr);
return(null);
}
}
/// <summary>
/// Please refer to the documentation of <see cref="GentleProvider"/> and the
/// <see cref="IPersistenceEngine"/> interface it implements for details.
/// </summary>
public override IDbConnection GetConnection()
{
try
{
SybaseConnection sc = new SybaseConnection(ConnectionString);
//new AsaConnection( connectionString );
sc.Open();
Check.VerifyEquals(sc.State, ConnectionState.Open, Error.NoNewConnection);
return(sc);
}
catch (GentleException)
{
throw; // expose the errors raised by ourselves (i.e. the data framework) in the try block
}
catch (Exception e)
{
Check.Fail(e, Error.DatabaseUnavailable, Name, ConnectionString);
throw new GentleException(Error.Unspecified, "Unreachable code");
}
}
private ObservableCollection<Broker> GetBrokerMapping()
{
lstBrokers = new ObservableCollection<Broker>();
try
{
using (SybaseConnection con = new SybaseConnection(model.Constants.SybaseConnection))
{
using (SybaseCommand com = new SybaseCommand(model.Constants.SelectBrokerMapping, con) { CommandType = CommandType.Text })
{
con.Open();
try
{
using (SybaseDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
lstBrokers.Add(new Broker(Convert.ToInt32(reader["id"].ToString()), reader["portia_broker_name"].ToString(),
reader["dtc_broker_name"].ToString(), reader["executing_broker_id"].ToString(),
Convert.ToDateTime(reader["effective_date"].ToString()),
reader["updatedBy"].ToString()
));
}
}
}
catch (Exception ex)
{
Utility.PostMessage("Broker Mapping", ex.InnerException.ToString(), Mcm.Logger.Log.Level.Error);
return null;
}
}
}
}
catch (Exception ex)
{
Utility.PostMessage("Broker Mapping", ex.InnerException.ToString(), Mcm.Logger.Log.Level.Error);
return null;
}
return lstBrokers;
}
static void Main(string[] args)
{
string connectionString = "";
if (args.Length == 3 || args.Length == 4)
{
if (args.Length == 3)
{
connectionString = String.Format(
"Server={0};" +
"Database={1};" +
"User ID={2};",
args[0], args[1], args[2]);
}
else if (args.Length == 4)
{
connectionString = String.Format(
"Server={0};" +
"Database={1};" +
"User ID={2};" +
"Password={3}",
args[0], args[1], args[2], args[3]);
}
}
else
{
Console.WriteLine("Usage: mono SybaseTest.exe sql_server database user_id password");
return;
}
SybaseConnection cnc = new SybaseConnection();
cnc.ConnectionString = connectionString;
cnc.Open();
DoSybaseTest(cnc);
cnc.Close();
}
public static void Main(string[] args)
{
Console.WriteLine("Start TestSqlConnection.");
if (args.Length != 6 && args.Length != 7)
{
Console.WriteLine(
"\nUsage: mono TestSqlConnection.exe Client Table Column Server Database UserID [Password]\n\n" +
#if IncludeSybaseAndTdsClient
"\tClient is one of the following: SqlClient, TdsClient, or SybaseClient\n" +
#else
"\tClient is: SqlClient. No support for TdsClient nor SybaseClient\n" +
#endif // IncludeSybaseAndTdsClient
"\tTable is the name of the database table to select from\n" +
"\tColumn is the name of the column in the Table to select from\n" +
"\tServer is the SQL Server to connect. Use one of the following forms:\n" +
"\t\tHOSTNAME Ex: MYHOST\n" +
"\t\tHOSTNAME,port Ex: MYHOST,1433\n" +
"\t\tHOSTNAME\\\\instance Ex: MYHOST\\\\NETSDK Note: only works with SqlClient\n" +
"\tDatabase is the name of the database to use\n" +
"\tUser ID is the user's User ID\n" +
"\tPassword is the user's Password Note: if ommitted, a blank password is used\n" +
"Exampes:\n" +
"\tEx 1: SqlClient employee lname MYHOST pubs myuserid mypassword\n" +
"\tEx 3: SqlClient employee lname MYHOST,1443 pubs myuserid mypassword\n" +
"\tEx 2: SqlClient Products ProductName MYHOST\\\\NETSDK myuserid mypassword\n" +
"\tEx 4: SqlClient employee lname MYHOST pubs myuserid\n" +
"\tEx 5: TdsClient sometable somecolumn MYHOST test myuserid mypassword\n" +
"\tEx 6: SybaseClient sometable somecolumn MYHOST test myuserid mypassword\n");
return;
}
string client = args[0];
string tableName = args[1];
string columnName = args[2];
string server = args[3];
string database = args[4];
string userid = args[5];
string password = "";
if (args.Length == 7)
{
password = args[6];
}
string constr;
string sql;
Console.WriteLine("\nClient: " + client);
Console.WriteLine("Table Name: " + tableName);
Console.WriteLine("Column Name: " + columnName);
Console.WriteLine("Server: " + server);
Console.WriteLine("Database: " + database);
Console.WriteLine("User ID: " + userid);
Console.WriteLine("Password: "******"SELECT " + columnName + " FROM " + tableName;
constr =
"Server=" + server + ";" +
"Database=" + database + ";" +
"User ID=" + userid + ";" +
"Password="******";";
Console.WriteLine("\nConnectionString: " + constr);
Console.WriteLine("SQL: " + sql);
Console.WriteLine("\nCreating Connection...");
IDbConnection con = null;
switch (client.ToUpper())
{
case "SQLCLIENT":
con = new SqlConnection();
break;
#if IncludeSybaseAndTdsClient
case "TDSCLIENT":
con = new TdsConnection();
break;
case "SYBASECLIENT":
con = new SybaseConnection();
break;
default:
Console.WriteLine("Invalid client: " + client + "\nUse SqlClient, TdsClient, or SybaseClient");
return;
#else
default:
Console.WriteLine("Invalid client: " + client + "\nUse SqlClient. No support for TdsClient nor SybaseClient.");
return;
#endif
}
Console.WriteLine("set connection string...");
con.ConnectionString = constr;
Console.WriteLine("open connection...");
try {
con.Open();
}
catch (SqlException se) {
Console.WriteLine("SqlException caught");
Console.WriteLine("Message: " + se.Message);
Console.WriteLine("Procedure: " + se.Procedure);
Console.WriteLine("Class: " + se.Class);
Console.WriteLine("Number: " + se.Number);
Console.WriteLine("Source: " + se.Source);
Console.WriteLine("State: " + se.State);
Console.WriteLine("Errors:");
foreach (SqlError error in se.Errors)
{
Console.WriteLine(" SqlError:");
Console.WriteLine(" Message: " + se.Message);
Console.WriteLine(" Line Number: " + se.LineNumber);
Console.WriteLine(" Procedure: " + se.Procedure);
Console.WriteLine(" Class: " + se.Class);
Console.WriteLine(" Number: " + se.Number);
Console.WriteLine(" Server: " + se.Server);
Console.WriteLine(" Source: " + se.Source);
Console.WriteLine(" State: " + se.State);
}
Console.WriteLine("StackTrace: " + se.StackTrace);
Console.WriteLine("TargetSite: " + se.TargetSite);
Exception ie = se.InnerException;
if (ie != null)
{
Console.WriteLine("InnerException:");
Console.WriteLine(" Message: " + se.Message);
Console.WriteLine(" Class: " + se.Class);
Console.WriteLine(" Number: " + se.Number);
Console.WriteLine(" Source: " + se.Source);
Console.WriteLine(" State: " + se.State);
Console.WriteLine(" StackTrace: " + se.StackTrace);
Console.WriteLine(" TargetSite: " + se.TargetSite);
}
return;
}
Console.WriteLine("Creating command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("set SQL...");
cmd.CommandText = sql;
Console.WriteLine("execute reader...");
IDataReader reader = cmd.ExecuteReader();
Console.WriteLine("read first row...");
if (reader.Read())
{
Console.WriteLine(" Value: " + reader[columnName].ToString());
}
else
{
Console.WriteLine(" No data returned. Or either, no permission to read data.");
}
Console.WriteLine("Clean up...");
// clean up
reader.Close();
reader = null;
cmd.Dispose();
cmd = null;
con.Close();
con = null;
Console.WriteLine("Done.");
}
private object AddBroker(object param)
{
string portiaName = ((Tuple<string, string,string>)param).Item1;
string dtcName = ((Tuple<string, string, string>)param).Item2;
string execBrokerId = ((Tuple<string, string, string>)param).Item3;
if (!string.IsNullOrEmpty(dtcName) && !string.IsNullOrEmpty(portiaName))
{
using (SybaseConnection con = new SybaseConnection(model.Constants.SybaseConnection))
{
using (SybaseCommand com = new SybaseCommand(model.Constants.InsertBroker, con) { CommandType = CommandType.Text })
{
SybaseParameter oParam = new SybaseParameter("@portiaBrokerName", SybaseDbType.VarChar);
oParam.Value = portiaName;
com.Parameters.Add(oParam);
SybaseParameter oParam1 = new SybaseParameter("@dtcBrokerName", SybaseDbType.VarChar);
oParam1.Value = dtcName;
com.Parameters.Add(oParam1);
SybaseParameter oParam2 = new SybaseParameter("@executingBrokerId", SybaseDbType.VarChar);
oParam2.Value = execBrokerId;
com.Parameters.Add(oParam2);
con.Open();
try
{
com.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
//DtcBrokerName = string.Empty;
//PortiaBrokerName = string.Empty;
//ExecBrokerId = string.Empty;
return null;
}
private object RemoveAccount(object param)
{
DialogResult result = (DialogResult)System.Windows.MessageBox.Show("Are you sure you want to remove this account ?", "Message", MessageBoxButton.YesNoCancel, MessageBoxImage.Question);
if (result==DialogResult.Yes)
{
Account account = FocusedRow as Account;
string dtcAccount = account.DtcAccountNum;
string portiaAccount = account.PortiaAccountNum;
if (!string.IsNullOrEmpty(dtcAccount) && !string.IsNullOrEmpty(portiaAccount))
{
using (SybaseConnection con = new SybaseConnection(model.Constants.SybaseConnection))
{
using (SybaseCommand com = new SybaseCommand(model.Constants.DeleteAccount, con) { CommandType = CommandType.Text })
{
SybaseParameter oParam = new SybaseParameter("@dtcAccountNumber", SybaseDbType.VarChar);
oParam.Value = dtcAccount;
com.Parameters.Add(oParam);
SybaseParameter oParam1 = new SybaseParameter("@portiaAccountNumber", SybaseDbType.VarChar);
oParam1.Value = portiaAccount;
com.Parameters.Add(oParam1);
con.Open();
try
{
com.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
PopulateDtcAccount();
PopulatePortiaAccount();
LstAccounts = GetAccountMapping(dtcAccount, portiaAccount);
NotifyPropertyChanged("LstAccounts");
}
//DtcAccountNum = string.Empty;
//PortiaAccountNum = string.Empty;
return null;
}
private object AddAccount(object param)
{
string dtcAccount = ((Tuple<string, string>)param).Item1;
string portiaAccount = ((Tuple<string, string>)param).Item2;
if (!string.IsNullOrEmpty(dtcAccount) && !string.IsNullOrEmpty(portiaAccount))
{
using (SybaseConnection con = new SybaseConnection(model.Constants.SybaseConnection))
{
using (SybaseCommand com = new SybaseCommand(model.Constants.InsertAccount, con) { CommandType = CommandType.Text })
{
SybaseParameter oParam = new SybaseParameter("@dtcAccountNumber", SybaseDbType.VarChar);
oParam.Value = dtcAccount;
com.Parameters.Add(oParam);
SybaseParameter oParam1 = new SybaseParameter("@portiaAccountNumber", SybaseDbType.VarChar);
oParam1.Value = portiaAccount;
com.Parameters.Add(oParam1);
con.Open();
try
{
com.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
PopulateDtcAccount();
PopulatePortiaAccount();
//DtcAccountNum = string.Empty;
//PortiaAccountNum = string.Empty;
return null;
}
private ObservableCollection<Account> GetAccountMapping(string dtcAccount, string portiaAccount)
{
lstAccount = new ObservableCollection<Account>();
using (SybaseConnection con = new SybaseConnection(model.Constants.SybaseConnection))
{
using (SybaseCommand com = new SybaseCommand(model.Constants.SelectAccountMapping, con) { CommandType = CommandType.Text })
{
SybaseParameter oCom = new SybaseParameter("@portiaAccountNumber", SybaseDbType.VarChar);
oCom.Value = portiaAccount;
com.Parameters.Add(oCom);
oCom = new SybaseParameter("@dtcAccountNumber", SybaseDbType.VarChar);
oCom.Value = dtcAccount;
com.Parameters.Add(oCom);
con.Open();
try
{
using (SybaseDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
lstAccount.Add(new Account(Convert.ToInt32(reader["id"].ToString()), reader["portiaAccountNumber"].ToString(),
reader["dtcAccountNumber"].ToString(),
Convert.ToDateTime(reader["modifiedDate"].ToString()),
reader["modifiedBy"].ToString()
));
}
}
}
catch (Exception ex)
{
}
}
}
return lstAccount;
}
private ObservableCollection<PortiaAccount> GetPortiaAccounts()
{
lstPortiaAccount = new ObservableCollection<PortiaAccount>();
try
{
using (SybaseConnection con = new SybaseConnection(model.Constants.SybaseConnection))
{
using (SybaseCommand com = new SybaseCommand(model.Constants.SelectPortiaAccount, con) { CommandType = CommandType.Text })
{
con.Open();
try
{
using (SybaseDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
lstPortiaAccount.Add(new PortiaAccount(reader["portiaAccountNumber"].ToString()));
}
}
}
catch (Exception ex)
{
Utility.PostMessage("Account Mapping Portia", ex.InnerException.ToString(), Mcm.Logger.Log.Level.Error);
return null;
}
}
}
}
catch (Exception ex)
{
Utility.PostMessage("Account Mapping Portia", ex.InnerException.ToString(), Mcm.Logger.Log.Level.Error);
return null;
}
return lstPortiaAccount;
}