private void UserControl_Loaded(object sender, RoutedEventArgs e)
{
txt_db_host.Text = db_host = Global.cu.IniReadValue("DBConfig", "db_host");
txt_db_port.Text = db_port = Global.cu.IniReadValue("DBConfig", "db_port");
txt_db_name.Text = db_name = Global.cu.IniReadValue("DBConfig", "db_name");
txt_db_username.Text = db_username = Global.cu.IniReadValue("DBConfig", "db_username");
txt_db_password.Text = db_password = Global.cu.IniReadValue("DBConfig", "db_password");
if (StringUtil.isBlank(db_host))
{
AyMessageBox.ShowError("数据库地址不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(db_port))
{
AyMessageBox.ShowError("数据库端口不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(db_name))
{
AyMessageBox.ShowError("数据库名称不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(db_username))
{
AyMessageBox.ShowError("数据库账号不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(db_password))
{
AyMessageBox.ShowError("数据库密码不能为空或配置文件数据异常！");
return;
}
}
public void Initialize()
{
try
{
InitDic();
}
catch (System.Exception ex)
{
AyMessageBox.ShowError(ex.Message);
}
}
public static void ShowMessageBox(this CommonReturnDTO returnDTO, Window owner = null)
{
//查找aylayer
if (returnDTO.IsSuccess)
{
AyMessageBox.ShowRight(owner, GetMessageByType(returnDTO), Langs.share_tip.Lang());
}
else if (returnDTO.Error != null)
{
AyMessageBox.ShowError(owner, GetMessageByType(returnDTO), Langs.share_tip.Lang());
}
else
{
AyMessageBox.ShowInformation(owner, GetMessageByType(returnDTO), Langs.share_tip.Lang());
}
}
//此方法重写，为了导入AYUI
public override void Application_Run(Application appliation)
{
appliation.AYUI();
db_host = cu.IniReadValue("DBConfig", "db_host");
db_port = cu.IniReadValue("DBConfig", "db_port");
db_name = cu.IniReadValue("DBConfig", "db_name");
db_username = cu.IniReadValue("DBConfig", "db_username");
db_password = cu.IniReadValue("DBConfig", "db_password");
table_name = cu.IniReadValue("TableConfig", "table_name");
if (StringUtil.isBlank(db_host))
{
AyMessageBox.ShowError("数据库地址不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(db_port))
{
AyMessageBox.ShowError("数据库端口不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(db_name))
{
AyMessageBox.ShowError("数据库名称不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(db_username))
{
AyMessageBox.ShowError("数据库账号不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(db_password))
{
AyMessageBox.ShowError("数据库密码不能为空或配置文件数据异常！");
return;
}
else if (StringUtil.isBlank(table_name))
{
AyMessageBox.ShowError("数据表名称异常！");
return;
}
my = new MySQLManager(db_host, db_port, db_name, db_username, db_password);
my.showStr = false;
}
private void btn_save_Click(object sender, RoutedEventArgs e)
{
if (StringUtil.isBlank(txt_db_host.Text))
{
AyMessageBox.ShowError("数据库地址不能为空！");
return;
}
else if (StringUtil.isBlank(txt_db_port.Text))
{
AyMessageBox.ShowError("数据库端口不能为空！");
return;
}
else if (StringUtil.isBlank(txt_db_name.Text))
{
AyMessageBox.ShowError("数据库名称不能为空！");
return;
}
else if (StringUtil.isBlank(txt_db_username.Text))
{
AyMessageBox.ShowError("数据库账号不能为空！");
return;
}
else if (StringUtil.isBlank(txt_db_password.Text))
{
AyMessageBox.ShowError("数据库密码不能为空！");
return;
}
Global.cu.IniWriteValue("DBConfig", "db_host", txt_db_host.Text);
Global.cu.IniWriteValue("DBConfig", "db_port", txt_db_port.Text);
Global.cu.IniWriteValue("DBConfig", "db_name", txt_db_name.Text);
Global.cu.IniWriteValue("DBConfig", "db_username", txt_db_username.Text);
Global.cu.IniWriteValue("DBConfig", "db_password", txt_db_password.Text);
Global.RefreshConfigDataToGloBal();
Global.HomeWindow.RefreshTablesNameComBoBox();
Global.configPage = null;
AyLayer.Close("ConfigPage".ToLower());
}
public async void RefreshTablesNameComBoBox()
{
readTableNames = true;
String cmbString = "";
if (StringUtil.isNotBlank(cmb_tables.Text))
{
cmbString = cmb_tables.Text;
}
cmb_tables.IsEnabled = false;
btn_inputDataToDatabase.IsEnabled = false;
cmb_tables.Items.Clear();
if (StringUtil.isBlank(Global.db_name))
{
AyMessageBox.ShowError("数据库名称为空！");
return;
}
try
{
btn_gettables.IsEnabled = false;
btn_databaseConfig.IsEnabled = false;
List <TableItem> list_name = null;
await Task.Run(() => {
try
{
list_name = Global.my.QueryList <TableItem>("select table_name as name from information_schema.tables where table_schema='" + Global.db_name + "' and table_type='base table';");
}
catch
{
list_name = null;
AyMessageBox.ShowError("获取数据库表信息失败！");
}
finally
{
readTableNames = false;
}
});
if (list_name != null)
{
if (list_name.Count > 0)
{
for (int i = 0; i < list_name.Count; i++)
{
ComboBoxItem cbi = new ComboBoxItem();
cbi.Content = list_name[i].Name;
cmb_tables.Items.Add(cbi);
if (StringUtil.isNotBlank(cmbString))
{
if (cmbString.Equals(list_name[i].Name))
{
cmb_tables.SelectedIndex = i;
}
}
}
cmb_tables.IsEnabled = true;
if (list.Count > 0)
{
btn_inputDataToDatabase.IsEnabled = true;
}
}
}
}
catch (Exception ex)
{
cmb_tables.IsEnabled = false;
btn_inputDataToDatabase.IsEnabled = false;
}
finally
{
btn_gettables.IsEnabled = true;
btn_databaseConfig.IsEnabled = true;
GC.Collect();
}
}
private async void ReadExcelData(String @path)
{
if (StringUtil.isBlank(@path))
{
AyMessageBox.ShowError("文件路径不正确！");
btn_readFileData.IsEnabled = true;
btn_selectFile.IsEnabled = true;
return;
}
try
{
fileStream = new FileStream(@path, FileMode.Open, FileAccess.Read);
}
catch (Exception ex)
{
AyMessageBox.ShowError(ex.ToString());
btn_readFileData.IsEnabled = true;
btn_selectFile.IsEnabled = true;
return;
}
if (fileStream.CanRead)
{
String houzhui = fileStream.Name.Substring(fileStream.Name.LastIndexOf(".") + 1, fileStream.Name.Length - fileStream.Name.LastIndexOf(".") - 1);
if ("xls".Equals(houzhui))
{
workbook = new HSSFWorkbook(fileStream);
}
else if ("xlsx".Equals(houzhui))
{
workbook = new XSSFWorkbook(fileStream);
}
}
else
{
AyMessageBox.ShowError("文件读取失败！");
workbook = null;
return;
}
sheet = workbook.GetSheetAt(0);
if (sheet == null)
{
return;
}
table_datagrid.Columns.Clear();
DataGridTemplateColumn index_col_val = TransExpV2 <DataGridTemplateColumn, DataGridTemplateColumn> .Trans(index_col);
DataGridTemplateColumn input_state_col_val = TransExpV2 <DataGridTemplateColumn, DataGridTemplateColumn> .Trans(input_state_col);
index_col_val.DisplayIndex = 0;
table_datagrid.Columns.Add(index_col_val);
//获取总行数
rowCount = sheet.PhysicalNumberOfRows;
int i, j;
if (rowCount > 0)
{
//根据第一行，获取总列数
titleRow = sheet.GetRow(0);
colCount = titleRow.PhysicalNumberOfCells;
//初始化字段名称和表格标题名称的数组大小
fieldArray = new String[colCount];
headerArray = new String[colCount];
for (i = 0; i < colCount; i++)
{
String titleString = titleRow.GetCell(i).StringCellValue;
if (StringUtil.isNotBlank(titleString))
{
String[] titleStringSplitArr = titleString.Replace(" ", "").Split(new Char[] { '(', ')', ',', ':', '[', ']', '（', '）', '，', '：', '【', '】' }, StringSplitOptions.RemoveEmptyEntries);
if (titleStringSplitArr != null)
{
if (titleStringSplitArr.Count() == 3)
{
headerArray[i] = titleStringSplitArr[1];
fieldArray[i] = titleStringSplitArr[2];
if ("*".Equals(titleStringSplitArr[0]))
{
AddColumn(headerArray[i], "*", i + 1);
}
else
{
AddColumn(headerArray[i], titleStringSplitArr[0], i + 1);
}
}
else if (titleStringSplitArr.Count() == 2)
{
headerArray[i] = titleStringSplitArr[0];
fieldArray[i] = titleStringSplitArr[1];
AddColumn(headerArray[i], "200", i + 1);
}
else if (titleStringSplitArr.Count() == 1)
{
fieldArray[i] = titleStringSplitArr[0];
AddColumn(fieldArray[i], "200", i + 1);
}
else
{
AyMessageBox.ShowError("标题和字段信息初始化错误！");
}
}
}
}
}
//填充数据
AyTime.setTimeout(1, () => {
try
{
btn_inputDataToDatabase.IsEnabled = false;
cb_empty.IsEnabled = false;
cb_re.IsEnabled = false;
cmb_fielditem.IsEnabled = false;
cmb_fielditem.SelectedIndex = -1;
cmb_fielditem.Text = "";
cmb_fielditem.Items.Clear();
list.Clear();
for (i = 1; i < rowCount; i++)
{
RowViewItem rowItem = new RowViewItem();
rowItem._number = i.ToString();
IRow rowData = sheet.GetRow(i);
for (j = 0; j < colCount; j++)
{
String value = rowData.GetCell(j).ToString();
SetValue(ref rowItem, j + 1, value);
}
rowItem._state = "尚未导入";
list.Add(rowItem);
}
}
catch (Exception ex)
{
btn_inputDataToDatabase.IsEnabled = false;
cb_empty.IsEnabled = false;
cb_re.IsEnabled = false;
cmb_fielditem.IsEnabled = false;
cmb_fielditem.SelectedIndex = -1;
cmb_fielditem.Text = "";
cmb_fielditem.Items.Clear();
AyMessageBox.ShowError("数据填充时发生错误:" + ex.ToString());
}
finally
{
if (!readTableNames)
{
if (list.Count > 0)
{
if (cmb_tables.IsEnabled)
{
btn_inputDataToDatabase.IsEnabled = true;
}
}
}
btn_selectFile.IsEnabled = true;
btn_readFileData.IsEnabled = true;
}
cb_empty.IsEnabled = true;
cb_re.IsEnabled = true;
cmb_fielditem.IsEnabled = true;
int f_count = fieldArray.Count();
int h_count = headerArray.Count();
cmb_fielditem.Items.Add(new ComboBoxItem());
for (i = 0; i < f_count && i < h_count; i++)
{
ComboBoxItem cbi = new ComboBoxItem();
cbi.Content = headerArray[i];
cbi.Uid = fieldArray[i];
cmb_fielditem.Items.Add(cbi);
}
});
input_state_col_val.DisplayIndex = table_datagrid.Columns.Count();
table_datagrid.Columns.Add(input_state_col_val);
reFlag = false;
emptyFlag = false;
cb_re.IsChecked = false;
cb_empty.IsChecked = false;
cmb_fielditem.SelectedIndex = -1;
cmb_fielditem.Text = "";
//添加行数据
//table_datagrid.
GC.Collect();
}
//判断重复未写！
//导入功能未写！
public async void InputDateToDatabase()
{
if (StringUtil.isBlank(cmb_tables.Text))
{
AyMessageBox.ShowError("未选择数据表！");
return;
}
String table_name = cmb_tables.Text;
String fieldString = cmb_fielditem.Text;
int fieldIndex = cmb_fielditem.SelectedIndex;
if (reFlag && StringUtil.isBlank(fieldString))
{
AyMessageBox.ShowError("未选择需要筛选的重复字段！");
return;
}
btn_exit.IsEnabled = false;
btn_databaseConfig.IsEnabled = false;
btn_inputDataToDatabase.IsEnabled = false;
btn_readFileData.IsEnabled = false;
btn_selectFile.IsEnabled = false;
if (emptyFlag)
{
try
{
Global.my.Query("Truncate Table " + table_name);
}
catch
{
AyMessageBox.ShowError("清空表数据失败！");
return;
}
}
Int32 successCount = 0, failedCount = 0, reCount = 0;
String sqlStr = "INSERT INTO " + table_name + " (" + StringUtil.changeArrayToString(fieldArray, ",") + ") VALUES ";
String[] valueString = new String[rowCount];
List <RowViewItem> list_no_input = list.Where(x => true).ToList();
if (list_no_input != null)
{
if (list_no_input.Count > 0)
{
int list_no_input_count = list_no_input.Count;
for (int i = 0; i < list_no_input_count; i++)
{
valueString[i] = "(" + GetRowValueToString(table_datagrid, i) + ")";
await Task.Run(() => {
Thread.Sleep(5);
if (reFlag && StringUtil.isNotBlank(fieldString))
{
String reSqlString = "select count(1) from " + table_name + " where " + fieldArray[fieldIndex - 1] + " = '" + GetCellValue(i, fieldIndex) + "'";
String baseCountString = Global.my.ExecuteSelect(reSqlString);
int baseCount = Int32.Parse(baseCountString);
if (baseCount > 0)
{
list_no_input[i]._state = "数据重复";
reCount++;
}
else
{
if (Global.my.Query(sqlStr + valueString[i]))
{
list_no_input[i]._state = "成功";
successCount++;
}
else
{
list_no_input[i]._state = "数据失败";
failedCount++;
}
}
}
else
{
if (Global.my.Query(sqlStr + valueString[i]))
{
list_no_input[i]._state = "成功";
successCount++;
}
else
{
list_no_input[i]._state = "数据失败";
failedCount++;
}
}
});
}
table_datagrid.ItemsSource = list;
}
}
btn_exit.IsEnabled = true;
btn_databaseConfig.IsEnabled = true;
btn_inputDataToDatabase.IsEnabled = true;
btn_readFileData.IsEnabled = true;
btn_selectFile.IsEnabled = true;
AyMessageBox.ShowInformation("导入结束，共执行：" + list_no_input.Count + " 条命令"
+ "\n成功：" + successCount + " 条"
+ "\n重复：" + reCount + " 条"
+ "\n失败：" + failedCount + " 条");
GC.Collect();
}
private void BtnMessageBox_Click(object sender, RoutedEventArgs e)
{
AyMessageBox.ShowError(owner: this, "ayjs.net", "测试错误");
}
private void openMessageBox_Click(object sender, RoutedEventArgs e)
{
if (cboMsgType.Text == "信息")
{
AyMessageBox.ShowInformation(winThis, "必须填写用户名");
}
else if (cboMsgType.Text == "警告")
{
AyMessageBox.ShowWarning(winThis, "必须填写用户名");
}
else if (cboMsgType.Text == "错误")
{
AyMessageBox.ShowError(winThis, "必须填写用户名");
}
else if (cboMsgType.Text == "异常")
{
AyMessageBox.ShowError(winThis, "必须填写用户名");
}
else if (cboMsgType.Text == "疑问")
{
AyMessageBox.ShowQuestion(winThis, "必须填写用户名");
}
else if (cboMsgType.Text == "长文本内容")
{
AyMessageBox.ShowInformation(winThis, "我是AY,首先声明,我在做一件很枯燥的事情,我是个91后程序员，每天熬夜完成计划的过着下班后的生活\n" +
"那天有人反对，那天有人安慰，那天有人嘲讽，那天有人祝福。\n"
+ "过了6个月后，我对自己的梦想一直没有改变过，继续坚持，终于，AYUI诞生了。\n"
+ "今天有人说造轮子，今天有人说你好厉害，今天有人说开源吗 ? 有人说好喜欢...\n"
+ "有贬有褒，但是好的声音多了。\n"
+ "但是身体的各种问题也来了..");
}
else if (cboMsgType.Text == "DIY的弹窗")
{
AyMessageBox.Show(winThis, "我是AY,首先声明,我在做一件很枯燥的事情,我是个91后程序员，每天熬夜完成计划的过着下班后的生活\n" +
"那天有人反对，那天有人安慰，那天有人嘲讽，那天有人祝福。\n"
+ "过了6个月后，我对自己的梦想一直没有改变过，继续坚持，终于，AYUI诞生了。\n"
+ "今天有人说造轮子，今天有人说你好厉害，今天有人说开源吗 ? 有人说好喜欢...\n"
+ "有贬有褒，但是好的声音多了。\n"
+ "但是身体的各种问题也来了..", "AY自我介绍", MessageBoxButton.OK, AyMessageBoxImage.None, MessageBoxResult.Yes, MessageBoxOptions.RightAlign);
}
else if (cboMsgType.Text == "长文本内容")
{
}
else if (cboMsgType.Text == "删除")
{
if (MessageBoxResult.OK == AyMessageBox.ShowDelete(winThis, "确认删除吗", "删除"))
{
AyMessageBox.ShowRight("操作成功!");
}
else
{
AyMessageBox.ShowRight(winThis, "操作已经被取消!", "操作");
}
}
else if (cboMsgType.Text == "疑问确认取消")
{
if (MessageBoxResult.OK == AyMessageBox.ShowQuestionOkCancel(winThis, "确认操作吗？此操作不可以更改", "操作提示"))
{
AyMessageBox.ShowRight("操作成功!");
}
else
{
AyMessageBox.ShowRight(winThis, "操作已经被取消!", "操作");
}
}
else if (cboMsgType.Text == "ok")
{
AyMessageBox.ShowRight(winThis, "操作成功!");
}
}