Creating applications for Firebird with the use of various components and drivers: ADO.NET Entity Framework 6
This article will describe the process of creating applications for the Firebird database using the Entity Framework access components and the Visual Studio 2015 environment.
The ADO.NET Entity Framework (EF) , an object-oriented data access technology, is an object-relational mapping (ORM) solution for Microsoft's .NET Framework. Provides the ability to interact with objects both through LINQ in the form of LINQ to Entities, and using Entity SQL.
Entity Framework suggests three possible ways to interact with the database:
')
In our application, we will use the Code First approach, but you can easily use other approaches.
Our application will work with the database, the model of which is shown in the figure below.
To work with Firebird you need to install:
Installing the first two does not cause any difficulties. They are currently distributed and installed into the project using NuGet. But the latest library, designed for the work of the Visual Studio wizards, is not easily installed and can spend a lot of time and effort.
Good people tried to automate the installation process and include the installation of all components in one distribution . However, in some cases you may need to manually install all the components. In this case, you will need to download:
The following describes the installation process:
Installation is complete.
To verify that everything was successfully installed, we launch Visual Studio 2015. We find the server browser and try to connect to one of the existing Firebird databases.
In this article we will look at an example of creating a Windows Forms application. The other types of applications are different, but the principles of working with Firebird through the Entity Framework remain the same.
First of all, after creating the Windows Forms project, we need to add the following packages using the NuGet package manager:
To do this, right-click on the project name in the Solution Explorer and select the “NuGet Package Management” item in the drop-down menu.
In the package manager that appears, search for and install the required packages.
In our application, we will use the Code First approach.
To create an EDM model, right-click the project name in the Solution Explorer and select the menu item Add -> Create Item.
Next, in the Add New Item Wizard, select the “ADO.NET EDM Model” item.
Since we already have a database (see Creating Applications for Firebird DBMS Using Different Components and Drivers: FireDac ), we will generate an EDM model from the database.
Now you need to select the connection from which the model will be created. If there is no such connection, then it must be created.
In addition to the basic connection parameters, you may also need to specify a number of additional parameters, for example, the isolation level of transactions (the default is Read Commited), connection pooling, etc.
In the process of creating a model wizard, you will be asked how to store the connection string.
If you are building a web application or a three-server system, where all users will work with the database under the same account, then feel free to choose “Yes”. If your application needs to ask for credentials to connect to the database, select "No". However, it is much more convenient to work with masters when you have selected “Yes”. You can always change this in the finished application by simply editing the connection string in the application configuration file .exe.conf. The connection string will be saved in the connectionStrings section approximately as follows.
To stop the configuration file from storing confidential information, simply delete it from the connection string.
You will then be asked which tables and views should be included in the model.
In principle, the EDM model is ready. After running this wizard, you should have 5 new files. One model file and four files describing each of the model entities.
Let's see one of the generated files describing the essence of INVOICE.
The class contains properties that display the fields of the INVOICE table. Each of these properties is provided with attributes that describe the constraints. You can read more about various attributes in the Microsoft documentation for Code First Data Annotations .
In addition, two more navigation properties CUSTOMER and INVOICE_LINES were generated. The first contains a link to the supplier’s entity, the second a collection of invoice lines. It was generated because the INVOICE_LINE table has a foreign key to the INVOICE table. Of course, you can remove this property from the INVOICE entity, but this is not necessarily done. The fact is that in this case the properties CUSTOMER and INVOICE_LINES use the so-called “lazy loading”. With this, loading is performed at the first access to the object, i.e. if the associated data is not needed, they are not loaded. However, when you first access the navigation property, this data is automatically loaded from the database.
When using lazy loading, some points should be kept in mind when declaring classes. So, classes using lazy loading should be public, and their properties should have public and virtual modifiers.
In the same class, we will have the first unpleasant surprise. The TOTAL_SALE field was essentially displayed as double, although in the database it is of type NUMERIC (15, 2), so we have a loss of precision. I tend to regard this as a bug in the Firebird ADO.NET Provider. Let's try to fix this annoying mistake. In C #, there is a decimal type for operations on fixed-precision numbers.
In addition, we will change the description of all fields in all entities where the type Firebird NUMERIC (x, y) is used. Namely PRODUCT.PRICE, INVOICE_LINE.QUANTITY, INVOICE_LINE.SALE_PRICE.
Now open the DbModel.cs file describing the model as a whole.
Here we see the properties describing the data set for each entity. As well as setting additional properties for creating a model using the Fluent API. A full description of the Fluent API can be found in the Microsoft Configuring / Mapping Properties and Types with the Fluent API documentation.
In the OnModelCreating method, we set the accuracy for decimal properties using the Fluent API. To do this, we add the following lines
In our application, we will create two directories: a reference book of goods and a reference book of customers. Each directory contains a DataGridView grid, a panel with ToolStrip buttons, and a BindingSource component, which is used to simplify data binding to form controls.
Since both reference books are similar in functionality and implemented in a similar way, we will describe only one.
To work with our model, we need a method to get the context (or model). In principle, it’s enough to do this:
However, if the connection string does not store sensitive data (for example, a password), and we initialize it during the application startup, we will need a special method for storing and restoring the connection string or saving the previously created context. To do this, we will create a special class that, in addition to the method for obtaining the context, will also contain some global variables of the application level, for example, the working period.
The connection string itself is initialized when the application starts, after the authorization is successful. To do this, in the event handler for the main form, we will write the following code.
Now we will use the static CreateDbContext method to get the context.
The entities of the model themselves do not contain any data. The easiest way to load data is to call the Load method, like this:
However, this method has several disadvantages:
To circumvent these disadvantages, we will use LINQ (Language Integrated Query) technology, or more precisely LINQ to Entities. LINQ to Entities offers a simple and intuitive approach to retrieving data using expressions that are close in form to SQL expressions. LINQ syntax is available through LINQ to Entities .
LINQ extension methods can return two objects: IEnumerable and IQueryable . The IQueryable interface is inherited from IEnumerable; therefore, in theory, the IQueryable object is also an IEnumerable object. But between them there is a significant difference.
The IEnumerable interface is in the System.Collections namespace. An IEnumerable object represents a data set in memory and can only be moved forward through this data. When executing the query, IEnumerable loads all the data, and if we need to filter it, the filtering itself takes place on the client side.
The IQueryable interface is located in the System.Linq namespace. The IQueryable object provides remote access to the database and allows you to navigate through the data in direct order from beginning to end, and in reverse order. In the process of creating a query, the return object of which is IQueryable, query optimization occurs. As a result, in the process of its execution, less memory is spent, less network bandwidth.
The Local property returns an IEnumerable interface. Therefore, we can make LINQ requests to it.
However, as already mentioned, this query will be executed on the data in memory. In principle, for small tables that do not require pre-filtering, this is acceptable.
In order for a LINQ query to be converted to SQL and run on the server side, we need to use the LINQ query instead of accessing the dbContext.CUSTOMERS.Local property to immediately refer to dbContext.CUSTOMERS. In this case, we will not need to call dbContext.CUSTOMERS.Load ();
to load the collection into memory.
However, here we are trapped by one small ambush. IQueryable objects do not know how to return a BindingList. BindingList is the base class for creating a two-way data binding mechanism. From the IQueryable interface, we can get a regular list by calling ToList, but in this case we lose pleasant bonuses, such as sorting in the grid and many others. By the way, in .NET Framework 5 this has already been fixed and a special extension has been created. Make your extension, which will do the same.
In the same class there are some more extensions.
The NextValueFor method is designed to get the next value of the generator. The dbContext.Database.SqlQuery method allows you to execute SQL queries directly and display their results on some entity (projection). You can use it if you need to execute a SQL query directly.
The DetachAll method is designed to detach all objects of a DBSet collection from the context. This is necessary to update the internal cache. The point is that within the context, all recoverable are cached and not retrieved from the database again. However, this is not always useful, as it makes it difficult to obtain modified entries made in a different context.
The Refresh method is used to update the properties of an entity object. It is useful for updating properties of an object after editing or adding it.
So our data load code will look like this.
The code of the event handler at the press of the add button is as follows.
When adding a new record, we get the value of the next identifier using the generator. We could not initialize the value of the identifier, and in this case the BEFORE INSERT would execute a trigger that would still pull the next value of the generator. However, in this case, we could not update the newly added record.
The code of the event handler for pressing the edit button is as follows.
The form for editing the customer is as follows.
The data binding code is very simple.
The code of the event handler for pressing the delete button is as follows.
«-». .
- – , (, , …), - , , .. : , — . , DataGridView, BindingSource
. , . – , . , , , AppVariables (. ), , , , . ( ). .
Since most often the last documents entered are required, it makes sense to sort them by date in the reverse order. We will retrieve the data, as in the case with directories using LINQ. Taking into account the above, the method for loading data of invoice caps will look as follows:
As a projection, we used not the anonymous type, but the class InvoiceView. This simplifies type casting. The definition of the InvoiceView class is as follows:
The Load method allows us to quickly update 1 added or updated record in the grid, rather than completely reloading all records.
The code of the event handler at the press of the add button is as follows.
dbContext.Refresh, Load InvoiceView. , dbContext.Refresh , , LINQ .
.
. CurrentInvoice -. :
.
, - , :
- :
.
, - - . SQL , , . WEB .
, - , SQL ( ).
. BindingSource.
- :
InvoiceLineView.
, InvoiceView . , , .
.
In the methods for adding, editing, and deleting, we show how to work with stored procedures in the Entity Framework. For example, the method for adding a new entry looks like this:
Here updating the master master record is required because one of its fields (TotalSale) contains aggregated information by document lines.
The method for updating the record is implemented as follows.
The method for deleting a record is implemented as follows.
In the methods for adding and editing invoice items, we used the form for editing.
TextBox. , , . , . :
, , SaveChanges(), Entity Framework . , . EF . . , . :
, 10 . 10 . , . , :
In this case, we started the transaction with the default parameters. In order to set your transaction parameters you must use the UseTransaction method.
Here you go.Now we have only one transaction for the entire set of updates, and there are no extra commands to search for data. It remains only to add a dialog to enter the discount value and update the data in the grid. Try it yourself.
I hope this article has helped you understand the features of writing a C # application using the Entity Framework when working with Firebird.
Source codes of the sample application
Database and scripts for its creation
The ADO.NET Entity Framework (EF) , an object-oriented data access technology, is an object-relational mapping (ORM) solution for Microsoft's .NET Framework. Provides the ability to interact with objects both through LINQ in the form of LINQ to Entities, and using Entity SQL.
Entity Framework suggests three possible ways to interact with the database:
- Database first : The Entity Framework creates a set of classes that reflect the model of a particular database.
- Model first : first, the developer creates a database model, by which the Entity Framework then creates a real database on the server.
- Code first : the developer creates a data model class that will be stored in the database, and then the Entity Framework generates a database and its tables using this model
')
In our application, we will use the Code First approach, but you can easily use other approaches.
Our application will work with the database, the model of which is shown in the figure below.
| Attention! This model is just an example. Your subject area may be more complicated, or completely different. The model used in this article is simplified as much as possible in order not to clutter up the description of working with components with the description of creating and modifying the data model. |
Preparing Visual Studio 2015 to work with Firebird
To work with Firebird you need to install:
- FirebirdSql.Data.FirebirdClient.dll
- EntityFramework.Firebird.dll
- DDEX Provider for Visual Studio
Installing the first two does not cause any difficulties. They are currently distributed and installed into the project using NuGet. But the latest library, designed for the work of the Visual Studio wizards, is not easily installed and can spend a lot of time and effort.
Good people tried to automate the installation process and include the installation of all components in one distribution . However, in some cases you may need to manually install all the components. In this case, you will need to download:
- FirebirdSql.Data.FirebirdClient-4.10.0.0.msi
- EntityFramework.Firebird-4.10.0.0-NET45.7z
- DDEXProvider-3.0.2.0.7z
- DDEXProvider-3.0.2.0-src.7z
The following describes the installation process:
- Install FirebirdSql.Data.FirebirdClient-4.10.0.0.msi
- Extract the EntityFramework.Firebird-4.10.0.0-NET45.7z to the folder with the installed Firebird client. I have a folder c: \ Program Files (x86) \ FirebirdClient \
Important!
This must be done with administrator rights. Like other actions with protected directories. - You need to install the Firebird builds in the GAC. For convenience, write to% PATH% path to the gacutil utility for .NET Framework 4.5. I have this path c: \ Program Files (x86) \ Microsoft SDKs \ Windows \ v10.0A \ bin \ NETFX 4.6.1 Tools \
- Run the cmd command line on behalf of the administrator and go to the directory with the installed client.
chdir "c:\Program Files (x86)\FirebirdClient" - Now we check that FirebirdSql.Data.FirebirdClient is installed in the GAC. To do this, type the command
gacutil /l FirebirdSql.Data.FirebirdClient
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.0
c (Microsoft Corporation). .
:
FirebirdSql.Data.FirebirdClient, Version=4.10.0.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c, processorArchitecture=MSIL
= 1
If FirebirdSql.Data.FirebirdClient was not installed in the GAC, then do it with the commandgacutil /i FirebirdSql.Data.FirebirdClient.dll - Now install the EntityFramework.Firebird in the GAC
gacutil /i EntityFramework.Firebird.dll - Unpacking DDEXProvider-3.0.2.0.7z into a convenient directory. I unpacked it in c: \ Program Files (x86) \ FirebirdDDEX \
- There we unpack DDEXProvider-3.0.2.0-src.7z contents of the archive / reg_files / VS2015 subdirectory of the archive
Author's note
It's funny, but for some reason these files are not in the previous archive with compiled dll libraries, but they are present in the archive with source codes. - Open the FirebirdDDEXProvider64.reg file with notepad. Find the line that contains% path% and change it to the full path to the FirebirdSql.VisualStudio.DataTools.dll file
"CodeBase" = "c: \\ Program Files (x86) \\ FirebirdDDEX \\ FirebirdSql.VisualStudio.DataTools.dll" - Save this file, run it. At the request to add information to the registry, click YES.
- Now you need to edit the machine.config file, in my case it is located along the path: C: \ Windows \ Microsoft .NET \ Framework \ v4.0.30319 \ Config
Open this file with notepad. Find a section<system.data>
<DbProviderFactories>
Add a line to this section:<add name="FirebirdClient Data Provider" invariant="FirebirdSql.Data.FirebirdClient" description=".Net Framework Data Provider for Firebird" type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient, Version=4.10.0.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c" />Comment
All this is valid for version 4.10.0.
Do the same for machine.config. which is located in c: \ Windows \ Microsoft .NET \ Framework64 \ v4.0.30319 \ Config \
Installation is complete.
To verify that everything was successfully installed, we launch Visual Studio 2015. We find the server browser and try to connect to one of the existing Firebird databases.
Project creation
In this article we will look at an example of creating a Windows Forms application. The other types of applications are different, but the principles of working with Firebird through the Entity Framework remain the same.
First of all, after creating the Windows Forms project, we need to add the following packages using the NuGet package manager:
- FirebirdSql.Data.FirebirdClient
- EntityFramework
- EntityFramework.Firebird
To do this, right-click on the project name in the Solution Explorer and select the “NuGet Package Management” item in the drop-down menu.
In the package manager that appears, search for and install the required packages.
Creating an EDM model
In our application, we will use the Code First approach.
To create an EDM model, right-click the project name in the Solution Explorer and select the menu item Add -> Create Item.
Next, in the Add New Item Wizard, select the “ADO.NET EDM Model” item.
Since we already have a database (see Creating Applications for Firebird DBMS Using Different Components and Drivers: FireDac ), we will generate an EDM model from the database.
Now you need to select the connection from which the model will be created. If there is no such connection, then it must be created.
In addition to the basic connection parameters, you may also need to specify a number of additional parameters, for example, the isolation level of transactions (the default is Read Commited), connection pooling, etc.
In the process of creating a model wizard, you will be asked how to store the connection string.
If you are building a web application or a three-server system, where all users will work with the database under the same account, then feel free to choose “Yes”. If your application needs to ask for credentials to connect to the database, select "No". However, it is much more convenient to work with masters when you have selected “Yes”. You can always change this in the finished application by simply editing the connection string in the application configuration file .exe.conf. The connection string will be saved in the connectionStrings section approximately as follows.
<add name="DbModel" connectionString="character set=UTF8; data source=localhost;initial catalog=examples; port number=3050; user id=sysdba; dialect=3; isolationlevel=Snapshot; pooling=True; password=masterkey;" providerName="FirebirdSql.Data.FirebirdClient" /> To stop the configuration file from storing confidential information, simply delete it from the connection string.
password=masterkey; | A note about working with Firebird 3.0 Unfortunately, the current ADO .Net provider for Firebird (version 4.10.0) does not support SRP authentication (by default in Firebird 3.0). Therefore, if you want to work with Firebird 3.0, then you need to change some settings in firebird.conf (or in databases.conf for a specific database) in order for Firebird to work through Legacy_Auth. To do this, change the following settings: UserManager = Legacy_UserManagerSave settings. After that, you need to create a SYSDBA user and other users using Legacy_UserManager. |
You will then be asked which tables and views should be included in the model.
In principle, the EDM model is ready. After running this wizard, you should have 5 new files. One model file and four files describing each of the model entities.
Let's see one of the generated files describing the essence of INVOICE.
[Table("Firebird.INVOICE")] public partial class INVOICE { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public INVOICE() { INVOICE_LINES = new HashSet<INVOICE_LINE>(); } [Key] [DatabaseGenerated(DatabaseGeneratedOption.None)] public int INVOICE_ID { get; set; } public int CUSTOMER_ID { get; set; } public DateTime? INVOICE_DATE { get; set; } public double? TOTAL_SALE { get; set; } public short PAID { get; set; } public virtual CUSTOMER CUSTOMER { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<INVOICE_LINE> INVOICE_LINES { get; set; } } The class contains properties that display the fields of the INVOICE table. Each of these properties is provided with attributes that describe the constraints. You can read more about various attributes in the Microsoft documentation for Code First Data Annotations .
In addition, two more navigation properties CUSTOMER and INVOICE_LINES were generated. The first contains a link to the supplier’s entity, the second a collection of invoice lines. It was generated because the INVOICE_LINE table has a foreign key to the INVOICE table. Of course, you can remove this property from the INVOICE entity, but this is not necessarily done. The fact is that in this case the properties CUSTOMER and INVOICE_LINES use the so-called “lazy loading”. With this, loading is performed at the first access to the object, i.e. if the associated data is not needed, they are not loaded. However, when you first access the navigation property, this data is automatically loaded from the database.
When using lazy loading, some points should be kept in mind when declaring classes. So, classes using lazy loading should be public, and their properties should have public and virtual modifiers.
In the same class, we will have the first unpleasant surprise. The TOTAL_SALE field was essentially displayed as double, although in the database it is of type NUMERIC (15, 2), so we have a loss of precision. I tend to regard this as a bug in the Firebird ADO.NET Provider. Let's try to fix this annoying mistake. In C #, there is a decimal type for operations on fixed-precision numbers.
public decimal TOTAL_SALE { get; set; } In addition, we will change the description of all fields in all entities where the type Firebird NUMERIC (x, y) is used. Namely PRODUCT.PRICE, INVOICE_LINE.QUANTITY, INVOICE_LINE.SALE_PRICE.
Now open the DbModel.cs file describing the model as a whole.
public partial class DbModel : DbContext { public DbModel() : base("name=DbModel") { } public virtual DbSet<CUSTOMER> CUSTOMERS { get; set; } public virtual DbSet<INVOICE> INVOICES { get; set; } public virtual DbSet<INVOICE_LINE> INVOICE_LINES { get; set; } public virtual DbSet<PRODUCT> PRODUCTS { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<CUSTOMER>() .Property(e => e.ZIPCODE) .IsFixedLength(); modelBuilder.Entity<CUSTOMER>() .HasMany(e => e.INVOICES) .WithRequired(e => e.CUSTOMER) .WillCascadeOnDelete(false); modelBuilder.Entity<PRODUCT>() .HasMany(e => e.INVOICE_LINES) .WithRequired(e => e.PRODUCT) .WillCascadeOnDelete(false); modelBuilder.Entity<INVOICE>() .HasMany(e => e.INVOICE_LINES) .WithRequired(e => e.INVOICE) .WillCascadeOnDelete(false); } } Here we see the properties describing the data set for each entity. As well as setting additional properties for creating a model using the Fluent API. A full description of the Fluent API can be found in the Microsoft Configuring / Mapping Properties and Types with the Fluent API documentation.
In the OnModelCreating method, we set the accuracy for decimal properties using the Fluent API. To do this, we add the following lines
modelBuilder.Entity<PRODUCT>() .Property(p => p.PRICE) .HasPrecision(15, 2); modelBuilder.Entity<INVOICE>() .Property(p => p.TOTAL_SALE) .HasPrecision(15, 2); modelBuilder.Entity<INVOICE_LINE>() .Property(p => p.SALE_PRICE) .HasPrecision(15, 2); modelBuilder.Entity<INVOICE_LINE>() .Property(p => p.QUANTITY) .HasPrecision(15, 0); Creating user interface
In our application, we will create two directories: a reference book of goods and a reference book of customers. Each directory contains a DataGridView grid, a panel with ToolStrip buttons, and a BindingSource component, which is used to simplify data binding to form controls.
Since both reference books are similar in functionality and implemented in a similar way, we will describe only one.
Getting context
To work with our model, we need a method to get the context (or model). In principle, it’s enough to do this:
DbModel dbContext = new DbModel(); However, if the connection string does not store sensitive data (for example, a password), and we initialize it during the application startup, we will need a special method for storing and restoring the connection string or saving the previously created context. To do this, we will create a special class that, in addition to the method for obtaining the context, will also contain some global variables of the application level, for example, the working period.
static class AppVariables { private static DbModel dbContext = null; /// <summary> /// /// </summary> public static DateTime StartDate { get; set; } /// <summary> /// /// </summary> public static DateTime FinishDate { get; set; } /// <summary> /// () /// </summary> /// <returns></returns> public static DbModel CreateDbContext() { dbContext = dbContext ?? new DbModel(); return dbContext; } } The connection string itself is initialized when the application starts, after the authorization is successful. To do this, in the event handler for the main form, we will write the following code.
private void MainForm_Load(object sender, EventArgs e) { var dialog = new LoginForm(); if (dialog.ShowDialog() == DialogResult.OK) { var dbContext = AppVariables.getDbContext(); try { string s = dbContext.Database.Connection.ConnectionString; var builder = new FbConnectionStringBuilder(s); builder.UserID = dialog.UserName; builder.Password = dialog.Password; dbContext.Database.Connection.ConnectionString = builder.ConnectionString; // dbContext.Database.Connection.Open(); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); Application.Exit(); } } else Application.Exit(); } Now we will use the static CreateDbContext method to get the context.
var dbContext = AppVariables.getDbContext() Work with data
The entities of the model themselves do not contain any data. The easiest way to load data is to call the Load method, like this:
private void LoadCustomersData() { dbContext.CUSTOMERS.Load(); var customers = dbContext.CUSTOMERS.Local; bindingSource.DataSource = customers.ToBindingList(); } private void CustomerForm_Load(object sender, EventArgs e) { LoadCustomersData(); dataGridView.DataSource = bindingSource; dataGridView.Columns["CUSTOMER_ID"].Visible = false; } However, this method has several disadvantages:
- The Load method immediately loads all data from the CUSTOMER table into memory.
- Lazy properties (INVOICES), though not loaded immediately, but only as they are accessed, they will still be loaded when displaying records in the grid. And exactly as many times as the records will be displayed.
- The order of the entries is undefined.
To circumvent these disadvantages, we will use LINQ (Language Integrated Query) technology, or more precisely LINQ to Entities. LINQ to Entities offers a simple and intuitive approach to retrieving data using expressions that are close in form to SQL expressions. LINQ syntax is available through LINQ to Entities .
LINQ extension methods can return two objects: IEnumerable and IQueryable . The IQueryable interface is inherited from IEnumerable; therefore, in theory, the IQueryable object is also an IEnumerable object. But between them there is a significant difference.
The IEnumerable interface is in the System.Collections namespace. An IEnumerable object represents a data set in memory and can only be moved forward through this data. When executing the query, IEnumerable loads all the data, and if we need to filter it, the filtering itself takes place on the client side.
The IQueryable interface is located in the System.Linq namespace. The IQueryable object provides remote access to the database and allows you to navigate through the data in direct order from beginning to end, and in reverse order. In the process of creating a query, the return object of which is IQueryable, query optimization occurs. As a result, in the process of its execution, less memory is spent, less network bandwidth.
The Local property returns an IEnumerable interface. Therefore, we can make LINQ requests to it.
private void LoadCustomersData() { var dbContext = AppVariables.getDbContext(); dbContext.CUSTOMERS.Load(); var customers = from customer in dbContext.CUSTOMERS.Local orderby customer.NAME select new customer; bindingSource.DataSource = customers.ToBindingList(); } However, as already mentioned, this query will be executed on the data in memory. In principle, for small tables that do not require pre-filtering, this is acceptable.
In order for a LINQ query to be converted to SQL and run on the server side, we need to use the LINQ query instead of accessing the dbContext.CUSTOMERS.Local property to immediately refer to dbContext.CUSTOMERS. In this case, we will not need to call dbContext.CUSTOMERS.Load ();
to load the collection into memory.
However, here we are trapped by one small ambush. IQueryable objects do not know how to return a BindingList. BindingList is the base class for creating a two-way data binding mechanism. From the IQueryable interface, we can get a regular list by calling ToList, but in this case we lose pleasant bonuses, such as sorting in the grid and many others. By the way, in .NET Framework 5 this has already been fixed and a special extension has been created. Make your extension, which will do the same.
public static class DbExtensions { // private class IdResult { public int Id { get; set; } } // IQueryable BindingList public static BindingList<T> ToBindingList<T> (this IQueryable<T> source) where T : class { return (new ObservableCollection<T>(source)).ToBindingList(); } // public static int NextValueFor(this DbModel dbContext, string genName) { string sql = String.Format( "SELECT NEXT VALUE FOR {0} AS Id FROM RDB$DATABASE", genName); return dbContext.Database.SqlQuery<IdResult>(sql).First().Id; } // DbSet // public static void DetachAll<T>(this DbModel dbContext, DbSet<T> dbSet) where T : class { foreach (var obj in dbSet.Local.ToList()) { dbContext.Entry(obj).State = EntityState.Detached; } } // public static void Refresh(this DbModel dbContext, RefreshMode mode, IEnumerable collection) { var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext; objectContext.Refresh(mode, collection); } // public static void Refresh(this DbModel dbContext, RefreshMode mode, object entity) { var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext; objectContext.Refresh(mode, entity); } } In the same class there are some more extensions.
The NextValueFor method is designed to get the next value of the generator. The dbContext.Database.SqlQuery method allows you to execute SQL queries directly and display their results on some entity (projection). You can use it if you need to execute a SQL query directly.
The DetachAll method is designed to detach all objects of a DBSet collection from the context. This is necessary to update the internal cache. The point is that within the context, all recoverable are cached and not retrieved from the database again. However, this is not always useful, as it makes it difficult to obtain modified entries made in a different context.
| Comment In Web applications, the context usually lives for a very short time, and the new context does not have a full cache. |
The Refresh method is used to update the properties of an entity object. It is useful for updating properties of an object after editing or adding it.
So our data load code will look like this.
private void LoadCustomersData() { var dbContext = AppVariables.getDbContext(); // // // dbContext.DetachAll(dbContext.CUSTOMERS); var customers = from customer in dbContext.CUSTOMERS orderby customer.NAME select customer; bindingSource.DataSource = customers.ToBindingList(); } private void CustomerForm_Load(object sender, EventArgs e) { LoadCustomersData(); dataGridView.DataSource = bindingSource; dataGridView.Columns["INVOICES"].Visible = false; dataGridView.Columns["CUSTOMER_ID"].Visible = false; dataGridView.Columns["NAME"].HeaderText = "Name"; dataGridView.Columns["ADDRESS"].HeaderText = "Address"; dataGridView.Columns["ZIPCODE"].HeaderText = "ZipCode"; dataGridView.Columns["PHONE"].HeaderText = "Phone"; } The code of the event handler at the press of the add button is as follows.
private void btnAdd_Click(object sender, EventArgs e) { var dbContext = AppVariables.getDbContext(); // var customer = (CUSTOMER)bindingSource.AddNew(); // using (CustomerEditorForm editor = new CustomerEditorForm()) { editor.Text = " "; editor.Customer = customer; // editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) { if (editor.DialogResult == DialogResult.OK) { try { // // customer.CUSTOMER_ID = dbContext.NextValueFor("GEN_CUSTOMER_ID"); // dbContext.CUSTOMERS.Add(customer); // dbContext.SaveChanges(); // dbContext.Refresh(RefreshMode.StoreWins, customer); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); // fe.Cancel = true; } } else bindingSource.CancelEdit(); }; // editor.ShowDialog(this); } } When adding a new record, we get the value of the next identifier using the generator. We could not initialize the value of the identifier, and in this case the BEFORE INSERT would execute a trigger that would still pull the next value of the generator. However, in this case, we could not update the newly added record.
The code of the event handler for pressing the edit button is as follows.
private void btnEdit_Click(object sender, EventArgs e) { var dbContext = AppVariables.getDbContext(); // var customer = (CUSTOMER)bindingSource.Current; // using (CustomerEditorForm editor = new CustomerEditorForm()) { editor.Text = " "; editor.Customer = customer; // editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) { if (editor.DialogResult == DialogResult.OK) { try { // dbContext.SaveChanges(); dbContext.Refresh(RefreshMode.StoreWins, customer); // bindingSource.ResetCurrentItem(); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); // fe.Cancel = true; } } else bindingSource.CancelEdit(); }; // editor.ShowDialog(this); } } The form for editing the customer is as follows.
The data binding code is very simple.
public CUSTOMER Customer { get; set; } private void CustomerEditorForm_Load(object sender, EventArgs e) { edtName.DataBindings.Add("Text", this.Customer, "NAME"); edtAddress.DataBindings.Add("Text", this.Customer, "ADDRESS"); edtZipCode.DataBindings.Add("Text", this.Customer, "ZIPCODE"); edtPhone.DataBindings.Add("Text", this.Customer, "PHONE"); } The code of the event handler for pressing the delete button is as follows.
private void btnDelete_Click(object sender, EventArgs e) { var dbContext = AppVariables.getDbContext(); var result = MessageBox.Show(" ?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) { // var customer = (CUSTOMER)bindingSource.Current; try { dbContext.CUSTOMERS.Remove(customer); // dbContext.SaveChanges(); // bindingSource.RemoveCurrent(); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); } } } Magazines
«-». .
- – , (, , …), - , , .. : , — . , DataGridView, BindingSource
. , . – , . , , , AppVariables (. ), , , , . ( ). .
Since most often the last documents entered are required, it makes sense to sort them by date in the reverse order. We will retrieve the data, as in the case with directories using LINQ. Taking into account the above, the method for loading data of invoice caps will look as follows:
public void LoadInvoicesData() { var dbContext = AppVariables.getDbContext(); // LINQ SQL var invoices = from invoice in dbContext.INVOICES where (invoice.INVOICE_DATE >= AppVariables.StartDate) && (invoice.INVOICE_DATE <= AppVariables.FinishDate) orderby invoice.INVOICE_DATE descending select new InvoiceView { Id = invoice.INVOICE_ID, Cusomer_Id = invoice.CUSTOMER_ID, Customer = invoice.CUSTOMER.NAME, Date = invoice.INVOICE_DATE, Amount = invoice.TOTAL_SALE, Paid = (invoice.PAID == 1) ? "Yes" : "No" }; masterBinding.DataSource = invoices.ToBindingList(); } As a projection, we used not the anonymous type, but the class InvoiceView. This simplifies type casting. The definition of the InvoiceView class is as follows:
public class InvoiceView { public int Id { get; set; } public int Cusomer_Id { get; set; } public string Customer { get; set; } public DateTime? Date { get; set; } public decimal? Amount { get; set; } public string Paid { get; set; } public void Load(int Id) { var dbContext = AppVariables.getDbContext(); var invoices = from invoice in dbContext.INVOICES where invoice.INVOICE_ID == Id select new InvoiceView { Id = invoice.INVOICE_ID, Cusomer_Id = invoice.CUSTOMER_ID, Customer = invoice.CUSTOMER.NAME, Date = invoice.INVOICE_DATE, Amount = invoice.TOTAL_SALE, Paid = (invoice.PAID == 1) ? "Yes" : "No" }; InvoiceView invoiceView = invoices.ToList().First(); this.Id = invoiceView.Id; this.Cusomer_Id = invoiceView.Cusomer_Id; this.Customer = invoiceView.Customer; this.Date = invoiceView.Date; this.Amount = invoiceView.Amount; this.Paid = invoiceView.Paid; } } The Load method allows us to quickly update 1 added or updated record in the grid, rather than completely reloading all records.
The code of the event handler at the press of the add button is as follows.
private void btnAddInvoice_Click(object sender, EventArgs e) { var dbContext = AppVariables.getDbContext(); var invoice = dbContext.INVOICES.Create(); using (InvoiceEditorForm editor = new InvoiceEditorForm()) { editor.Text = " "; editor.Invoice = invoice; // editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) { if (editor.DialogResult == DialogResult.OK) { try { // invoice.INVOICE_ID = dbContext.NextValueFor("GEN_INVOICE_ID"); // dbContext.INVOICES.Add(invoice); // dbContext.SaveChanges(); // ((InvoiceView)masterBinding.AddNew()).Load(invoice.INVOICE_ID); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); // fe.Cancel = true; } } }; // editor.ShowDialog(this); } } dbContext.Refresh, Load InvoiceView. , dbContext.Refresh , , LINQ .
.
private void btnEditInvoice_Click(object sender, EventArgs e) { // var dbContext = AppVariables.getDbContext(); // var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id); if (invoice.PAID == 1) { MessageBox.Show(" , .", ""); return; } using (InvoiceEditorForm editor = new InvoiceEditorForm()) { editor.Text = "Edit invoice"; editor.Invoice = invoice; // editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) { if (editor.DialogResult == DialogResult.OK) { try { // dbContext.SaveChanges(); // CurrentInvoice.Load(invoice.INVOICE_ID); masterBinding.ResetCurrentItem(); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); // fe.Cancel = true; } } }; // editor.ShowDialog(this); } } . CurrentInvoice -. :
public InvoiceView CurrentInvoice { get { return (InvoiceView)masterBinding.Current; } } .
, - , :
private void btnInvoicePay_Click(object sender, EventArgs e) { var dbContext = AppVariables.getDbContext(); var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id); try { if (invoice.PAID == 1) throw new Exception(" , ."); invoice.PAID = 1; // dbContext.SaveChanges(); // CurrentInvoice.Load(invoice.INVOICE_ID); masterBinding.ResetCurrentItem(); } catch (Exception ex) { // MessageBox.Show(ex.Message, ""); } } - :
- - INVOICE_LINE ( LINQ) .
- - LINQ , .
.
, - - . SQL , , . WEB .
, - , SQL ( ).
. BindingSource.
private void masterBinding_CurrentChanged(object sender, EventArgs e) { LoadInvoiceLineData(this.CurrentInvoice.Id); detailGridView.DataSource = detailBinding; } - :
private void LoadInvoiceLineData(int? id) { var dbContext = AppVariables.getDbContext(); var lines = from line in dbContext.INVOICE_LINES where line.INVOICE_ID == id select new InvoiceLineView { Id = line.INVOICE_LINE_ID, Invoice_Id = line.INVOICE_ID, Product_Id = line.PRODUCT_ID, Product = line.PRODUCT.NAME, Quantity = line.QUANTITY, Price = line.SALE_PRICE, Total = Math.Round(line.QUANTITY * line.SALE_PRICE, 2) }; detailBinding.DataSource = lines.ToBindingList(); } InvoiceLineView.
public class InvoiceLineView { public int Id { get; set; } public int Invoice_Id { get; set; } public int Product_Id { get; set; } public string Product { get; set; } public decimal Quantity { get; set; } public decimal Price { get; set; } public decimal Total { get; set; } } , InvoiceView . , , .
.
public InvoiceLineView CurrentInvoiceLine { get { return (InvoiceLineView)detailBinding.Current; } } In the methods for adding, editing, and deleting, we show how to work with stored procedures in the Entity Framework. For example, the method for adding a new entry looks like this:
private void btnAddInvoiceLine_Click(object sender, EventArgs e) { var dbContext = AppVariables.getDbContext(); // - var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id); // - if (invoice.PAID == 1) { MessageBox.Show(" , - .", "Error"); return; } // - var invoiceLine = dbContext.INVOICE_LINES.Create(); invoiceLine.INVOICE_ID = invoice.INVOICE_ID; // using (InvoiceLineEditorForm editor = new InvoiceLineEditorForm()) { editor.Text = "Add invoice line"; editor.InvoiceLine = invoiceLine; // editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) { if (editor.DialogResult == DialogResult.OK) { try { // var invoiceIdParam = new FbParameter("INVOICE_ID", FbDbType.Integer); var productIdParam = new FbParameter("PRODUCT_ID", FbDbType.Integer); var quantityParam = new FbParameter("QUANTITY", FbDbType.Integer); // invoiceIdParam.Value = invoiceLine.INVOICE_ID; productIdParam.Value = invoiceLine.PRODUCT_ID; quantityParam.Value = invoiceLine.QUANTITY; // dbContext.Database.ExecuteSqlCommand( "EXECUTE PROCEDURE SP_ADD_INVOICE_LINE(@INVOICE_ID, @PRODUCT_ID, @QUANTITY)", invoiceIdParam, productIdParam, quantityParam); // // - CurrentInvoice.Load(invoice.INVOICE_ID); // LoadInvoiceLineData(invoice.INVOICE_ID); // masterBinding.ResetCurrentItem(); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); // fe.Cancel = true; } } }; // editor.ShowDialog(this); } } Here updating the master master record is required because one of its fields (TotalSale) contains aggregated information by document lines.
The method for updating the record is implemented as follows.
private void btnEditInvoiceLine_Click(object sender, EventArgs e) { var dbContext = AppVariables.getDbContext(); // - var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id); // - if (invoice.PAID == 1) { MessageBox.Show(" , .", "Error"); return; } // - var invoiceLine = invoice.INVOICE_LINES .Where(p => p.INVOICE_LINE_ID == this.CurrentInvoiceLine.Id) .First(); // using (InvoiceLineEditorForm editor = new InvoiceLineEditorForm()) { editor.Text = "Edit invoice line"; editor.InvoiceLine = invoiceLine; // editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) { if (editor.DialogResult == DialogResult.OK) { try { // var idParam = new FbParameter("INVOICE_LINE_ID", FbDbType.Integer); var quantityParam = new FbParameter("QUANTITY", FbDbType.Integer); // idParam.Value = invoiceLine.INVOICE_LINE_ID; quantityParam.Value = invoiceLine.QUANTITY; // dbContext.Database.ExecuteSqlCommand( "EXECUTE PROCEDURE SP_EDIT_INVOICE_LINE(@INVOICE_LINE_ID, @QUANTITY)", idParam, quantityParam); // // - CurrentInvoice.Load(invoice.INVOICE_ID); // LoadInvoiceLineData(invoice.INVOICE_ID); // masterBinding.ResetCurrentItem(); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); // fe.Cancel = true; } } }; // editor.ShowDialog(this); } } The method for deleting a record is implemented as follows.
private void btnDeleteInvoiceLine_Click(object sender, EventArgs e) { var result = MessageBox.Show(" -?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) { var dbContext = AppVariables.getDbContext(); // - var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id); try { // - if (invoice.PAID == 1) throw new Exception(" , - ."); // var idParam = new FbParameter("INVOICE_LINE_ID", FbDbType.Integer); // idParam.Value = this.CurrentInvoiceLine.Id; // dbContext.Database.ExecuteSqlCommand( "EXECUTE PROCEDURE SP_DELETE_INVOICE_LINE(@INVOICE_LINE_ID)", idParam); // // - CurrentInvoice.Load(invoice.INVOICE_ID); // LoadInvoiceLineData(invoice.INVOICE_ID); // masterBinding.ResetCurrentItem(); } catch (Exception ex) { // MessageBox.Show(ex.Message, "Error"); } } } In the methods for adding and editing invoice items, we used the form for editing.
TextBox. , , . , . :
public partial class InvoiceLineEditorForm : Form { public InvoiceLineEditorForm() { InitializeComponent(); } public INVOICE_LINE InvoiceLine { get; set; } private void InvoiceLineEditorForm_Load(object sender, EventArgs e) { if (this.InvoiceLine.PRODUCT != null) { edtProduct.Text = this.InvoiceLine.PRODUCT.NAME; edtPrice.Text = this.InvoiceLine.PRODUCT.PRICE.ToString("F2"); btnChooseProduct.Click -= this.btnChooseProduct_Click; } if (this.InvoiceLine.QUANTITY == 0) this.InvoiceLine.QUANTITY = 1; edtQuantity.DataBindings.Add("Value", this.InvoiceLine, "QUANTITY"); } private void btnChooseProduct_Click(object sender, EventArgs e) { GoodsForm goodsForm = new GoodsForm(); if (goodsForm.ShowDialog() == DialogResult.OK) { InvoiceLine.PRODUCT_ID = goodsForm.CurrentProduct.Id; edtProduct.Text = goodsForm.CurrentProduct.Name; edtPrice.Text = goodsForm.CurrentProduct.Price.ToString("F2"); } } } , , SaveChanges(), Entity Framework . , . EF . . , . :
var dbContext = AppVariables.getDbContext(); foreach (DataGridViewRow gridRows in dataGridView.SelectedRows) { int id = (int)gridRows.Cells["Id"].Value; // var product = dbContext.PRODUCTS.Find(id); // 10% decimal discount = 10.0m; product.PRICE = product.PRICE * (100 - discount) /100; } // // dbContext.SaveChanges(); , 10 . 10 . , . , :
var dbContext = AppVariables.getDbContext(); // using (var dbTransaction = dbContext.Database.BeginTransaction()) { string sql = "UPDATE PRODUCT " + "SET PRICE = PRICE * ROUND((100 - @DISCOUNT)/100, 2) " + "WHERE PRODUCT_ID = @PRODUCT_ID"; try { // var idParam = new FbParameter("PRODUCT_ID", FbDbType.Integer); var discountParam = new FbParameter("DISCOUNT", FbDbType.Decimal); // SQL var sqlCommand = dbContext.Database.Connection.CreateCommand(); sqlCommand.CommandText = sql; // , sqlCommand.Transaction = dbTransaction.UnderlyingTransaction; sqlCommand.Parameters.Add(discountParam); sqlCommand.Parameters.Add(idParam); // sqlCommand.Prepare(); // foreach (DataGridViewRow gridRows in dataGridView.SelectedRows) { int id = (int)gridRows.Cells["Id"].Value; // idParam.Value = id; discountParam.Value = 10.0m; // 10% // sql sqlCommand.ExecuteNonQuery(); } dbTransaction.Commit(); } catch (Exception ex) { dbTransaction.Rollback(); MessageBox.Show(ex.Message, "error"); } } In this case, we started the transaction with the default parameters. In order to set your transaction parameters you must use the UseTransaction method.
private void btnDiscount_Click(object sender, EventArgs e) { DiscountEditorForm editor = new DiscountEditorForm(); editor.Text = "Enter discount"; if (editor.ShowDialog() != DialogResult.OK) return; bool needUpdate = false; var dbContext = AppVariables.getDbContext(); var connection = dbContext.Database.Connection; // using (var dbTransaction = connection.BeginTransaction(IsolationLevel.Snapshot)) { dbContext.Database.UseTransaction(dbTransaction); string sql = "UPDATE PRODUCT " + "SET PRICE = ROUND(PRICE * (100 - @DISCOUNT)/100, 2) " + "WHERE PRODUCT_ID = @PRODUCT_ID"; try { // var idParam = new FbParameter("PRODUCT_ID", FbDbType.Integer); var discountParam = new FbParameter("DISCOUNT", FbDbType.Decimal); // SQL var sqlCommand = connection.CreateCommand(); sqlCommand.CommandText = sql; // sqlCommand.Transaction = dbTransaction; sqlCommand.Parameters.Add(discountParam); sqlCommand.Parameters.Add(idParam); // sqlCommand.Prepare(); // foreach (DataGridViewRow gridRows in dataGridView.SelectedRows) { int id = (int)gridRows.Cells["PRODUCT_ID"].Value; // idParam.Value = id; discountParam.Value = editor.Discount; // sql needUpdate = (sqlCommand.ExecuteNonQuery() > 0) || needUpdate; } dbTransaction.Commit(); } catch (Exception ex) { dbTransaction.Rollback(); MessageBox.Show(ex.Message, "error"); needUpdate = false; } } // <a href="http://ib-aid.com/download/docs/NET_DB.zip"></a> if (needUpdate) { // foreach (DataGridViewRow gridRows in dataGridView.SelectedRows) { var product = (PRODUCT)bindingSource.List[gridRows.Index]; dbContext.Refresh(RefreshMode.StoreWins, product); } bindingSource.ResetBindings(false); } } Here you go.Now we have only one transaction for the entire set of updates, and there are no extra commands to search for data. It remains only to add a dialog to enter the discount value and update the data in the grid. Try it yourself.
I hope this article has helped you understand the features of writing a C # application using the Entity Framework when working with Firebird.
Links
Source codes of the sample application
Database and scripts for its creation
Source: https://habr.com/ru/post/278405/
All Articles