by Joche Ojeda | May 24, 2023 | XPO
.NET communicates with a database using ADO.NET, which provides a set of classes and interfaces for accessing and manipulating data stored in a database.
ADO.NET supports various database systems, including relational databases and XML databases.
ADO.NET uses the following protocols to communicate with a database:
1. OLE DB (Object Linking and Embedding, Database) – This is a low-level, component-based API that provides a generic interface for accessing various types of data sources, including relational databases, spreadsheet data, and more.
2. ODBC (Open Database Connectivity) – This is a widely used API for accessing relational databases. It provides a standardized interface for accessing data, regardless of the underlying database management system.
3. ADO.NET Provider – This is a high-level, .NET-based API for accessing data stored in a specific database management system, such as Microsoft SQL Server, Oracle, or MySQL.
The choice of protocol used by ADO.NET to communicate with a database depends on the specific requirements of the application and the database management system being used.
The ADO.NET provider approach is the most common and provides a flexible and scalable solution for accessing and manipulating data stored in a database, supporting a wide range of database systems and communication protocols.
ADO.NET Main Components
The main classes in ADO.NET include:
1. Connection – Represents a connection to a database, providing methods for opening and closing a connection and for executing commands against the database.
2. Command – Represents a database command, such as a SELECT, INSERT, UPDATE, or DELETE statement, and provides methods for executing the command and for retrieving the results of the command.
3. DataReader – Provides a forward-only, read-only view of the results of a command. The DataReader is optimized for retrieving large amounts of data from a database, as it retrieves data in a streaming manner, rather than retrieving all data into memory at once.
4. DataAdapter – Represents a set of data commands and a database connection that are used to fill a DataSet and to resolve changes made to the data back to the database. The DataAdapter is often used in combination with a DataSet to provide a flexible and scalable way to access and manipulate data stored in a database.
5. DataSet – Represents an in-memory cache of data, providing a disconnected view of data that can be used to work with data independently of a database connection. The DataSet provides a rich set of features for manipulating and querying data, including support for relationships between tables, constraints, and transactions.
6. Parameter – Represents a parameter for a database command, providing a way to specify input values for a command, such as the values for parameters in a stored procedure.
These are the main classes in ADO.NET, and they provide a comprehensive and flexible set of tools for accessing and manipulating data stored in a database.
Design problems in ADO.NET 1
ADO.NET 1 refers to the first version of ADO.NET, which was introduced in .NET Framework 1.0, released in 2002. In this version of ADO.NET, data access was performed using direct methods and properties of the various ADO.NET classes, such as SqlConnection and SqlCommand for SQL Server.
The main design problem for ADO.NET 1 is that it requires developers to write database-specific code for each database management system that they wanted to access.
ADO.NET 2 and the DbProviderFactory
With the introduction of .NET Framework 2.0 in 2005, the ADO.NET DbProviderFactory was introduced.
The DbProviderFactory abstract class provides a set of methods for creating database-specific implementations of various ADO.NET classes, such as DbConnection, DbCommand, and DbDataAdapter, and makes it easier for developers to write database-agnostic code.
ADO.NET DbProviderFactory is a factory pattern that provides a standard way to create instances of database-specific classes, such as connection and command classes, in ADO.NET.
The DbProviderFactory is used by the ADO.NET data providers, such as the SqlClient data provider for SQL Server, the OleDb data provider for OLE DB data sources, and the ODBC data provider for ODBC data sources, to provide a common way to create instances of the classes that they implement. This makes it easier for developers to switch between different data providers and to write database-agnostic code that can work with different databases without modification.
The introduction of DbProviderFactory in ADO.NET was an exceptional milestone that revolutionized database access in .NET. By providing a standardized interface for creating database-specific connection objects, DbProviderFactory enhanced the flexibility and portability of data access code. Its ingenious design allowed developers to write data access logic without being tightly coupled to a specific database provider, thereby promoting code reusability and adaptability. This breakthrough was the steppingstone to the emergence and widespread adoption of Object-Relational Mapping Systems (ORMs).
ORMs leveraged DbProviderFactory’s capabilities to abstract the complexities of database interactions and map database entities to object-oriented representations seamlessly. As a result, developers could focus more on business logic and application development rather than dealing with low-level data access intricacies.
The symbiotic relationship between DbProviderFactory and ORMs continues to shape modern software development, empowering developers with powerful tools to efficiently manage and manipulate data in a database-agnostic manner.
Until next time ))
We are excited to announce that we are currently in the process of writing a comprehensive book about DevExpress XPO. As we work on this project, we believe it is essential to involve our readers and gather their valuable feedback. Therefore, we have decided to share articles from the book as we complete them, giving you an opportunity to provide input and suggestions that we can consider for inclusion in the final release. Keep in mind that the content presented is subject to change. We greatly appreciate your participation in this collaborative effort.
Related Articles
Relational database systems: the holy grail of data
by Joche Ojeda | May 23, 2023 | XPO
RDBMS stands for Relational Database Management System. It is a type of database management system that is based on the relational model, which organizes data into tables with rows and columns, and uses relationships between tables to link data together.
In an RDBMS, data is stored in tables, with each table representing a specific type of data (such as customers, orders, or products). Each table has columns that represent the attributes of the data, and rows that represent individual instances of the data. Relationships between tables can be established using keys, allowing data from multiple tables to be linked and retrieved in a single query.
RDBMSs provide a variety of features and tools for managing, querying, and manipulating data stored in the database, including data validation, constraint enforcement, transaction management, backup and recovery, reporting and analysis capabilities. They are widely used in enterprise applications, web applications, and other systems that require the management of large amounts of structured data.
Examples of popular RDBMSs include Oracle, Microsoft SQL Server, MySQL, and PostgreSQL.
A Relational Database Management System (RDBMS) uses Data Manipulation Language (DML) and Data Definition Language (DDL) queries to interact with the data stored in the database. The specific syntax of the queries will vary depending on the database system being used.
DML queries are used to retrieve, insert, update, and delete data in the database. The most used DML queries are:
- SELECT: Retrieves data from one or more tables in the database.
- INSERT: Adds a new row of data to a table in the database.
- UPDATE: Modifies existing data in a table in the database.
- DELETE: Deletes data from a table in the database.
DDL queries are used to create, modify, and delete database structures, such as tables, indexes, and constraints. The most used DDL queries are:
- CREATE: Creates a new database object, such as a table or index.
- ALTER: Modifies the structure of an existing database object.
- DROP: Deletes a database object.
For example, the following is a DDL query to create a table named customers:
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
email VARCHAR(255),
address VARCHAR(255)
);
And the following is a DML query to insert a new customer into the customers table:
INSERT INTO customers (name, email, address)
VALUES ('John Doe', 'johndoe@example.com', '123 Main St');
In the next article we will be talking about the origin of data access in net framework a.k.a ADO.NET
See you then )))
We are excited to announce that we are currently in the process of writing a comprehensive book about DevExpress XPO. As we work on this project, we believe it is essential to involve our readers and gather their valuable feedback. Therefore, we have decided to share articles from the book as we complete them, giving you an opportunity to provide input and suggestions that we can consider for inclusion in the final release. Keep in mind that the content presented is subject to change. We greatly appreciate your participation in this collaborative effort.
by Joche Ojeda | May 17, 2023 | AlchemyDotNet, C#
Alright, it’s time to start writing some code, but first, let’s decide how this project will be organized.
So far, the repository structure that I’ve found most appealing is the one I used for the SyncFramework (https://github.com/egarim/SyncFramework). Here is a representation with bullet points:
- Repo Folder: This is the parent folder that will contain all the code from our project. o Git Files: These are Ignore and attributes files.
- Clean.bat: This is a batch file that deletes all child BIN and OBJ folders to ensure our repository does not contain binary files (sometimes the ‘clean’ command from Visual Studio does not clear the outputs completely).
- CHANGES.MD: This is a Github markdown file that contains the history of changes in the code.
- README.MD: This is the landing page of the current repository where we can write some basic instructions and other important information about the project.
- src (folder): This is where the actual code resides.
Before I conclude this post, I want to discuss versioning. The main idea here is this: the project AlchemyDotNet.Core will start at version 1.0.0, and the version will change when there is a fix or an update to the core specification. We will only move to a new version like 2.0.0 when the specification introduces breaking changes. This means that we will be on version 1 for a very long time.
Link to the repo
https://github.com/egarim/Alchemy.Net
Previous posts
Alchemy Framework: 1 – Creating a framework for import data
by Joche Ojeda | May 15, 2023 | Uncategorized
Last Friday, I received a message from a dear friend and colleague, Pedro Hernandez. He asked me if I had the latest compiled version of the XPO import framework we created in our office. As it turned out, I did not have it readily available and had to search extensively for it.
While conducting this search across my computers, repositories, and virtual machines, I was inspired to create another import framework — yes, another piece of code to maintain.
Most of the time, my research projects begin in the same manner, typically after a conversation with my close friend Jose Javier Columbie. As always, he would say something like this: “Jose, do you think this can be possible? Why don’t you give it a try? If we succeed, that will be el batazo (like hitting a home run).”
In this specific case, that conversation didn’t happen. However, I could hear his words echoing in my mind.
Furthermore, I want this project to be community-driven, not just a technical experiment I have to maintain alone. I truly believe in the power of a community.
First Step
So, let’s begin. I’ll start with my favorite part: naming the project. I spent all weekend pondering this, attempting to condense the concept and associate it with a literary term or a Latin word (these are my preferred methods for naming a project).
Let’s define what the ultimate goal is. In an import process, the aim is to take information from a source ‘A’, translate or transform the information, and then store it in a target ‘B’.
Growing up, I was an avid reader – and I mean, I read a lot. (Now, I’ve switched to audiobooks.) Therefore, after defining what this project is about, naming it became incredibly easy.
noun
noun: alchemy
- the medieval forerunner of chemistry, concerned with the transmutation of matter, in particular with attempts to convert base metals into gold or find a universal elixir.
“occult sciences, such as alchemy and astrology”
Origin
late Middle English: via Old French and medieval Latin from Arabic al-kīmiyā’, from al ‘the’ + kīmiyā’ (from Greek khēmia, khēmeia ‘art of transmuting metals’).
The alchemy Framework
Alchemy is a framework created for DotNet, designed to import data from a data source to a data target. These sources and targets can be anything from a text file, CSV file, a database, ORMs, and so on.
The framework consists of a set of contracts, interfaces, and base classes. When implemented, these allow you to import and transform data between various sources and targets.
The requirement in a few words
As stated in the framework’s description, the requirement is only to define the contracts that represent the sources and targets, as well as a job configuration that describes how the information flows from one source to a target. The concrete implementations are not important at this point and should be discussed individually for each case.
The design patterns.
For this project, we will use the SOLID design principles and dependency injection. This will enable us to easily replace small functionalities, allowing us to mix and match different implementations depending on the data source and data target.
by Joche Ojeda | May 5, 2023 | Application Framework, XAF, XPO, XPO Database Replication
I will explain what XAF is just for the sake of the consistency of this article, XAF is a low code application framework for line of business applications that runs on NET framework (windows forms and web forms) and in dotnet (windows forms, Blazor and Web API)
XAF is laser focus on productivity, DevExpress team has created several modules that encapsulate design patterns and common tasks needed on L.O.B apps.
The starting point in XAF is to provide a domain model using an ORMs like XPO or Entity framework and then XAF will create an application for you using the target platform of choice.
It’s a common misunderstanding that you need to use and ORM in order to provide a domain model to XAF
DevExpress team has created ObjectSpace abstraction so XAF can be extended to use different data access technologies ( you can read more about it here https://docs.devexpress.com/eXpressAppFramework/DevExpress.ExpressApp.BaseObjectSpace)
Out of the box XAF provide 3 branches of object spaces as show is the graph below.
XPObjectSpace: this is the object space that allows you to use XPO as a data access technology.
EfCoreObjectSpace: this is the object space that allows you to use Microsoft Entity Framework as a data access technology.
NonPersistenObjectSpace: this object space is interesting as it provides the domain model needed for XAF to generate the views and interact with the data is not attached to an ORM technology so it’s up to us to provide the data, also this type of object space can be used in combination with XPObjectSpace and EfCoreObjectSpace
When querying external data sources, you also need to solve the problem of filtering and sorting data in order to provide a full solution, for that reason DevExpress team provide us with the DynamicCollection class, that is a proxy collection that allows you to filter and sort an original collection without changing it.
Now that we know the parts involved in presenting data in a XAF application, we can define the required flow.
[DefaultClassOptions]
[DefaultProperty(nameof(Article.Title))]
[DevExpress.ExpressApp.ConditionalAppearance.Appearance("", Enabled = false, TargetItems = "*")]
[DevExpress.ExpressApp.DC.DomainComponent]
public class Article : NonPersistentObjectBase {
internal Article() { }
public override void Setup(XafApplication application) {
base.Setup(application);
// Manage various aspects of the application UI and behavior at the module level.
application.SetupComplete += Application_SetupComplete;
}
- Wire the application object space created event.
private void Application_SetupComplete(object sender, EventArgs e) {
Application.ObjectSpaceCreated += Application_ObjectSpaceCreated;
}
private void Application_ObjectSpaceCreated(object sender, ObjectSpaceCreatedEventArgs e) {
var npos = e.ObjectSpace as NonPersistentObjectSpace;
if (npos != null) {
new ArticleAdapter(npos);
new ContactAdapter(npos);
}
}
public ArticleAdapter(NonPersistentObjectSpace npos) {
this.objectSpace = npos;
objectSpace.ObjectsGetting += ObjectSpace_ObjectsGetting;
}
private void ObjectSpace_ObjectsGetting(object sender, ObjectsGettingEventArgs e) {
if(e.ObjectType == typeof(Article)) {
var collection = new DynamicCollection(objectSpace, e.ObjectType, e.Criteria, e.Sorting, e.InTransaction);
collection.FetchObjects += DynamicCollection_FetchObjects;
e.Objects = collection;
}
}
private void DynamicCollection_FetchObjects(object sender, FetchObjectsEventArgs e) {
if(e.ObjectType == typeof(Article)) {
e.Objects = articles;
e.ShapeData = true;
}
}
Full source code here
In conclusion the ObjectSpace abstraction ensures that different data access technologies can be employed, while the DynamicCollection class allows for seamless filtering and sorting of data from external sources. By following the outlined steps, developers can create robust, adaptable, and efficient applications with XAF, ultimately saving time and effort while maximizing application performance.
