public HistoryWindowController(SQLiteConnection dbConnection, string table)
{
_dbConnection = dbConnection;
_dbConnection.Update += DatabaseUpdated;
_table = table;
DataContext = new ControllerDataContext()
{
Entries = new ObservableCollection<TableStruct>(),
Title = table
};
var query = "SELECT * FROM " + table;
var command = new SQLiteCommand(query, dbConnection);
var reader = command.ExecuteReader();
var viewIndex = 1;
while (reader.Read())
{
var entry = new TableStruct() { Id = (long)reader["Id"], ViewIndex = viewIndex, Rank = (int)reader["Rank"], Date = (DateTime)reader["Date"] };
viewIndex++;
entry.PropertyChanged += EntryChanged;
DataContext.Entries.Add(entry);
}
DataContext.Entries.CollectionChanged += EntriesUpdated;
}
/// <summary>
/// Deletes the selected item in an datagrid
/// </summary>
/// <param name="selectedStruct"></param>
public override void DeleteItem(TableStruct selectedStruct)
{
var query = $"DELETE FROM {_table} WHERE Id = {selectedStruct.Id};";
var command = new SQLiteCommand(query, _dbConnection);
command.ExecuteNonQuery();
DataContext.Entries.Remove(selectedStruct);
}
/// <summary>
/// Called when the database recieves an update event.
/// This method inserts newly items into the GUI when new
/// items are inserted into the database.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DatabaseUpdated(object sender, UpdateEventArgs e)
{
if (!e.Event.Equals(UpdateEventType.Insert))
{
return;
}
var query = $"SELECT * FROM {_table} WHERE Id={(int) e.RowId}";
var command = new SQLiteCommand(query, _dbConnection);
var reader = command.ExecuteReader();
//Fill entry list with entries to be shown
while (reader.Read())
{
var entry = new TableStruct()
{
Id = (long)reader["Id"],
ViewIndex = DataContext.Entries.Count + 1,
Rank = (int)reader["Rank"],
Date = (DateTime)reader["Date"]
};
entry.PropertyChanged += EntryChanged;
Application.Current.Dispatcher.Invoke(delegate
{
DataContext.Entries.Add(entry);
});
}
}
protected void ListBoxTable_SelectedIndexChanged(object sender, EventArgs e)
{
Adap = new ReadyAdaptation();
if (ListBoxTable.SelectedIndex == 0)
{
LoadFieldsGrid(null);
return;
}
TableStruct SelectedTable = (from t in Adap.AvaibleTables where t.TableName == ListBoxTable.SelectedValue select t).SingleOrDefault();
switch (Adap.AdapterStep)
{
case "OnFieldTableChangedEvent":
txtInformation.Text = " TESTE OnFieldTableChangedEvent";
break;
case "OnTableChangedEvent":
LoadFieldsGrid(Adap.AvaibleTables[0]);
break;
case "OnTableCreatedOrRenamedEvent":
if (SelectedTable != null)
{
LoadFieldsGrid(SelectedTable);
}
break;
default:
break;
}
}
public HistoryWindowController(SQLiteConnection dbConnection, string table)
{
_dbConnection = dbConnection;
_dbConnection.Update += DatabaseUpdated;
_table = table;
DataContext = new ControllerDataContext()
{
Entries = new ObservableCollection <TableStruct>(),
Title = table
};
var query = "SELECT * FROM " + table;
var command = new SQLiteCommand(query, dbConnection);
var reader = command.ExecuteReader();
var viewIndex = 1;
while (reader.Read())
{
var entry = new TableStruct()
{
Id = (long)reader["Id"], ViewIndex = viewIndex, Rank = (int)reader["Rank"], Date = (DateTime)reader["Date"]
};
viewIndex++;
entry.PropertyChanged += EntryChanged;
DataContext.Entries.Add(entry);
}
DataContext.Entries.CollectionChanged += EntriesUpdated;
}
/// <summary>
/// Deletes the selected item in an datagrid
/// </summary>
/// <param name="selectedStruct"></param>
public override void DeleteItem(TableStruct selectedStruct)
{
var query = $"DELETE FROM {_table} WHERE Id = {selectedStruct.Id};";
var command = new SQLiteCommand(query, _dbConnection);
command.ExecuteNonQuery();
DataContext.Entries.Remove(selectedStruct);
}
private void SaveData()
{
if (dt_roles.GetChanges() != null)
{
TableStruct ts = new TableStruct();
ts.TableName = "auth_roles";
string[] p_keys = { "id" };
ts.p_keys = p_keys;
string[] columns = { "name", "rights" };
ts.columns = columns;
DBFunctions.WriteToDB(dt_roles, ts);
}
}
public void LoadFieldsGrid(TableStruct OldTable)
{
if (OldTable == null)
{
GrdFields.DataSource = null;
GrdFields.DataBind();
return;
}
FieldStructs = OldTable.Fields;
DataTable dt = new DataTable(Adap.AdaptingTable.TableName);
dt.Columns.Add("NewField");
dt.Columns.Add("OldField");
for (int i = 0; i < Adap.AdaptingTable.Fields.Count; i++)
{
FieldStruct NewField = Adap.AdaptingTable.Fields[i];
DataRow dr = dt.NewRow();
dr["NewField"] = NewField.FieldName;
dr["OldField"] = "";
FieldStruct FieldProperty = (from f
in OldTable.Fields
where f.FieldName == NewField.FieldName
select f).FirstOrDefault();
if (FieldProperty != null)
{
dr["OldField"] = FieldProperty.FieldName;
}
dt.Rows.Add(dr);
}
GrdFields.DataSource = dt;
GrdFields.DataBind();
}
private TableStruct[] GetAnimLengthXlsx()
{
TableStruct[] tss = new TableStruct[4];
TableStruct ts = new TableStruct();
ts.fieldDesc = "主键ID";
ts.fieldNameClient = "id";
ts.fieldNameServer = "id";
ts.fieldType = "int";
ts.primary = "primary";
tss [0] = ts;
ts = new TableStruct();
ts.fieldDesc = "模型名称";
ts.fieldNameClient = "modelName";
ts.fieldNameServer = "modelName";
ts.fieldType = "string";
ts.primary = "index";
tss [1] = ts;
ts = new TableStruct();
ts.fieldDesc = "动画名称";
ts.fieldNameClient = "animName";
ts.fieldNameServer = "animName";
ts.fieldType = "string";
tss [2] = ts;
ts = new TableStruct();
ts.fieldDesc = "动画时长";
ts.fieldNameClient = "animLength";
ts.fieldNameServer = "animLength";
ts.fieldType = "float";
tss [3] = ts;
return(tss);
}
/// <summary>
/// Called when the database recieves an update event.
/// This method inserts newly items into the GUI when new
/// items are inserted into the database.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DatabaseUpdated(object sender, UpdateEventArgs e)
{
if (!e.Event.Equals(UpdateEventType.Insert)) return;
var query = $"SELECT * FROM {_table} WHERE Id={(int) e.RowId}";
var command = new SQLiteCommand(query, _dbConnection);
var reader = command.ExecuteReader();
//Fill entry list with entries to be shown
while (reader.Read())
{
var entry = new TableStruct()
{
Id = (long) reader["Id"],
ViewIndex = DataContext.Entries.Count+1,
Rank = (int) reader["Rank"],
Date = (DateTime) reader["Date"]
};
entry.PropertyChanged += EntryChanged;
Application.Current.Dispatcher.Invoke(delegate
{
DataContext.Entries.Add(entry);
});
}
}
/// <summary>
/// 获取脚本
/// </summary>
/// <param name="dbName">数据库名称</param>
/// <param name="tableName">表名</param>
private string getInsertScript(string dbName, string tableName)
{
StringBuilder scriptText = new StringBuilder();
SqlHelper sqlHelper = new SqlServerHelper(CurConn.ConnectionString, dbName);
string sql = String.Format("SELECT syscolumns.name AS coumnname ,systypes.name AS type,syscolumns.isnullable,syscolumns.length FROM syscolumns,systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = OBJECT_ID('{0}') AND COLUMNPROPERTY( OBJECT_ID('[{0}]'),syscolumns.name,'IsIdentity') <> 1", tableName);
List <TableStruct> tableStructs = new List <TableStruct>();
var dt = sqlHelper.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
HiPerfTimer hiperTimer = new HiPerfTimer();
hiperTimer.Start();
foreach (DataRow dataRow in dt.Rows)
{
var tableStruct = new TableStruct();
tableStruct.coumnname = dataRow["coumnname"].ToString();
tableStruct.type = dataRow["type"].ToString();
tableStruct.isnullable = Convert.ToInt32(dataRow["isnullable"]);
tableStruct.length = Convert.ToInt16(dataRow["length"]);
tableStructs.Add(tableStruct);
}
var insertTmplete = getInsertTemplate(tableStructs, tableName);
sql = String.Format("SELECT COUNT(1) AS DtSourceCount FROM {0} {1}", tableName, whereString);
//数据行数，显示最多3000行，太多会内存溢出
int dtSourceCount = 0;
var dtCount = sqlHelper.ExecuteDataTable(sql);
if (dtCount.Rows.Count > 0)
{
int.TryParse(dtCount.Rows[0][0].ToString(), out dtSourceCount);
}
sql = String.Format("SELECT TOP 3000 * FROM {0} {1}", tableName, whereString);
var dtSource = sqlHelper.ExecuteDataTable(sql);
if (dtSource.Rows.Count > 0)
{
//单线程，用于测试
//for (int i = 0; i < dtSource.Rows.Count; i++)
//{
// var dataRow = dtSource.Rows[i];
// var values = "";
// foreach (TableStruct tableStruct in tableStructs)
// {
// if (numberTypes.Contains(tableStruct.type)) //数值类型处理
// {
// if (DBNull.Value != dataRow[tableStruct.coumnname] &&
// dataRow[tableStruct.coumnname].ToString() != "")
// values += "'" + dataRow[tableStruct.coumnname] + "',";
// else
// {
// if (tableStruct.isnullable == 0)
// {
// values += "'0',";
// }
// else
// {
// values += "null,";
// }
// }
// }
// else if (tableStruct.type.ToUpper() == "UNIQUEIDENTIFIER") //UNIQUEIDENTIFIER类型处理
// {
// var valueStr = dataRow[tableStruct.coumnname].ToString();
// if (string.IsNullOrWhiteSpace(valueStr))
// {
// values += "CAST(NULL AS UNIQUEIDENTIFIER),";
// }
// else
// {
// values += string.Format("CAST('{0}' AS UNIQUEIDENTIFIER),", dataRow[tableStruct.coumnname].ToString());
// }
// }
// else if (tableStruct.type.ToUpper() == "TIMESTAMP")
// {
// values += "DEFAULT,";
// }
// else
// {
// values += "'" + dataRow[tableStruct.coumnname].ToString().Replace("'", "''") + "',";
// }
// }
// values = values.Substring(0, values.Length - 1);
// scriptText.Append(String.Format(insertTmplete, values) + "\r\n");
//}
//多线程
object lockObj = new object();
Parallel.For(0, dtSource.Rows.Count, i =>
{
var dataRow = dtSource.Rows[i];
var values = "";
foreach (TableStruct tableStruct in tableStructs)
{
if (numberTypes.Contains(tableStruct.type)) //数值类型处理
{
if (DBNull.Value != dataRow[tableStruct.coumnname] &&
dataRow[tableStruct.coumnname].ToString() != "")
{
values += "'" + dataRow[tableStruct.coumnname] + "',";
}
else
{
if (tableStruct.isnullable == 0)
{
values += "'0',";
}
else
{
values += "null,";
}
}
}
else if (tableStruct.type.ToUpper() == "UNIQUEIDENTIFIER") //UNIQUEIDENTIFIER类型处理
{
var valueStr = dataRow[tableStruct.coumnname].ToString();
if (string.IsNullOrWhiteSpace(valueStr))
{
values += "CAST(NULL AS UNIQUEIDENTIFIER),";
}
else
{
values += string.Format("CAST('{0}' AS UNIQUEIDENTIFIER),", dataRow[tableStruct.coumnname].ToString());
}
}
else if (tableStruct.type.ToUpper() == "TIMESTAMP") //TIMESTAMP类型处理，不能显示的插入值
{
values += "DEFAULT,";
}
else
{
values += "'" + dataRow[tableStruct.coumnname].ToString().Replace("'", "''") + "',";
}
}
values = values.Substring(0, values.Length - 1);
lock (lockObj)
{
scriptText.Append(String.Format(insertTmplete, values) + "\r\n");
}
});
}
hiperTimer.Stop();
statusString = "共计" + dtSourceCount + "条数据，取数" + dtSource.Rows.Count + "条用时：" + hiperTimer.Duration + "秒";
}
return(scriptText.ToString());
}
public MSSQLBulider(object entity, TableStruct table)
{
Entity = entity;
Struct = table;
}
public abstract void DeleteItem(TableStruct selectedStruct);
public abstract void DeleteItem(TableStruct selectedStruct);
/// <summary>
/// 获取脚本
/// </summary>
/// <param name="userName">用户名</param>
/// <param name="tableName">表名</param>
private string getInsertScript(string userName, string tableName)
{
statusString = "共计0条数据";
StringBuilder scriptText = new StringBuilder();
SqlHelper sqlHelper = new OracleHelper(Conn.ConnectionString, userName);
string sql = string.Format("SELECT COLUMN_NAME AS COUMNNAME,DATA_TYPE AS TYPE,(CASE WHEN NULLABLE='Y' THEN 1 ELSE 0 END) AS ISN,DATA_LENGTH AS LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME='{0}' ORDER BY COLUMN_ID", tableName);
List <TableStruct> tableStructs = new List <TableStruct>();
var dt = sqlHelper.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow dataRow in dt.Rows)
{
var tableStruct = new TableStruct();
tableStruct.coumnname = dataRow["COUMNNAME"].ToString();
tableStruct.type = dataRow["TYPE"].ToString();
tableStruct.isnullable = Convert.ToInt32(dataRow["ISN"]);
tableStruct.length = Convert.ToInt16(dataRow["LENGTH"]);
tableStructs.Add(tableStruct);
}
var insertTmplete = getInsertTemplate(tableStructs, tableName);
sql = String.Format("SELECT COUNT(1) AS DtSourceCount FROM {0} {1}", tableName, whereString);
//数据行数，显示最多3000行，太多会内存溢出
int dtSourceCount = 0;
var dtCount = sqlHelper.ExecuteDataTable(sql);
if (dtCount.Rows.Count > 0)
{
int.TryParse(dtCount.Rows[0][0].ToString(), out dtSourceCount);
}
sql = string.Format("SELECT * FROM {0} {1}", tableName, whereString);
var dtSource = sqlHelper.ExecuteDataTable(sql);
if (dtSource.Rows.Count > 0)
{
//如果有clob类型字段，则申明变量
var clobStructs =
tableStructs.Where(i => lobTypes.Contains(i.type)).ToList();
if (clobStructs.Count > 0)
{
foreach (TableStruct clobStruct in clobStructs)
{
scriptText.Append("DECLARE V_" + clobStruct.coumnname + " NCLOB;\r\n");
}
}
bool hasBegin = false;
if (scriptText.Length > 0)
{
scriptText.Append("BEGIN\r\n");
hasBegin = true;
}
HiPerfTimer hiperTimer = new HiPerfTimer();
hiperTimer.Start();
//object lockObj = new object();
//Parallel.For(0, dtSource.Rows.Count, i =>
//{
// var dataRow = dtSource.Rows[i];
// var values = "";
// foreach (TableStruct tableStruct in tableStructs)
// {
// if (dataTypes.Contains(tableStruct.type))
// {
// values += "TO_DATE('" + dataRow[tableStruct.coumnname] + "', 'yyyy-mm-dd hh24:mi:ss'),";
// }
// else if (lobTypes.Contains(tableStruct.type))
// {
// var varName = "V_" + tableStruct.coumnname;
// values += varName + ",";
// var varValue = "'" + dataRow[tableStruct.coumnname] + "'";
// lock (lockObj)
// {
// scriptText.Append(varName + ":=" + varValue + ";\r\n");
// }
// }
// else if (numberTypes.Contains(tableStruct.type))
// {
// if (DBNull.Value != dataRow[tableStruct.coumnname] &&
// dataRow[tableStruct.coumnname].ToString() != "")
// {
// values += "'" + dataRow[tableStruct.coumnname] + "',";
// }
// else
// {
// if (tableStruct.isnullable == 0)
// {
// values += "0,";
// }
// else
// {
// values += "NULL,";
// }
// }
// }
// else
// {
// values += "'" + dataRow[tableStruct.coumnname] + "',";
// }
// }
// values = values.Substring(0, values.Length - 1);
// lock (lockObj)
// {
// scriptText.Append(string.Format(insertTmplete, values) + "\r\n");
// }
//});
foreach (DataRow dataRow in dtSource.Rows)
{
var values = "";
foreach (TableStruct tableStruct in tableStructs)
{
if (dataTypes.Contains(tableStruct.type))
{
values += "TO_DATE('" + dataRow[tableStruct.coumnname] + "', 'yyyy-mm-dd hh24:mi:ss'),";
}
else if (lobTypes.Contains(tableStruct.type))
{
var varName = "V_" + tableStruct.coumnname;
values += varName + ",";
var varValue = "'" + dataRow[tableStruct.coumnname].ToString().Replace("'", "''") + "'";
scriptText.Append(varName + ":=" + varValue + ";\r\n");
}
else if (numberTypes.Contains(tableStruct.type))
{
if (DBNull.Value != dataRow[tableStruct.coumnname] &&
dataRow[tableStruct.coumnname].ToString() != "")
{
values += "'" + dataRow[tableStruct.coumnname] + "',";
}
else
{
if (tableStruct.isnullable == 0)
{
values += "0,";
}
else
{
values += "NULL,";
}
}
}
else
{
values += "'" + dataRow[tableStruct.coumnname] + "',";
}
}
values = values.Substring(0, values.Length - 1);
scriptText.Append(String.Format(insertTmplete, values) + "\r\n");
}
if (hasBegin)
{
scriptText.Append("END;");
}
hiperTimer.Stop();
statusString = "共计" + dtSourceCount + "条数据，取数" + dtSource.Rows.Count + "条用时：" + hiperTimer.Duration + "秒";
}
}
return(scriptText.ToString());
}
