This topic demonstrates creating a method in Entity Developer NHibernate model, which will execute a parameterized query returning the table records that satisfy the query condition as the list of corresponding entity objects.
For this, perform the following steps:
| 1. | Add an entity for a table to the model. |
| 2. | In the Model Explorer context menu select the Add submenu and then select New Method, or click the corresponding button on the Model Explorer toolbar or on the main application toolbar. |
| 3. | In the Method Editor dialog specify the SQL for the method, set the entity as the method return type and customize the parameters: |
| 4. | Customize the parameters: |
| 5. | Click OK. |
The method is created.
As a result of the code generation for the model, the corresponding method is generated:
C#:
public List<Department> GetDepartmentsByLocation (string Loc) {
List<Department> result = new List<Department>();
DbConnection connection = this.Database.GetDbConnection(); bool needClose = false; if (connection.State != ConnectionState.Open) { connection.Open(); needClose = true; }
try { using (DbCommand cmd = connection.CreateCommand()) { if (this.Database.GetCommandTimeout().HasValue) cmd.CommandTimeout = this.Database.GetCommandTimeout().Value; cmd.CommandType = CommandType.Text; cmd.CommandText = @"SELECT * FROM Department WHERE Loc = :Loc";
DbParameter LocParameter = cmd.CreateParameter(); LocParameter.ParameterName = "Loc"; LocParameter.Direction = ParameterDirection.Input; if (Loc != null) { LocParameter.Value = Loc; } else { LocParameter.DbType = DbType.String; LocParameter.Size = -1; LocParameter.Value = DBNull.Value; } cmd.Parameters.Add(LocParameter);
using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Department row = new Department(); if (!reader.IsDBNull(reader.GetOrdinal("DeptNo"))) row.DeptNo = (int)Convert.ChangeType(reader.GetValue(reader.GetOrdinal(@"DeptNo")), typeof(int));
if (!reader.IsDBNull(reader.GetOrdinal("DName"))) row.DName = (string)Convert.ChangeType(reader.GetValue(reader.GetOrdinal(@"DName")), typeof(string)); else row.DName = null;
if (!reader.IsDBNull(reader.GetOrdinal("Loc"))) row.Loc = (string)Convert.ChangeType(reader.GetValue(reader.GetOrdinal(@"Loc")), typeof(string)); else row.Loc = null;
result.Add(row); } } } } finally { if (needClose) connection.Close(); } return result; } |
Now it is possible to use this query in the application with the help of method wrapper. This allows working with the query with all possible convenience, as wrapper methods are strongly typed, are found by IntelliSense and have the corresponding signature.
