Model:
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public bool IsMan { get; set; }
}
Service:
放到list/查
public class Service
{
public List<User> GetList()
{
try
{
List<User> list = new List<User>();
string connString = "server=192.168.1.226;uid=sa;pwd=sa;database=chart";//数据库连接字符串
SqlConnection conn = new SqlConnection(connString); //创建数据库连接对象
conn.Open();//打开数据连接
SqlCommand cmd = new SqlCommand("select * from User",conn);//创建命令对象,如果是存储过程“exec 存储过程名称”
var dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行命令对象,返回数据读取对象
while (dataReader.Read())//一直读数据直到没有数据为止
{
User user = new User();//创建用户实体对象
user.Id = Convert.ToInt32(dataReader["Id"]);//读取Id字段
user.Name = Convert.ToString(dataReader["Name"]);//读取Name字段
user.Email = Convert.ToString(dataReader["Email"]);
user.IsMan = Convert.ToBoolean(dataReader["IsMan"]);
list.Add(user);//把创建的实体对象加入list中
}
dataReader.Dispose();//释放数据读取对象
cmd.Dispose();//释放命令对象
conn.Close();//关闭连接对象
conn.Dispose();//释放连接对象
return list;//返回list
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
增:
public class Service
{
public bool Insert(User entity)
{
try
{
List<User> list = new List<User>();
string connString = "server=192.168.1.226;uid=sa;pwd=sa;database=chart";//数据库连接字符串
SqlConnection conn = new SqlConnection(connString); //创建数据库连接对象
conn.Open();//打开数据连接
SqlCommand cmd = new SqlCommand("insert into User values('" + entity.Name + "," + entity.Email + "," + entity.IsMan + ")",conn);//创建命令对象
cmd.ExecuteNonQuery();
cmd.Dispose();//释放命令对象
conn.Close();//关闭连接对象
conn.Dispose();//释放连接对象
return true;
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
删:
public class Service
{
public bool Delete(int Id)
{
try
{
List<User> list = new List<User>();
string connString = "server=192.168.1.226;uid=sa;pwd=sa;database=chart";//数据库连接字符串
SqlConnection conn = new SqlConnection(connString); //创建数据库连接对象
conn.Open();//打开数据连接
SqlCommand cmd = new SqlCommand("Delete from User where Id = " + Id,conn);//创建命令对象
cmd.ExecuteNonQuery();
cmd.Dispose();//释放命令对象
conn.Close();//关闭连接对象
conn.Dispose();//释放连接对象
return true;
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
改:
public class Service
{
public bool Update(User entity)
{
try
{
List<User> list = new List<User>();
string connString = "server=192.168.1.226;uid=sa;pwd=sa;database=chart";//数据库连接字符串
SqlConnection conn = new SqlConnection(connString); //创建数据库连接对象
conn.Open();//打开数据连接
SqlCommand cmd = new SqlCommand("Update User set Name = '" + entity.Name + ", Email = '" + entity.Email + ", IsMan = " + entity.IsMan,conn);//创建命令对象
cmd.ExecuteNonQuery();
cmd.Dispose();//释放命令对象
conn.Close();//关闭连接对象
conn.Dispose();//释放连接对象
return true;
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
Controller:
某个方法里调用
var service = new Service();
var list = service.GetList();
foreach (var user in list)
{
Console.WriteLine($"ID={user.Id},Name={user.Name},IsMan={user.IsMan}");
}