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 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 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();
}
}
//Added for CSharp Station Report Wizard CR13
internal static bool ExecuteSelect(string SqlStatement, bool ErrMsg,
string table, string CallingClass, string CallingFunction,
out SAConnection _conn, out SADataReader myDataReader, bool bLocalDB)
{
bool RetVal = true;
myDataReader = null;
_conn = null;
try
{
_conn = bLocalDB ? MainClass.GetConnection() : MainClass.GetMasterConnection();
_conn.Open();
RetVal = (ConnectionState.Open == _conn.State);
if (RetVal)
{
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);
}
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));
}
}
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));
}
}
//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);
}
private SAConnection CreateConnection()
{
SAConnection conn = new SAConnection(ConnectionName);
conn.Open();
return(conn);
}
public override bool Open()
{
try
{
if (_currentState != ConnectionState.Open)
{
_connection.Open();
_currentState = _connection.State;
}
}
catch (System.Exception e)
{
return(false);
}
return(true);
}
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);
}
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());
}
}
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 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);
}
}
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 override bool Open()
{
try
{
if (_currentState != ConnectionState.Open)
{
_connection.Open();
Connection = _connection;
_currentState = _connection.State;
}
}
catch (System.Exception e)
{
Logger.Error(e.Message);
return(false);
}
return(true);
}
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);
}
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 override IDbConnection OpenNewDbConnection()
{
IDbConnection connection = new SAConnection(_connectionString);
try
{
connection.Open();
}
catch (System.Exception ex)
{
string msg = "Error during opening a connection. Reason:" + ex.Message;
Logger.Info(msg);
throw new SqlExecutorException(msg);
}
return(connection);
}
public ActionResult Login(string login, string password)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("getAdmin", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
string dbUserName = "";
string dbPassword = "";
//List<Partido> listaPartidos = new List<Partido>();
while (reader.Read())
{
dbUserName = reader.GetString(0);
dbPassword = reader.GetString(1);
}
reader.Close();
conn.Close();
if (dbUserName == login && dbPassword == password)
{
Session["UserID"] = 1;
Session["UserName"] = "******";
ViewBag.Message = "";
return(RedirectToAction("Index"));
}
else
{
ViewBag.Message = "Login inválido!";
return(View());
}
// return Json(listaPartidos);
}
catch (SAException ex)
{
conn.Close();
return(RedirectToAction("Login"));
}
}
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();
}
}
}
}
// Open a DB connection
static bool TryOpenDB(out SAConnection _conn, out string ErrMsg)
{
bool RetVal = false;
ErrMsg = "";
_conn = null;
try
{
_conn = GetConnection();
_conn.Open();
RetVal = (ConnectionState.Open == _conn.State);
}
catch (Exception ex)
{
ErrMsg = ex.Message.ToString();
}
return(RetVal);
}
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);
}
}
public JsonResult listarEleitores(int idCaderno)
{
// CALL "dev"."listarEleitores"("idCaderno" = 4)
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL listarEleitores(\"idCaderno\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Integer;
param.Direction = ParameterDirection.Input;
param.Value = idCaderno;
cmd.Parameters.Add(param);
SADataReader reader = cmd.ExecuteReader();
List <cadernoEleitor> cadernoEleitores = new List <cadernoEleitor>();
while (reader.Read())
{
cadernoEleitores.Add(new cadernoEleitor {
nome = reader.GetString(0), cc = reader.GetString(1), isVoted = reader.GetBoolean(2)
});
}
reader.Close();
conn.Close();
return(Json(cadernoEleitores));
}
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 JsonResult loadBoletim(int idEleicao)
{
//carregarBoletim
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL carregarBoletim(\"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();
List <Boletim> itensBoletim = new List <Boletim>();
while (reader.Read())
{
itensBoletim.Add(new Boletim {
idCandidato = reader.GetInt32(0), nomeCandidato = reader.GetString(1), nomePartido = reader.GetString(2), idEleicao = idEleicao
});
}
reader.Close();
conn.Close();
return(Json(itensBoletim));
}
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 bool GetRecord(string tagId, out TableData data)
{
bool Retval = false;
data = null;
#if !NO_ASA
string SqlStatement = "SELECT * FROM LOT_SERIAL where tagIDDisplay = :tagId";
try
{
using (SAConnection con = MainClass.GetConnection())
{
con.Open();
using (SACommand cmd = new SACommand(SqlStatement, con))
{
cmd.Parameters.Add(new SAParameter("tagId", SADbType.VarChar, 64)).Value = tagId;
using (SADataReader sa = cmd.ExecuteReader())
{
if (sa.Read())
{
MakeDataRec(sa, out data);
Retval = true;
}
}
}
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + "=" + tagId + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
#endif
return(Retval);
}
public static bool ExecuteSql(string sql, bool throwException, params SAParameter[] list)
{
try
{
using (SAConnection con = MainClass.GetConnection())
{
con.Open();
using (SACommand cmd = new SACommand(sql, con))
{
cmd.Parameters.AddRange(list);
cmd.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception ex)
{
if (throwException)
{
throw ex;
}
}
return(false);
}
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 static SAConnection GetConnection()
{
var conn = new SAConnection(_connectionString);
conn.Open();
return conn;
}
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();
}
}
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);
}
}
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;
}
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 JsonResult checkEleitor(string nrCC, int idEleicao, string pin)
{
//checkEleitor"(IN nrCC VARCHAR(80), IN idEleicao INTEGER, OUT flag INTEGER
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL checkEleitor(\"nrCC\" = ?, \"idEleicao\"= ? , \"pinIN\" = ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Text;
param1.Direction = ParameterDirection.Input;
param1.Value = nrCC;
cmd.Parameters.Add(param1);
SAParameter param2 = cmd.CreateParameter();
param2.SADbType = SADbType.Integer;
param2.Direction = ParameterDirection.Input;
param2.Value = idEleicao;
cmd.Parameters.Add(param2);
SAParameter param3 = cmd.CreateParameter();
param3.SADbType = SADbType.Text;
param3.Direction = ParameterDirection.Input;
param3.Value = pin;
cmd.Parameters.Add(param3);
SAParameter _flag = cmd.CreateParameter();
_flag.SADbType = SADbType.Integer;
_flag.Direction = ParameterDirection.Output;
cmd.Parameters.Add(_flag);
SADataReader reader = cmd.ExecuteReader();
int flag = (int)_flag.Value;
reader.Close();
conn.Close();
if (flag == 1)
{
return(Json(true));
}
else
{
return(Json(false));
}
}
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 JsonResult guardarVoto(int idEleicao, int votos)
{
var escolha = new Voto();
escolha.idEleicao = idEleicao;
escolha.opcao = votos;
var json = JsonConvert.SerializeObject(escolha);
var source = json;
using (MD5 md5Hash = MD5.Create())
{
string hash = GetMd5Hash(md5Hash, source);
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL guardarBoletim(\"boletimTxt\" = ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Text;
param1.Direction = ParameterDirection.Input;
param1.Value = json;
cmd.Parameters.Add(param1);
//SAParameter param2 = cmd.CreateParameter();
//param2.SADbType = SADbType.Integer;
//param2.Direction = ParameterDirection.Input;
//param2.Value = idEleicao;
//cmd.Parameters.Add(param2);
SAParameter outP = cmd.CreateParameter();
outP.SADbType = SADbType.Integer;
outP.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outP);
SADataReader reader = cmd.ExecuteReader();
int idBoletim = (int)outP.Value;
reader.Close();
SACommand cmd2 = new SACommand("CALL guardarIntegridade(\"validCheck\" = ?, \"idBoletim\"= ? )", conn);
cmd2.CommandType = CommandType.Text;
SAParameter param3 = cmd.CreateParameter();
param3.SADbType = SADbType.Text;
param3.Direction = ParameterDirection.Input;
param3.Value = hash;
cmd2.Parameters.Add(param3);
SAParameter param4 = cmd.CreateParameter();
param4.SADbType = SADbType.Integer;
param4.Direction = ParameterDirection.Input;
param4.Value = idBoletim;
cmd2.Parameters.Add(param4);
SADataReader reader2 = cmd2.ExecuteReader();
reader2.Close();
conn.Close();
}
catch (SAException ex)
{
return(Json(false));
}
//Console.WriteLine("The MD5 hash of " + source + " is: " + hash + ".");
//Console.WriteLine("Verifying the hash...");
//if (VerifyMd5Hash(md5Hash, source, hash))
//{
// Console.WriteLine("The hashes are the same.");
//}
//else
//{
// Console.WriteLine("The hashes are not same.");
//}
}
return(Json(true));
}