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