/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="columns"></param>
/// <returns></returns>
public override bool CheckTable(string tableName, out DbColumn[] columns)
{
columns = null;
string commandText = string.Format("SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'U')", tableName);
if (SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, commandText).ToInt() > 0)
{
var list = new List<DbColumn>();
commandText = string.Format("select c.name as ColumnName, t.name as ColumnType, c.scale, c.length,c.isnullable from syscolumns c join systypes t on c.xtype=t.xtype where t.name <> 'sysname' and c.id=object_id('{0}') order by colorder ASC", tableName);
using (var dataReader = SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, commandText))
{
while (dataReader.Read())
{
var column = new DbColumn();
column.Name = dataReader[0].ToNotNullString();
column.DbType = dataReader[1].ToNotNullString();
column.Scale = dataReader[2].ToInt();
column.Length = dataReader[3].ToLong();
column.Type = ConvertToObjectType(ConvertToDbType(column.DbType));
list.Add(column);
}
}
columns = list.ToArray();
return true;
}
return false;
}
public CsvReader(TextReader reader, CsvReaderOptions options)
{
_lineReader = new LineReader(options, reader);
_cursor = _lineReader.GetLines().GetEnumerator();
// Read the first line, as we expect it to have headers
_cursor.MoveNext();
// The file format here is that the first row includes the
// columnHeaders in the format "<name>:<type>"
var columnHeaders = _cursor.Current;
var columns = new DbColumn[columnHeaders.Length];
for (var i = 0; i < columnHeaders.Length; i++) {
// Unquote headers
var header = columnHeaders[i].Replace("\"", "");
var splitHeader = header.Split(':');
if (splitHeader.Length == 2) {
var columnName = splitHeader[0];
var typeHeader = splitHeader[1];
var mungType = MungType.Parse(typeHeader);
columns[i] = new DbColumn(columnName, mungType);
} else {
columns[i] = new DbColumn(header, MungType.Get(MungTypeCode.String));
}
}
_columns = columns.ToList();
}
protected qCom_Contact(DbRow c)
{
container = c;
container.SetContainerName("qCom_Contacts");
contact_id = container.NewColumn<Int32>("ContactID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
first_name = container.NewColumn<String>("FirstName");
last_name = container.NewColumn<String>("LastName");
email = container.NewColumn<String>("Email");
keywords = container.NewColumn<String>("Keywords");
source = container.NewColumn<String>("Source");
user_id = container.NewColumn<Int32>("UserID");
ok_email = container.NewColumn<String>("OkEmail");
did = container.NewColumn<String>("DID");
partner = container.NewColumn<String>("Partner");
main_group = container.NewColumn<Int32>("MainGroup");
sub_group = container.NewColumn<Int32>("SubGroup");
custom_html_element = container.NewColumn<String>("CustomHTMLElement");
dm_misc = container.NewColumn<String>("DM_Misc");
unsubscribed = container.NewColumn<DateTime?>("Unsubscribed");
unsubscribed_campaign_id = container.NewColumn<Int32>("UnsubscribedCampaignID");
reported_as_spam = container.NewColumn<DateTime?>("ReportedAsSpam");
reported_as_spam_campaign_id = container.NewColumn<Int32>("ReportedAsSpamCampaignID");
action_log = container.NewColumn<String>("ActionLog");
}
public static DbTable LoadDb(DbDataReader reader)
{
if (!reader.Read()) {
return null;
}
var tbl = new DbTable((string)reader["TABLE_NAME"]);
var firstColumn = new DbColumn(
(string)reader["COLUMN_NAME"],
(string)reader["DATA_TYPE"]
);
tbl.Columns = new List<DbColumn>() { firstColumn };
while (reader.Read()) {
if (tbl.Name != (string)reader["TABLE_NAME"]) {
break;
}
tbl.Columns.Add(new DbColumn(
(string)reader["COLUMN_NAME"],
(string)reader["DATA_TYPE"]
));
}
return tbl;
}
protected qPtl_RoleAction(DbRow c)
{
container = c;
container.SetContainerName("qPtl_RoleActions");
role_action_id = container.NewColumn<Int32>("RoleActionID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32?>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
role_id = container.NewColumn<Int32>("RoleID");
action_id = container.NewColumn<Int32>("ActionID");
start_days_from_now = container.NewColumn<Int32>("StartDaysFromNow");
end_days_from_now = container.NewColumn<Int32>("EndDaysFromNow");
after_num_logins = container.NewColumn<Int32>("AfterNumLogins");
priority = container.NewColumn<Int32>("Priority");
skip_allowed = container.NewColumn<String>("SkipAllowed");
number_skips_allowed = container.NewColumn<Int32>("NumberSkipsAllowed");
required = container.NewColumn<String>("Required");
option_opt_out = container.NewColumn<String>("OptionOptOut");
redirect_url = container.NewColumn<String>("RedirectURL");
redirect_skip_url = container.NewColumn<String>("RedirectSkipURL");
}
protected qPtl_UserRole(DbRow c)
{
container = c;
container.SetContainerName("qPtl_UserRoles");
user_id = container.NewColumn<Int32>("UserID", true);
role_id = container.NewColumn<Int32>("RoleID", true);
}
protected qPtl_Task(DbRow c)
{
container = c;
container.SetContainerName("qPtl_Tasks");
task_id = container.NewColumn<Int32>("TaskID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32?>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
task_type_id = container.NewColumn<Int32>("TaskTypeID");
assigned_to = container.NewColumn<Int32?>("AssignedTo");
assigned_text = container.NewColumn<String>("AssignedText");
start_date = container.NewColumn<DateTime?>("StartDate");
due_date = container.NewColumn<DateTime?>("DueDate");
percent_completed = container.NewColumn<Double>("PercentCompleted");
status = container.NewColumn<String>("Status");
name = container.NewColumn<String>("Name");
description = container.NewColumn<String>("Description");
importance = container.NewColumn<Int32>("Importance");
content_type_id = container.NewColumn<Int32>("ContentTypeID");
reference_id = container.NewColumn<Int32>("ReferenceID");
}
protected qHtl_AbsenteeAnalysisVariable(DbRow c)
{
container = c;
container.SetContainerName("qHtl_AbsenteeAnalysisVariables");
absentee_analysis_variable_id = container.NewColumn<Int32>("AbsenteeAnalysisVariableID", true);
school_district_id = container.NewColumn<Int32>("SchoolDistrictID");
solution_start_date = container.NewColumn<DateTime?>("SolutionStartDate");
green_rate_std_multiplier = container.NewColumn<Decimal>("GreenRateSTDMultiplier");
yellow_rate_std_multiplier = container.NewColumn<Decimal>("YellowRateSTDMultiplier");
red_rate_std_multiplier = container.NewColumn<Decimal>("RedRateSTDMultiplier");
green_illness_boundary = container.NewColumn<Decimal>("GreenIllnessBoundary");
yellow_illness_boundary = container.NewColumn<Decimal>("YellowIllnessBoundary");
red_illness_boundary = container.NewColumn<Decimal>("RedIllnessBoundary");
green_gastrointestinal_boundary = container.NewColumn<Decimal>("GreenGastrointestinalBoundary");
yellow_gastrointestinal_boundary = container.NewColumn<Decimal>("YellowGastrointestinalBoundary");
red_gastrointestinal_boundary = container.NewColumn<Decimal>("RedGastrointestinalBoundary");
green_respiratory_boundary = container.NewColumn<Decimal>("GreenRespiratoryBoundary");
yellow_respiratory_boundary = container.NewColumn<Decimal>("YellowRespiratoryBoundary");
red_respiratory_boundary = container.NewColumn<Decimal>("RedRespiratoryBoundary");
green_rash_boundary = container.NewColumn<Decimal>("GreenRashBoundary");
yellow_rash_boundary = container.NewColumn<Decimal>("YellowRashBoundary");
red_rash_boundary = container.NewColumn<Decimal>("RedRashBoundary");
green_other_illness_boundary = container.NewColumn<Decimal>("GreenOtherIllnessBoundary");
yellow_other_illness_boundary = container.NewColumn<Decimal>("YellowOtherIllnessBoundary");
red_other_illness_boundary = container.NewColumn<Decimal>("RedOtherIllnessBoundary");
green_unknown_illness_boundary = container.NewColumn<Decimal>("GreenUnknownIllnessBoundary");
yellow_unknown_illness_boundary = container.NewColumn<Decimal>("YellowUnknownIllnessBoundary");
red_unknown_illness_boundary = container.NewColumn<Decimal>("RedUnknownIllnessBoundary");
sd_formula_type = container.NewColumn<String>("SDFormulaType");
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="columns"></param>
/// <returns></returns>
public override bool CheckTable(string tableName, out DbColumn[] columns)
{
columns = null;
string commandText = string.Format("SELECT count(1) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`='{0}' AND `TABLE_NAME`='{1}'", ConnectionSetting.DatabaseName, tableName);
if (MySqlHelper.ExecuteScalar(ConnectionString, commandText).ToInt() > 0)
{
var list = new List<DbColumn>();
commandText = string.Format("SELECT Column_Name AS ColumnName,Data_Type AS ColumnType, NUMERIC_SCALE AS scale, CHARACTER_MAXIMUM_LENGTH AS Length FROM information_schema.`columns` WHERE `TABLE_SCHEMA`='{0}' AND `TABLE_NAME`='{1}'", ConnectionSetting.DatabaseName, tableName);
using (var dataReader = MySqlHelper.ExecuteReader(ConnectionString, commandText))
{
while (dataReader.Read())
{
var column = new DbColumn();
column.Name = dataReader[0].ToNotNullString();
column.DbType = dataReader[1].ToNotNullString();
column.Scale = dataReader[2].ToInt();
column.Length = dataReader[3].ToLong();
column.Type = ConvertToObjectType(ConvertToDbType(column.DbType));
list.Add(column);
}
}
columns = list.ToArray();
return true;
}
return false;
}
protected qOrg_School(DbRow c)
{
container = c;
container.SetContainerName("qOrg_Schools");
school_id = container.NewColumn<Int32>("SchoolID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
school_selector = container.NewColumn<String>("SchoolSelector");
school = container.NewColumn<String>("School");
address1 = container.NewColumn<String>("Address1");
address2 = container.NewColumn<String>("Address2");
city = container.NewColumn<String>("City");
state_province = container.NewColumn<String>("StateProvince");
postal_code = container.NewColumn<String>("PostalCode");
country = container.NewColumn<String>("Country");
district = container.NewColumn<String>("District");
school_type = container.NewColumn<String>("SchoolType");
school_level = container.NewColumn<String>("SchoolLevel");
school_district_id = container.NewColumn<Int32>("SchoolDistrictID");
school_phone = container.NewColumn<String>("SchoolPhone");
school_fax = container.NewColumn<String>("SchoolFax");
longitude = container.NewColumn<Decimal>("Longitude");
latitude = container.NewColumn<Decimal>("Latitude");
}
protected qPtl_User(DbRow c)
{
container = c;
container.SetContainerName("qPtl_Users");
user_id = container.NewColumn<Int32>("UserID", true);
org_unit_id = container.NewColumn<Int32>("OrgUnitID");
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
username = container.NewColumn<String>("UserName");
password = container.NewColumn<String>("Password");
password_reset_code = container.NewColumn<String>("PasswordResetCode");
first_name = container.NewColumn<String>("FirstName");
last_name = container.NewColumn<String>("LastName");
email = container.NewColumn<String>("Email");
comments = container.NewColumn<String>("Comments");
dm_user_id = container.NewColumn<Int32>("DM_UserID");
dm_status_id = container.NewColumn<Int32>("DM_StatusID");
highest_role = container.NewColumn<String>("HighestRole");
highest_rank = container.NewColumn<Int32>("HighestRank");
last_time_seen = container.NewColumn<DateTime?>("LastTimeSeen");
last_ip_address = container.NewColumn<String>("LastIPAddress");
profile_pict = container.NewColumn<String>("ProfilePict");
status = container.NewColumn<String>("Status");
status_date = container.NewColumn<DateTime?>("StatusDate");
account_status = container.NewColumn<String>("AccountStatus");
last_activity = container.NewColumn<Int32>("LastActivity");
number_logins = container.NewColumn<Int32>("NumberLogins");
registration_type = container.NewColumn<String>("RegistrationType");
registration_notes = container.NewColumn<String>("RegistrationNotes");
}
protected qHtl_HealthProvider(DbRow c)
{
container = c;
container.SetContainerName("qHtl_HealthProviders");
health_provider_id = container.NewColumn<Int32>("HealthProviderID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
health_provider_type = container.NewColumn<String>("HealthProviderType");
name = container.NewColumn<String>("name");
description = container.NewColumn<String>("description");
address1 = container.NewColumn<String>("Address1");
address2 = container.NewColumn<String>("Address2");
city = container.NewColumn<String>("City");
state_province = container.NewColumn<String>("StateProvince");
postal_code = container.NewColumn<String>("PostalCode");
country = container.NewColumn<String>("Country");
phone = container.NewColumn<String>("Phone");
website = container.NewColumn<String>("Website");
service_type = container.NewColumn<String>("ServiceType");
service_categories = container.NewColumn<String>("ServiceCategories");
}
protected qHtl_HealthWarning(DbRow c)
{
container = c;
container.SetContainerName("qHtl_HealthWarnings");
health_warning_id = container.NewColumn<Int32>("HealthWarningID", true);
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
data_date = container.NewColumn<DateTime?>("DataDate");
type = container.NewColumn<String>("Type");
title = container.NewColumn<String>("Title");
text = container.NewColumn<String>("Text");
notes = container.NewColumn<String>("Notes");
severity = container.NewColumn<String>("Severity");
issued_by = container.NewColumn<Int32>("IssuedBy");
issued_by_name = container.NewColumn<String>("IssuedByName");
review_status = container.NewColumn<String>("ReviewStatus");
issued_to = container.NewColumn<String>("IssuedTo");
valid_from = container.NewColumn<DateTime?>("ValidFrom");
valid_until = container.NewColumn<DateTime?>("ValidUntil");
multiple_classrooms = container.NewColumn<String>("MultipleClassrooms");
multiple_schools = container.NewColumn<String>("MultipleSchools");
school_levels = container.NewColumn<String>("SchoolLevels");
content_type_id = container.NewColumn<Int32>("ContentTypeID");
reference_id = container.NewColumn<Int32>("ReferenceID");
email_id = container.NewColumn<Int32>("EmailID");
notification_id = container.NewColumn<Int32>("NotificationID");
sms_message_id = container.NewColumn<Int32>("SMSMessageID");
}
protected qPtl_UserAction(DbRow c)
{
container = c;
container.SetContainerName("qPtl_UserActions");
user_action_id = container.NewColumn<Int32>("UserActionID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32?>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
user_id = container.NewColumn<Int32>("UserID");
action_id = container.NewColumn<Int32>("ActionID");
assign_type = container.NewColumn<String>("AssignType");
available_from = container.NewColumn<DateTime?>("AvailableFrom");
available_to = container.NewColumn<DateTime?>("AvailableTo");
after_num_logins = container.NewColumn<Int32>("AfterNumLogins");
priority = container.NewColumn<Int32>("Priority");
skip_allowed = container.NewColumn<String>("SkipAllowed");
number_skips_allowed = container.NewColumn<Int32>("NumberSkipsAllowed");
required = container.NewColumn<String>("Required");
optional_opt_out = container.NewColumn<String>("OptionalOptOut");
user_completed = container.NewColumn<DateTime?>("UserCompleted");
user_opt_out = container.NewColumn<DateTime?>("UserOptOut");
user_num_skips = container.NewColumn<Int32>("UserNumSkips");
user_last_skip = container.NewColumn<DateTime?>("UserLastSkip");
last_user_action_session_id = container.NewColumn<Int32>("LastUserActionSessionID");
reference_id = container.NewColumn<Int32>("ReferenceID");
redirect_url = container.NewColumn<String>("RedirectURL");
redirect_skip_url = container.NewColumn<String>("RedirectSkipURL");
}
protected qCom_UserPreference(DbRow c)
{
container = c;
container.SetContainerName("qCom_UserPreferences");
contact_preference_id = container.NewColumn<Int32>("ContactPreferenceID", true);
user_id = container.NewColumn<Int32>("UserID");
available = container.NewColumn<String>("Available");
scope_id = container.NewColumn<Int32>("ScopeID");
created = container.NewColumn<DateTime?>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
ok_bulk_email = container.NewColumn<String>("OkBulkEmail");
ok_email = container.NewColumn<String>("OkEmail");
ok_phone = container.NewColumn<String>("OkPhone");
ok_fax = container.NewColumn<String>("OkFax");
ok_solicit = container.NewColumn<String>("OkSolicit");
ok_mail = container.NewColumn<String>("OkMail");
ok_partner_contact = container.NewColumn<String>("OkPartnerContact");
ok_sms = container.NewColumn<String>("OkSms");
ok_notifications = container.NewColumn<String>("OkNotifications");
confirm_sms = container.NewColumn<String>("ConfirmSms");
language_id = container.NewColumn<Int32>("LanguageID");
mobile_pin = container.NewColumn<String>("MobilePIN");
mobile_pin_verified = container.NewColumn<DateTime?>("MobilePINVerified");
default_sms_did = container.NewColumn<String>("DefaultSmsDid");
sms_active_minutes_window = container.NewColumn<Int32>("SmsActiveMinutesWindow");
}
protected qPtl_Announcement(DbRow c)
{
container = c;
container.SetContainerName("qPtl_Announcements");
announcement_id = container.NewColumn<Int32>("AnnouncementID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
announcement_for_campaign_id = container.NewColumn<Int32>("AnnouncementForCampaignID");
uri = container.NewColumn<String>("URI");
title = container.NewColumn<String>("Title");
text = container.NewColumn<String>("Text");
generic = container.NewColumn<String>("Generic");
generic_available_from = container.NewColumn<DateTime?>("GenericAvailableFrom");
generic_available_to = container.NewColumn<DateTime?>("GenericAvailableTo");
remind_every_days = container.NewColumn<Int32>("RemindEveryDays");
content_type_id = container.NewColumn<Int32>("ContentTypeID");
reference_id = container.NewColumn<Int32>("ReferenceID");
one_time = container.NewColumn<Boolean>("OneTime");
associated_announcement_id = container.NewColumn<Int32>("AssociatedAnnouncementID");
}
protected qPtl_Language(DbRow c)
{
container = c;
container.SetContainerName("qPtl_Languages");
language_id = container.NewColumn<Int32>("LanguageID", true);
display_name = container.NewColumn<String>("DisplayName");
code = container.NewColumn<String>("Code");
available = container.NewColumn<Boolean>("Available");
}
protected qCom_TempBulkEmailLog(DbRow c)
{
container = c;
container.SetContainerName("qCom_TempBulkEmailLogs");
temp_bulk_email_log_id = container.NewColumn<Int32>("TempBulkEmailLogID", true);
user_id = container.NewColumn<Int32>("UserID");
email_item_id = container.NewColumn<Int32>("EmailItemID");
email_address = container.NewColumn<String>("EmailAddress");
timestamp = container.NewColumn<DateTime?>("Timestamp");
}
/// <summary>
/// The get database collection.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="includeTable">
/// The include table.
/// </param>
/// <param name="includeColumn">
/// The include column.
/// </param>
/// <returns>
/// The <see cref="DatabaseCollection"/>.
/// </returns>
public DatabaseCollection GetDatabaseCollection(
string connectionString,
bool includeTable = false,
bool includeColumn = false)
{
var databaseCollection = new DatabaseCollection();
using (var sqlConnection = new SqlConnection(connectionString))
{
var serverConnection = new ServerConnection(sqlConnection);
var server = new Server(serverConnection);
foreach (
var database in
server.Databases.Cast<Database>().AsQueryable().Where(d => d.IsSystemObject == false))
{
var dbItem = new EntityTemplate.Database { Name = database.Name };
if (includeTable && database.Tables.Count > 0)
{
foreach (var table in database.Tables.Cast<Table>().AsQueryable().Where(t => t.IsSystemObject == false))
{
var dbTableItem = new DbTable { Name = table.Name };
dbTableItem.Description = table.ExtendedProperties.Count > 0 ? table.ExtendedProperties["MS_Description"].Value.ToString() : string.Empty;
if (includeColumn && table.Columns.Count > 0)
{
foreach (var column in table.Columns.Cast<Column>().AsQueryable())
{
var dbColumnItem = new DbColumn();
dbColumnItem.Name = column.Name;
dbColumnItem.Description = column.ExtendedProperties.Count > 0 ? column.ExtendedProperties["MS_Description"].Value.ToString() : string.Empty;
dbColumnItem.IsPrimaryKey = column.InPrimaryKey;
dbColumnItem.IsIdentityColumn = column.Identity;
dbColumnItem.ColumnType = column.DataType.SqlDataType.ToString();
dbColumnItem.AllowEmpty = column.Nullable;
dbColumnItem.DefaultValue = column.Default;
dbTableItem.Columns.Add(dbColumnItem);
}
}
dbItem.Tables.Add(dbTableItem);
}
}
databaseCollection.Add(dbItem);
}
}
return databaseCollection;
}
protected qPtl_UserLevelRedirect(DbRow c)
{
container = c;
container.SetContainerName("qPtl_UserLevelRedirects");
user_level_redirect_id = container.NewColumn<Int32>("UserLevelRedirectID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
highest_rank = container.NewColumn<Int32>("HighestRank");
content_type_id = container.NewColumn<Int32>("ContentTypeID");
request_url = container.NewColumn<String>("RequestUrl");
url = container.NewColumn<String>("URL");
}
public qGis_ClientGeography(DbRow existing_container)
{
container = existing_container;
container.SetContainerName("qGis_ClientGeography");
geography_id = container.NewColumn<Int32>("GeographyID", true);
rule_id = container.NewColumn<Int32>("RuleID");
reference_table = container.NewColumn<String>("ReferenceTable");
reference_filter = container.NewColumn<String>("ReferenceFilter");
reference_columns = container.NewColumn<String>("ReferenceColumns");
}
public DbTable(string tableName, MungedDataReader reader)
{
Name = tableName;
Columns = new List<DbColumn>();
for (var i = 0; i < reader.ColumnNames.Length; i++) {
var type = reader.ColumnTypes[i];
var name = reader.ColumnNames[i];
var c = new DbColumn(name, type);
Columns.Add(c);
}
}
protected qPtl_TempSession(DbRow c)
{
container = c;
container.SetContainerName("qPtl_TempSessions");
temp_session_id = container.NewColumn<Int32>("TempSessionID", true);
ipaddress = container.NewColumn<String>("IPAddress");
computer_type = container.NewColumn<String>("ComputerType");
browser_type = container.NewColumn<String>("BrowserType");
start_time = container.NewColumn<DateTime>("StartTime");
stop_time = container.NewColumn<DateTime?>("StopTime");
last_time_seen = container.NewColumn<DateTime?>("LastTimeSeen");
entry_page = container.NewColumn<String>("EntryPage");
}
protected qPtl_Role(DbRow c)
{
container = c;
container.SetContainerName("qPtl_Roles");
role_id = container.NewColumn<Int32>("RoleID", true);
available = container.NewColumn<String>("Available");
role_name = container.NewColumn<String>("RoleName");
description = container.NewColumn<String>("Description");
role_rank = container.NewColumn<Int32>("RoleRank");
login_allowed = container.NewColumn<String>("LoginAllowed");
global_account = container.NewColumn<String>("GlobalAccount");
social_allowed = container.NewColumn<String>("SocialAllowed");
}
public qGis_ClientRule(DbRow existing_container)
{
container = existing_container;
container.SetContainerName ("qGis_ClientRules");
rule_id = container.NewColumn<Int32>("RuleID", true);
zoom = container.NewColumn<Int32>("Zoom");
data_group_id = container.NewColumn<Int32>("DataGroupID");
cluster_minimum_size = container.NewColumn<Int32>("ClusterMinimumSize");
cluster_maximum_size = container.NewColumn<Int32>("ClusterMaximumSize");
grid_horizontal_divisions = container.NewColumn<Int32>("GridHorizontalDivisions");
grid_vertical_divisions = container.NewColumn<Int32>("GridVerticalDivisions");
}
protected qHtl_SupportedPostalCode(DbRow c)
{
container = c;
container.SetContainerName("qHtl_SupportedPostalCodes");
supported_postal_code_id = container.NewColumn<Int32>("SupportedPostalCodeID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
postal_code = container.NewColumn<String>("PostalCode");
}
protected qOrg_LocationType(DbRow c)
{
container = c;
container.SetContainerName("qOrg_LocationTypes");
location_type_id = container.NewColumn<Int32>("LocationTypeID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
location_type_name = container.NewColumn<String>("LocationTypeName");
}
protected qPtl_Redirect(DbRow c)
{
container = c;
container.SetContainerName("qPtl_Redirects");
redirect_id = container.NewColumn<Int32>("RedirectID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime>("LastModified");
last_modified_by = container.NewColumn<Int32>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
entry_url = container.NewColumn<String>("EntryURL");
redirect_url = container.NewColumn<String>("RedirectURL");
}
protected qGis_Object(DbRow c)
{
container = c;
container.SetContainerName("qGIS_Objects");
gis_object_id = container.NewColumn <int>("GISObjectID", true);
spatial_type = container.NewColumn<string>("SpatialType");
data_group_id = container.NewColumn<int>("DataGroupID");
content_type_id = container.NewColumn<int>("ContentTypeID");
reference_id = container.NewColumn<int>("ReferenceID");
longitude = container.NewColumn <double>("Longitude");
latitude = container.NewColumn <double>("Latitude");
title = container.NewColumn<string>("Title");
description = container.NewColumn<string>("Description");
image_url = container.NewColumn<string>("ImageURL");
}
protected qPtl_UserFamily(DbRow c)
{
container = c;
container.SetContainerName("qPtl_UserFamilies");
user_family_id = container.NewColumn<Int32>("UserFamilyID", true);
scope_id = container.NewColumn<Int32>("ScopeID");
available = container.NewColumn<String>("Available");
created = container.NewColumn<DateTime>("Created");
created_by = container.NewColumn<Int32>("CreatedBy");
last_modified = container.NewColumn<DateTime?>("LastModified");
last_modified_by = container.NewColumn<Int32?>("LastModifiedBy");
mark_as_delete = container.NewColumn<Int32>("MarkAsDelete");
user_id = container.NewColumn<Int32>("UserID");
family_id = container.NewColumn<Int32>("FamilyID");
}
/// <summary>
/// Converts a cell value into a string for SQL script
/// </summary>
/// <param name="value">The cell to convert</param>
/// <param name="column">The column metadata for the cell to insert</param>
/// <returns>String version of the cell value for use in SQL scripts</returns>
public static string FormatValue(DbCellValue value, DbColumn column)
{
Validate.IsNotNull(nameof(value), value);
return(FormatValue(value.RawObject, column));
}
private TreeNode CreateOrUpdateColumnTreeNode(TreeNode parentNode, DbTable table, DbColumn column, bool active, bool sticky)
{
bool stickyButNotActive = active == false && sticky;
string relationships = column.firstForeignKeyLabel;
/*
* string relationships = "";
* foreach (Relationship r in A.db.tables[table.name], column.name))
* relationships = T.AppendTo(relationships, r.parentTable.name + "." + r.parentColumn.name, " ");
*/
if (relationships != "")
{
relationships = "->" + relationships + "";
}
string key = table.name + "!" + column.name;
ObjectsTreeNode columnNode = null;
if (nodesByObjectName.ContainsKey(key))
{
columnNode = nodesByObjectName[key];
}
else
{
columnNode = new ObjectsTreeNode(column.name);
nodesByObjectName.Add(key, columnNode);
parentNode.Nodes.Add(columnNode);
}
columnNode.ToolTipText = column.name + " - " + column.definition
+ (column.allowNulls ? " null" : "")
+ (column.primaryKey ? " *" : "") + column.columnInfo;
columnNode.rightStuff = relationships;
columnNode.ImageIndex = UI.GetTreeImageIndexForObject(column);
if (active)
{
columnNode.ForeColor = UI.stickyForeColor; // stickyButNotActive ? UI.passiveRedColor : UI.activeRed;
}
return(columnNode);
}
public SchemaField(DbColumn dbColumn)
{
Name = dbColumn.ColumnName;
Type = dbColumn.LeafDataType();
Index = dbColumn.ColumnOrdinal.Value;
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
if (Cache_ForeignKeys.Rows.Count == 0)
{
return;
}
// adding the foreign keys
foreach (DataRow keysDataRow in Cache_ForeignKeys.Rows)
{
var foreignKeyTableName = keysDataRow["FOREIGN_KEY_TABLE_NAME"].ToString();
var primaryKeyTableName = keysDataRow["PRIMARY_KEY_TABLE_NAME"].ToString();
var foreignKeyConstraintName = keysDataRow["FOREIGN_KEY_CONSTRAINT_NAME"].ToString();
var primaryKeyConstraintName = keysDataRow["PRIMARY_KEY_CONSTRAINT_NAME"].ToString();
string foreignKeyColumnName = null;
string primaryKeyColumnName = null;
// read the columns info
DataRow[] columnInfo;
// reading foreign key column info
columnInfo = Cache_All_Constraints.Select(string.Format("CONSTRAINT_NAME='{0}'",
foreignKeyConstraintName));
if (columnInfo != null && columnInfo.Length > 0)
{
foreignKeyColumnName = columnInfo[0]["COLUMN_NAME"].ToString();
}
// reading primary key column info
columnInfo = Cache_All_Constraints.Select(string.Format("CONSTRAINT_NAME='{0}'",
primaryKeyConstraintName));
if (columnInfo != null && columnInfo.Length > 0)
{
primaryKeyColumnName = columnInfo[0]["COLUMN_NAME"].ToString();
}
// there should be column names!
if (foreignKeyColumnName == null || primaryKeyColumnName == null)
{
continue;
}
// find schema tables model
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// there should be tables!
if (foreignKeyTable == null || primaryKeyTable == null)
{
continue;
}
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKey = new DbForeignKey()
{
ForeignKeyName = foreignKeyConstraintName,
LocalColumnName = foreignKeyColumnName,
ForeignColumnName = primaryKeyColumnName,
ForeignTableName = primaryKeyTableName,
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey.ForeignKeyName))
{
continue;
}
//oneMultiplicityKey.UpdateAction =
// ConvertOracleForeignKeyAction(keysDataRow["UPDATE_RULE"].ToString());
oneMultiplicityKey.DeleteAction =
ConvertOracleForeignKeyAction(keysDataRow["DELETE_RULE"].ToString());
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey.LocalColumnName);
oneMultiplicityKey.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKey.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey.ForeignColumnName);
oneMultiplicityKey.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKey.ForeignTable = null;
oneMultiplicityKey.ForeignColumn = null;
}
}
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKey = new DbForeignKey()
{
ForeignKeyName = primaryKeyConstraintName,
LocalColumnName = primaryKeyColumnName,
ForeignColumnName = foreignKeyColumnName,
ForeignTableName = foreignKeyTableName,
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey.ForeignKeyName))
{
continue;
}
//manyMultiplicityKey.UpdateAction =
// ConvertOracleForeignKeyAction(keysDataRow["UPDATE_RULE"].ToString());
manyMultiplicityKey.DeleteAction =
ConvertOracleForeignKeyAction(keysDataRow["DELETE_RULE"].ToString());
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey);
// apply local column
DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey.LocalColumnName);
manyMultiplicityKey.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKey.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey.ForeignColumnName);
manyMultiplicityKey.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKey.ForeignTable = null;
manyMultiplicityKey.ForeignColumn = null;
}
}
}
}
public FieldDescriptor(FieldInfo fieldInfo, TypeDescriptor declaringEntityDescriptor, string columnName)
: base(declaringEntityDescriptor)
{
this._fieldInfo = fieldInfo;
this._column = new DbColumn(columnName, fieldInfo.FieldType);
}
/// <summary>
/// Finds the sequence name for the PRIMARY KEY!!!
/// </summary>
private string GetTableColumnSequenceName_FromDB(string tableName, DbColumn column)
{
const string oracleSequenceExt = "_SEQ";
const int maxNameLen = 31;
var sequenceName = tableName + "_" + column.FieldNameDb + oracleSequenceExt;
if (sequenceName.Length > maxNameLen)
{
sequenceName = sequenceName.Substring(0, maxNameLen - 1);
}
sequenceName = sequenceName.ToUpper();
// here we know the sequence name
// but we have to be sure of it is there
// so a query to the db is required!
const string sequenceSelectCommand = "SELECT SEQUENCE_NAME FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = N'{0}' ";
try
{
using (var adapter = new OracleDataAdapter(
String.Format(sequenceSelectCommand, sequenceName),
(OracleConnection)_dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var descriptionData = new DataTable())
{
// Jjust to avoid stupid "Failed to enable constraints" error!
using (var tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(descriptionData);
// Get from db
adapter.Fill(descriptionData);
}
// if something found
if (descriptionData.Rows.Count > 0)
{
var row = descriptionData.Rows[0];
var seqName = row["SEQUENCE_NAME"].ToString();
if (!string.IsNullOrWhiteSpace(seqName))
{
return(seqName);
}
return(string.Empty);
}
}
}
}
catch
{
// something is wrong! don't stop here!
// TODO: inform user
}
return(string.Empty);
}
public virtual GeneralQueryState AsSubQueryState()
{
DbSqlQueryExpression sqlQuery = this.CreateSqlQuery();
DbSubQueryExpression subQuery = new DbSubQueryExpression(sqlQuery);
QueryModel newQueryModel = new QueryModel(this._queryModel.ScopeParameters, this._queryModel.ScopeTables, this._queryModel.IgnoreFilters);
DbTableSegment tableSeg = new DbTableSegment(subQuery, newQueryModel.GenerateUniqueTableAlias(), LockType.Unspecified);
DbFromTableExpression fromTable = new DbFromTableExpression(tableSeg);
newQueryModel.FromTable = fromTable;
DbTable aliasTable = new DbTable(tableSeg.Alias);
//TODO 根据旧的生成新 ResultModel
IObjectModel newResultModel = this.QueryModel.ResultModel.ToNewObjectModel(sqlQuery, aliasTable, fromTable);
newQueryModel.ResultModel = newResultModel;
//得将 subQuery.SqlQuery.Orders 告诉 以下创建的 result
//将 orderPart 传递下去
for (int i = 0; i < this.QueryModel.Orderings.Count; i++)
{
DbOrdering ordering = this.QueryModel.Orderings[i];
DbExpression orderingExp = ordering.Expression;
string alias = null;
DbColumnSegment columnExpression = sqlQuery.ColumnSegments.Find(a => DbExpressionEqualityComparer.EqualsCompare(orderingExp, a.Body));
// 对于重复的则不需要往 sqlQuery.Columns 重复添加了
if (columnExpression != null)
{
alias = columnExpression.Alias;
}
else
{
alias = Utils.GenerateUniqueColumnAlias(sqlQuery);
DbColumnSegment columnSeg = new DbColumnSegment(orderingExp, alias);
sqlQuery.ColumnSegments.Add(columnSeg);
}
DbColumnAccessExpression columnAccessExpression = new DbColumnAccessExpression(aliasTable, DbColumn.MakeColumn(orderingExp, alias));
newQueryModel.Orderings.Add(new DbOrdering(columnAccessExpression, ordering.OrderType));
}
newQueryModel.InheritOrderings = true;
GeneralQueryState queryState = new GeneralQueryState(newQueryModel);
return(queryState);
}
/// <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, "[" + columnName + "]")
{
DataTypeDotNet = ((Type)dr["DataType"]).AliasOrName(),
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);
// Primary keys that aren't a valid source of dapper access patterns aren't valid
if (column.PrimaryKey)
{
if (column.DataTypeDotNet == "System.DateTime")
{
column.PrimaryKey = false;
}
}
// 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);
}
/// <summary>
/// Executes the service
/// </summary>
/// <param name="values">The values.</param>
/// <param name="theWorkspace">The workspace.</param>
/// <returns></returns>
public StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace)
{
if (values == null)
{
throw new InvalidDataContractException("No parameter values provided.");
}
string database = null;
string tableName = null;
string schema = null;
StringBuilder tmp;
values.TryGetValue("Database", out tmp);
if (tmp != null)
{
database = tmp.ToString();
}
values.TryGetValue("TableName", out tmp);
if (tmp != null)
{
tableName = tmp.ToString();
}
values.TryGetValue("Schema", out tmp);
if (tmp != null)
{
schema = tmp.ToString();
}
Dev2JsonSerializer serializer = new Dev2JsonSerializer();
if (string.IsNullOrEmpty(database))
{
var res = new DbColumnList("No database set.");
Dev2Logger.Log.Debug("No database set.");
return(serializer.SerializeToBuilder(res));
}
if (string.IsNullOrEmpty(tableName))
{
var res = new DbColumnList("No table name set.");
Dev2Logger.Log.Debug("No table name set.");
return(serializer.SerializeToBuilder(res));
}
Dev2Logger.Log.Info(String.Format("Get Database Columns For Table. Database:{0} Schema:{1} Table{2}", database, schema, tableName));
try
{
var dbSource = serializer.Deserialize <DbSource>(database);
var runtTimedbSource = ResourceCatalog.Instance.GetResource <DbSource>(theWorkspace.ID, dbSource.ResourceID);
DataTable columnInfo;
using (var connection = new SqlConnection(runtTimedbSource.ConnectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();
// GUTTED TO RETURN ALL REQUIRED DATA ;)
if (schema == null)
{
schema = string.Empty;
}
var sql = @"select top 1 * from " + schema.Trim(new[] { '"' }) + "." + tableName.Trim(new[] { '"' });
using (var sqlcmd = new SqlCommand(sql, connection))
{
// force it closed so we just get the proper schema ;)
using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection))
{
columnInfo = sdr.GetSchemaTable();
}
}
}
var dbColumns = new DbColumnList();
if (columnInfo != null)
{
foreach (DataRow row in columnInfo.Rows)
{
var columnName = row["ColumnName"] as string;
var isNullable = row["AllowDBNull"] is bool && (bool)row["AllowDBNull"];
var isIdentity = row["IsIdentity"] is bool && (bool)row["IsIdentity"];
var dbColumn = new DbColumn {
ColumnName = columnName, IsNullable = isNullable, IsAutoIncrement = isIdentity
};
SqlDbType sqlDataType;
var typeValue = row["DataTypeName"] as string;
if (Enum.TryParse(typeValue, true, out sqlDataType))
{
dbColumn.SqlDataType = sqlDataType;
}
var columnLength = row["ColumnSize"] is int?(int)row["ColumnSize"] : -1;
dbColumn.MaxLength = columnLength;
dbColumns.Items.Add(dbColumn);
}
}
return(serializer.SerializeToBuilder(dbColumns));
}
catch (Exception ex)
{
Dev2Logger.Log.Error(ex);
var res = new DbColumnList(ex);
return(serializer.SerializeToBuilder(res));
}
}
private static void ModifyTableSchema(SchemaTable schema, DbBaseProvider dbprovider, string tableName, DbColumn[] columns)
{
var list = new List <DbColumn>();
foreach (var keypair in schema.Columns)
{
string name = keypair.Value.Name;
var column = Array.Find(columns, p => string.Equals(p.Name, name, StringComparison.CurrentCultureIgnoreCase));
if (column == null)
{
column = new DbColumn();
column.Id = keypair.Value.Id;
column.Name = name;
column.Type = keypair.Value.ColumnType;
column.Length = keypair.Value.ColumnLength;
column.Scale = keypair.Value.ColumnScale;
column.Isnullable = keypair.Value.Isnullable;
column.IsKey = keypair.Value.IsKey;
column.DbType = keypair.Value.DbType.ToString();
column.IsIdentity = keypair.Value.IsIdentity;
column.IdentityNo = keypair.Value.IdentityNo;
list.Add(column);
}
else
{
//no modify type: text,blob,byte[], enum,list,dict
if ((column.Type == typeof(decimal) && keypair.Value.ColumnScale > 0 && column.Scale != keypair.Value.ColumnScale) ||
(keypair.Value.IsSerialized && keypair.Value.DbType == ColumnDbType.Varchar && column.Type == typeof(string) && column.Length != keypair.Value.ColumnLength) ||
(keypair.Value.ColumnType.IsEnum && column.Type != typeof(int)) ||
(keypair.Value.ColumnType == typeof(ushort) && column.Type != typeof(short)) ||
(keypair.Value.ColumnType == typeof(uint) && column.Type != typeof(int)) ||
(keypair.Value.ColumnType == typeof(ulong) && column.Type != typeof(long)) ||
(!keypair.Value.IsSerialized &&
keypair.Value.ColumnType != typeof(byte[]) &&
!keypair.Value.ColumnType.IsEnum &&
!keypair.Value.IsDictionary &&
!keypair.Value.IsList &&
keypair.Value.ColumnType != typeof(ushort) &&
keypair.Value.ColumnType != typeof(uint) &&
keypair.Value.ColumnType != typeof(ulong) &&
column.Type != keypair.Value.ColumnType
)
)
{
column.Type = keypair.Value.ColumnType;
column.Length = keypair.Value.ColumnLength;
column.Scale = keypair.Value.ColumnScale;
column.Isnullable = keypair.Value.Isnullable;
column.IsKey = keypair.Value.IsKey;
column.DbType = keypair.Value.DbType.ToString();
column.IsIdentity = keypair.Value.IsIdentity;
column.IdentityNo = keypair.Value.IdentityNo;
column.IsModify = true;
list.Add(column);
}
}
}
if (list.Count > 0)
{
list.Sort((a, b) => a.Id.CompareTo(b.Id));
dbprovider.CreateColumn(tableName, list.ToArray());
}
}
public override DbExpression Visit(DbInsertExpression exp)
{
this._sqlBuilder.Append("INSERT INTO ");
this.AppendTable(exp.Table);
this._sqlBuilder.Append("(");
bool first = true;
foreach (var item in exp.InsertColumns)
{
if (first)
{
first = false;
}
else
{
this._sqlBuilder.Append(",");
}
this.QuoteName(item.Key.Name);
}
this._sqlBuilder.Append(")");
this._sqlBuilder.Append(" VALUES(");
first = true;
foreach (var item in exp.InsertColumns)
{
if (first)
{
first = false;
}
else
{
this._sqlBuilder.Append(",");
}
DbExpression valExp = item.Value.StripInvalidConvert();
AmendDbInfo(item.Key, valExp);
DbValueExpressionTransformer.Transform(valExp).Accept(this);
}
this._sqlBuilder.Append(")");
if (exp.Returns.Count > 0)
{
this._sqlBuilder.Append(" RETURNING ");
string outputParamNames = "";
for (int i = 0; i < exp.Returns.Count; i++)
{
if (i > 0)
{
this._sqlBuilder.Append(",");
outputParamNames = outputParamNames + ",";
}
DbColumn outputColumn = exp.Returns[i];
string paramName = Utils.GenOutputColumnParameterName(outputColumn.Name);
DbParam outputParam = new DbParam()
{
Name = paramName, DbType = outputColumn.DbType, Precision = outputColumn.Precision, Scale = outputColumn.Scale, Size = outputColumn.Size, Value = DBNull.Value, Direction = ParamDirection.Output
};
outputParam.Type = outputColumn.Type;
this.QuoteName(outputColumn.Name);
outputParamNames = outputParamNames + paramName;
this._parameters.Add(outputParam);
}
this._sqlBuilder.Append(" INTO ", outputParamNames);
}
return(exp);
}
public virtual GeneralQueryState AsSubQueryState()
{
DbSqlQueryExpression sqlQuery = this.CreateSqlQuery();
DbSubQueryExpression subQuery = new DbSubQueryExpression(sqlQuery);
ResultElement result = new ResultElement(this._resultElement.ScopeParameters, this._resultElement.ScopeTables);
DbTableSegment tableSeg = new DbTableSegment(subQuery, result.GenerateUniqueTableAlias());
DbFromTableExpression fromTable = new DbFromTableExpression(tableSeg);
result.FromTable = fromTable;
DbTable table = new DbTable(tableSeg.Alias);
//TODO 根据旧的生成新 MappingMembers
IMappingObjectExpression newMoe = this.Result.MappingObjectExpression.ToNewObjectExpression(sqlQuery, table);
result.MappingObjectExpression = newMoe;
//得将 subQuery.SqlQuery.Orders 告诉 以下创建的 result
//将 orderPart 传递下去
if (this.Result.Orderings.Count > 0)
{
for (int i = 0; i < this.Result.Orderings.Count; i++)
{
DbOrdering ordering = this.Result.Orderings[i];
DbExpression orderingExp = ordering.Expression;
string alias = null;
DbColumnSegment columnExpression = sqlQuery.ColumnSegments.Find(a => DbExpressionEqualityComparer.EqualsCompare(orderingExp, a.Body));
// 对于重复的则不需要往 sqlQuery.Columns 重复添加了
if (columnExpression != null)
{
alias = columnExpression.Alias;
}
else
{
alias = Utils.GenerateUniqueColumnAlias(sqlQuery);
DbColumnSegment columnSeg = new DbColumnSegment(orderingExp, alias);
sqlQuery.ColumnSegments.Add(columnSeg);
}
DbColumnAccessExpression columnAccessExpression = new DbColumnAccessExpression(table, DbColumn.MakeColumn(orderingExp, alias));
result.Orderings.Add(new DbOrdering(columnAccessExpression, ordering.OrderType));
}
}
result.InheritOrderings = true;
GeneralQueryState queryState = new GeneralQueryState(result);
return(queryState);
}
static bool SqlQueryExecute()
{
var availableDatabases = new string[]
{
"data-full",
//"data",
//"data-light",
//"data-extra-light",
"data-bin"
};
var sw = new System.Diagnostics.Stopwatch();
CsvDb.CsvDb db = null;
bool IsObjectNull(object obj, string msg, bool testFor = true)
{
if ((obj == null) == testFor)
{
con.WriteLine(msg);
return(true);
}
return(false);
}
object CreateClass(Type type, object[] parameters)
{
if (type == null)
{
return(null);
}
try
{
object obj = Activator.CreateInstance(type, parameters ?? new object[] { });
return(obj);
}
catch (Exception ex)
{
con.WriteLine($"error: {ex.Message}");
return(null);
}
}
//Action displayHelp = () =>
void displayHelp()
{
con.WriteLine(" ┌────────────────────────────────┬────────────────────────────────┬──────────────────────────────────┐");
con.WriteLine(" │ Help h help │ Clear c clear │ Quit q quit │");
con.WriteLine(" ├────────────────────────────────┴────────┬───────────────────────┴──────────────────────────────────┤");
con.WriteLine(" │ Mount database m|use|mount 'db name' │ Display database(s) display │");
con.WriteLine(" │ Kill/close database k kill │ Display Tables Info display /tables │");
con.WriteLine(" │ Search Database search │ Display Index Tree Structure │");
con.WriteLine(" │ Eexecute Queries execute │ display 'table.column' /i │");
con.WriteLine(" │ Xtreme class x │ Display Index Tree Node Structure │");
con.WriteLine(" │ │ display 'table.column' /n │");
con.WriteLine(" │ │ Non-indexed search │");
con.WriteLine(" │ │ display 'table.column' /oper:> constant │");
con.WriteLine(" │ │ Page display 'table.column' /p /offset:int │");
con.WriteLine(" │ │ Visualize recs display 'table.column' /r count │");
con.WriteLine(" ├─────────────────────────────────────────┴──────────────────────────────────────────────────────────┤");
con.WriteLine(" │ SELECT [*] | [t0.col0, t0.col1,..] | [COUNT|AVG|SUM](col) │");
con.WriteLine(" │ FROM table [t0] │");
con.WriteLine(" │ WHERE │");
con.WriteLine(" │ [INNER|CROSS|(LEFT|RIGHT|FULL) OUTER] JOIN table0 t0 ON expr:<left> oper <right> │");
con.WriteLine(" └────────────────────────────────────────────────────────────────────────────────────────────────────┘");
// ORDER BY
// SELECT * FROM [table] t
// WHERE t.Name == ""
//
// *\t[table]\t[[column],==,[value]>]
//SELECT route_id, rout_short_name FROM routes r
// WHERE r.agency_id == "NJT" AND
// r.route_type == 2
// route_id,route_short_name\t[routes]\t[agency_id],==,"NJT"\tAND\t[route_type],==,2
// SELECT * | column0,column1,...
// | a.agency_id, b.serice_id,...
//
// FROM table [descriptor]
//
// WHERE [descriptor].column = constant AND|OR ...
// SKIP number
// LIMIT number
//
}
System.Reflection.Assembly assembly = null;
var nl = Environment.NewLine;
bool end = false;
//this's the matched rule
CommandArgRulesAction matchedRule = null;
//con.TreatControlCAsInput = true;
var ruleCollection = new CommandArgRules(
new CommandArgRulesAction[]
{
new CommandArgRulesAction(
CommandArgRule.Command("q", "quit"),
() =>
{
end = true;
if (db != null)
{
db.Dispose();
}
}
),
new CommandArgRulesAction(CommandArgRule.Command("h", "help"), () => displayHelp()),
new CommandArgRulesAction(CommandArgRule.Command("c", "clear"), () => con.Clear()),
new CommandArgRulesAction(
CommandArgRule.Command("k", "kill"),
() =>
{
if (!IsObjectNull(db, $" no database to close"))
{
con.WriteLine($" closing database [{db.Name}]");
db.Dispose();
db = null;
}
}
),
new CommandArgRulesAction(
CommandArgRule.Command("m", "mount", "use"),
() =>
{
//m "data-bin"
if (!IsObjectNull(db, $"\r\nplease first unmount current database", testFor: false))
{
if ((db = OpenDatabase(
dbName: matchedRule[1].Arg.GetKey(),
logTimes: true)) != null)
{
con.WriteLine($"\r\nUsing database: {db.Name}{db.IsBinary.IfTrue(" [Binary]")}{db.IsCsv.IfTrue(" [Csv]")}");
}
}
}
).Add(CommandArgRule.KeyTypeAs(CommandArgItemType.Identifier | CommandArgItemType.String)),
new CommandArgRulesAction(
CommandArgRule.Command("s", "search"),
() =>
{
if (!IsObjectNull(db, " there's no database in use"))
{
con.Write(" query >");
var query = con.In.ReadLine();
con.WriteLine();
sw.Restart();
var dbQuery = DbQuery.Parse(query, new CsvDbDefaultValidator(db));
sw.Stop();
con.WriteLine(" query parsed on {0} ms", sw.ElapsedMilliseconds);
con.WriteLine($" {dbQuery}");
var visualizer = new DbVisualizer(
db,
dbQuery,
DbVisualize.Paged |
DbVisualize.UnderlineHeader |
DbVisualize.Framed |
DbVisualize.LineNumbers);
visualizer.Display();
visualizer.Dispose();
}
}
),
new CommandArgRulesAction(
CommandArgRule.Command("execute"),
() =>
{
if (!IsObjectNull(db, " there's no database in use"))
{
con.WriteLine("Execute database queries:\r\n -empty query ends");
string query = null;
bool finish = false;
do
{
con.Write("\r\n query: ");
query = con.ReadLine();
if (!(finish = String.IsNullOrWhiteSpace(query)))
{
try
{
sw.Restart();
var dbQuery = DbQuery.Parse(query, new CsvDbDefaultValidator(db));
sw.Stop();
con.WriteLine(" query parsed on {0} ms", sw.ElapsedMilliseconds);
con.WriteLine($" {dbQuery}");
}
catch (Exception ex)
{
con.WriteLine($" error: {ex.Message}");
}
}
} while (!finish);
}
}
),
new CommandArgRulesAction(
CommandArgRule.Command("x"),
() =>
{
if (!IsObjectNull(db, " there's no database in use"))
{
con.Write("\r\n database table as class: ");
var tbleName = con.ReadLine();
DbTable table = db.Table(tbleName);
//
if (table == null)
{
con.WriteLine($"cannot find table [{tbleName}]");
}
else
{
if (assembly == null)
{
assembly = Utils.CreateDbClasses(db);
if (assembly == null)
{
con.WriteLine("Cannot generate database table classes");
}
else
{
con.WriteLine("database table classes generated succesfully!");
}
}
if (assembly != null)
{
//I can compile code once and load assembly at start
// just recompile when database table changes
//var an = System.Reflection.AssemblyName.GetAssemblyName(filePath);
//System.Reflection.Assembly.Load(an);
//AppDomain.CurrentDomain.Load(assembly.GetName());
//get it OK!
//Type type = assembly.GetType($"CsvDb.Dynamic.{tbleName}");
//object obj = Activator.CreateInstance(type);
//this was a test, constructor must be parameterless so CsvHelper can create it
Type dynTbleClass = assembly.GetType($"CsvDb.Dynamic.{tbleName}");
object obj = CreateClass(
dynTbleClass,
new object[] {
//table
}
);
var mthd = dynTbleClass.GetMethod("Link");
mthd.Invoke(obj, new object[]
{
table
});
//now I can use CsvHelper to parse CSV rows using this classes if needed
//don't get it
var classType = Type.GetType($"CsvDb.Dynamic.{tbleName}");
con.WriteLine("ok");
}
}
}
}
),
new CommandArgRulesAction(
CommandArgRule.Command("display"),
() =>
{
//compare
//display "routes.route_id" /oper:>= 250
if (!IsObjectNull(db, " there's no database in use"))
{
var dbTblCol = matchedRule[1].Arg.GetKey();
var operArg = matchedRule[2].Arg as CommandArgKeypair;
var constArg = matchedRule[3].Arg;
DbColumn column = null;
if ((column = db.Index(dbTblCol)) != null &&
operArg.Value.TryParseToken(out TokenType token))
{
var reader = DbTableDataReader.Create(db, column.Table);
object value = column.TypeEnum.ToObject(constArg.Key);
var collection = (IEnumerable <int>)Utils.CallGeneric(
reader, nameof(DbTableDataReader.Compare), column.TypeEnum,
new object[] { column, token, value },
System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance
);
var lista = collection.ToList();
con.WriteLine($" ({lista.Count}) match(es)");
}
}
}
/// <summary>
/// Extracts a DbColumn's datatype and turns it into script ready
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
/// <seealso cref="Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SmoColumnCustomNodeHelper.GetTypeSpecifierLabel"/>
/// <exception cref="InvalidOperationException"></exception>
public static string FormatColumnType(DbColumn column, bool useSemanticEquivalent = false)
{
string typeName = column.DataTypeName.ToUpperInvariant();
// TODO: This doesn't support UDTs at all.
// TODO: It's unclear if this will work on a case-sensitive db collation
// Strip any unecessary info from the front certain types
if (typeName.EndsWith("HIERARCHYID") || typeName.EndsWith("GEOGRAPHY") || typeName.EndsWith("GEOMETRY"))
{
string[] typeNameComponents = typeName.Split(".");
typeName = typeNameComponents[typeNameComponents.Length - 1];
}
// Replace timestamp columns with semantic equivalent if requested
if (useSemanticEquivalent && typeName == "TIMESTAMP")
{
typeName = "VARBINARY(8)";
}
// If the type supports length parameters, the add those
switch (typeName)
{
// Types with length
case "CHAR":
case "NCHAR":
case "VARCHAR":
case "NVARCHAR":
case "BINARY":
case "VARBINARY":
if (!column.ColumnSize.HasValue)
{
throw new InvalidOperationException(SR.SqlScriptFormatterLengthTypeMissingSize);
}
string length = column.ColumnSize.Value == int.MaxValue
? "MAX"
: column.ColumnSize.Value.ToString();
typeName += $"({length})";
break;
// Types with precision and scale
case "NUMERIC":
case "DECIMAL":
if (!column.NumericPrecision.HasValue || !column.NumericScale.HasValue)
{
throw new InvalidOperationException(SR.SqlScriptFormatterDecimalMissingPrecision);
}
typeName += $"({column.NumericPrecision}, {column.NumericScale})";
break;
// Types with scale only
case "DATETIME2":
case "DATETIMEOFFSET":
case "TIME":
if (!column.NumericScale.HasValue)
{
throw new InvalidOperationException(SR.SqlScriptFormatterScalarTypeMissingScale);
}
typeName += $"({column.NumericScale})";
break;
}
return(typeName);
}
/*
* bool TableHasActiveOrStickyColumns(DbTable table)
* {
* if (Parser.activeTablesWithColumns.Contains(table))
* return true;
* if (table.HasBookmarkedColumns())
* return true;
* return false;
*
* }
*/
public void UpdateObjects(bool clear)
{
bool all = Name.Contains("all");
updating = true;
BeginUpdate();
if (clear)
{
nodesByObjectName.Clear();
Nodes.Clear();
}
QListSort sort = S.Get("recentObjectsAtTop", false) ? QListSort.Descending : QListSort.None;
foreach (DbTable table in A.db.tables.Each(sort))
{
if (search != "")
{
bool matchingColumnExists = false;
if (searchColumns)
{
foreach (string columnName in table.columns.Keys)
{
if (columnName.StartsWith(search))
{
matchingColumnExists = true;
break;
}
}
}
if (matchingColumnExists == false)
{
if (searchTables == false || table.name.StartsWith(search) == false)
{
continue;
}
}
}
bool tableIsActive = table.active;
bool tableIsSticky = table.bookmarked;
bool tableIsShowAllColumns = table.showAllColumns;
bool showColumnsAnyways = !table.HasBookmarkedOrActiveColumns();
if (all || tableIsActive || tableIsSticky)
{
TreeNode tableNode = CreateOrUpdateTableTreeNode(table, tableIsActive, tableIsSticky);
foreach (string columnName in table.columns.Keys)
{
DbColumn column = table.columns[columnName];
if ((search == "") || (searchColumns == false) || columnName.StartsWith(search))
{
bool columnIsActive = column.active;
bool columnIsSticky = column.bookmarked;
if (all || tableIsShowAllColumns || columnIsActive || columnIsSticky || showColumnsAnyways)
{
CreateOrUpdateColumnTreeNode(tableNode, table, column, columnIsActive, columnIsSticky);
}
}
}
if (search != "" && searchColumns)
{
tableNode.Expand();
}
else if (!ExpandNodeBySettings(table, tableNode))
{
if (all == false && tableIsShowAllColumns)
{
tableNode.Expand();
}
else if (expandByDefault)
{
tableNode.Expand();
}
}
}
}
EndUpdate();
updating = false;
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables, SQLServerVersions sqlServer)
{
/*
* sql 2005 format
* SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME,
* O1.name) AS PKTABLE_NAME, CONVERT(SYSNAME, C1.name) AS PKCOLUMN_NAME, CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER,
* CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS FKTABLE_NAME, CONVERT(SYSNAME, C2.name)
* AS FKCOLUMN_NAME, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE,
* CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME,
* OBJECT_NAME(F.object_id)) AS FK_NAME, CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action,
* F.update_referential_action
* FROM sys.all_objects AS O1 INNER JOIN
* sys.foreign_keys AS F INNER JOIN
* sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN
* sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN
* sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN
* sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN
* sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id
*/
// GENERAL command format
string foreignKeySql = @"SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable',
c1.name AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.name AS 'PKColumnName' ,
-1 as update_referential_action, -1 as delete_referential_action
FROM sysforeignkeys AS f
INNER JOIN syscolumns AS c1 ON f.fkeyid = c1.id AND f.fkey = c1.colid
INNER JOIN syscolumns AS c2 ON f.rkeyid = c2.id AND f.rkey = c2.colid
ORDER BY 'FKTable', c1.colid ";
// NEW command format
if (sqlServer > SQLServerVersions.SQL2000)
{
foreignKeySql =
@"SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME,
O1.name) AS 'PKTable', CONVERT(SYSNAME, C1.name) AS 'PKColumnName', CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER,
CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS 'FKTable', CONVERT(SYSNAME, C2.name)
AS 'FKColumnName', CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE,
CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME,
OBJECT_NAME(F.object_id)) AS 'ForeignKey', CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action,
F.update_referential_action
FROM sys.all_objects AS O1 INNER JOIN
sys.foreign_keys AS F INNER JOIN
sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN
sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN
sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN
sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN
sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id
ORDER BY 'FKTable', C2.column_id";
}
try
{
using (var adapter = new SqlDataAdapter(foreignKeySql, (SqlConnection)_dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable())
{
// Just to avoid stupid "Failed to enable constraints" error!
using (var tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(keysData);
// Get from db
adapter.Fill(keysData);
}
if (keysData.Rows.Count > 0)
{
foreach (DataRow keysDataRow in keysData.Rows)
{
var foreignKeyTableName = keysDataRow["FKTable"].ToString();
var primaryKeyTableName = keysDataRow["PKTable"].ToString();
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// one-to-many foreign relation will be added
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKey_Local = new DbForeignKey()
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignTableName = keysDataRow["FKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.ManyToOne &&
x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName))
{
continue;
}
manyMultiplicityKey_Local.UpdateAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
manyMultiplicityKey_Local.DeleteAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local);
// apply local column
DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName);
manyMultiplicityKey_Local.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKey_Local.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName);
manyMultiplicityKey_Local.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKey_Local.ForeignTable = null;
manyMultiplicityKey_Local.ForeignColumn = null;
}
}
// one-to-? foreign relation will be added
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKey_Foreign = new DbForeignKey()
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignTableName = keysDataRow["PKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.OneToMany &&
x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
{
continue;
}
oneMultiplicityKey_Foreign.UpdateAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
oneMultiplicityKey_Foreign.DeleteAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName);
oneMultiplicityKey_Foreign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName);
oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKey_Foreign.ForeignTable = null;
oneMultiplicityKey_Foreign.ForeignColumn = null;
}
}
}// all foreign keys
// look for one-to-one situation!
}
}
}
}
catch
{
// Seems this version of SQL Server doesn't support this query!
// don't stop here!
}
}
internal DbColumnModelInfo(DbColumn col)
{
_col = col ?? throw new ArgumentNullException(nameof(col));
}
/// <summary>
/// Specifies a table alias of a mapped column.
/// </summary>
/// <param name="column">Is a mapped column object that represents a column in the SQL Server.</param>
/// <param name="tableAlias">Is a table alias that should belong to a table where the column is mapped to.</param>
public static OfChainer Of(this DbColumn column, string tableAlias)
{
return(new OfChainer(null, column, tableAlias));
}
/// <summary>
/// Column additional information
/// </summary>
private bool FillColumnAdditionalInfo(DbColumn toSetColumn, DataTable columnsDbTypeTable, string tableName, string columnName)
{
DataRow[] addInfo = columnsDbTypeTable.Select(String.Format("TABLE_NAME='{0}' AND COLUMN_NAME='{1}'",
tableName,
columnName));
if (addInfo.Length == 0)
{
// can't find because of invalid name?? try this
for (int i = 0; i < columnsDbTypeTable.Rows.Count; i++)
{
var row = columnsDbTypeTable.Rows[i];
if (row["TABLE_NAME"].ToString() == tableName &&
row["COLUMN_NAME"].ToString() == columnName)
{
addInfo = new DataRow[] { row };
break;
}
}
}
if (addInfo.Length == 0)
{
// sometimes when a column has invalid name for string format this happends!
// Still no chance
return(false);
}
object tempInfo = null;
DataRow columnInfo = addInfo[0];
toSetColumn.DataTypeDb = columnInfo["DATA_TYPE"].ToString();
toSetColumn.Owner = columnInfo["TABLE_SCHEMA"].ToString();
tempInfo = columnInfo["CHARACTER_MAXIMUM_LENGTH"];
if (tempInfo != null && tempInfo != DBNull.Value)
{
toSetColumn.DataTypeMaxLength = Convert.ToInt32(tempInfo);
if (toSetColumn.DataTypeMaxLength == -1)
{
//toSetColumn.LengthIsMax = true;
toSetColumn.DataTypeMaxLength = int.MaxValue;
}
else
{
//toSetColumn.LengthIsMax = false;
}
}
else
{
toSetColumn.DataTypeMaxLength = toSetColumn.Length;
}
tempInfo = columnInfo["NUMERIC_SCALE"];
if (tempInfo != null && tempInfo != DBNull.Value)
{
toSetColumn.NumericScale = Convert.ToInt32(tempInfo);
}
else
{
toSetColumn.NumericScale = -1;
}
tempInfo = columnInfo["NUMERIC_PRECISION"];
if (tempInfo != null && tempInfo != DBNull.Value)
{
toSetColumn.NumericPrecision = Convert.ToInt32(tempInfo);
}
else
{
toSetColumn.NumericPrecision = -1;
}
return(true);
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
try
{
// Used to get columns Sql DataType
const string cmd = "SELECT A.COLUMN_NAME as UNIQUE_COLUMN_NAME,C.COLUMN_NAME as CONSTRAINT_COLUMN_NAME , B.* FROM INFORMATION_SCHEMA.indexes A INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS B ON UNIQUE_CONSTRAINT_NAME = INDEX_NAME inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON C.CONSTRAINT_NAME=B.CONSTRAINT_NAME";
using (var adapter = new SqlCeDataAdapter(cmd, _dbConnection.ConnectionString))
using (var foreignKeysTable = new DataTable())
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(foreignKeysTable);
// nothing found!)
if (foreignKeysTable.Rows.Count == 0)
{
return;
}
foreach (DataRow keyRow in foreignKeysTable.Rows)
{
var foreignKeyTableName = keyRow["CONSTRAINT_TABLE_NAME"].ToString();
var primaryKeyTableName = keyRow["UNIQUE_CONSTRAINT_TABLE_NAME"].ToString();
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// one-to-many foreign relation will be added
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKey_Local = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["UNIQUE_COLUMN_NAME"].ToString(),
ForeignColumnName = keyRow["CONSTRAINT_COLUMN_NAME"].ToString(),
ForeignTableName = keyRow["CONSTRAINT_TABLE_NAME"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName))
{
continue;
}
manyMultiplicityKey_Local.UpdateAction =
ConvertSqlCeForeignKeyAction(keyRow["UPDATE_RULE"].ToString());
manyMultiplicityKey_Local.DeleteAction =
ConvertSqlCeForeignKeyAction(keyRow["DELETE_RULE"].ToString());
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local);
// apply local column
DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName);
manyMultiplicityKey_Local.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKey_Local.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName);
manyMultiplicityKey_Local.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKey_Local.ForeignTable = null;
manyMultiplicityKey_Local.ForeignColumn = null;
}
}
// one-to-one foreign relation will be added
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKey_Foreign = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["CONSTRAINT_COLUMN_NAME"].ToString(),
ForeignColumnName = keyRow["UNIQUE_COLUMN_NAME"].ToString(),
ForeignTableName = keyRow["UNIQUE_CONSTRAINT_TABLE_NAME"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
{
continue;
}
oneMultiplicityKey_Foreign.UpdateAction =
ConvertSqlCeForeignKeyAction(keyRow["UPDATE_RULE"].ToString());
oneMultiplicityKey_Foreign.DeleteAction =
ConvertSqlCeForeignKeyAction(keyRow["DELETE_RULE"].ToString());
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName);
oneMultiplicityKey_Foreign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName);
oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKey_Foreign.ForeignTable = null;
oneMultiplicityKey_Foreign.ForeignColumn = null;
}
}
} // all keys
}
}
finally
{
_dbConnection.Close();
}
}
public async Task <object> RunQuery(string query)
{
// List<object> rows = new List<object>();
StringBuilder sb = new StringBuilder();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
command.CommandText = query;
DbDataReader reader = await command.ExecuteReaderAsync();
sb.Append("[");
if (reader.HasRows)
{
var colsDb = reader.GetColumnSchema();
DbColumn[] colNames = new DbColumn[reader.FieldCount];
colsDb.CopyTo(colNames, 0);
bool first = true;
while (await reader.ReadAsync())
{
if (!first)
{
sb.Append(",");
}
sb.Append("{");
Object[] row = new Object[reader.FieldCount];
reader.GetValues(row);
int i = 0;
foreach (var col in row)
{
var columnDB = colNames[i];
string colName = columnDB.ColumnName;
sb.Append("\"" + colName + "\": " + "\"" + col.ToString() + "\"");
if (i < reader.FieldCount - 1)
{
sb.Append(",");
}
i++;
}
sb.Append("}");
first = false;
}
}
sb.Append("]");
reader.Dispose();
}
}
finally
{
conn.Close();
}
string s = sb.ToString();
return(s);
}
public static bool Save(IList <NodeExcel> rootList)
{
var list = new List <Excel>();
foreach (var e in rootList)
{
var ex = new Excel()
{
Name = e.Name, Description = e.Description
};
ex.Nodes = new List <Tab>();
list.Add(ex);
if (e.Nodes == null)
{
continue;
}
foreach (var t in e.Nodes)
{
var tab = new Tab()
{
Name = t.Name, Description = t.Description
};
tab.Nodes = new List <DbTable>();
ex.Nodes.Add(tab);
if (t.Nodes == null)
{
continue;
}
foreach (var table in t.Nodes)
{
var dbTable = new DbTable()
{
Name = table.Name, Description = table.Description
};
dbTable.Nodes = new List <DbColumn>();
tab.Nodes.Add(dbTable);
if (table.Nodes == null)
{
continue;
}
foreach (var column in table.Nodes)
{
var dbColumn = new DbColumn()
{
DbColumnName = column.DBColumnName, Name = column.Name, Description = column.Description
};
dbTable.Nodes.Add(dbColumn);
}
}
}
}
var exePath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
var dataPath = string.Format("{0}\\Data", exePath);
var data = string.Format("{0}\\data.json", dataPath);
var json = JsonConvert.SerializeObject(list, Formatting.Indented);
var fi = new FileInfo(data);
using (var stream = fi.CreateText())
{
stream.Write(json);
}
return(true);
}
public override DbExpression Visit(DbSqlQueryExpression exp)
{
if (exp.TakeCount != null)
{
DbSqlQueryExpression newSqlQuery = CloneWithoutLimitInfo(exp, "TTAKE");
if (exp.SkipCount == null)
{
AppendLimitCondition(newSqlQuery, exp.TakeCount.Value);
}
else
{
AppendLimitCondition(newSqlQuery, exp.TakeCount.Value + exp.SkipCount.Value);
newSqlQuery.SkipCount = exp.SkipCount.Value;
}
newSqlQuery.Accept(this);
return(exp);
}
else if (exp.SkipCount != null)
{
DbSqlQueryExpression subSqlQuery = CloneWithoutLimitInfo(exp, "TSKIP");
string row_numberName = GenRowNumberName(subSqlQuery.ColumnSegments);
DbColumnSegment row_numberSeg = new DbColumnSegment(OracleSemantics.DbMemberExpression_ROWNUM, row_numberName);
subSqlQuery.ColumnSegments.Add(row_numberSeg);
DbTable table = new DbTable("T");
DbSqlQueryExpression newSqlQuery = WrapSqlQuery(subSqlQuery, table, exp.ColumnSegments);
DbColumnAccessExpression columnAccessExp = new DbColumnAccessExpression(table, DbColumn.MakeColumn(row_numberSeg.Body, row_numberName));
newSqlQuery.Condition = DbExpression.GreaterThan(columnAccessExp, DbExpression.Constant(exp.SkipCount.Value));
newSqlQuery.Accept(this);
return(exp);
}
this.BuildGeneralSql(exp);
return(exp);
}
private static void ModifyTableSchema(SchemaTable schema, DbBaseProvider dbprovider, string tableName, DbColumn[] columns)
{
var list = new List <DbColumn>();
foreach (var keypair in schema.Columns)
{
var field = keypair.Value;
string name = field.Name;
var dbColumn = Array.Find(columns, p => MathUtils.IsEquals(p.Name, name, true));
if (dbColumn == null)
{
dbColumn = new DbColumn();
dbColumn.Id = field.Id;
dbColumn.Name = name;
dbColumn.Type = field.ColumnType;
dbColumn.Length = field.ColumnLength;
dbColumn.Scale = field.ColumnScale;
dbColumn.Isnullable = field.Isnullable;
dbColumn.IsKey = field.IsKey;
dbColumn.IsUnique = field.IsUnique;
dbColumn.DbType = field.DbType.ToString();
dbColumn.IsIdentity = field.IsIdentity;
dbColumn.IdentityNo = field.IdentityNo;
list.Add(dbColumn);
}
else
{
var fieldType = field.ColumnType;
//no modify type: text,blob,byte[], enum,list,dict
if (
//对象序列化类型
(field.IsSerialized &&
(
(field.DbType == ColumnDbType.Varchar && (dbColumn.Type != typeof(string) || (field.ColumnLength > 0 && dbColumn.Length != field.ColumnLength))) ||
(field.DbType != ColumnDbType.Varchar && (dbColumn.Type != typeof(string) || dbColumn.DbType.StartsWith("varchar", true)))
)
) ||
//特殊值类型
(dbColumn.Type == typeof(decimal) && field.ColumnScale > 0 && dbColumn.Scale != field.ColumnScale) ||
(fieldType.IsEnum && dbColumn.Type != typeof(int)) ||
(fieldType == typeof(ushort) && dbColumn.Type != typeof(short)) ||
(fieldType == typeof(uint) && dbColumn.Type != typeof(int)) ||
(fieldType == typeof(ulong) && dbColumn.Type != typeof(long)) ||
(fieldType == typeof(string) && field.ColumnLength > 0 && dbColumn.Length != field.ColumnLength) ||
//非对象类型
(!field.IsSerialized &&
!fieldType.IsEnum &&
!field.IsDictionary &&
!field.IsList &&
fieldType != typeof(byte[]) &&
fieldType != typeof(ushort) &&
fieldType != typeof(uint) &&
fieldType != typeof(ulong) &&
dbColumn.Type != fieldType
) ||
//check key
((field.IsKey && dbColumn.KeyNo == 0) || (!field.IsKey && dbColumn.KeyNo > 0))
)
{
dbColumn.Type = fieldType;
dbColumn.Length = field.ColumnLength;
dbColumn.Scale = field.ColumnScale;
dbColumn.Isnullable = field.Isnullable;
dbColumn.IsKey = field.IsKey;
dbColumn.DbType = field.DbType.ToString();
dbColumn.IsIdentity = field.IsIdentity;
dbColumn.IdentityNo = field.IdentityNo;
dbColumn.IsModify = true;
list.Add(dbColumn);
}
}
}
if (list.Count > 0)
{
list.Sort((a, b) => a.Id.CompareTo(b.Id));
dbprovider.CreateColumn(tableName, list.ToArray());
}
}
static DbColumnSegment CloneColumnSegment(DbColumnSegment rawColumnSeg, DbTable newBelongTable)
{
DbColumnAccessExpression columnAccessExp = new DbColumnAccessExpression(newBelongTable, DbColumn.MakeColumn(rawColumnSeg.Body, rawColumnSeg.Alias));
DbColumnSegment newColumnSeg = new DbColumnSegment(columnAccessExp, rawColumnSeg.Alias);
return(newColumnSeg);
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(string tableName)
{
var result = new List <DbColumn>();
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
using (var adapter = new SQLiteDataAdapter(String.Format("SELECT * FROM {0} LIMIT 1 ", tableName), _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 (var 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
FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// Add to result
result.Add(column);
}
}
}
return(result);
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
// Used to get columns Sql DataType
using (DataTable foreignKeysTable = _dbConnection.GetSchema("ForeignKeys"))
{
// nothing found!
if (foreignKeysTable.Rows.Count == 0)
{
return;
}
// find description if there is any
foreach (var table in tables)
{
// only FOREIGN KEY
foreignKeysTable.DefaultView.RowFilter =
string.Format(" CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='{0}' ", table.TableName);
// Fetch the rows
foreach (DataRowView keysData in foreignKeysTable.DefaultView)
{
// foreign key found!
DataRow keyRow = keysData.Row;
var foreignKeyTableName = keyRow["FKEY_TO_TABLE"].ToString();
var primaryKeyTableName = table.TableName;
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = table;
if (primaryKeyTable != null)
{
// foreign key
var foreignKey = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(),
ForeignColumnName = keyRow["FKEY_TO_COLUMN"].ToString(),
ForeignTableName = keyRow["FKEY_TO_TABLE"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// add foreign key
table.ForeignKeys.Add(foreignKey);
// apply local column
DbColumn localColumn = table.FindColumnDb(foreignKey.LocalColumnName);
foreignKey.LocalColumn = localColumn;
//apply foreign table
DbTable foreignTable = foreignKeyTable;
// referenced key
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = foreignTable;
}
if (foreignTable != null)
{
foreignKey.ForeignTable = foreignTable;
// apply foreign column
DbColumn foreignColumn = foreignTable.FindColumnDb(foreignKey.ForeignColumnName);
foreignKey.ForeignColumn = foreignColumn;
}
else
{
foreignKey.ForeignTable = null;
foreignKey.ForeignColumn = null;
}
}
// adding the relation to the foreign table!
if (foreignKeyTable != null)
{
// foreign key
var oneMultiplicityKey_Foreign = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["FKEY_TO_COLUMN"].ToString(),
ForeignColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(),
ForeignTableName = primaryKeyTableName,
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
{
continue;
}
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName);
oneMultiplicityKey_Foreign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName);
oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKey_Foreign.ForeignTable = null;
oneMultiplicityKey_Foreign.ForeignColumn = null;
}
}
}
}
}
}
public void UpdateColumns()
{
try
{
/*if (DbCurrent.GetSchema("DataTypes") == null)
* {
* DbColumns = new DbColumn[] { };
* return;
* }*/
Conversion cnv = new Conversion();
DataTable dt = DbCurrent.GetSchema("Columns");
string ColumnDataType = "";
string ColumnDecimalDigits = "";
string ColumnSize = "";
bool NumericPrecisionExists = false;
if (ColumnExists(dt, "TYPE_NAME"))
{
ColumnDataType = "TYPE_NAME";
}
else if (ColumnExists(dt, "DATA_TYPE"))
{
ColumnDataType = "DATA_TYPE";
}
else if (ColumnExists(dt, "COLUMN_DATA_TYPE"))
{
ColumnDataType = "COLUMN_DATA_TYPE";
}
if (ColumnExists(dt, "NUMERIC_SCALE"))
{
ColumnDecimalDigits = "NUMERIC_SCALE";
}
else if (ColumnExists(dt, "DECIMAL_DIGITS"))
{
ColumnDecimalDigits = "DECIMAL_DIGITS";
}
if (ColumnExists(dt, "CHARACTER_MAXIMUM_LENGTH"))
{
ColumnSize = "CHARACTER_MAXIMUM_LENGTH";
}
else if (ColumnExists(dt, "COLUMN_SIZE"))
{
ColumnSize = "COLUMN_SIZE";
}
NumericPrecisionExists = ColumnExists(dt, "NUMERIC_PRECISION");
//DbColumn[] cols = new DbColumn[dt.Rows.Count];
List <DbColumn> cols = new List <DbColumn>();
for (int i = 0; i < dt.Rows.Count; i++)
{
/*cols[i] = new DbColumn();
* cols[i].TABLE_NAME = dt.Rows[i]["TABLE_NAME"].ToString();
* cols[i].COLUMN_NAME = dt.Rows[i]["COLUMN_NAME"].ToString();
* cols[i].DATA_TYPE = dt.Rows[i][ColumnDataType].ToString();
* cols[i].DECIMAL_DIGITS = cnv.ToInt(dt.Rows[i][ColumnDecimalDigits]);
*
* if (NumericPrecisionExists)
* { cols[i].COLUMN_SIZE = cnv.ToInt(dt.Rows[i]["NUMERIC_PRECISION"]) == 0 ? cnv.ToInt(dt.Rows[i][ColumnSize]) : cnv.ToInt(dt.Rows[i]["NUMERIC_PRECISION"]); }
* else
* { cols[i].COLUMN_SIZE = cnv.ToInt(dt.Rows[i][ColumnSize]); }
*/
DbColumn column = new DbColumn();
column.TABLE_NAME = dt.Rows[i]["TABLE_NAME"].ToString();
column.COLUMN_NAME = dt.Rows[i]["COLUMN_NAME"].ToString();
column.DATA_TYPE = dt.Rows[i][ColumnDataType].ToString();
column.DECIMAL_DIGITS = cnv.ToInt(dt.Rows[i][ColumnDecimalDigits]);
if (NumericPrecisionExists)
{
column.COLUMN_SIZE = cnv.ToInt(dt.Rows[i]["NUMERIC_PRECISION"]) == 0 ? cnv.ToInt(dt.Rows[i][ColumnSize]) : cnv.ToInt(dt.Rows[i]["NUMERIC_PRECISION"]);
}
else
{
column.COLUMN_SIZE = cnv.ToInt(dt.Rows[i][ColumnSize]);
}
cols.Add(column);
}
DbColumns = cols.ToArray();
}
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show("Erro ao pesquisar as colunas.\n" + ex.Message); }
}
/// <summary>
/// Specifies a table alias of a mapped column.
/// </summary>
/// <param name="column">Is a mapped column object that represents a column in the SQL Server.</param>
/// <param name="tableAlias">Is a table alias that should belong to a table where the column is mapped to.</param>
public static OfChainer Of(this DbColumn column, int tableAlias)
{
return(new OfChainer(null, column, tableAlias.ToString()));
}
/// <summary>
/// Reads tables index keys
/// </summary>
private void ApplyTablesConstraintKeys(List <DbTable> tables, OracleServerVersions sqlVersion)
{
if (Cache_Indexes.Rows.Count == 0)
{
return;
}
// find description if there is any
foreach (var table in tables)
{
// filter row
Cache_Indexes.DefaultView.RowFilter = string.Format("TABLE_NAME='{0}'", table.TableName);
// fetch findings, if there is any
foreach (DataRowView keysDataRow in Cache_Indexes.DefaultView)
{
// found table !
DataRow keyRow = keysDataRow.Row;
var indexName = keyRow["INDEX_NAME"].ToString();
// it should not be a primary key!
DataRow[] indexColumnInfo = Cache_IndexColumns.Select(string.Format("INDEX_NAME='{0}'", indexName));
// column information
if (indexColumnInfo == null || indexColumnInfo.Length == 0)
{
continue;
}
var columnName = indexColumnInfo[0]["COLUMN_NAME"].ToString();
// check if this is aprimary key!
DataRow[] primaryKeyInfo = Cache_All_Constraints.Select(string.Format("OWNER='{0}' AND TABLE_NAME='{1}' AND CONSTRAINT_TYPE='{2}' AND COLUMN_NAME='{3}'",
SpecificOwner.ToUpper(),
table.TableName,
STR_ConstraintType_Primarykey,
columnName));
if (primaryKeyInfo != null && primaryKeyInfo.Length > 0)
{
// sorry! this is a primary key and it is already added
// next!
continue;
}
const string STR_IndexUniqueName = "UNIQUE";
const string STR_IndexNonUniqueName = "NONUNIQUE";
// constraint Key and its uniqueness
var constraintKey = new DbConstraintKey()
{
IsUnique = (keyRow["UNIQUENESS"].ToString() == STR_IndexUniqueName),
KeyColumnName = columnName,
KeyName = indexName
};
// constraint keys
table.ConstraintKeys.Add(constraintKey);
// find key column
DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName);
constraintKey.KeyColumn = keyColumn;
}
}
}
