public DataBaseModel ExecuteNonQuery(string query)
{
var ret = new DataBaseModel();
var conn = GetMySqlConnection();
if (conn != null)
{
if (conn.State == System.Data.ConnectionState.Open)
{
try
{
var mySqlCommand = new MySqlCommand(query, conn);
mySqlCommand.ExecuteNonQuery();
ret.Sucesso = true;
conn.Clone();
}
catch (Exception ex)
{
ret.Sucesso = false;
ret.MensagemErro = $"Erro: {ex.Message}";
}
}
else
{
ret.Sucesso = false;
ret.MensagemErro = "Erro ao Abrir a Connexão com banco de dados!";
}
}
else
{
ret.Sucesso = false;
ret.MensagemErro = "Erro ao Abrir a Connexão com banco de dados!";
}
return(ret);
}
public WatchCarsForm()
{
InitializeComponent();
try
{
db = new DataBaseModel();
dataGridView1.DataSource = db.har_avto.Select(x => new {
x.CarNum,
x.Firm,
x.Model,
x.Price,
x.Mileage,
x.Color,
СтрранаПроизводителя = x.Country,
x.Year,
x.EngineVolume,
x.Power,
x.FuelType, x.Status
}).ToList();
}
catch (Exception err)
{
}
}
private void btnSave_Click(object sender, EventArgs e)
{
string msg;
dbinfo = new DataBaseModel();
dbinfo.ID = id;
dbinfo.NAME = labelName.Text;
dbinfo.DB = DB.Text;
dbinfo.USER = User.Text;
dbinfo.PWD = Password.Text;
if (string.IsNullOrEmpty(dbinfo.NAME) || string.IsNullOrEmpty(dbinfo.DB) || string.IsNullOrEmpty(dbinfo.USER) || string.IsNullOrEmpty(dbinfo.PWD))
{
MessageBox.Show("请填写相关信息", "提示", MessageBoxButtons.OK);
return;
}
if (bll.SaveDBSet(dbinfo, out msg))
{
DialogResult dr = MessageBox.Show(msg, "提示", MessageBoxButtons.OK);
if (dr == DialogResult.OK)
{
ParentEvent();
this.Close();
}
}
else
{
MessageBox.Show(msg, "提示", MessageBoxButtons.OK);
}
}
public SellerForm()
{
InitializeComponent();
db = new DataBaseModel();
initClients();
initCars();
}
public Registration(Form form)
{
InitializeComponent();
previous = form;
db = new DataBaseModel();
FormClosed += Login_FormClosed;
}
/// <summary>
/// 获取数据库设置详情
/// </summary>
/// <param name="sqlwhere"></param>
/// <returns></returns>
public DataBaseModel GetDBSetDetail(string id)
{
try
{
var XmlDoc = new XmlDocument();
XmlDoc.Load(CommonConfig.SettingPath);
XElement xelem = XElement.Parse(XmlDoc.SelectSingleNode("//Settings//DataBaseList").OuterXml);
DataBaseModel db = (from o in xelem.Descendants()
where o.Attribute("Id").Value == id
select new DataBaseModel
{
ID = o.Attribute("Id").Value,
NAME = o.Attribute("Name").Value,
DB = o.Attribute("DB").Value,
USER = o.Attribute("User").Value,
PWD = o.Attribute("Password").Value,
ISCACHED = o.Attribute("IsCached").Value,
PATH = o.Attribute("Path").Value
}).ToList()[0];
return(db);
}
catch (Exception ex)
{
throw ex;
}
}
public Prodaji()
{
InitializeComponent();
try
{
db = new DataBaseModel();
dataGridView1.DataSource = (from s in db.Prodaji
join p in db.Prodavets on s.Buyer equals p.idprodavets
join b in db.Pokupatel on s.Buyer equals b.idPocupatel
join car in db.har_avto on s.CarNum equals car.CarNum
select new
{
s.CarNum,
car.Firm,
car.Model,
car.Price,
car.Year,
Покупатель = b.FullName,
Продавец = p.FullName,
ДатаПродажи = s.SeleDate
}).ToList();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
/// <summary>
/// 设置缓存对应的路径
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public Boolean SetCache(DataBaseModel model, out string msg)
{
try
{
if (!string.IsNullOrEmpty(model.ID))
{
XElement rootNode = XElement.Load(CommonConfig.SettingPath);
IEnumerable <XElement> dblist = from target in rootNode.Descendants("DataBaseModel")
where target.Attribute("Id").Value == model.ID
select target as XElement;
foreach (XElement db in dblist)
{
db.SetAttributeValue("IsCached", "1");
db.SetAttributeValue("Path", model.PATH);
break;
}
rootNode.Save(CommonConfig.SettingPath);
msg = "缓存成功";
return(true);
}
msg = "id不能为空";
return(false);
}
catch (Exception ex)
{
msg = ex.Message;
return(false);
}
}
public Boolean IsUserExist()
{
DataBaseModel database = new DataBaseModel();
DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE Login = @Login", database.getConnection());
command.Parameters.Add("@Login", SqlDbType.VarChar).Value = textBoxLogin.Text;
adapter.SelectCommand = command;
adapter.Fill(table);
if (table.Rows.Count > 0)
{
MessageBox.Show("Same login already exist");
return(true);
}
else
{
MessageBox.Show("User not exist");
return(false);
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
String login = LoginTextBox.Text;
String password = PasswordBox.Password;
try
{
using (var context = new DataBaseModel())
{
var user = context.Users.FirstOrDefault(_user => (_user.Login == login && _user.Password == password));
if (user != null)
{
UserSingleton.Instance().Login = login;
Information information = new Information();
information.Show();
this.Close();
}
else
{
throw new Exception("");
}
}
}
catch (Exception)
{
MessageBox.Show("Проверьте введеные вами данные", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public ProfileData(int Id)
{
InitializeComponent();
db = new DataBaseModel();
UserId = Id;
SetData();
}
/// <summary>
/// 程序运行从xml文件加载数据库连接配置
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void MainForm_Load(object sender, EventArgs e)
{
try
{
//加载数据库连接配置
string srcDataBasepath = Application.StartupPath + @"\xml\oracleDataBase.xml"; //数据库连接配置
XmlDocument xmlDocSrc = new XmlDocument(); //声明读取xml的对象
xmlDocSrc.Load(srcDataBasepath); //获取xml文件
XmlNode xmlNodeSrc = xmlDocSrc.SelectSingleNode("Connections"); //获取根节点
foreach (XmlNode xmlElement in xmlNodeSrc.ChildNodes)
{
DataBaseModel model = new DataBaseModel();
XmlElement item = (XmlElement)xmlElement;
model.key = item.GetAttribute("key");
model.conntext = item.GetAttribute("value");
string dataBaseName = item.GetAttribute("dataBaseName");
model.dataBaseName = dataBaseName;
string type = item.GetAttribute("type");
if (type.ToLower().Equals("mysql"))
{
model.type = DataBaseTypeEnum.Mysql;
}
else if (type.ToLower().Equals("oracle"))
{
model.type = DataBaseTypeEnum.Oracle;
}
_srcDataBaseModels.Add(model); //数据库连接对象
cboDataBase.Items.Add(model.key); //数据库连接名称
}
}
catch (Exception e1)
{
MessageBox.Show(e1.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public DataBaseModel GetDatabaseModel(bool flg = true)
{
DataBaseModel model = new DataBaseModel();
model.DatabaseName = _dbSetting.Database;
List<TableModel> tmList = this.GetTableModelList(flg);
model.Tables = tmList;
return model;
}
private void NewUser_Click(object sender, RoutedEventArgs e)
{
SignUpWindow suw = new SignUpWindow();
DataBaseModel.CloseConnection();
Close();
suw.Show();
}
public DataBaseModel GetDatabaseModel(string dbName, bool flg = false)
{
DataBaseModel model = new DataBaseModel();
model.DatabaseName = dbName;
List<TableModel> tmList = this.GetTableModelList(dbName, flg);
model.Tables = tmList;
return model;
}
private void ChangeUser_Click(object sender, RoutedEventArgs e)
{
LogInWindow liw = new LogInWindow();
DataBaseModel.CloseConnection();
Close();
liw.Show();
}
public static int?Login(LoginModel model)
{
using (var db = new DataBaseModel())
{
var user = db.Users.FirstOrDefault(u => u.Name == model.Name && u.Password == model.Password);
return(user?.Id);
}
}
public List <string> GetAllDistinctCategories()
{
using (DataBaseModel context = new DataBaseModel())
{
var distinctCategories = context.Set <CategoryDataModel>()
.Select(x => x.Category).Distinct();
return(distinctCategories.ToList());
}
}
/// <summary>
/// 单击单元格(编辑、删除、缓存)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dgvSource_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex == -1)
{
return;
}
string id = this.dgvSource.Rows[e.RowIndex].Cells["ID"].Value.ToString();
if (e.ColumnIndex == 0)//修改
{
OpenDetailWin(id);
}
else if (e.ColumnIndex == 1)//删除
{
DialogResult dr = MessageBox.Show("是否确定删除？", "提示", MessageBoxButtons.OKCancel);
if (dr == DialogResult.OK)
{
string msg;
bll.DeleteDBSet(id, out msg);
MessageBox.Show(msg, "提示", MessageBoxButtons.OK);
BindDBList();
}
}
else if (e.ColumnIndex == 2)//缓存
{
DialogResult dr = MessageBox.Show("是否确定缓存数据源？", "提示", MessageBoxButtons.OKCancel);
if (dr == DialogResult.OK)
{
this.Enabled = false;
PgsBar pgs = new PgsBar(pgsDBList, lblShow);
TableBLL auxbll = new TableBLL();
string filename = bll.GetFilePath(id, this.dgvSource.Rows[e.RowIndex].Cells["NAME"].Value.ToString());
DBSetting setmodel = new DBSetting();
setmodel.Flag = CommonConfig.DataType.DB;
setmodel.DBAccount.DB = this.dgvSource.Rows[e.RowIndex].Cells["DB"].Value.ToString();
setmodel.DBAccount.User = this.dgvSource.Rows[e.RowIndex].Cells["USER"].Value.ToString();
setmodel.DBAccount.Pwd = this.dgvSource.Rows[e.RowIndex].Cells["PWD"].Value.ToString();
auxbll.IninDefinitionDAL(setmodel);
string retmsg;
if (auxbll.CacheTable(pgs, filename, out retmsg))
{
DataBaseModel model = new DataBaseModel();
model.ID = id;
model.PATH = filename;
if (!bll.SetCache(model, out retmsg))
{
MessageBox.Show(retmsg, "提示", MessageBoxButtons.OK);
}
}
this.Enabled = true;
BindDBList();
}
}
}
public DataBaseModel GetDatabaseModel(bool flg = true)
{
DataBaseModel model = new DataBaseModel();
model.DatabaseName = _dbSetting.Database;
List <TableModel> tmList = this.GetTableModelList(flg);
model.Tables = tmList;
return(model);
}
public DataBaseModel GetDatabaseModel(string dbName, bool flg = false)
{
DataBaseModel model = new DataBaseModel();
model.DatabaseName = dbName;
List <TableModel> tmList = this.GetTableModelList(dbName, flg);
model.Tables = tmList;
return(model);
}
/// <summary>
/// ORACLE数据库选择
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void CboDataBase_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
ComboBox cbox = (ComboBox)sender;
if (cbox.SelectedItem == null)
{
return;
}
splitContainer.Enabled = false;//禁用主界面控件，为了等待数据库连接完成
string key = cboDataBase.SelectedItem.ToString();
DataBaseModel model = _srcDataBaseModels.First(item => item.key.Equals(key));
_srcSelectDatabase = model;
string selectTableSpaceSql = @"SELECT a.tablespace_name,
round(total / (1024 * 1024 * 1024),2) total,
round(free / (1024 * 1024 * 1024),2) free,
round((total - free) / (1024 * 1024 * 1024),2) tableSpaceUseSize,
round((total - free) / total, 4) * 100 usePercent
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name";
//查询表空间使用情况
var dt = OracleConn.Search(selectTableSpaceSql, _srcSelectDatabase.conntext);
dgvList.DataSource = dt;
for (int i = 0; i < dgvList.Rows.Count; i++)//行
{
//for (int j = 0; j < this.dgvList.Columns.Count; j++)//列
//{
//第5列大于80 背景标红
string usePercent = dgvList.Rows[i].Cells[4].Value.ToString();
double percent = double.Parse(usePercent);
if (percent >= 80.00)
{
dgvList[4, i].Style.BackColor = Color.Red;
}
//}
}
}
catch (Exception e1)
{
MessageBox.Show(e1.Message, "程序异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
splitContainer.Enabled = true;//启用主界面控件
}
public List <Item> GetItemsOfSingleReceipt(int receiptId)
{
using (DataBaseModel context = new DataBaseModel())
{
var temp = context.Set <ReceiptDataModel>()
.FirstOrDefault(x => x.ReceiptId == receiptId);
return(temp.Items.Select(x => new Item {
Category = x.Category, Name = x.Name, Price = x.Price, ItemId = x.ItemId
}).ToList());
}
}
public AddSellerForm()
{
InitializeComponent();
try
{
db = new DataBaseModel();
}
catch (Exception)
{
}
}
private void SelectData(string request)
{
DataBaseModel.OpenConnection();
MySqlDataAdapter data = new MySqlDataAdapter(request, DataBaseModel.GetConnection());
DataTable table = new DataTable();
data.Fill(table);
InfoGrid.DataContext = table;
Table = table;
Data = data;
ComBuild = new MySqlCommandBuilder(Data);
}
public void AddUser(User user)
{
using (DataBaseModel context = new DataBaseModel())
{
context.Set <UserDataModel>()
.Add(new UserDataModel()
{
Username = user.Username, Email = user.Email, Password = user.Password, FullName = user.Fullname
});
context.SaveChanges();
}
}
public bool InsertarUsuario(TUsuario usuario)
{
bool result = false;
using (DataBaseModel context = new DataBaseModel())
{
context.TUsuario.Add(usuario);
context.SaveChanges();
}
return(result);
}
public ViewUserOfClientModel()
{
using (var context = new DataBaseModel())
{
context.UserOfClient.Load();
UserOfClients = new ObservableCollection <UserOfClientModel>();
foreach (var item in context.UserOfClient.Local)
{
UserOfClients.Add(UserOfClientModel.FromUserOfClientModel(item));
}
}
}
public static DataBaseModel ToModel(string id)
{
DataBaseModel item = new DataBaseModel();
using (var conn = new SQLiteConnection(new SQLitePlatformWinRT(), App.RemindListDBPath))
{
item = (from p in conn.Table <DataBaseModel>()
where p.Id.Equals(id)
select p).FirstOrDefault();
}
return(item);
}
public WatchSellersForm()
{
InitializeComponent();
try
{
db = new DataBaseModel();
dataGridView1.DataSource = db.Prodavets.Select(x => new { x.FullName, x.Phone }).ToList();
}
catch (Exception err)
{
}
}
public List <User> GetExistingUsers()
{
using (DataBaseModel context = new DataBaseModel())
{
return(context.Set <UserDataModel>()
.Select(x => new User()
{
Email = x.Email, Username = x.Username, Fullname = x.FullName, Password = x.Password
})
.ToList());
}
}
public List <Item> LoadData(string Username)
{
using (DataBaseModel context = new DataBaseModel())
{
var temp = context.Set <ItemDataModel>()
.Select(x => x)
.Where(x => x.Receipt.User.Username == Username);
return(temp.Select(x => new Item {
Category = x.Category, Name = x.Name, Price = x.Price
}).ToList());
}
}
public List<DataBaseModel> GetDBList()
{
List<DataBaseModel> dbmList = new List<DataBaseModel>();
DataTable dt = GetDBTableNameList();
foreach (DataRow row in dt.Rows)
{
string dbName = row["name"].ToString();
DataBaseModel dbm = new DataBaseModel();
dbm.DatabaseName = dbName;
dbmList.Add(dbm);
}
return dbmList;
}
