Comparison between EntityFramework and Ado.net -- what are the advantages of EF ?,
More accurately, EF is more accurate than SqlHelper ado.net.
Public class SqlHelper {public static readonly string connstr = "Server = .; database = PhoneBook; Uid = sa; Pwd = ********; "; // parameters with variable params length. public static int ExecuteNonQuery (string SQL, params SqlParameter [] parameters) {// using (SqlConnection conn = new SqlConnection (connstr) {conn. open (); using (SqlCommand cmd = conn. createCommand () {cmd. commandText = SQL; cmd. parameters. addRange (parameters ); Return cmd. executeNonQuery () ;}// write Method B // SqlConnection conn = new SqlConnection (connstr); // conn. open (); // SqlCommand cmd = conn. createCommand (); // cmd. commandText = SQL; // cmd. parameters. addRange (parameters); // return cmd. executeNonQuery ();} public static object ExecuteScalar (string SQL, params SqlParameter [] parameters) {using (SqlConnection conn = new SqlConnection (connstr) {conn. open (); Using (SqlCommand cmd = conn. createCommand () {cmd. commandText = SQL; cmd. parameters. addRange (parameters); return cmd. executeScalar () ;}} public static DataTable ExecuteDataTable (string SQL, params SqlParameter [] parameters) {using (SqlConnection conn = new SqlConnection (connstr) {conn. open (); using (SqlCommand cmd = conn. createCommand () {cmd. commandText = SQL; cmd. parameters. addRange (par Ameters); DataSet dataset = new DataSet (); SqlDataAdapter adapter = new SqlDataAdapter (cmd); adapter. fill (dataset); return dataset. tables [0] ;}}// enter NULL data: // In SqlParameter, if it is "", the data in the database is "" or 0. // If SqlParameter is Null, the program runs an error. "No parameters are provided. "// how does one indicate Null in the database? SqlParameter uses DBNull. Value. // if the database Value is Null, AOD.net reads it as DBNull. Value. The DBNull. Value cannot be converted to string. // can be empty, int? Bool? Public static object FromDBValue (object value) {if (value = DBNull. value) {return null;} else {return value;} public static object ToDBValue (object value) {if (value = null) {return DBNull. value ;}else {return value ;}}}SqlHelper1. new operation.
Use EF:
Var db = new PhoneBookEntities (); db. database. log = Console. write; // print the SQL statement List <GroupInfo> list = new List <GroupInfo> (); list. add (new GroupInfo () {GroupName = "li"}); list. add (new GroupInfo () {GroupName = "ge"}); list. add (new GroupInfo () {GroupName = "xiao"}); db. groupInfo. addRange (list); db. saveChanges ();
Monitoring results using SQL server profiler:
Use SqlHelper
Public class GroupInfoDAL {public GroupInfo ToGroupInfo (DataRow row) {GroupInfo model = new GroupInfo (); model. groupId = (System. int32) row ["GroupId"]; model. groupName = (System. string) row ["GroupName"]; return model;} public GroupInfo GetById (int id) {DataTable table = SqlHelper. executeDataTable ("select * from GroupInfo where GroupId = @ GroupId", new SqlParameter ("@ GroupId", id); if (table. rows. count <= 0) {return null;} else if (table. rows. count> 1) {throw new Exception ("Duplicate Id, serious error");} else {DataRow row = table. rows [0]; return ToGroupInfo (row) ;}} public void DeleteById (int id) {SqlHelper. executeNonQuery ("delete from GroupInfo where GroupId = @ GroupId", new SqlParameter ("@ GroupId", id);} public GroupInfo [] GetAll () {DataTable dt = SqlHelper. executeDataTable ("select * from GroupInfo"); GroupInfo [] models = new GroupInfo [dt. rows. count]; for (int I = 0; I <dt. rows. count; I ++) {models [I] = ToGroupInfo (dt. rows [I]);} return models;} public void Update (GroupInfo model) {SqlHelper. executeNonQuery ("update GroupInfo set GroupName = @ GroupName where GroupId = @ GroupId", new SqlParameter ("@ GroupId", model. groupId), new SqlParameter ("@ GroupName", model. groupName);} public void Insert (GroupInfo model) {SqlHelper. executeNonQuery ("insert into GroupInfo (GroupName) values (@ GroupName)", new SqlParameter ("@ GroupName", model. groupName ));}}GroupInfoDal static void InsertTest () {var giDal = new GroupInfoDAL (); List <GroupInfo> list = new List <GroupInfo> (); list. add (new GroupInfo () {GroupName = "li"}); list. add (new GroupInfo () {GroupName = "ge"}); list. add (new GroupInfo () {GroupName = "xiao"}); foreach (var item in list) {giDal. insert (item );}}InsertTest
Monitoring results using SQL server profiler:
Test results:
EF: Execute three SQL statements for one connection.
Using as the expression a in SqlHelper may cause multiple Connection Reset;
B Does not use using, and the connection cannot be released.
Similarly, updating and deleting multiple data entries is also a truth.
You can even use entiure in EntityFramework. Extended to store multiple queries in one connection. SeeEntityFramework and EntityFramework. Extended instructions for use-performance, syntax, and generated SQL
2. update
EF is automatically optimized. Only fields with changed set values are updated.
EF can also easily update only the specified attributes of an object, resulting in fewer set fields in the generated SQL statement.
Public void Edit (TEntity model, string [] propertyNames) {// check the legality of the model if (model = null) {throw new Exception ("model object cannot be Null");} // The propertyNames attribute must have at least one if (propertyNames = null | propertyNames. any () = false) {throw new Exception ("The propertyNames array must have at least one value");} var entry = db. entry (model); entry. state = EntityState. unchanged; foreach (var item in propertyNames) {entry. property (item ). isModified = true;} db. configuration. validateOnSaveEnabled = false ;}Update specified attributes 3. Only query specified fields in the table
SqlHelper converts each row of the queried data table into an object. The SQL statement: select * from... is not recommended.
Is it a little troublesome to solve this problem? (If you have a good method, you can leave a message. If you want to consider DTO and domain model in the answer, complex services will certainly be used .)
The select Projection Method in EF is easy to handle, and only the fields required by 'select' in the SQL statement.
4. there is a smart prompt in the use of the linq and lamda expressions, and the error cannot be compiled. write the SQL statement string and call sqlhelper. The SQL statement is compiled by mistake. 5. security saves the trouble of preventing SQL injection. 6. with EF, it is easier to switch database changes
Although the databases used will be determined at the beginning of the project, who can ensure that there is no need for unusual database changes?
If you are using EF, you will have a smile.
7. In terms of development efficiency, EF is more efficient than Ado.net.
Currently, Database First is used to directly generate entity classes (code generation function );
If it is Code First, the corresponding database will also be created. It is very convenient. This is only a small aspect.
More importantly, EF makes it easier to operate databases.
8. code readability.
With EF, the code is more readable.
...
If any error occurs, please point it out. If there are any omissions, please help improve.