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 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; }
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; }