/// <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() == ""); } } }