/// <summary>
/// Fills the table parameter with the information from the row
/// </summary>
/// <param name="table"></param>
/// <param name="row"></param>
protected override void FillTableData(DBSchemaTable table, DataRow row)
{
DataTable tbl = row.Table;
DataColumn name = GetColumn(tbl, "TABLE_NAME", true);
table.Name = GetColumnStringValue(row, name);
}
protected override void FillTableData(DBSchemaTable table, DataRow row)
{
DataColumn cat = GetColumn(row.Table, "CATALOG", false);
DataColumn schema = GetColumn(row.Table, "OWNER", false);
DataColumn name = GetColumn(row.Table, "TABLE_NAME", true);
table.Catalog = GetColumnStringValue(row, cat);
table.Schema = GetColumnStringValue(row, schema);
table.Name = GetColumnStringValue(row, name);
}
private bool IsColumnExist(DBSchemaTable table, params DBColumn[] columns)
{
foreach (DBColumn col in columns)
{
if (table.Columns.Contains(col.Name))
{
return(true);
}
;
}
return(false);
}
public static async Task <int> synSever(string ClientID)
{
System.Data.DataTable dt = new DataTable();
ClientServer c = new ClientServer(ClientID);
Tz.ClientManager.Server s = c.GetServer();
Tech.Data.DBDatabase db = Tech.Data.DBDatabase.Create(s.Connection(), "MySql.Data.MySqlClient");
Tech.Data.Schema.DBSchemaProvider provider = db.GetSchemaProvider();
IEnumerable <Tech.Data.Schema.DBSchemaItemRef> tables = provider.GetAllTables();
int count = 0;
List <Tz.Net.Entity.Table> dtTb = Net.Entity.Table.GetTables(ClientID, s.ServerID);
await Task.Run(() =>
{
var dm = new DataManager(s, c.ClientID);
foreach (Tech.Data.Schema.DBSchemaItemRef df in tables)
{
if (dtTb.Where(x => x.TableName.ToLower() == df.Name.ToLower()).FirstOrDefault() != null)
{
continue;
}
dm.NewTable(df.Name, df.Catalog);
DBSchemaTable schema = provider.GetTable(df.Name);
foreach (Tech.Data.Schema.DBSchemaTableColumn dc in schema.Columns)
{
if (dc.ColumnFlags == Tech.Data.DBColumnFlags.Nullable)
{
dm.AddField(dc.Name, dc.DbType, dc.Size, true);
}
else if (dc.ColumnFlags == Tech.Data.DBColumnFlags.PrimaryKey)
{
dm.AddPrimarykey(dc.Name, dc.DbType, dc.Size);
}
else
{
dm.AddField(dc.Name, dc.DbType, dc.Size, false);
}
}
try {
dm.AcceptChanges(true);
}
catch (Exception ex) {
}
count = count + 1;
}
return(count);
});
return(count);
}
public void AddEmployeeNoAliasField()
{
DBSchemaProvider provider = db.GetSchemaProvider();
DBSchemaTable table = provider.GetTable("sys_User");
var fld = table.Columns.Where(x => x.Name.ToLower() == "oldemployeeno").FirstOrDefault();
if (fld == null)
{
string tblschema = "ALTER TABLE sys_User ADD oldemployeeno varchar(255) NULL";
try
{
int r = db.ExecuteNonQuery(tblschema);
}
catch (System.Exception ex)
{
// throw new Exception.TableSchemaException(this.Table, fields, Exception.OperaitonType.addkey, ex.Message);
}
}
// DBSchemaItemRef table = tables.Where(x => x.Name.ToLower() == ).FirstOrDefault();
}
/// <summary>
///
/// </summary>
/// <param name="tb"></param>
/// <returns></returns>
public JsonResult GetFields(string clientid, string tb)
{
System.Data.DataTable dt = new DataTable();
ClientServer c = new ClientServer(clientid);
Data.DBDatabase db = Data.DBDatabase.Create(c.GetServer().Connection(), "MySql.Data.MySqlClient");
// Data.DBDatabase db = Data.DBDatabase.Create("Server=52.163.241.42; Uid=admin;Pwd=smrtalentoz3106;Initial Catalog=jumbo_talentoz; ", "MySql.Data.MySqlClient");
Data.Schema.DBSchemaProvider provider = db.GetSchemaProvider();
DBSchemaTable tables = provider.GetTable(tb);
if (tables == null)
{
DBSchemaView view = provider.GetView(tb);
return(Json(Newtonsoft.Json.JsonConvert.SerializeObject(view), JsonRequestBehavior.AllowGet));
}
else
{
return(Json(Newtonsoft.Json.JsonConvert.SerializeObject(tables), JsonRequestBehavior.AllowGet));
}
}
//
// Table overrides
//
#region protected override DBSchemaTable LoadATable(DbConnection con, DBSchemaItemRef tableref)
/// <summary>
/// Loads the info on a specific table
/// </summary>
/// <param name="con"></param>
/// <param name="tableref"></param>
/// <returns></returns>
protected override DBSchemaTable LoadATable(DbConnection con, DBSchemaItemRef tableref)
{
DataTable dtTable = GetTableData(con, tableref);
DBSchemaTable atable = null;
if (null != dtTable && dtTable.Rows.Count > 0)
{
atable = new DBSchemaTable();
this.FillTableData(atable, dtTable.Rows[0]);
DataTable dtColumns = this.GetTableColumns(con, tableref);
if (null != dtColumns)
{
this.FillTableColumns(atable.Columns, dtColumns);
}
DBSchemaItemRefCollection idxs = new DBSchemaItemRefCollection();
DBSchemaItemRefCollection fks = new DBSchemaItemRefCollection();
this.LoadForeignKeyRefs(con, fks, tableref);
this.LoadIndexRefs(con, idxs, tableref);
DBSchemaIndexCollection indexes = new DBSchemaIndexCollection();
foreach (DBSchemaItemRef idx in idxs)
{
DBSchemaIndex same;
DBSchemaIndex anindex = this.LoadAnIndex(con, idx);
if (indexes.TryGetIndex(idx, out same))
{
same.Columns.AddRange(anindex.Columns);
}
else
{
indexes.Add(anindex);
}
}
foreach (DBSchemaIndex idx in indexes)
{
if (idx.IsPrimaryKey)
{
foreach (DBSchemaIndexColumn idxcol in idx.Columns)
{
DBSchemaTableColumn tblcol;
if (atable.Columns.TryGetColumn(idxcol.ColumnName, out tblcol))
{
tblcol.PrimaryKey = true;
}
}
}
}
DBSchemaForeignKeyCollection foreignkeys = new DBSchemaForeignKeyCollection();
foreach (DBSchemaItemRef fk in fks)
{
DBSchemaForeignKey aForeignKey = this.LoadAForeignKey(con, fk);
foreignkeys.Add(aForeignKey);
}
atable.ForeignKeys = foreignkeys;
atable.Indexes = indexes;
}
return(atable);
}
//
// generate CREATE scripts
//
#region public override void GenerateCreateTableScript(DBSchemaTable schemaTable)
public override void GenerateCreateTableScript(DBSchemaTable schemaTable)
{
if (string.IsNullOrEmpty(schemaTable.Name))
{
throw new ArgumentNullException("schemaTable.Name");
}
if (null == schemaTable.Columns || schemaTable.Columns.Count == 0)
{
throw new ArgumentNullException("schemaTable.Columns");
}
this.BeginCreate(DBSchemaTypes.Table, "");
if (string.IsNullOrEmpty(schemaTable.Catalog) == false)
{
this.BeginIdentifier();
this.WriteObjectName(schemaTable.Catalog);
this.EndIdentifier();
this.AppendIdSeparator();
}
if (string.IsNullOrEmpty(schemaTable.Schema) == false)
{
this.BeginIdentifier();
this.WriteObjectName(schemaTable.Schema);
this.EndIdentifier();
this.AppendIdSeparator();
}
else if (string.IsNullOrEmpty(schemaTable.Catalog) == false)
{
this.AppendIdSeparator();
}
this.BeginIdentifier();
this.WriteObjectName(schemaTable.Name);
this.EndIdentifier();
this.BeginBlock();
List <DBSchemaTableColumn> pks = new List <DBSchemaTableColumn>();
List <DBSchemaColumn> sorted = this.SortColumnsByOrdinal(schemaTable.Columns.GetColumns());
int index = 0;
this.IncrementStatementDepth();
bool hasauto = false;
foreach (DBSchemaTableColumn tc in sorted)
{
if (index > 0)
{
this.BeginNewLine();
}
if (tc.PrimaryKey)
{
pks.Add(tc);
}
if (tc.AutoAssign)
{
hasauto = true;
if (!tc.PrimaryKey)
{
throw new ArgumentException(Errors.MySqlAutoIncrementMustBeKey);
}
}
this.ScriptCreateTableColumn(tc, index);
index++;
if (index < sorted.Count)
{
this.WriteReferenceSeparator();
}
}
if (pks.Count > 1 && hasauto)
{
throw new ArgumentException(Errors.MySqlAutoIncrementMustBeKey);
}
if (pks.Count > 0)
{
this.WriteReferenceSeparator();
this.BeginNewLine();
this.WriteRawSQLString("PRIMARY KEY");
this.BeginBlock();
index = 0;
foreach (DBSchemaTableColumn tc in pks)
{
this.BeginIdentifier();
this.WriteObjectName(tc.Name);
this.EndIdentifier();
index++;
if (index < pks.Count)
{
this.WriteReferenceSeparator();
}
}
this.EndBlock();
}
this.DecrementStatementDepth();
this.EndBlock();
this.EndCreate(DBSchemaTypes.Table, false);
}
