登录，从sql server里把数据读出来放到List里，增删改查实现(最简单，可以说是裸露式)

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}");
            }