/// <summary>
/// Read columns schema from database
/// </summary>
private List<DbColumn> ReadColumns(String tableName, string ownerName)
{
List<DbColumn> result = new List<DbColumn>();
using (SqlDataAdapter adapter = new SqlDataAdapter(String.Format("SELECT TOP 1 * FROM [{0}].[{1}]", ownerName, tableName), (SqlConnection)_dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable columnsSchema;
// Jjust to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
using (DataTable columnsList = new DataTable())
{
tempDs.Tables.Add(columnsList);
// Get from db
adapter.Fill(columnsList);
// Get schema
using (DataTableReader reader = new DataTableReader(columnsList))
columnsSchema = reader.GetSchemaTable();
}
}
// Used to get columns Sql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("COLUMNS"))
{
// Fetch the rows
foreach (DataRow dr in columnsSchema.Rows)
{
string columnName = dr["ColumnName"].ToString();
DbColumn column = new DbColumn(columnName)
{
DataTypeDotNet = dr["DataType"].ToString(),
Length = Convert.ToInt32(dr["ColumnSize"]),
PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
AutoIncrement = Convert.ToBoolean(dr["IsAutoIncrement"]),
AllowNull = Convert.ToBoolean(dr["AllowDBNull"]),
ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]),
};
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
var succeed = FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// if additional info readin is failed, don't add it to the list
if (succeed)
{
// Add to result
result.Add(column);
}
else
{
// TODO: inform the user
}
}
}
}
return result;
}
public void SchemaTest ()
{
DataTable another = new DataTable ("another");
another.Columns.Add ("x", typeof (string));
another.Rows.Add (new object [] {"test 1" });
another.Rows.Add (new object [] {"test 2" });
another.Rows.Add (new object [] {"test 3" });
DataTableReader reader = new DataTableReader (new DataTable [] { dt, another });
try {
DataTable schema = reader.GetSchemaTable ();
Assert.AreEqual (dt.Columns.Count, schema.Rows.Count, "#1 should be same");
Assert.AreEqual (dt.Columns [1].DataType.ToString (), schema.Rows [1] ["DataType"].ToString (), "#2 data type should match");
reader.NextResult (); //schema should change here
schema = reader.GetSchemaTable ();
Assert.AreEqual (another.Columns.Count, schema.Rows.Count, "#3 should be same");
Assert.AreEqual (another.Columns [0].DataType.ToString (), schema.Rows [0] ["DataType"].ToString (), "#4 data type should match");
} finally {
if (reader != null && !reader.IsClosed)
reader.Close ();
}
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt_fk = schema.GetForeignKeys(new[] { table.Name.ToLower() });
string sqlInfo = String.Format("select * from [{0}] limit 1", table.Name.ToLower());
var adapter = new CUBRIDDataAdapter(sqlInfo, conn);
var tableInfo = new DataTable();
adapter.FillSchema(tableInfo, SchemaType.Source);
using (var reader = new DataTableReader(tableInfo))
{
DataTable schemaTable = reader.GetSchemaTable();
for (var k = 0; k < schemaTable.Rows.Count; k++)
{
string columnName = schemaTable.Rows[k]["ColumnName"].ToString().ToLower();
var isUnique = (bool)schemaTable.Rows[k]["IsUnique"];
var isNullable = (bool)schemaTable.Rows[k]["AllowDBNull"];
var isPrimaryKey = (bool)schemaTable.Rows[k]["IsKey"];
var isIdentity = (bool)schemaTable.Rows[k]["IsAutoIncrement"];
var dataLength = (int)schemaTable.Rows[k]["ColumnSize"];
int dataPrecision = 0;
if (schemaTable.Rows[k]["NumericPrecision"].ToString() != String.Empty)
{
dataPrecision = (int)schemaTable.Rows[k]["NumericPrecision"];
}
int dataScale = 0;
if (schemaTable.Rows[k]["NumericScale"].ToString() != String.Empty)
{
dataScale = (int)schemaTable.Rows[k]["NumericScale"];
}
bool isForeignKey = false;
string fkTableName = "";
string constraintName = "";
for (var i_fk = 0; i_fk < dt_fk.Rows.Count; i_fk++)
{
if (dt_fk.Rows[i_fk]["FKCOLUMN_NAME"].ToString().ToLower() == columnName)
{
isForeignKey = true;
fkTableName = dt_fk.Rows[i_fk]["PKTABLE_NAME"].ToString();
constraintName = dt_fk.Rows[i_fk]["FK_NAME"].ToString();
break;
}
}
string dataType;
using (var cmd = new CUBRIDCommand(sqlInfo, conn))
{
using (var CUBRIDReader = (CUBRIDDataReader)cmd.ExecuteReader())
{
CUBRIDReader.Read();
dataType = CUBRIDReader.GetColumnTypeName(k);
}
}
var m = new DataTypeMapper();
columns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsUnique = isUnique,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = isForeignKey,
IsIdentity = isIdentity,
DataLength = dataLength,
DataPrecision = dataPrecision,
DataScale = dataScale,
ForeignKeyTableName = fkTableName,
ConstraintName = constraintName,
MappedDataType =
m.MapFromDBType(ServerType.CUBRID, dataType, null, null, null).ToString(),
});
}
}
}
table.Columns = columns;
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
finally
{
conn.Close();
}
return columns;
}
public DataTable GetSchemaTable()
{
checkDisposed();
if (_schemaTable == null)
{
DataTableReader reader = new DataTableReader(_table);
_schemaTable = reader.GetSchemaTable();
}
return _schemaTable;
}
public static void AppendButtons(string sVIEW_NAME, Guid gASSIGNED_USER_ID, Control ctl, bool bIsMobile, DataTableReader rdr, CommandEventHandler Page_Command)
{
if (ctl == null)
{
return;
}
bool bIsPostBack = ctl.Page.IsPostBack;
DataTable dt = CRMCache.DynamicButtons(sVIEW_NAME);
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
int gID = TypeConvert.ToInteger(row["ID"]);
int nCONTROL_INDEX = TypeConvert.ToInteger(row["CONTROL_INDEX"]);
string sCONTROL_TYPE = TypeConvert.ToString(row["CONTROL_TYPE"]);
string sMODULE_NAME = TypeConvert.ToString(row["MODULE_NAME"]);
string sMODULE_ACCESS_TYPE = TypeConvert.ToString(row["MODULE_ACCESS_TYPE"]);
string sTARGET_NAME = TypeConvert.ToString(row["TARGET_NAME"]);
string sTARGET_ACCESS_TYPE = TypeConvert.ToString(row["TARGET_ACCESS_TYPE"]);
bool bMOBILE_ONLY = TypeConvert.ToBoolean(row["MOBILE_ONLY"]);
bool bADMIN_ONLY = TypeConvert.ToBoolean(row["ADMIN_ONLY"]);
string sCONTROL_TEXT = TypeConvert.ToString(row["CONTROL_TEXT"]);
string sCONTROL_TOOLTIP = TypeConvert.ToString(row["CONTROL_TOOLTIP"]);
string sCONTROL_ACCESSKEY = TypeConvert.ToString(row["CONTROL_ACCESSKEY"]);
string sCONTROL_CSSCLASS = TypeConvert.ToString(row["CONTROL_CSSCLASS"]);
string sTEXT_FIELD = TypeConvert.ToString(row["TEXT_FIELD"]);
string sARGUMENT_FIELD = TypeConvert.ToString(row["ARGUMENT_FIELD"]);
string sCOMMAND_NAME = TypeConvert.ToString(row["COMMAND_NAME"]);
string sURL_FORMAT = TypeConvert.ToString(row["URL_FORMAT"]);
string sURL_TARGET = TypeConvert.ToString(row["URL_TARGET"]);
string sONCLICK_SCRIPT = TypeConvert.ToString(row["ONCLICK_SCRIPT"]);
DataView vwSchema = null;
if (rdr != null)
vwSchema = new DataView(rdr.GetSchemaTable());
string[] arrTEXT_FIELD = sTEXT_FIELD.Split(' ');
object[] objTEXT_FIELD = new object[arrTEXT_FIELD.Length];
for (int i = 0; i < arrTEXT_FIELD.Length; i++)
{
if (!TypeConvert.IsEmptyString(arrTEXT_FIELD[i]))
{
objTEXT_FIELD[i] = String.Empty;
if (rdr != null && vwSchema != null)
{
vwSchema.RowFilter = "ColumnName = '" + TypeConvert.EscapeSQL(arrTEXT_FIELD[i]) + "'";
if (vwSchema.Count > 0)
objTEXT_FIELD[i] = TypeConvert.ToString(rdr[arrTEXT_FIELD[i]]);
}
}
}
if (String.Compare(sCONTROL_TYPE, "Button", true) == 0)
{
Button btn = new Button();
ctl.Controls.Add(btn);
if (!TypeConvert.IsEmptyString(sARGUMENT_FIELD))
{
if (rdr != null && vwSchema != null)
{
vwSchema.RowFilter = "ColumnName = '" + TypeConvert.EscapeSQL(sARGUMENT_FIELD) + "'";
if (vwSchema.Count > 0)
btn.CommandArgument = TypeConvert.ToString(rdr[sARGUMENT_FIELD]);
}
}
btn.Text = " " + Translation.GetTranslation.Term(sCONTROL_TEXT) + " ";
btn.CssClass = sCONTROL_CSSCLASS;
btn.Command += Page_Command;
btn.CommandName = sCOMMAND_NAME;
btn.OnClientClick = sONCLICK_SCRIPT;
btn.Visible = (bMOBILE_ONLY && bIsMobile || !bMOBILE_ONLY) && (bADMIN_ONLY && CRMSecurity.IS_ADMIN || !bADMIN_ONLY);
if (btn.Visible && !TypeConvert.IsEmptyString(sMODULE_NAME) && !TypeConvert.IsEmptyString(sMODULE_ACCESS_TYPE))
{
int nACLACCESS = CRM.CRMSecurity.GetUserAccess(sMODULE_NAME, sMODULE_ACCESS_TYPE);
btn.Visible = (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && CRMSecurity.USER_ID != gASSIGNED_USER_ID);
if (btn.Visible && !TypeConvert.IsEmptyString(sTARGET_NAME) && !TypeConvert.IsEmptyString(sTARGET_ACCESS_TYPE))
{
nACLACCESS = CRM.CRMSecurity.GetUserAccess(sTARGET_NAME, sTARGET_ACCESS_TYPE);
btn.Visible = (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && CRMSecurity.USER_ID != gASSIGNED_USER_ID);
}
}
if (!TypeConvert.IsEmptyString(sCONTROL_TOOLTIP))
{
btn.ToolTip = Translation.GetTranslation.Term(sCONTROL_TOOLTIP);
if (btn.ToolTip.Contains("[Alt]"))
{
if (btn.AccessKey.Length > 0)
btn.ToolTip = btn.ToolTip.Replace("[Alt]", "[Alt+" + btn.AccessKey + "]");
else
btn.ToolTip = btn.ToolTip.Replace("[Alt]", String.Empty);
}
}
btn.Attributes.Add("style", "margin-right: 3px;");
}
else if (String.Compare(sCONTROL_TYPE, "HyperLink", true) == 0)
{
HyperLink lnk = new HyperLink();
ctl.Controls.Add(lnk);
lnk.Text = Translation.GetTranslation.Term(sCONTROL_TEXT);
lnk.NavigateUrl = String.Format(sURL_FORMAT, objTEXT_FIELD);
lnk.Target = sURL_TARGET;
lnk.CssClass = sCONTROL_CSSCLASS;
lnk.Visible = (bMOBILE_ONLY && bIsMobile || !bMOBILE_ONLY) && (bADMIN_ONLY && CRMSecurity.IS_ADMIN || !bADMIN_ONLY);
if (lnk.Visible && !TypeConvert.IsEmptyString(sMODULE_NAME) && !TypeConvert.IsEmptyString(sMODULE_ACCESS_TYPE))
{
int nACLACCESS = CRM.CRMSecurity.GetUserAccess(sMODULE_NAME, sMODULE_ACCESS_TYPE);
lnk.Visible = (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && CRMSecurity.USER_ID != gASSIGNED_USER_ID);
if (lnk.Visible && !TypeConvert.IsEmptyString(sTARGET_NAME) && !TypeConvert.IsEmptyString(sTARGET_ACCESS_TYPE))
{
nACLACCESS = CRM.CRMSecurity.GetUserAccess(sTARGET_NAME, sTARGET_ACCESS_TYPE);
lnk.Visible = (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && CRMSecurity.USER_ID != gASSIGNED_USER_ID);
}
}
if (!TypeConvert.IsEmptyString(sONCLICK_SCRIPT))
{
lnk.Attributes.Add("onclick", sONCLICK_SCRIPT);
}
if (!TypeConvert.IsEmptyString(sCONTROL_TOOLTIP))
{
lnk.ToolTip = Translation.GetTranslation.Term(sCONTROL_TOOLTIP);
if (lnk.ToolTip.Contains("[Alt]"))
{
if (lnk.AccessKey.Length > 0)
lnk.ToolTip = lnk.ToolTip.Replace("[Alt]", "[Alt+" + lnk.AccessKey + "]");
else
lnk.ToolTip = lnk.ToolTip.Replace("[Alt]", String.Empty);
}
}
lnk.Attributes.Add("style", "margin-right: 3px; margin-left: 3px;");
}
else if (String.Compare(sCONTROL_TYPE, "ButtonLink", true) == 0)
{
Button btn = new Button();
ctl.Controls.Add(btn);
btn.Text = " " + Translation.GetTranslation.Term(sCONTROL_TEXT) + " ";
btn.CssClass = sCONTROL_CSSCLASS;
btn.Command += Page_Command;
btn.CommandName = sCOMMAND_NAME;
btn.OnClientClick = "window.location.href='" + TypeConvert.EscapeJavaScript(String.Format(sURL_FORMAT, objTEXT_FIELD)) + "'; return false;";
btn.Visible = (bMOBILE_ONLY && bIsMobile || !bMOBILE_ONLY) && (bADMIN_ONLY && CRMSecurity.IS_ADMIN || !bADMIN_ONLY);
if (btn.Visible && !TypeConvert.IsEmptyString(sMODULE_NAME) && !TypeConvert.IsEmptyString(sMODULE_ACCESS_TYPE))
{
int nACLACCESS = CRM.CRMSecurity.GetUserAccess(sMODULE_NAME, sMODULE_ACCESS_TYPE);
btn.Visible = (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && CRMSecurity.USER_ID != gASSIGNED_USER_ID);
if (btn.Visible && !TypeConvert.IsEmptyString(sTARGET_NAME) && !TypeConvert.IsEmptyString(sTARGET_ACCESS_TYPE))
{
nACLACCESS = CRM.CRMSecurity.GetUserAccess(sTARGET_NAME, sTARGET_ACCESS_TYPE);
btn.Visible = (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && CRMSecurity.USER_ID != gASSIGNED_USER_ID);
}
}
if (!TypeConvert.IsEmptyString(sCONTROL_TOOLTIP))
{
btn.ToolTip = Translation.GetTranslation.Term(sCONTROL_TOOLTIP);
if (btn.ToolTip.Contains("[Alt]"))
{
if (btn.AccessKey.Length > 0)
btn.ToolTip = btn.ToolTip.Replace("[Alt]", "[Alt+" + btn.AccessKey + "]");
else
btn.ToolTip = btn.ToolTip.Replace("[Alt]", String.Empty);
}
}
btn.Attributes.Add("style", "margin-right: 3px;");
}
}
}
}
/// <summary>
/// Test DataTableReader GetSchemaTable() method
/// </summary>
private static void Test_GetSchemaTable()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string sql = "select * from athlete order by `code`";
CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(sql, conn);
DataTable table = new DataTable();
//To retrieve the AlolowDBNull, IsUnique, IsKey, IsAutoIncrement and BaseTableName values from the Database Server
//you must use the FillSchema() method.
adapter.FillSchema(table, SchemaType.Source);
using (DataTableReader reader = new DataTableReader(table))
{
DataTable schemaTable = reader.GetSchemaTable();
DataRow row = schemaTable.Rows[0];
Debug.Assert(row["ColumnName"].ToString() == "code");
Debug.Assert(row["ColumnOrdinal"].ToString() == "0");
Debug.Assert(row["ColumnSize"].ToString() == "-1");
Debug.Assert(row["NumericPrecision"].ToString() == "");
Debug.Assert(row["NumericScale"].ToString() == "");
Debug.Assert(row["IsUnique"].ToString() == "True");
Debug.Assert(row["IsKey"].ToString() == "True");
Debug.Assert(row["BaseTableNamespace"].ToString() == "");
Debug.Assert(row["BaseColumnNamespace"].ToString() == "");
Debug.Assert(row["BaseCatalogName"].ToString() == "");
Debug.Assert(row["BaseColumnName"].ToString() == "code");
Debug.Assert(row["BaseSchemaName"].ToString() == "");
Debug.Assert(row["BaseTableName"].ToString() == "athlete");
Debug.Assert(row["DataType"].ToString() == "System.Int32");
Debug.Assert(row["AllowDBNull"].ToString() == "False");
Debug.Assert(row["ProviderType"].ToString() == "");
Debug.Assert(row["Expression"].ToString() == "");
Debug.Assert(row["AutoIncrementSeed"].ToString() == "0");
Debug.Assert(row["AutoincrementStep"].ToString() == "1");
Debug.Assert(row["IsAutoIncrement"].ToString() == "True");
Debug.Assert(row["IsRowVersion"].ToString() == "False");
Debug.Assert(row["IsLong"].ToString() == "False");
Debug.Assert(row["IsReadOnly"].ToString() == "False");
Debug.Assert(row["ColumnMapping"].ToString() == "1");
Debug.Assert(row["DefaultValue"].ToString() == "");
}
}
}
