/// <summary>
/// Import XML file to database
/// </summary>
/// <param name="accessPath"></param>
public void ImportFileFromXML(string accessPath)
{
try
{
_fs = new FileStream(accessPath, FileMode.Open, FileAccess.Read);
_ls = (List <RecipesBase>)_xs.Deserialize(_fs);
RecipesBase m = new RecipesBase();
if (DbFunc <RecipesBase> .GetCount() <= 0)
{
foreach (var r in _ls)
{
m.Id = r.Id; m.RecipesName = r.RecipesName; m.Ingredients = r.Ingredients; m.AmountsMeal = r.AmountsMeal; m.ShortDescription = r.ShortDescription; m.LongDescription = r.LongDescription; m.NumberPortions = r.NumberPortions; m.CategoryCuisines = r.CategoryCuisines; m.CategoryRating = r.CategoryRating; m.CategoryDifficultLevel = r.CategoryDifficultLevel; m.CategoryPreparationTime = r.CategoryPreparationTime; m.SnackMeal = r.SnackMeal; m.DinnerMeal = r.DinnerMeal; m.SoupMeal = r.SoupMeal; m.DessertMeal = r.DessertMeal; m.DrinkMeal = r.DrinkMeal; m.PreservesMeal = r.PreservesMeal; m.SaladMeal = r.SaladMeal; m.IdFishIngredients = r.IdFishIngredients; m.IdPastaIngredients = r.IdPastaIngredients; m.IdFruitsIngredients = r.IdFruitsIngredients; m.IdMuschroomsIngredients = r.IdMuschroomsIngredients; m.IdBirdIngredients = r.IdBirdIngredients; m.IdMeatIngredients = r.IdMeatIngredients; m.IdEggsIngredients = r.IdEggsIngredients; m.PhotoLinkLocation = r.PhotoLinkLocation; m.Vegetarian = r.Vegetarian; m.Grams = r.Grams;
DbFunc <RecipesBase> .Add(m);
}
MessageBox.Show("Baza danych została zaimportowana");
_fs.Close();
}
else
{
MessageBox.Show("Baza danych przed importem musi zostać wyczyszczona.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void query()
{
DataSet ds = null;
try
{
string strSql = string.Format("SELECT ISNULL(b.UserID,0) UserID,b.UserName,ISNULL(b.RoleID,2) RoleID FROM dbo.TStaff a LEFT JOIN dbo.TUser b on a.UID=b.UserID AND a.ID={0}", ID);
ds = DbFunc.GetTableNoName(strSql);
if (ds != null && ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0)
{
DataTable dt = ds.Tables[0];
UID = (int)dt.Rows[0]["UserID"];
RoleID = (int)dt.Rows[0]["RoleID"];
UName = dt.Rows[0]["UserName"].ToString();
//ComboBox控件实现 (与ListBox的实现类似)
comboBox1.DataSource = query2();
comboBox1.DisplayMember = "RoleName";
comboBox1.ValueMember = "RoleID";
comboBox1.SelectedValue = RoleID;
textBox2.Text = UName;
}
}
catch
{
}
}
public void Test1()
{
using var db = ApplicationDbContext.UseSqlServer();
using (var trans = db.Database.BeginTransaction())
{
db.YearMonthModels.AddRange(new[]
{
new YearMonthModel {
Date = new DateTime(2012, 1, 1), Year = 2012, Month = 1, Day = 1
},
new YearMonthModel {
Date = new DateTime(2012, 4, 16), Year = 2012, Month = 4, Day = 16
},
new YearMonthModel {
Date = new DateTime(2012, 5, 18), Year = 2012, Month = 5, Day = 18
},
});
db.SaveChanges();
var query = db.YearMonthModels.Where(x => DbFunc.DateTime(x.Year, x.Month, x.Day) >= DbFunc.DateTime(2012, 4, 16));
var sql = query.ToSql();
Assert.Equal(2, query.Count());
trans.Rollback();
}
}
static void Test(ApplicationDbContext db, TestDatabases databases)
{
using var trans = db.Database.BeginTransaction();
using var directScope = db.BeginDirectScope();
db.YearMonthModels.Truncate();
db.YearMonthModels.AddRange(new[]
{
new YearMonthModel {
Date = new DateTime(2012, 1, 1), Year = 2012, Month = 1, Day = 1
},
new YearMonthModel {
Date = new DateTime(2012, 4, 16), Year = 2012, Month = 4, Day = 16
},
new YearMonthModel {
Date = new DateTime(2012, 5, 18), Year = 2012, Month = 5, Day = 18
},
});
db.SaveChanges();
var query = db.YearMonthModels.Where(x => DbFunc.DateTime(x.Year, x.Month, x.Day, 1, 1, 1) >= DbFunc.DateTime(2012, 4, 16));
var sql = query.ToSql();
Assert.Equal(2, query.Count());
trans.Rollback();
}
public void AddEmail()
{
EmailBase modelAdd = new EmailBase();
modelAdd.Email = txtAddEmail.Text.ToLower();
if (string.IsNullOrWhiteSpace(txtAddEmail.Text))
{
errorProvider1.SetError(txtAddEmail, "Wpisz Email");
}
else
{
DbFunc <EmailBase> .Add(modelAdd);
DisappearModifyElement();
btnDelete.Enabled = true;
btnModify.Enabled = true;
btnCancel.Visible = false;
btnAdd.TurnOffTheButton();
add = false;
}
Fill();
}
/// <summary>
/// Export all files from database to XML
/// </summary>
/// <param name="accessPath"></param>
public void ExportDatabase(string accessPath)
{
try
{
_fs = new FileStream(accessPath, FileMode.Create, FileAccess.Write);
foreach (var r in DbFunc <RecipesBase> .GetAll())
{
_ls.Add(new RecipesBase(r.Id, r.RecipesName, r.Ingredients, r.AmountsMeal, r.ShortDescription, r.LongDescription, r.NumberPortions, r.CategoryCuisines, r.CategoryRating, r.CategoryDifficultLevel, r.CategoryPreparationTime, r.SnackMeal, r.DinnerMeal, r.SoupMeal, r.DessertMeal, r.DrinkMeal, r.PreservesMeal, r.SaladMeal, r.IdFishIngredients, r.IdPastaIngredients, r.IdFruitsIngredients, r.IdMuschroomsIngredients, r.IdBirdIngredients, r.IdMeatIngredients, r.IdEggsIngredients, r.PhotoLinkLocation, r.Vegetarian, r.Grams));
}
_xs.Serialize(_fs, _ls);
_fs.Close();
MessageBox.Show("Eksport bazy danych zakończył się sukcesem");
}
catch (IOException ex)
{
MessageBox.Show("Błąd podczas eksportu", ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Błąd podczas eksportu", ex.Message);
}
}
private void btn_confirm_Click(object sender, EventArgs e)
{
DataSet ds = null;
int result = -1;
try
{
string strSql = string.Format("exec UP_TSale2 '{0}','{1}','{2}','{3}'",
ID, textBox1.Text, textBox4.Text, textBox5.Text);
ds = DbFunc.GetTableNoName(strSql);
if (ds != null && ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0)
{
DataTable dt = ds.Tables[0];
result = (int)dt.Rows[0]["Result"];
}
}
catch
{
}
ReturnValue = result;
if (result == -1)
{
MessageBox.Show("修改失败，请稍后重试！", "提示");
}
else
{
MessageBox.Show("修改成功！", "提示");
this.DialogResult = DialogResult.OK;
this.Close();
}
}
private void btn_confirm_Click(object sender, EventArgs e)
{
DataSet ds = null;
int result = -1;
string a = comboBox1.SelectedIndex.ToString();
string b = comboBox2.SelectedIndex.ToString();
try
{
string strSql = string.Format("exec UP_TStaff '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}'",
ID, 0, textBox1.Text, comboBox1.SelectedIndex, textBox3.Text, textBox4.Text, textBox5.Text, textBox7.Text, dateTimePicker1.Text, dateTimePicker2.Text, Resume, comboBox2.SelectedIndex);
ds = DbFunc.GetTableNoName(strSql);
if (ds != null && ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0)
{
DataTable dt = ds.Tables[0];
result = (int)dt.Rows[0]["Result"];
}
}
catch
{
}
ReturnValue = result;
if (result == -1)
{
MessageBox.Show(tag + "失败，请稍后重试！", "提示");
}
else
{
MessageBox.Show(tag + "成功！", "提示");
this.DialogResult = DialogResult.OK;
this.Close();
}
}
/// <summary>
/// Import single XML file to database
/// </summary>
/// <param name="accessPath"></param>
public void ImportSingleFIleFromXML(string accessPath)
{
try
{
foreach (var r in DbFunc <RecipesBase> .GetAll())
{
NewId = r.Id;
}
_fs = new FileStream(accessPath, FileMode.Open, FileAccess.Read);
_ls = (List <RecipesBase>)_xs.Deserialize(_fs);
RecipesBase m = new RecipesBase();
foreach (var r in _ls)
{
m.Id = NewId + 1; m.RecipesName = r.RecipesName; m.Ingredients = r.Ingredients; m.AmountsMeal = r.AmountsMeal; m.ShortDescription = r.ShortDescription; m.LongDescription = r.LongDescription; m.NumberPortions = r.NumberPortions; m.CategoryCuisines = r.CategoryCuisines; m.CategoryRating = r.CategoryRating; m.CategoryDifficultLevel = r.CategoryDifficultLevel; m.CategoryPreparationTime = r.CategoryPreparationTime; m.SnackMeal = r.SnackMeal; m.DinnerMeal = r.DinnerMeal; m.SoupMeal = r.SoupMeal; m.DessertMeal = r.DessertMeal; m.DrinkMeal = r.DrinkMeal; m.PreservesMeal = r.PreservesMeal; m.SaladMeal = r.SaladMeal; m.IdFishIngredients = r.IdFishIngredients; m.IdPastaIngredients = r.IdPastaIngredients; m.IdFruitsIngredients = r.IdFruitsIngredients; m.IdMuschroomsIngredients = r.IdMuschroomsIngredients; m.IdBirdIngredients = r.IdBirdIngredients; m.IdMeatIngredients = r.IdMeatIngredients; m.IdEggsIngredients = r.IdEggsIngredients; m.PhotoLinkLocation = r.PhotoLinkLocation; m.Vegetarian = r.Vegetarian; m.Grams = r.Grams;
DbFunc <RecipesBase> .Add(m);
}
MessageBox.Show(m.RecipesName + "\n" + "został zaimportowany.", "PLIK ");
_fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Fill()
{
dgGrid.Rows.Clear();
foreach (var item in DbFunc <EmailBase> .GetAll())
{
dgGrid.Rows.Add(item.Id, item.Email);
}
}
public int GetRecordCount(string sql)
{
if (!string.IsNullOrEmpty(sql))
{
DbFunc dbFunc = new DbFunc();
dbFunc.ConnectionString = connstr;
var c = dbFunc.ExecuteScalar("Select Count(*) from (" + sql + ") as T");
return(Convert.ToInt32(c));
}
return(0);
}
private void SingForm_Load(object sender, EventArgs e)
{
this.Size = new Size(400, 430);
CreateDataGridView();
comLength = CompareLinesLength(amountsSing, gramsSing, ingredientSing);
foreach (var item in DbFunc <EmailBase> .GetAll())
{
dgGrid.Rows.Add(item.Id, item.Email);
}
}
/// <summary>
/// Complete the DatagridView data if they meet the 'vegeterian' condition from the selected checkbox or text
/// </summary>
/// <param name="_propName"></param>
/// <param name="vegetarian"></param>
/// <param name="text"></param>
/// <returns></returns>
public bool CompleteGridVegetarian(string _propName, CheckBox vegetarian, string text)
{
bool found = false;
try
{
if (vegetarian.Checked)
{
foreach (var r in DbFunc <RecipesBase> .GetAll())
{
string[] tab = r.RecipesName.Split(' ');
if ((int)GetPropValue(r, _propName) == 1 && (int)GetPropValue(r, "Vegetarian") == 1)
{
if (SearchName == 1)
{
if (text != string.Empty)
{
if (r.RecipesName.Contains(text))
{
foreach (var item in tab)
{
if (item.StartsWith(text))
{
CompleteDataGridRow(r);
found = true;
}
}
}
}
else
{
CompleteDataGridRow(r);
found = true;
}
}
else
{
if (r.Ingredients.ToUpper().Contains(text))
{
CompleteDataGridRow(r);
found = true;
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(found);
}
/// <summary>
/// Complete the DatagridView data from the selected checkbox
/// </summary>
/// <param name="_propName"></param>
/// <param name="_checkBoxName"></param>
/// <param name="nazwa"></param>
public void CompleteGrid(string propName, CheckBox _checkBoxName)
{
if (_checkBoxName.Checked)
{
foreach (var r in DbFunc <RecipesBase> .GetAll())
{
if ((int)GetPropValue(r, propName) == 1)
{
CompleteDataGridRow(r);
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
btnCancel.Visible = true;
btnDeleteAll.TurnOnTheButton();
EmailBase search = new EmailBase();
if (MessageBox.Show("Czy na pewno usunąć Bazę danych? \nOperacja nie do odwrócenia", "Uwaga!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
DbFunc <EmailBase> .ClearDb("EmilBase");
MessageBox.Show("Dokument został usunięty");
Fill();
btnCancel.Visible = false;
}
btnDeleteAll.TurnOffTheButton();
}
//do poprawy
/// <summary>
/// Checks if the given name already exists in the database
/// </summary>
/// <param name="name"></param>
/// <param name="correctName"></param>
/// <returns></returns>
public static bool CheckName(TextBox name, string correctName)
{
bool variable = false;
foreach (var r in DbFunc <RecipesBase> .GetAll())
{
if (r.RecipesName == name.Text && r.RecipesName != correctName)
{
variable = true;
MessageBox.Show("Taka nazwa już istnieje w bazie danych");
name.Text = "";
break;
}
}
return(variable);
}
/*public string TableName { get; private set; }
* public string ColumnName { get; private set; }
* public Type ParentType { get; private set; }*/
private ObProperty(Type modelType, string tableName, string propertyName, DbFunc dbFunc = DbFunc.Null)
{
TableName = tableName;
PropertyInfo propertyInfo;
if (!modelType.Exists(propertyName, out propertyInfo))
{
throw new Exception(string.Format("{0}.{1}属性名未找到", TableName, propertyName));
}
ModelType = modelType;
//TODO ToColumnName
ColumnName = propertyInfo.ToColumnName();
PropertyName = propertyName;
DbFunc = dbFunc;
AriSymbol = DbAriSymbol.Null;
AsProperty = this;
}
/// <summary>
/// Complete the DatagridView data from the selected checkbox or text
/// </summary>
/// <param name="_propName"></param>
/// <param name="_checkBoxName"></param>
/// <param name="nazwa"></param>
public void CompleteGrid(string propName, string search)
{
try
{
foreach (var r in DbFunc <RecipesBase> .GetAll())
{
string[] tab = r.RecipesName.Split(' ');
if (SearchName == 1)
{
if ((int)GetPropValue(r, propName) == 1 && (r.RecipesName.Contains(search) && r.RecipesName != string.Empty))
{
foreach (var item in tab)
{
if (item.StartsWith(search))
{
CompleteDataGridRow(r);
}
}
}
else if ((int)GetPropValue(r, propName) == 1 && string.IsNullOrEmpty(search))
{
foreach (var item in tab)
{
if (item.StartsWith(search))
{
CompleteDataGridRow(r);
}
}
}
}
else
{
if ((int)GetPropValue(r, propName) == 1 && (r.Ingredients.ToUpper().Contains(search)))
{
CompleteDataGridRow(r);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void DeleteRecipes(int recipesId)
{
if (MessageBox.Show("Czy na pewno usunąć Plik? \nOperacja nie do odwrócenia", "Uwaga!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
var s = DbFunc <RecipesBase> .GetById(recipesId);
DbFunc <RecipesBase> .DeleteSingleFile(s.Id);
MessageBox.Show("Dokument został usunięty");
}
catch (Exception ex)
{
MessageBox.Show("Błąd podczas usuwania w funkcji - DeleteRecipes");
}
}
}
/// <summary>
/// Complete the DataGridView with data from dataBase
/// </summary>
public void CompletedgGrid()
{
try
{
foreach (var r in DbFunc <RecipesBase> .GetAll())
{
CompleteDataGridRow(r);
}
}
catch (NullReferenceException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public void ModifyEmail()
{
var mod = DbFunc <EmailBase> .GetById(id);
mod.Email = txtAddEmail.Text;
DbFunc <EmailBase> .Update(mod);
MessageBox.Show("Modyfikacja przebiegła pomyślnie!!!");
btnModify.TurnOffTheButton();
Fill();
txtAddEmail.Text = "";
DisappearModifyElement();
btnAdd.Enabled = true;
btnDelete.Enabled = true;
btnCancel.Visible = false;
}
private DataTable query2()
{
DataSet ds = null;
DataTable dt = null;
try
{
string strSql = string.Format("SELECT * FROM dbo.TRole");
ds = DbFunc.GetTableNoName(strSql);
if (ds != null && ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0)
{
dt = ds.Tables[0];
}
return(dt);
}
catch
{
return(null);
}
}
/// <summary>
/// Complete DataGridView with data from dataBase
/// </summary>
/// <param name="text"></param>
/// <returns></returns>
public bool CompletedgGrid(string text)
{
bool found = false;
try
{
foreach (var r in DbFunc <RecipesBase> .GetAll())
{
string[] tab = r.RecipesName.Split(' ');
if (SearchName == 1)
{
if (r.RecipesName.Contains(text))
{
foreach (var item in tab)
{
if (item.StartsWith(text) || r.RecipesName.StartsWith(text))
{
CompleteDataGridRow(r);
found = true;
}
}
}
}
else
{
if (r.Ingredients.ToUpper().Contains(text))
{
CompleteDataGridRow(r);
found = true;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(found);
}
public static double Tax(int year, int horsepower, string type)
{
double money;
switch (type)
{
case "car": money = Car.Tax(horsepower, year); break;
case "truck": money = Truck.Tax(horsepower, year); break;
case "bike": money = Bike.Tax(horsepower); break;
default: money = Yacht.Tax(horsepower); break;
}
DbFunc.AsyncDbAdd(new VehicleModel {
Horsepower = horsepower, Year = year, Type = type, Tax = (int)money
});
return(money);
}
public IEnumerable <TTrain> OrderTrainList <TTrain>(TTrain tp, string column, int pg, out int ttl, bool asc = true) where TTrain : ITrainBase
{
Type typ = tp.GetType();
switch (typ.ToString())
{
case "Base_Period":
break;
case "Base_Subject":
break;
case "Base_Grade":
break;
case "Base_Genre":
break;
}
string ord = asc ? "asc" : "desc";
DbFunc dbFunc = new DbFunc();
dbFunc.ConnectionString = connstr;
_sb = new StringBuilder();
_sb.Append($"SELECT ROW_NUMBER() over (Order by {column} { ord }) od, * FROM " + typ.ToString());
ttl = GetRecordCount(_sb.ToString());
_sb.Append("with tmp as(" + _sb);
_sb.AppendFormat(") select * from tmp where od > {0} and od <= {1}", (pg - 1) * 10, pg * 10);
var dt = dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null)
{
}
throw new NotImplementedException();
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (dgGrid.Rows.Count > 0)
{
btnCancel.Visible = true;
btnDeleteAll.Visible = true;
btnDelete.TurnOnTheButton();
if (id == 0)
{
MessageBox.Show("Wybierz kontakt do usunięcia");
}
else if (MessageBox.Show("Czy na pewno usunąć Plik? \nOperacja nie do odwrócenia", "Uwaga!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
var s = DbFunc <EmailBase> .GetById(id);
DbFunc <EmailBase> .DeleteSingleFile(s.Id);
MessageBox.Show("Email został usunięty");
btnDelete.TurnOffTheButton();
btnDeleteAll.Visible = false;
btnCancel.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Fill();
}
else
{
MessageBox.Show("Brak adresów Email Do skasowania");
Cancel();
}
}
/// <summary>
/// Stored Procudure 실행 후 OUTPUT, RETURN_VALUE 값을 응답개체(XdsResponseItem)에 추가한다.
/// </summary>
/// <param name="outputs">Command Parameter 중에 INPUT이 아닌 Parameter 들</param>
/// <param name="response">Instance of XdsResponseItem to build with result of execution of DbCommand
/// <param name="responseIndex">index of response</param>
protected static void ExtractParameters(IAdoParameter[] outputs, XdsResponseItem response, int responseIndex)
{
if (outputs == null || outputs.Length == 0)
{
return;
}
foreach (var output in outputs)
{
response.Fields.Add(new XdsField(output.Name.RemoveParameterPrefix(),
DbFunc.GetLanguageType(output.ValueType).FullName,
output.Size.GetValueOrDefault(0)));
}
var record = new XdsRecord();
foreach (var output in outputs)
{
record.Columns.AddColumn(output.Value);
}
response.Records.Add(record);
}
public IEnumerable <Base_Period> Query(string whr)
{
_sb = new StringBuilder();
_sb.Append(forepart);
if (!string.IsNullOrEmpty(whr))
{
_sb.Append(" where" + whr);
}
_sb.Append(" order by coalesce(OrderCode ,10000),OrderCode asc");
dbFunc = new DbFunc();
dbFunc.ConnectionString = connstr;
var dt = dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <Base_Period> .FillModel(dt));
}
else
{
return(null);
}
}
public UserLessonDAL()
{
_sb = new StringBuilder();
_dbFun = new DbFunc();
}
public DataTable Query(string whr)
{
_sb.Append(forepart + whr);
dbFunc = new DbFunc();
return(dbFunc.ExecuteDataTable(_sb.ToString()));
}
