using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
using Newtonsoft.Json;
using Model;
namespace DAL
{
public class infoDAL
{
MySqlConnection con = new MySqlConnection("Database='day20';Data Source='localhost';User Id='root';Password='keke';charset='utf8';pooling=true");
/// 显示两表信息
public List<infoModel> ShowInfo(string name="")
{
string sql = "SELECT * from classinfo c join studentinfo s on c.CID=s.ClassID where 1=1";
if(!string .IsNullOrWhiteSpace(name))
{
sql += " and SName like '%" + name + "%'";
}
MySqlDataAdapter ada = new MySqlDataAdapter(sql,con);
DataTable dt = new DataTable();
ada.Fill(dt);
var data = JsonConvert.SerializeObject(dt);
var result = JsonConvert.DeserializeObject<List<infoModel>>(data);
return result;
}
/// 新增
public int Addinfo(infoModel m)
{
con.Open();
string sql = "INSERT into studentinfo( SName,SNum,SSex,STime,ClassID,SImg) VALUES('" + m.SName + "','" + m.SNum + "','" + m.SSex + "','" + m.STime + "','" + m.ClassID + "','" + m.SImg + "')";
MySqlCommand cmd = new MySqlCommand(sql, con);
var result = cmd.ExecuteNonQuery();
con.Close();
return result;
}
/// 删除
public int Deleteinfo(int id)
{
con.Open();
string sql = "DELETE FROM studentinfo WHERE SID='" + id + "'";
MySqlCommand cmd = new MySqlCommand(sql, con);
var result = cmd.ExecuteNonQuery();
con.Close();
return result;
}
/// 显示要修改信息
public List<infoModel> GetUpt(int id)
{
string sql = "SELECT * from studentinfo where SID='" + id + "' ";
MySqlDataAdapter ada = new MySqlDataAdapter(sql, con);
DataTable dt = new DataTable();
ada.Fill(dt);
var data = JsonConvert.SerializeObject(dt);
var result = JsonConvert.DeserializeObject<List<infoModel>>(data);
return result;
}
/// 修改
public int Updatenfo(infoModel m)
{
con.Open();
string sql = "UPDATE studentinfo SET SName='" + m.SName + "',SNum='" + m.SNum + "',SSex='" + m.SSex + "',STime='" + m.STime + "',ClassID='" + m.ClassID + "',SImg= '" + m.SImg + "' WHERE SID='" + m.SID + "'";
MySqlCommand cmd = new MySqlCommand(sql, con);
var result = cmd.ExecuteNonQuery();
con.Close();
return result;
}
}
}