WebMatrix数据访问系列目录:
WebMatrix是微软专为Asp.net web pages打造的一款轻量快捷型的Web开发工具,适合非专业开发人员,新手入门,详细可参考此处。下文是结合微软教程而总结的WebMatrix简单访问数据库的方式。当然,所选数据库是WebMatrix御用轻量型数据库Sql Server Compact Edition,也可以选择创建连接选择服务器版本的Sql server:
上图可以看出WebMatrix创建的数据库连接方式只支持SQL Server 2005/2008。
以下是WebMatrix访问数据之CRUD:
(Asp.net web pages 中的Razor语法可参考此文:Introduction to ASP.NET Web Programming Using the Razor Syntax)
1.Create
@{ var MovieName=""; var MovieGenre=""; var MovieYear=""; if(IsPost){ MovieName=Request["formName"]; MovieGenre=Request["formGenre"]; MovieYear=Request["formYear"]; var SQLINSERT = "INSERT INTO Favorites (Name, Genre, ReleaseYear) VALUES (@0, @1, @2)"; var db = Database.Open("Movies"); db.Execute(SQLINSERT, MovieName, MovieGenre, MovieYear); Response.Redirect("datamovies.cshtml"); } }
使用命名空间WebMatrix.Data下的类Database的静态方法Open创建一个SSCE的数据库连接(准确来说是一个Func<DbConnection>,实现延迟创建DbConnection,后文会介绍);方法Execute执行sql插入语句,参数占位符使用类似于@0, @1, @2,防止sql注入(Execute方法内部会根据传入最后三个参数值创建相应的DbParameter)。
2.Retrieve
@{ var db= Database.Open("Movies"); var sqlQ = "SELECT * FROM Favorites"; var data = db.Query(sqlQ); }
查询单行语句可使用DataBase实例方法QuerySingle。
3.Update
@{ var id=Request["id"]; var SQLSELECT = "SELECT * FROM Favorites where ID=@0"; var db = Database.Open("Movies"); var Movie = db.QuerySingle(SQLSELECT,id); var MovieName=Movie.Name; var MovieGenre=Movie.Genre; var MovieYear=Movie.ReleaseYear; if(IsPost){ MovieName=Request["formName"]; MovieGenre=Request["formGenre"]; MovieYear=Request["formYear"]; var SQLUPDATE = "UPDATE Favorites Set Name=@0, Genre=@1, ReleaseYear=@2 WHERE id=@3"; db.Execute(SQLUPDATE, MovieName, MovieGenre, MovieYear,id); Response.Redirect("dataMovies.cshtml"); } }
在if(IsPost)通过Requese请求获取需要更新的列值,同样是使用Execute方法。
4.Delete
@{ var id=Request["id"]; var SQLSELECT = "SELECT * FROM Favorites where ID=@0"; var db = Database.Open("Movies"); var Movie = db.QuerySingle(SQLSELECT,id); var MovieName=Movie.Name; if(IsPost){ var SQLDELETE = "DELETE FROM Favorites WHERE ID=@0"; db.Execute(SQLDELETE,id); Response.Redirect("dataMovies.cshtml"); } }