“21天好习惯”第一期-6

ASP.NET MVC 连接数据库

利用连接SQL server数据库的代码和SQL语句来在MVC程序中实现数据的增删改查操作

接下来实现改操作

---

1.Edit页面

在昨天的项目中发生了一点小错误

 原因是这里的SET后面没有空格

PhotoService中的Update方法实现更新数据的功能

public List<Photo> Update(Photo photo)
        {
            string connString = "Data Source=DESKTOP-S1LO8P3\\H;Initial Catalog=PhotoDB;Integrated Security=SSPI;";
            SqlConnection connection = new SqlConnection(connString);

            //string sql = "UPDATE [dbo].[Photo] SET";  //发生语法错误
            string sql = "UPDATE [dbo].[Photo] SET ";
            //sql += "photoFileName='photo.photoFileName',photoShootTime='photo.photoShootTime',photographer=' photo.photographer'";
            //使用上面的语法日期转换会发生错误
            sql += string.Format("photoFileName='{0}',photoShootTime='{1}',photographer='{2}'",photo.photoFileName,photo.photoShootTime,photo.photographer);
            sql += "WHERE photoID=" + photo.photoID;
            sql += "SELECT photoID,photoFileName,photoShootTime,photographer FROM [dbo].[Photo]"; //查找更新后的所有数据

            SqlCommand command = new SqlCommand(sql, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            List<Photo> list = new List<Photo>();
            while (reader.Read())
            {
                Photo p = new Photo()
                {
                    photoID = (int)reader["photoID"],
                    photoFileName = reader["photoFileName"].ToString(),
                    photoShootTime = Convert.ToDateTime(reader["photoShootTime"]),
                    photographer = reader["photographer"].ToString()
                };
                list.Add(p);
            }
            connection.Close(); //关闭数据库连接
            return list;

        }

对应控制器中的Edit操作方法

  // GET: Photo/Edit/5
        public ActionResult Edit(int id)
        {
            Photo photo = service.Find(id);
            return View(photo);
        }

        // POST: Photo/Edit/5
        [HttpPost]
        public ActionResult Edit(Photo photo)
        {
            List<Photo> list = service.Update(photo);
            return View("index",list);
        }

记住要生成对应的Edit模板视图

2.Create页面

 public List<Photo> Add(Photo photo)
        {
            string connString = "Data Source=DESKTOP-S1LO8P3\\H;Initial Catalog=PhotoDB;Integrated Security=SSPI;";
            SqlConnection connection = new SqlConnection(connString);

            string sql = "INSERT INTO [dbo].[Photo] (photoFileName,photoShootTime,photographer) VALUES";
            sql += string.Format("('{0}','{1}','{2}')",photo.photoFileName,photo.photoShootTime,photo.photographer);
            sql += "SELECT photoID,photoFileName,photoShootTime,photographer FROM [dbo].[Photo]";

            SqlCommand command = new SqlCommand(sql, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            List<Photo> list = new List<Photo>();
            while (reader.Read())
            {
                Photo p = new Photo()
                {
                    photoID = (int)reader["photoID"],
                    photoFileName = reader["photoFileName"].ToString(),
                    photoShootTime = Convert.ToDateTime(reader["photoShootTime"]),
                    photographer = reader["photographer"].ToString()
                };
                list.Add(p);
            }
            connection.Close(); //关闭数据库连接
            return list;
        }

从SQL语句后面的代码下来多有重复，可考虑新建一个方法，优化简便代码，但是sql语句的命名需要统一。最后记得添加Create视图页面

3.Delete页面

   public List<Photo> Delete(int id)
        {
            string connString = "Data Source=DESKTOP-S1LO8P3\\H;Initial Catalog=PhotoDB;Integrated Security=SSPI;";
            SqlConnection connection = new SqlConnection(connString);

            string sql = "DELETE FROM [dbo].[Photo] WHERE photoID=" + id;
            sql += "SELECT photoID,photoFileName,photoShootTime,photographer FROM [dbo].[Photo]";

            SqlCommand command = new SqlCommand(sql, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            List<Photo> list = new List<Photo>();
            while (reader.Read())
            {
                Photo p = new Photo()
                {
                    photoID = (int)reader["photoID"],
                    photoFileName = reader["photoFileName"].ToString(),
                    photoShootTime = Convert.ToDateTime(reader["photoShootTime"]),
                    photographer = reader["photographer"].ToString()
                };
                list.Add(p);
            }
            connection.Close(); //关闭数据库连接
            return list;

        }

 public ActionResult Delete(int id)
        {
            Photo photo = service.Find(id);
            return View(photo);
        }

        // POST: Photo/Delete/5
        [HttpPost]
        public ActionResult Delete(int id, FormCollection collection)
        {
            List<Photo> list = service.Delete(id);
            return View("index", list);
        }

最后记得添加视图

这就完成了链接数据库的所有的增删改查操作