private void btnShowTables_Click(object sender, System.EventArgs e) { try { this.Cursor = Cursors.WaitCursor; if (this.optSQL.Checked) { typeOfDb = TypeOfDatabase.SQL; } else { typeOfDb = TypeOfDatabase.Oralce; } lstAllTables.Items.Clear(); lstTables.Items.Clear(); ArrayList arr = TableDAO.GetTables(GetConnectionString(cbxDatabases.SelectedValue.ToString()), typeOfDb); for (int i = 0; i < arr.Count; i++) { lstAllTables.Items.Add(((Table)arr[i]).TableName); } this.Cursor = Cursors.Arrow; } catch (Exception ex) { MessageBox.Show("Khong ket noi duoc toi Database, hay kiem tra lai file Config !" + ex.ToString()); } }
public SessionConfig(TypeOfDatabase DatabaseType, string DatabaseConnectionString) : base(DatabaseType, DatabaseConnectionString) { this.current_user = null; this.CurDBEngine = null; this.CurDBEngine = new DatabaseEngine(DatabaseType, DatabaseConnectionString); this.CurDBEngine.Connect(); }
public LogManager(TypeOfDatabase DBType, string Conn) : base(DBType, Conn) { this.DataStructure = new Swordfish_v2_Core.CoreManagers.DataStructure(); this.IsReady = false; this.Conn = ""; this.CurSQLFactory = null; this.DBType = TypeOfDatabase.MSSQL; this.ErrMsg = ""; this.DBType = DBType; this.Conn = Conn; }
public LogManager(SessionConfig PrivateConfig) : base(PrivateConfig) { this.DataStructure = new Swordfish_v2_Core.CoreManagers.DataStructure(); this.IsReady = false; this.Conn = ""; this.CurSQLFactory = null; this.DBType = TypeOfDatabase.MSSQL; this.ErrMsg = ""; this.DBType = PrivateConfig.DatabaseType; this.Conn = PrivateConfig.DatabaseConnectionString; }
public static ArrayList GetTableRefColumn(string connStr, string tableName, TypeOfDatabase typeOfDb) { string refCol = string.Empty; StringBuilder sb = new StringBuilder(); ArrayList al = new ArrayList(); DataTable dt = new DataTable(); switch (typeOfDb) { case TypeOfDatabase.SQL: dt = DataProvider.FillDataSet(connStr, "exec sp_fkeys @fktable_name='" + tableName + "'").Tables[0]; break; case TypeOfDatabase.Oralce: sb.Append("SELECT a.TABLE_NAME FKTable_Name, a.COLUMN_NAME FKColumn_Name, " + "d.TABLE_NAME PKTable_Name, d.COLUMN_NAME PKColumn_Name " + "FROM user_cons_columns a join user_constraints b on a.constraint_name = b.constraint_name " + "join user_cons_columns d on B.R_CONSTRAINT_NAME = d.constraint_name " + "and a.POSITION = d.POSITION " + "WHERE b.constraint_type = 'R' " + " and a.TABLE_NAME='" + tableName + "'"); dt = DataProvider.FillDataSet(connStr, sb.ToString()).Tables[0]; break; } try { IEnumerator rows = dt.Rows.GetEnumerator(); while (rows.MoveNext()) { Column iCol = new Column(); DataRow row = (DataRow)rows.Current; iCol.ColumnName = row["FKColumn_Name"].ToString(); iCol.RefTable = row["PKTable_Name"].ToString(); iCol.RefColumn = row["PKColumn_Name"].ToString(); al.Add(iCol); } dt.Dispose(); } catch (Exception ex) { throw ex; } return(al); }
public static string GetTableSchema(string connStr, string tableName, TypeOfDatabase typeOfDb) { string schema = string.Empty; StringBuilder sb = new StringBuilder(); switch (typeOfDb) { case TypeOfDatabase.SQL: sb.Append("SELECT RTRIM(tbl.table_schema) AS TableSchema"); sb.Append(" FROM INFORMATION_SCHEMA.TABLES tbl"); sb.Append(" WHERE tbl.TABLE_NAME = '" + tableName + "'"); try { schema = DataProvider.ExecuteScalar(connStr, sb.ToString()).ToString(); } catch (Exception ex) { throw ex; } break; case TypeOfDatabase.Oralce: //Provider=msdaora;Data Source=KDT;User Id=binhh;Password=binhh char[] spliter1 = { ';' }; string[] ret1 = connStr.Split(spliter1); char[] spliter2 = { '=' }; string[] ret2 = ret1[2].Split(spliter2); try { //schema = OLEDBDataProvider.ExecuteScalar(connStr, sb.ToString()).ToString(); schema = ret2[1].ToString().Trim().ToUpper(); } catch (Exception ex) { throw ex; } break; } return(schema); }
public static ArrayList GetTableKeyColumn(string connStr, string tableName, TypeOfDatabase typeOfDb) { string identityCol = string.Empty; StringBuilder sb = new StringBuilder(); ArrayList al = new ArrayList(); DataTable dt = new DataTable(); switch (typeOfDb) { case TypeOfDatabase.SQL: ArrayList arr = new ArrayList(); arr.Add(new OleDbParameter("@Table_Name", tableName)); dt = DataProvider.FillDataSet(connStr, "sp_pkeys", arr).Tables[0]; break; case TypeOfDatabase.Oralce: sb.Append("SELECT COLUMN_NAME FROM user_cons_columns a, user_constraints b WHERE a.constraint_name = b.constraint_name AND b.constraint_type = 'P' and a.TABLE_NAME='" + tableName + "'"); dt = DataProvider.FillDataSet(connStr, sb.ToString()).Tables[0]; break; } try { //IEnumerator rows = dt.Rows.GetEnumerator(); for (int i = 0; i < dt.Rows.Count; i++) { Column iCol = new Column(); DataRow row = (DataRow)dt.Rows[i]; iCol.ColumnName = row["Column_Name"].ToString(); al.Add(iCol); } dt.Dispose(); } catch (Exception ex) { throw ex; } return(al); }
// accessors public static ArrayList GetTables(string connStr, TypeOfDatabase typeOfDb) { ArrayList al = new ArrayList(); DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder(); //SQL Provider switch (typeOfDb) { case TypeOfDatabase.SQL: sb.Append("SELECT DISTINCT RTRIM(tbl.table_name) AS TableName"); sb.Append(" FROM INFORMATION_SCHEMA.TABLES tbl"); sb.Append(" WHERE tbl.TABLE_TYPE = 'BASE TABLE'"); break; case TypeOfDatabase.Oralce: sb.Append("SELECT TABLE_NAME AS TableName "); sb.Append("FROM USER_TABLES"); break; } try { dt = DataProvider.FillDataSet(connStr, sb.ToString()).Tables[0]; IEnumerator rows = dt.Rows.GetEnumerator(); while (rows.MoveNext()) { Table dataType = new Table(); DataRow row = (DataRow)rows.Current; dataType.TableName = row["TableName"].ToString(); al.Add(dataType); } dt.Dispose(); } catch (Exception ex) { throw ex; } return(al); }
public static ArrayList GetTableIdentityColumn(string connStr, string tableName, TypeOfDatabase typeOfDb) { string identityCol = string.Empty; StringBuilder sb = new StringBuilder(); ArrayList al = new ArrayList(); DataTable dt = new DataTable(); if (typeOfDb == TypeOfDatabase.Oralce) { return(al); } ArrayList arr = new ArrayList(); dt = DataProvider.FillDataSet(connStr, "Select c.name as Column_Name from sysobjects o join syscolumns c on o.id=c.id where o.name='" + tableName + "' and not c.autoval is null").Tables[0]; try { if (dt.Rows.Count > 0) { IEnumerator rows = dt.Rows.GetEnumerator(); while (rows.MoveNext()) { Column iCol = new Column(); DataRow row = (DataRow)rows.Current; iCol.ColumnName = row["Column_Name"].ToString(); al.Add(iCol); } } dt.Dispose(); } catch (Exception ex) { throw ex; } return(al); }
public ApplicationUser Login(string uid, string pwd, bool is_admin, string conn, TypeOfDatabase DatabaseType, ref string ReturnMessage) { DatabaseParameters keyParameters = new DatabaseParameters(); keyParameters.Add(new DatabaseParameter(this.CurDataStructure.Tables.MasterUsers.FirstName.ActualFieldName, uid)); keyParameters.Add(new DatabaseParameter(this.CurDataStructure.Tables.MasterUsers.Password.ActualFieldName, pwd)); keyParameters.Add(new DatabaseParameter(this.CurDataStructure.Tables.MasterUsers.Status.ActualFieldName, "1")); DataTable table = this.QueryData(keyParameters, this.CurDataStructure.Tables.MasterUsers.ActualTableName); if ((table != null) && (table.Rows.Count == 1)) { return(this.FillUserData(table.Rows[0], true)); } ReturnMessage = base.CurDBEngine.ErrorMessage; return(null); }
// accessors public static ArrayList GetColumns(string connStr, string tableName, string tableSchema, TypeOfDatabase typeOfDb) { ArrayList al = new ArrayList(); DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder(); switch (typeOfDb) { case TypeOfDatabase.SQL: sb.Append("SELECT DISTINCT RTRIM(tbl.table_name) AS TableName"); sb.Append(", RTRIM(col.table_schema) AS TableSchema"); sb.Append(", RTRIM(col.column_name) AS ColumnName"); sb.Append(", RTRIM(col.IS_NULLABLE) AS AllowNull"); sb.Append(", col.ordinal_position AS OrdinalPosition"); sb.Append(", RTRIM(col.DATA_TYPE) AS DataType"); sb.Append(", ISNULL(RTRIM(CHARACTER_MAXIMUM_LENGTH), '') AS MaxLen"); sb.Append(", ISNULL(RTRIM(NUMERIC_PRECISION), '') AS [Precision]"); sb.Append(", ISNULL(RTRIM(NUMERIC_SCALE), '') AS Scale"); sb.Append(" FROM INFORMATION_SCHEMA.TABLES tbl"); sb.Append(" INNER JOIN INFORMATION_SCHEMA.Columns col ON col.TABLE_NAME = tbl.TABLE_NAME"); sb.Append(" WHERE tbl.TABLE_SCHEMA = '" + tableSchema + "' AND tbl.TABLE_TYPE = 'BASE TABLE'"); sb.Append(" AND tbl.table_name = '" + tableName + "'"); sb.Append(" ORDER BY col.ordinal_position"); break; case TypeOfDatabase.Oralce: sb.Append("SELECT TABLE_NAME as TableName, "); sb.Append("'TableSchema' as TableSchema, "); sb.Append("COLUMN_NAME as ColumnName, "); sb.Append("COLUMN_ID as OrdinalPosition, "); sb.Append("DATA_TYPE as DataType, "); sb.Append("decode(NULLABLE,'N','NO','Y','YES') as AllowNull, "); sb.Append("DATA_LENGTH as MaxLen, "); sb.Append("DATA_PRECISION as Precision, "); sb.Append("DATA_SCALE as Scale "); sb.Append("FROM USER_TAB_COLS WHERE TABLE_NAME = '" + tableName + "'"); break; } try { dt = DataProvider.FillDataSet(connStr, sb.ToString()).Tables[0]; IEnumerator rows = dt.Rows.GetEnumerator(); while (rows.MoveNext()) { Column dataType = new Column(); DataRow row = (DataRow)rows.Current; dataType.TableSchema = row["TableSchema"].ToString(); dataType.TableName = row["TableName"].ToString(); dataType.ColumnName = row["ColumnName"].ToString(); dataType.AllowNull = ((row["AllowNull"].ToString()).ToUpper() == "YES"?true:false); dataType.OrdinalPosition = row["OrdinalPosition"].ToString(); dataType.DataType = row["DataType"].ToString(); if (row["MaxLen"].ToString().Equals(string.Empty)) { dataType.MaxLen = 0; } else { dataType.MaxLen = Int32.Parse(row["MaxLen"].ToString()); } dataType.Length = Common.getLength(dataType); dataType.Precision = row["Precision"].ToString(); dataType.Scale = row["Scale"].ToString(); dataType.SQLDataType = Common.getSQLClientType(dataType); dataType.OracleDataType = Common.getOracleClientType(dataType); dataType.VBDataType = Common.convertTypesFromSql(dataType); dataType.JavaDataType = Common.convertToJavaTypesFromSql(dataType); dataType.Oracle2DBType = Common.getOracle2DBType(dataType); al.Add(dataType); } dt.Dispose(); ArrayList tmpTableKeyColumns = TableDAO.GetTableKeyColumn(connStr, tableName, typeOfDb); ArrayList tableRefColumns = TableDAO.GetTableRefColumn(connStr, tableName, typeOfDb); ArrayList tableIdentityColumns = TableDAO.GetTableIdentityColumn(connStr, tableName, typeOfDb); if (al.Count > 0) { //sap xep lai thu cu cot //keyColumn for (int z = 0; z < al.Count; z++) { //Primery key for (int t = 0; t < tmpTableKeyColumns.Count; t++) { if (((Column)tmpTableKeyColumns[t]).ColumnName.Equals(((Column)al[z]).ColumnName)) { ((Column)al[z]).IsPK = true; break; } } //Foreign Key for (int t = 0; t < tableRefColumns.Count; t++) { if (((Column)tableRefColumns[t]).ColumnName.Equals(((Column)al[z]).ColumnName)) { ((Column)al[z]).IsFK = true; ((Column)al[z]).RefTable = ((Column)tableRefColumns[t]).RefTable; ((Column)al[z]).RefColumn = ((Column)tableRefColumns[t]).RefColumn; break; } } //Identity Column for (int t = 0; t < tableIdentityColumns.Count; t++) { if (((Column)tableIdentityColumns[t]).ColumnName.Equals(((Column)al[z]).ColumnName)) { ((Column)al[z]).IsIdentity = true; break; } } } } } catch (Exception ex) { throw ex; } return(al); }