private void button2_Click_2(object sender, EventArgs e)
{
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataSet tester
{
DTAcctLog.Rows.Clear(); //doesn't affect the update.
conn.Open(); //open connection from using block
SADataAdapter da = new SADataAdapter("Select * from acctlog", conn); //create a new data adapter. I don't know what's special about this.
SACommandBuilder cb = new SACommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges; //cheaty bullshit for just overpowering the conflict changes negating the concurrency violation.
da.Fill(DTAcctLog); //you have to fill it to update it.
DTAcctLog.Rows[4]["repuid"] = 2000042;
DTAcctLog.Rows[5]["repuid"] = 2000032;
DTAcctLog.Rows[6]["repuid"] = 2000012;
DTAcctLog.Rows[7]["repuid"] = 2000015;
DTAcctLog.Rows[8]["repuid"] = 2000017;
DTAcctLog.Rows[9]["repuid"] = 2000025;
da.UpdateCommand = cb.GetUpdateCommand();
da.Update(DTAcctLog);
DTAcctLog.AcceptChanges();
dataGridView1.DataSource = DTAcctLog;
//da.Fill(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message + "\n\n" + ex.ToString());
}
}
private void button1_Click_1(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataSet tester
{
conn.Open(); //open connection from using block
SADataAdapter da = new SADataAdapter(); //create a new data adapter. I don't know what's special about this.
da.SelectCommand = new SACommand("Select * from AcctLog", conn);
dataGridView1.DataSource = DTAcctLog;
SACommandBuilder cb = new SACommandBuilder(da);
da.Fill(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message);
}
finally
{
dataGridView1.Refresh();
}
}
public int ExecuteNonSPQuery(string _sql, List <SQLParam> _parameters)
{
int _retValue = -1;
SAConnection _conn = null;
try
{
_conn = this.CreateConnection();
SACommand _cmd = this.CreateCommand(CommandType.Text, _conn, _sql, _parameters);
_retValue = _cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string _err = string.Format("SQLAnywhereProvider.ExecuteNonSPQuery error: {0}", ex);
throw new Exception(_err);
}
finally
{
this.Dispose(_conn);
}
return(_retValue);
}
public bool inserirCandidatoEleicao(int idEleicao, int idCandidato)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL inserirCandidatoEleicao(\"idCandidato\" = ?, \"idEleicao\"= ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Integer;
param1.Direction = ParameterDirection.Input;
param1.Value = idCandidato;
cmd.Parameters.Add(param1);
SAParameter param2 = cmd.CreateParameter();
param2.SADbType = SADbType.Integer;
param2.Direction = ParameterDirection.Input;
param2.Value = idEleicao;
cmd.Parameters.Add(param2);
SADataReader reader = cmd.ExecuteReader();
return(true);
}
catch (Exception ex)
{
conn.Close();
return(false);
}
}
// Execute a SQL select statement using an existing DB connection
internal static bool ExecuteSelect(SAConnection _conn, string SqlStatement, bool ErrMsg,
string table, string CallingClass, string CallingFunction,
out SADataReader myDataReader)
{
bool RetVal = true;
myDataReader = null;
try
{
SACommand cmd = new SACommand(SqlStatement, _conn);
myDataReader = cmd.ExecuteReader();
}
catch (Exception ex)
{
RetVal = false;
if (ErrMsg)
{
string err = String.Format(StringTable.GetString("DatabaseError"),
table, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, CallingClass, CallingFunction, err);
}
}
return(RetVal);
}
//Added for CSharp Station Report Wizard CR13
public static bool TestDBConnection(bool bLocalDB)
{
SAConnection _conn = null;
bool RetVal = false;
try
{
_conn = bLocalDB ? MainClass.GetConnection() : MainClass.GetMasterConnection();
_conn.Open();
RetVal = (ConnectionState.Open == _conn.State);
}
catch (Exception ex)
{
RetVal = false;
}
finally
{
if (_conn != null)
{
_conn.Close();
}
}
return(RetVal);
}
public JsonResult apagarEleicao(int idEleicao)
{
// CALL "dev"."listarEleitores"("idCaderno" = 4)
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL apagarEleicao(\"idEleicao\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Integer;
param.Direction = ParameterDirection.Input;
param.Value = idEleicao;
cmd.Parameters.Add(param);
SADataReader reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
return(Json(true));
}
catch (SAException ex)
{
conn.Close();
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
return(Json(false));
}
}
DataTable IPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable dt = cn.GetSchema("Tables", new string[] { null, null, "VIEW" });
metaData = context.CreateTablesDataTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
DataRow dtRow = dt.Rows[i];
row["TABLE_CATALOG"] = cn.Database;
row["TABLE_SCHEMA"] = dtRow["TABLE_SCHEMA"];
row["TABLE_NAME"] = dtRow["TABLE_NAME"];
}
}
}
catch { }
return(metaData);
}
public JsonResult guardarPartido(string nomePartido)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL guardarPartido(\"nomePartido\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Text;
param.Direction = ParameterDirection.Input;
param.Value = nomePartido;
cmd.Parameters.Add(param);
SADataReader reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
return(Json(true));
}
catch (SAException ex)
{
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
conn.Close();
return(Json(false));
}
}
public static void DBConnect()
{
if (BaseTableAdapter.conn == null)
{
Logger filelogger = new FileLogger("DB");
LogManager.GetInstance().AddLogger(filelogger, "DB", false);
string connstring = ConfigurationManager.ConnectionStrings["SM_Remote_DB_Conn"].ConnectionString.Trim();
if (connstring == null)
{
System.Windows.Forms.MessageBox.Show(MissDBParam);
throw(new Exception(MissDBParam));
}
try
{
BaseTableAdapter.conn = new SAConnection(ConfigurationManager.ConnectionStrings["SM_Remote_DB_Conn"].ConnectionString.Trim());
BaseTableAdapter.conn.Open();
}
catch (Exception e)
{
string errstr = String.Format("{0} ConnectionString={1}", WrongDBParam, connstring);
System.Windows.Forms.MessageBox.Show(errstr);
throw (e);
}
}
}
static internal IDbCommand CreateCommand(string commandText, string connStr)
{
SAConnection cn = new SAConnection(connStr);
return(new SACommand(commandText, cn));
//IDbCommand cmd = _appDomain.CreateInstanceAndUnwrap
//(
// "iAnywhere.Data.SQLAnywhere," + GetAssemblyVersion(connStr),
// "iAnywhere.Data.SQLAnywhere.SACommand",
// false,
// BindingFlags.OptionalParamBinding,
// null,
// new object[] { commandText },
// null,
// null,
// null
//) as IDbCommand;
//IDbConnection cn = SybasePlugin.CreateConnection(connStr);
//cmd.Connection = cn;
//cn.Open();
//return cmd;
}
public ActionResult electionMain()
{
if (Session["UserID"] != null)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
conn.Open();
SACommand cmd = new SACommand("listarCandidatos", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <Candidato> listaCandidatos = new List <Candidato>();
while (reader.Read())
{
listaCandidatos.Add(new Candidato {
id = reader.GetInt32(0), nomeCandidato = reader.GetString(1), nomePartido = reader.GetString(2)
});
}
reader.Close();
conn.Close();
ViewBag.listaCandidatos = listaCandidatos;
return(View());
}
else
{
return(RedirectToAction("Login"));
}
}
public SACommand CreateAddProductCommand(SAConnection conn, SATransaction transaction)
{
var cmd = new SACommand(_sql, conn, transaction);
CreateParameters(cmd);
return cmd;
}
public DataSet ExecuteDataSet(string _procName, List <SQLParam> _parameters)
{
DataSet _ds = new DataSet();
SAConnection _conn = null;
try
{
_conn = this.CreateConnection();
SACommand _cmd = this.CreateCommand(CommandType.Text, _conn, _procName, _parameters);
SADataAdapter _adp = new SADataAdapter(_cmd);
_adp.Fill(_ds);
}
catch (Exception ex)
{
string _err = string.Format("SQLAnywhereProvider.ExecuteDataSet error: {0}", ex);
throw new Exception(_err);
}
finally
{
this.Dispose(_conn);
}
return(_ds);
}
public DataTable GetData(CommandType type, string sql, List <SQLParam> parms)
{
DataTable dt = new DataTable();
SAConnection _conn = null;
try
{
_conn = this.CreateConnection();
SACommand _cmd = this.CreateCommand(type, _conn, sql, parms);
SADataAdapter _adp = new SADataAdapter(_cmd);
_adp.Fill(dt);
}
catch (Exception ex)
{
string _err = string.Format("SQLAnywhereProvider.ExecuteDataSet error: {0}", ex);
throw new Exception(_err);
}
finally
{
this.Dispose(_conn);
}
return(dt);
}
private void SetConnection(IDbConnection value)
{
if (_connection != value)
{
_connection = (SAConnection)value;
}
}
public IDbConnection GetConnection()
{
var conn = new SAConnection(Connstr);
conn.Open();
return(conn);
}
public IDbConnection GetConnection(string strConn)
{
var conn = new SAConnection(strConn);
conn.Open();
return(conn);
}
public JsonResult mostrarListaPartidos()
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("listarPartidos", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <Partido> listaPartidos = new List <Partido>();
while (reader.Read())
{
listaPartidos.Add(new Partido {
id = reader.GetInt32(0), nome = reader.GetString(1)
});
}
reader.Close();
conn.Close();
return(Json(listaPartidos));
}
catch (SAException ex)
{
conn.Close();
return(Json(false));
}
}
private SAConnection CreateConnection()
{
SAConnection conn = new SAConnection(ConnectionName);
conn.Open();
return(conn);
}
public JsonResult listarCaderno()
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("listarCaderno", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <cadernoEleitoral> cadernoEleitorals = new List <cadernoEleitoral>();
while (reader.Read())
{
cadernoEleitorals.Add(new cadernoEleitoral {
id = Int32.Parse(reader.GetString(0)), periodo = reader.GetString(1)
});
}
reader.Close();
conn.Close();
return(Json(cadernoEleitorals));
}
catch (SAException ex)
{
conn.Close();
return(Json(false));
}
}
public static SAConnection GetMasterConnection()
{
SAConnection RetConnection = null;
RetConnection = new SAConnection("DSN=pSTARMAIN");
RetConnection.InitString = OEMAUTHENTICATION;
return(RetConnection);
}
public void Connect()
{
if (_connection != null)
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
}
else
{
if (_connectionString != String.Empty)
{
StringCollection initKeys = new StringCollection();
// ToDo - Figure this out
initKeys.AddRange(new string[]
{
"ARITHABORT", "ANSI_NULLS", "ANSI_WARNINGS", "ARITHIGNORE", "ANSI_DEFAULTS",
"ANSI_NULL_DFLT_OFF", "ANSI_NULL_DFLT_ON", "ANSI_PADDING", "ANSI_WARNINGS"
});
StringBuilder initStatements = new StringBuilder();
StringBuilder connectionString = new StringBuilder();
Hashtable attribs = this.ParseConfigString(_connectionString);
foreach (string key in attribs.Keys)
{
if (initKeys.Contains(key.Trim().ToUpper()))
{
initStatements.AppendFormat("SET {0} {1};", key, attribs[key]);
}
else if (key.Trim().Length > 0)
{
connectionString.AppendFormat("{0}={1};", key, attribs[key]);
}
}
_connection = new SAConnection(connectionString.ToString());
_connection.Open();
if (initStatements.Length > 0)
{
SACommand cmd = new SACommand();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = initStatements.ToString();
cmd.Connection = _connection;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
else
{
throw new InvalidOperationException(
"You must set a connection object or specify a connection string before calling Connect.");
}
}
}
public static SAConnection OpenConnection()
{
_dbConnection = new SAConnection("Eng =" + ConfigurationManager.AppSettings["dbEng"] + "; Uid = " + ConfigurationManager.AppSettings["dbUser"] + "; Pwd = sql; DBN =" + ConfigurationManager.AppSettings["dbn"] + "; Links = tcpip(Host = " + ConfigurationManager.AppSettings["dbIp"] + "); ");
if (_dbConnection.State == System.Data.ConnectionState.Closed)
{
_dbConnection.Open();
}
return(_dbConnection);
}
private static void SchemaTest()
{
SchemaProvider NewSchema = new SchemaProvider(new SAConnection("dsn=" + SchemaSourceDSN));
SAConnection ConnectionToOldDatabase = new SAConnection("dsn=" + SchemaDestDSN);
SchemaProvider OldSchema = new SchemaProvider(ConnectionToOldDatabase);
new SchemaChanger(OldSchema, NewSchema, ConnectionToOldDatabase).Upgrade();
}
// Return an SAConnection to the database
public static SAConnection GetConnection()
{
SAConnection RetConnection = null;
string strDSN = "DSN=PSTAR";
RetConnection = new SAConnection(strDSN);
//Sybase 16 OEM
RetConnection.InitString = OEMAUTHENTICATION;
return(RetConnection);
}
public static SADataReader GetReportData(string sqlStmt, out SAConnection _conn)
{
SADataReader myDataReader;
if (MainClass.ExecuteSelect(sqlStmt, true, TableName, "ReportTemp", "GetReportData", out _conn, out myDataReader))
{
return(myDataReader);
}
return(null);
}
// Execute an insert, delete, or update SQL statement
// Select statements should be done by the ExecuteSelect method
// INPUT:
// SqlStatement - the SQL statement to execute
// LogErrMsg - if true, write to error table if error occurs. Should always be true unless caller is LogErrorMsg itself.
// table - name of DB table being accessed (or something like "pocket join location")
// CallingClass - the calling class
// CallingFunction - the calling function
// Return value: true if successful
public static bool ExecuteSql(string SqlStatement, bool LogErrMsg,
string table, string CallingClass, string CallingFunction)
{
bool RetVal = true;
#if !NO_ASA
SAConnection _conn = null;
RetVal = ExecuteSql(false, SqlStatement, LogErrMsg, table, CallingClass, CallingFunction, out _conn);
#endif
return(RetVal);
}
private void Dispose(SAConnection _conn)
{
if (_conn != null)
{
if (_conn.State == ConnectionState.Open)
{
_conn.Close();
}
_conn.Dispose();
}
}
// Open a DB connection
static bool OpenDB(string CallingClass, string CallingFunction, out SAConnection _conn)
{
bool RetVal = false;
_conn = null;
string ErrMsg = "";
// GHB 6/23/08: Removed retry on DB for performance reasons on DB Down event
RetVal = TryOpenDB(out _conn, out ErrMsg);
return(RetVal);
}
public JsonResult listarEleicoes(bool isActive)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("listarEleicoes", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <Eleicao> listaEleicoes = new List <Eleicao>();
DateTime tmpInicio;
DateTime tmpFim;
DateTime today = DateTime.Now.Date;
while (reader.Read())
{
tmpInicio = reader.GetDateTime(2);
tmpFim = reader.GetDateTime(3);
if (isActive)
{
if (today >= tmpInicio && today < tmpFim)
{
listaEleicoes.Add(new Eleicao {
id = reader.GetInt32(0), nome = reader.GetString(1), inicio = reader.GetDateTime(2), fim = reader.GetDateTime(3)
});
}
}
else
{
listaEleicoes.Add(new Eleicao {
id = reader.GetInt32(0), nome = reader.GetString(1), inicio = reader.GetDateTime(2), fim = reader.GetDateTime(3)
});
}
}
reader.Close();
conn.Close();
return(Json(listaEleicoes));
}
catch (SAException ex)
{
conn.Close();
return(Json(false));
}
}
List <string> IPlugin.GetPrimaryKeyColumns(string database, string table)
{
IDataReader reader = null;
List <string> primaryKeys = new List <string>();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
string query = @"select cname from sys.syscolumns WHERE creator = '{0}' AND tname = '{1}' AND in_primary_key = 'Y' ORDER BY TNAME, COLNO";
query = string.Format(query, schema, table);
using (SACommand cmd = new SACommand(query, cn))
{
cn.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
primaryKeys.Add(reader.GetString(0));
}
}
}
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(primaryKeys);
}
public void CreateStoredProcedures()
{
using (var cn = new SAConnection(Properties.Settings.Default.ConnectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
var script = Regex.Split(Properties.Resources.DatabaseReset, @"^\s*;\s*$", RegexOptions.Multiline)
.Select(s=>s.Trim())
.Where(s=> !String.IsNullOrWhiteSpace(s));
foreach (var sql in script)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
}
}
List<string> IPlugin.GetPrimaryKeyColumns(string database, string table)
{
IDataReader reader = null;
List<string> primaryKeys = new List<string>();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
string query = @"select cname from sys.syscolumns WHERE creator = '{0}' AND tname = '{1}' AND in_primary_key = 'Y' ORDER BY TNAME, COLNO";
query = string.Format(query, schema, table);
using (SACommand cmd = new SACommand(query, cn))
{
cn.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
primaryKeys.Add(reader.GetString(0));
}
}
}
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return primaryKeys;
}
private void button1_Click_1(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataSet tester
{
conn.Open(); //open connection from using block
SADataAdapter da = new SADataAdapter(); //create a new data adapter. I don't know what's special about this.
da.SelectCommand = new SACommand("Select * from AcctLog", conn);
dataGridView1.DataSource = DTAcctLog;
SACommandBuilder cb = new SACommandBuilder(da);
da.Fill(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message);
}
finally
{
dataGridView1.Refresh();
}
}
private void button2_Click_2(object sender, EventArgs e)
{
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataSet tester
{
DTAcctLog.Rows.Clear(); //doesn't affect the update.
conn.Open(); //open connection from using block
SADataAdapter da = new SADataAdapter("Select * from acctlog", conn); //create a new data adapter. I don't know what's special about this.
SACommandBuilder cb = new SACommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges; //cheaty bullshit for just overpowering the conflict changes negating the concurrency violation.
da.Fill(DTAcctLog); //you have to fill it to update it.
DTAcctLog.Rows[4]["repuid"] = 2000042;
DTAcctLog.Rows[5]["repuid"] = 2000032;
DTAcctLog.Rows[6]["repuid"] = 2000012;
DTAcctLog.Rows[7]["repuid"] = 2000015;
DTAcctLog.Rows[8]["repuid"] = 2000017;
DTAcctLog.Rows[9]["repuid"] = 2000025;
da.UpdateCommand = cb.GetUpdateCommand();
da.Update(DTAcctLog);
DTAcctLog.AcceptChanges();
dataGridView1.DataSource = DTAcctLog;
//da.Fill(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message + "\n\n" + ex.ToString() );
}
}
DataTable IPlugin.GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable dt = new DataTable();
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
string query = "select * from sys.sysindexes where creator = '{0}' and indextype <> 'Foreign Key' and tname = '{0}'";
query = string.Format(query, schema, table);
SADataAdapter ad = new SADataAdapter(query, cn);
ad.Fill(dt);
metaData = context.CreateIndexesDataTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dtRow = dt.Rows[i];
string cols = dtRow["colnames"].ToString();
string[] columns = cols.Split(',');
foreach (string column in columns)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = table;
row["INDEX_NAME"] = dtRow["iname"];
row["PRIMARY_KEY"] = false;
switch(dtRow["indextype"].ToString().ToLower())
{
case "primary key":
row["PRIMARY_KEY"] = true;
row["UNIQUE"] = true;
break;
case "non-unique":
row["UNIQUE"] = false;
break;
case "unique":
row["UNIQUE"] = true;
break;
}
string[] columnData = column.Split(' ');
row["COLUMN_NAME"] = columnData[0];
if (columnData[1] == "ASC")
{
row["COLLATION"] = 1;
}
else
{
row["COLLATION"] = 2;
}
}
}
}
}
catch { }
return metaData;
}
private void button2_Click_1(object sender, EventArgs e) //send to DB
{
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataTable Tester
{
conn.Open();
SADataAdapter da = new SADataAdapter("select * from acctlog", conn);
SACommandBuilder cb = new SACommandBuilder(da);
da.Fill(DTAcctLog);
da.Update(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message);
}
}
static internal IDbCommand CreateCommand(string commandText, string connStr)
{
SAConnection cn = new SAConnection(connStr);
return new SACommand(commandText, cn);
//IDbCommand cmd = _appDomain.CreateInstanceAndUnwrap
//(
// "iAnywhere.Data.SQLAnywhere," + GetAssemblyVersion(connStr),
// "iAnywhere.Data.SQLAnywhere.SACommand",
// false,
// BindingFlags.OptionalParamBinding,
// null,
// new object[] { commandText },
// null,
// null,
// null
//) as IDbCommand;
//IDbConnection cn = SybasePlugin.CreateConnection(connStr);
//cmd.Connection = cn;
//cn.Open();
//return cmd;
}
DataTable IPlugin.GetForeignKeys(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
DataTable fks = cn.GetSchema(iAnywhere.Data.SQLAnywhere.SAMetaDataCollectionNames.MetaDataCollections);//, new string[] { table });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
DataTable dt = new DataTable();
string query = "select * from sys.sysforeignkeys where primary_creator = '{0}' and (primary_tname = '{1}' OR foreign_tname = '{2}')";
query = string.Format(query, schema, table, table);
SADataAdapter ad = new SADataAdapter(query, cn);
ad.Fill(dt);
metaData = context.CreateForeignKeysDataTable();
foreach (DataRow dtRow in dt.Rows)
{
string cols = (string)dtRow["columns"];
cols = cols.Replace(" IS ", ";");
string[] fkColumns = cols.Split(',');
foreach (string fkCol in fkColumns)
{
if (fkCol.Length == 0) break;
string[] fkCols = fkCol.Split(';');
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["FK_NAME"] = dtRow["role"];
row["PK_NAME"] = "Primary Key";
row["PK_TABLE_CATALOG"] = cn.Database;
row["PK_TABLE_SCHEMA"] = dtRow["primary_creator"];
row["PK_TABLE_NAME"] = dtRow["primary_tname"];
row["FK_TABLE_CATALOG"] = cn.Database;
row["FK_TABLE_SCHEMA"] = dtRow["foreign_creator"];
row["FK_TABLE_NAME"] = dtRow["foreign_tname"];
row["FK_COLUMN_NAME"] = fkCols[0];
row["PK_COLUMN_NAME"] = fkCols[1];
string pkQuery = "select iname from sys.sysindexes where creator = '{0}' and indextype = 'Primary key' and tname = '{1}'";
pkQuery = string.Format(pkQuery, schema, dtRow["primary_tname"]);
cn.Open();
using (SACommand pkCmd = new SACommand(pkQuery, cn))
{
row["PK_NAME"] = (string)pkCmd.ExecuteScalar();
cn.Close();
}
}
}
}
}
catch { }
return metaData;
}
public SchemaProvider ReadApplication(string ApplicationName, string ApplicationFolder, string exeName, string XPOConnectionString, string DBConnectionString)
{
IList<string> moduleList = new List<string>(){
"Para.Core.Modules.SystemModule.Win.dll",
"Para.Model.Definition.dll",
"Para.Modules.WorkflowLight.dll",
"Para.Core.Modules.SpellCheckerModule.dll",
"Para.Modules.App.dll",
"Para.Modules.App.Win.dll",
"Para.Modules.ABF.dll",
"Para.Modules.BH.dll",
"Para.Modules.RA.dll",
"Para.Modules.FV.dll",
"Para.Modules.Workflow.dll",
"Para.Modules.Document.Activities.dll",
"Para.Modules.Intern.Win.dll",
"Para.Modules.MV.dll"
};
IList<Assembly> assemblies = new List<Assembly>();
foreach (var file in moduleList)
assemblies.Add(Assembly.LoadFile(ApplicationFolder + file));
ControllersManager controllerManager = new ControllersManager();
ApplicationModulesManager man = new ApplicationModulesManager(controllerManager, ApplicationFolder);
man.AddModuleFromAssemblies(assemblies.Select(ass => ass.FullName).ToArray());
var sec = new SecurityDummy();
SAConnection conn = new SAConnection(DBConnectionString);
conn.Open();
XpoDefault.DataLayer = XpoDefault.GetDataLayer("XpoProvider=Asa;DataSourceName='paradat'", new ReflectionDictionary(), AutoCreateOption.None);
XPObjectSpaceProvider provider = new XPObjectSpaceProvider(new ConnectionStringDataStoreProvider(XPOConnectionString));
DesignerModelFactory dmf = new DesignerModelFactory();
string ApplicationFileName = ApplicationFolder + exeName;
string ApplicationConfigFileName = String.Format("{0}{1}.config", ApplicationFolder, exeName);
var app = dmf.CreateApplicationByConfigFile(ApplicationConfigFileName, ApplicationFileName, ref ApplicationFolder);
app.DatabaseVersionMismatch += app_DatabaseVersionMismatch;
app.Setup("paraOffice", provider, man, sec);
var dict = (app.CreateObjectSpace() as XPObjectSpace).Session.Dictionary;
SchemaProvider schemaProvider = new SchemaProvider();
foreach (var classinfo in dict.Classes.Cast<XPClassInfo>().Where(c => c.IsPersistent))
{
if (classinfo.Table.IsView)
{
schemaProvider.Views.Add(new FastSchemaProvider.View() { Name = classinfo.Table.Name });
continue;
}
var table = new Table();
table.ActualName = classinfo.Table.Name;
foreach (var column in classinfo.Table.Columns)
table.Columns.Add(new Column() { ActualName = column.Name, DataType = column.ColumnType.ToSchemaDbType(), MaxLength = column.Size, IsPrimaryKeyColumn = column.IsKey, IsIdentity = column.IsIdentity });
table.BuildPrimaryKey();
foreach (var index in classinfo.Table.Indexes)
{
var schemaIndex = new Index() { IndexName = index.Name, IndexType = index.IsUnique ? IndexTypes.Unqiue : IndexTypes.NonUnqiue };
int i = 1;
foreach (var col in index.Columns)
{
var indexCol = new IndexColumn();
indexCol.ColumnName = col;
indexCol.Sequence = i;
indexCol.Order = Ordering.Ascending;
i++;
schemaIndex.Columns.Add(indexCol);
}
table.Indizes.Add(schemaIndex);
}
foreach (var fk in classinfo.Table.ForeignKeys)
{
var foreignKey = new ForeignKey();
foreignKey.Name = fk.Name;
foreignKey.Columns.AddRange(fk.Columns.Cast<string>());
foreignKey.DetailTable = classinfo.TableName;
foreignKey.MasterTable = fk.PrimaryKeyTable;
foreignKey.UniqueColumns.AddRange(fk.PrimaryKeyTableKeyColumns.Cast<string>());
table.ForeignKeys.Add(foreignKey);
}
schemaProvider.Tables.Add(table);
}
return schemaProvider;
}
public static SAConnection GetConnection()
{
var conn = new SAConnection(_connectionString);
conn.Open();
return conn;
}
public static void DBConnect()
{
if (BaseTableAdapter.conn == null)
{
Logger filelogger = new FileLogger("DB");
LogManager.GetInstance().AddLogger(filelogger, "DB", false);
string connstring = ConfigurationManager.ConnectionStrings["SM_Remote_DB_Conn"].ConnectionString.Trim();
if (connstring == null)
{
System.Windows.Forms.MessageBox.Show(MissDBParam);
throw(new Exception(MissDBParam));
}
try
{
BaseTableAdapter.conn = new SAConnection(ConfigurationManager.ConnectionStrings["SM_Remote_DB_Conn"].ConnectionString.Trim());
BaseTableAdapter.conn.Open();
}
catch (Exception e)
{
string errstr = String.Format("{0} ConnectionString={1}", WrongDBParam, connstring);
System.Windows.Forms.MessageBox.Show(errstr);
throw (e);
}
}
}
DataTable IPlugin.GetTableColumns(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable dt = cn.GetSchema("Columns", new string[] { null, table, null });
metaData = context.CreateColumnsDataTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
DataRow dtRow = dt.Rows[i];
row["TABLE_CATALOG"] = cn.Database;
row["TABLE_SCHEMA"] = dtRow["TABLE_SCHEMA"];
row["TABLE_NAME"] = dtRow["TABLE_NAME"];
row["COLUMN_NAME"] = dtRow["COLUMN_NAME"];
row["ORDINAL_POSITION"] = dtRow["ORDINAL_POSITION"];
row["TYPE_NAME"] = dtRow["DATA_TYPE"];
if (dtRow["IS_NULLABLE"] != DBNull.Value)
{
string isNullable = dtRow["IS_NULLABLE"].ToString().ToUpper();
row["IS_NULLABLE"] = (isNullable == "N") ? false : true;
}
if (dtRow["COLUMN_DEFAULT"] != DBNull.Value)
{
row["COLUMN_HASDEFAULT"] = true;
row["COLUMN_DEFAULT"] = dtRow["COLUMN_DEFAULT"];
}
else
{
row["COLUMN_HASDEFAULT"] = false;
}
if (row["COLUMN_DEFAULT"] != DBNull.Value)
{
switch(row["COLUMN_DEFAULT"].ToString().ToLower())
{
case "autoincrement":
row["IS_AUTO_KEY"] = true;
row["AUTO_KEY_SEED"] = 0;
row["AUTO_KEY_INCREMENT"] = 1;
break;
case "current timestamp":
row["IS_COMPUTED"] = true;
break;
case "timestamp":
row["IS_COMPUTED"] = true;
row["IS_CONCURRENCY"] = true;
break;
}
}
long charMax = 0;
int precision = 0;
short scale = 0;
if (dt.Columns.Contains("CHARACTER_MAXIMUM_LENGTH") && dtRow["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
charMax = Convert.ToInt64(dtRow["CHARACTER_MAXIMUM_LENGTH"]);
}
if (dt.Columns.Contains("PRECISION") && dtRow["PRECISION"] != DBNull.Value)
{
precision = Convert.ToInt32(dtRow["PRECISION"]);
}
if (dt.Columns.Contains("SCALE") && dtRow["SCALE"] != DBNull.Value)
{
scale = Convert.ToInt16(dtRow["SCALE"]);
}
string datatype = (string)row["TYPE_NAME"];
switch (datatype)
{
case "decimal":
case "money":
case "numeric":
case "smallmoney":
row["NUMERIC_PRECISION"] = precision;
row["NUMERIC_SCALE"] = scale;
row["CHARACTER_MAXIMUM_LENGTH"] = 0;
row["TYPE_NAME_COMPLETE"] = datatype + "(" + precision.ToString() + "," + scale.ToString() + ")";
break;
case "char":
case "nchar":
case "ntext":
case "nvarchar":
case "sysname":
case "text":
case "uniqueIdentifierstr":
case "varchar":
case "xml":
row["NUMERIC_PRECISION"] = 0;
row["NUMERIC_SCALE"] = 0;
row["CHARACTER_MAXIMUM_LENGTH"] = charMax;
row["TYPE_NAME_COMPLETE"] = datatype + "(" + charMax.ToString() + ")";
break;
default:
row["NUMERIC_PRECISION"] = 0;
row["NUMERIC_SCALE"] = 0;
row["CHARACTER_MAXIMUM_LENGTH"] = 0;
row["TYPE_NAME_COMPLETE"] = datatype;
break;
}
}
}
}
catch { }
return metaData;
}
public void DropProductIndexes(SAConnection connection)
{
return;
}
DataTable IPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable dt = cn.GetSchema("Tables", new string[] { null, null, "VIEW" });
metaData = context.CreateTablesDataTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
DataRow dtRow = dt.Rows[i];
row["TABLE_CATALOG"] = cn.Database;
row["TABLE_SCHEMA"] = dtRow["TABLE_SCHEMA"];
row["TABLE_NAME"] = dtRow["TABLE_NAME"];
}
}
}
catch { }
return metaData;
}
public void CreateProductIndexes(SAConnection connection)
{
return;
}
public void TestBootStrapper()
{
con = new SAConnection("dsn=" + SchemaSourceDSN);
}
public void TestTearDown()
{
con.Close();
con.Dispose();
con = null;
}
