private void PopulateTables()
{
OleDbConnection con = null;
SqlConnection conSQL = null;
SQLiteConnection conSQLite = null;
OracleConnection conOracle = null;
if (rdbAccess.Checked)
{
con = OpenOleConnection(txtFileName.Text);
if (con == null) return;
}
else if (rdbSQL.Checked)
{
conSQL = OpenSQLConnection();
if (conSQL == null) return;
}
else if (rdbSQLite.Checked)
{
conSQLite = OpenSQLiteConnection(txtFileName.Text);
if (conSQLite == null) return;
}
else if (rdbOracle.Checked)
{
conOracle = OpenOracleConnection();
if (conOracle == null) return;
}
try
{
DataTable schemaTable = null;
DataRow[] schemaRows = null;
string user="";
if (rdbAccess.Checked)
{
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, null });
schemaRows = schemaTable.Select("1 = 1", "TABLE_TYPE, TABLE_NAME");
}
else if (rdbSQL.Checked)
{
schemaTable = conSQL.GetSchema("Tables");
schemaRows = schemaTable.Select("1 = 1", "TABLE_TYPE, TABLE_NAME");
}
else if (rdbSQLite.Checked)
{
schemaTable = conSQLite.GetSchema("Tables");
schemaRows = schemaTable.Select("1 = 1", "TABLE_TYPE, TABLE_NAME");
}
else if (rdbOracle.Checked)
{
schemaTable = conOracle.GetSchema("Tables");
int i1 = conOracle.ConnectionString.IndexOf("User Id=") + 8;
int i2 = conOracle.ConnectionString.IndexOf(";") ;
int l = i2 - i1;
user = conOracle.ConnectionString.Substring(i1, l);
schemaRows = schemaTable.Select(string.Format("OWNER = '{0}'",user), "TYPE, TABLE_NAME");
//foreach (DataRow r in schemaTable.Rows)
//{
// if( r[""]
//}
}
if (rdbAccess.Checked || rdbSQL.Checked || rdbSQLite.Checked)
{
#region Xử lý cho access, SQL, SQLite
foreach (DataRow row in schemaRows)
{
DBTable table = new DBTable();
table.Name = row["TABLE_NAME"].ToString();
table.Type = row["TABLE_TYPE"].ToString().Replace("BASE ", string.Empty);
table.IsView = "VIEW".Equals(table.Type, StringComparison.CurrentCultureIgnoreCase);
// table.IsFull = chkIsFull.Checked;
bool IsTable = table.Type.Equals("TABLE", StringComparison.CurrentCultureIgnoreCase);
if (IsTable || table.IsView)
{
if (!(bsTables.Contains(table) || bsClasses.Contains(table))) bsTables.Add(table);
string[] objTable;
string[] objKeys;
objTable = new string[] { null, null, table.Name, null };
objKeys = new string[] { null, null, table.Name };
DataTable tableSchema = null;
if (rdbAccess.Checked)
tableSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objTable);
else if (rdbSQL.Checked)
tableSchema = clsDalADO.GetTableSchema(table.Name, conSQL);
else
tableSchema = clsDalADOLite.GetTableSchema(table.Name);
List<string> htKeys = new List<string>();
if (IsTable)
{
if (rdbAccess.Checked)
{
DataTable tableKey = null;
tableKey = con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, objKeys);
foreach (DataRow fieldrow in tableKey.Rows)
{
htKeys.Add(fieldrow["COLUMN_NAME"].ToString());
}
}
else if (rdbSQL.Checked)
{
DataRow[] drs = tableSchema.Select("ISPK=1", "ORDINAL_POSITION ASC");
foreach (DataRow fieldrow in drs)
{
htKeys.Add(fieldrow["COLUMN_NAME"].ToString());
}
}
else
{
//tableKey = conSQLite.GetSchema("ForeignKeys", objKeys);
}
}
foreach (DataRow fieldrow in tableSchema.Select("1 = 1", "ORDINAL_POSITION ASC"))
{
DBField Field = new DBField();
Field.Name = fieldrow["COLUMN_NAME"].ToString();
Field.DBType = fieldrow["DATA_TYPE"].ToString();
Field.IsNullAble = clsAll.ConvertToBool(fieldrow["IS_NULLABLE"].ToString()); ;
//TuyenHM: Chỉnh sửa, vẫn phải sử dụng kiểu bit cho phép null
//if (Field.DBType == "bit")
// Field.IsNullAble = false;
if (rdbAccess.Checked)
Field.DBTypeName = clsDalOLE.GetOleDbType(Field.DBType).ToString();
else if (rdbSQL.Checked)
Field.DBTypeName = clsDalADO.GetSqlDbType(Field.DBType).ToString();
else if (rdbSQLite.Checked)
Field.DBTypeName = clsDalADOLite.GetDbType(Field.DBType).ToString();
Field.MaxLength = fieldrow["CHARACTER_MAXIMUM_LENGTH"].ToString();
Field.Type = GetFieldTypeName(Field.DBType, Field.IsNullAble, Field.MaxLength);
Field.HasDefault = clsAll.ConvertToBool(fieldrow["COLUMN_HASDEFAULT"].ToString());
Field.Default = fieldrow["COLUMN_DEFAULT"].ToString().Replace("(", string.Empty).Replace(")", string.Empty).Replace("N'", string.Empty).Replace("'", string.Empty);
if ((Field.DBType == "11" || Field.DBType == "bit") && Field.HasDefault)
Field.Default = clsAll.ConvertToBool(Field.Default).ToString().ToLower();
Field.Description = fieldrow["DESCRIPTION"].ToString();
if (rdbAccess.Checked)
{
Field.IsAuto = false; //(Field.OleType == 3 && Field.OleFlag == 122);
}
else
{
Field.IsAuto = clsAll.ConvertToBool(fieldrow["ISIDENTITY"].ToString());
if (!Field.IsAuto) Field.IsAuto = clsAll.ConvertToBool(fieldrow["ISCOMPUTED"].ToString());
}
if (IsTable)
Field.IsKey = htKeys.Contains(Field.Name);
else
Field.IsKey = false;
table.AddField(Field);
}
}
}
#endregion
}
else
{
#region Xử lý cho Oracle
foreach (DataRow row in schemaRows)
{
//BỎ QUA NHỮNG TABLE CỦA HỆ THỐNG
if (row["TABLE_NAME"].ToString().IndexOf("$")>-1) continue;
DBTable table = new DBTable();
table.Name = row["TABLE_NAME"].ToString();
table.Type = row["TYPE"].ToString().Replace("BASE ", string.Empty);
table.IsView = "VIEW".Equals(table.Type, StringComparison.CurrentCultureIgnoreCase);
//table.IsFull = chkIsFull.Checked;
bool IsTable = table.Type.Equals("USER", StringComparison.CurrentCultureIgnoreCase);
if (IsTable || table.IsView)
{
if (!(bsTables.Contains(table) || bsClasses.Contains(table))) bsTables.Add(table);
string[] objTable;
string[] objKeys;
objTable = new string[] { null, null, table.Name, null };
objKeys = new string[] { null, null, table.Name };
DataTable tableSchema = null;
if (rdbOracle.Checked)
tableSchema = clsDalORACLE.GetTableSchema(table.Name,user, conOracle);
List<string> htKeys = new List<string>();
if (IsTable)
{
if (rdbOracle.Checked)
{
DataRow[] drs = tableSchema.Select("ISPK=1", "ORDINAL_POSITION ASC");
foreach (DataRow fieldrow in drs)
{
htKeys.Add(fieldrow["COLUMN_NAME"].ToString());
}
}
else
{
//tableKey = conSQLite.GetSchema("ForeignKeys", objKeys);
}
}
foreach (DataRow fieldrow in tableSchema.Select("1 = 1", "ORDINAL_POSITION ASC"))
{
DBField Field = new DBField();
Field.Name = fieldrow["COLUMN_NAME"].ToString();
Field.DBType = fieldrow["DATA_TYPE"].ToString();
Field.IsNullAble = clsAll.ConvertToBool(fieldrow["IS_NULLABLE"].ToString()); ;
//TuyenHM: Chỉnh sửa, vẫn phải sử dụng kiểu bit cho phép null
//if (Field.DBType == "bit")
// Field.IsNullAble = false;
if (rdbOracle.Checked)
Field.DBTypeName = clsDalORACLE.GetOracleType(Field.DBType).ToString();
Field.MaxLength = fieldrow["CHARACTER_MAXIMUM_LENGTH"].ToString();
Field.Type = GetFieldTypeName(Field.DBType, Field.IsNullAble, Field.MaxLength);
Field.HasDefault = clsAll.ConvertToBool(fieldrow["COLUMN_HASDEFAULT"].ToString());
Field.Default = fieldrow["COLUMN_DEFAULT"].ToString().Replace("(", string.Empty).Replace(")", string.Empty).Replace("N'", string.Empty).Replace("'", string.Empty);
if ((Field.DBType == "11" || Field.DBType == "bit") && Field.HasDefault)
Field.Default = clsAll.ConvertToBool(Field.Default).ToString().ToLower();
Field.Description = fieldrow["DESCRIPTION"].ToString();
//if (rdbAccess.Checked)
//{
// Field.IsAuto = false; //(Field.OleType == 3 && Field.OleFlag == 122);
//}
//else
//{
// Field.IsAuto = clsAll.ConvertToBool(fieldrow["ISIDENTITY"].ToString());
// if (!Field.IsAuto) Field.IsAuto = clsAll.ConvertToBool(fieldrow["ISCOMPUTED"].ToString());
//}
//TUYENHM
//CHƯA CHƠI ĐƯỢC TỰ TĂNG VỚI ORACLE
Field.IsAuto = false;
if (IsTable)
Field.IsKey = htKeys.Contains(Field.Name);
else
Field.IsKey = false;
table.AddField(Field);
}
}
}
#endregion
}
//if (rdbSQL.Checked)
// PopulateSPs(conSQL);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally {
if (con != null) con.Close();
if (conSQL != null) conSQL.Close();
if (conSQLite != null) conSQLite.Close();
if (conOracle != null) conOracle.Close();
}
}
private void PopulateSPs(SqlConnection conSQL)
{
DataTable tableSchema = null;
tableSchema = clsDalADO.GetSPParams(conSQL);
DBTable sp = null;
string SPName = string.Empty;
foreach (DataRow fieldrow in tableSchema.Rows)
{
string spN = fieldrow["SPECIFIC_NAME"].ToString();
if (!spN.Equals(SPName, StringComparison.CurrentCultureIgnoreCase))
{
if (sp != null)
GetSPSchema(conSQL, sp);
SPName = spN;
sp = new DBTable();
sp.Name = SPName;
sp.Type = "SP";
sp.IsView = false;
sp.IsSP = true;
// sp.IsFull = chkIsFull.Checked;
if (!(bsTables.Contains(sp) || bsClasses.Contains(sp))) bsTables.Add(sp);
}
DBField Param = new DBField();
Param.Name = fieldrow["PARAMETER_NAME"].ToString().Replace("@", string.Empty);
Param.IsNullAble = true;
Param.DBType = fieldrow["DATA_TYPE"].ToString();
Param.DBTypeName = clsDalADO.GetSqlDbType(Param.DBType).ToString();
Param.Mode = fieldrow["PARAMETER_MODE"].ToString();
Param.MaxLength = fieldrow["CHARACTER_MAXIMUM_LENGTH"].ToString();
Param.Type = GetFieldTypeName(Param.DBType, Param.IsNullAble, Param.MaxLength);
sp.AddParam(Param);
}
if (sp != null)
GetSPSchema(conSQL, sp);
}
public void AddParam(DBField param)
{
m_Params.Add(param);
}
private void GetSPSchema(SqlConnection conSQL, DBTable sp)
{
List<IDbDataParameter> lstParam=new List<IDbDataParameter>();
foreach (DBField f in sp.Params)
{
ParameterDirection drt = ParameterDirection.Input;
switch (f.Mode.ToUpper())
{
case "IN":
drt = ParameterDirection.Input;
break;
case "OUT":
drt = ParameterDirection.Output;
break;
case "INOUT":
drt = ParameterDirection.InputOutput;
break;
}
IDbDataParameter param = clsDalADO.CreateParameter(f.Name, f.DBTypeName, DBNull.Value, drt);
lstParam.Add(param);
}
DataTable dtShema = clsDalADO.GetSPSchema(sp.Name, conSQL, lstParam);
if (dtShema != null && dtShema.Rows.Count > 0)
{
foreach (DataRow fieldrow in dtShema.Rows)
{
DBField field = new DBField();
field.Name = fieldrow["ColumnName"].ToString();
field.IsNullAble = true;
field.DBType = fieldrow["DataTypeName"].ToString();
field.DBTypeName = clsDalADO.GetSqlDbType(field.DBType).ToString();
field.MaxLength = fieldrow["ColumnSize"].ToString();
field.Type = GetFieldTypeName(field.DBType, field.IsNullAble, field.MaxLength);
sp.AddField(field);
}
}
}
public void AddField(DBField field)
{
m_Fields.Add(field);
}