In-Memory Databases

.In-Memory Databases

In-Memory Databases

Web page by Kevin Harris of Homer IL

Please contact Kevin Harris of Homer IL concerning this web site

"A DataAdapter retrieves data and populates in-memory representations of tables within a DataSet. The DataAdapter can also resolve data changes back to the source. Using DataSets allows the data to be used while the data locks have been released and server resources freed. DataSets are frequently used to bind data to controls. DataTables within a DataSet can contain table relations and data constraints reflecting those of a source relational database. "



IMDB

Traditional databases are hard-wired to access data from disk storage. Accessing data from memory is an order of magnitude faster than accessing data from disk. Additional benefits of In-Memory Data Bases (IMDB) include higher concurrency through the release of database locks. This can solve a host of problems associated with distributed database applications. Working with in-memory representations of database tables can remove the network latency required by round-trips to the database. It also removes the need to keep database connections open for long periods of time, thereby locking crucial data. Performance and scalability are the benefits typically associated with IMDB, however I would also add convenience and decreased development time. Web Forms contains several controls which bind to in-memory DataTables. The Visual Studio debugger contains a viewer which displays result sets residing in DataTables, so you can see the results of your SQL selects during the debugging process.


Microsoft IMDB

In 1999, at its annual TechEd conference, Microsoft revealed a COM+ IMDB as part of the Windows 2000 Beta. However some programmers were disappointed when Microsoft decided not to release IMDB with its final release of Windows 2000. Unknown to the general public, was the fact that Microsoft was working on a more powerful and slightly different version of IMDB to be included in the Next Generation of Windows Services (what is now known as .NET). While IMDB did not make it into ADO, it is a powerful feature of ADO.NET which Microsoft refers to as its Disconnected Data Model.


ADO.NET Data Providers

ADO.NET uses a Data Provider model which supports a common interface for interacting with data stores. ADO.NET includes the classes for direct access to Microsoft SQL Server and indirect access (ODBC, OLE DB) for other database systems. The direct access to other database systems is provided by third-party providers which adhere to the common interface classes. Each database vendor is associated with a particular implementation of the IDbProvider interface. For example ADO.NET contains the SqlConnection class for opening a connection to a SQL Server database, while MySQL provides the corresponding MySqlConnection class for connecting to a MySQL database. All the major database manufacturers provide support or ADO.NET. A list of third-party ADO.NET data providers can be seen on the ADO.NET Data Providers web page.

"Data processing has traditionally relied primarily on a connection-based, two-tier model. As data processing increasingly uses multi-tier architectures, programmers are switching to a disconnected approach to provide better scalability for their applications."


Connect and Disconnected Data Models

ADO.NET supports different ways for accessing data which corresponds to their Connected Model and Disconnected Model architecures. The connected model includes the traditional connection and command objects as well as a DataReader that provides fast read-only data access. The Disconnected Model centers around the DataSet class which is optimized for moving disconnected set of data across the Internet, and across intranets, including through firewalls. The DataAdapter class connects the data source with classes in the Disconnected Model, with the help of some classes from the Connected Model. Some of the classes in the two models include:

  1. Connected Model
    • Connection - provides the means to connect to a database. It manages transactions and connection pooling. Its ConnectionString propertycontains key-value pairs which provides the Connection class with information about connecting to a database.

    • Command - provides methods for storing and executing SQL statements and stored procedures. The Command class contains the three following methods:
      • ExecuteReader - returns data to the client as rows.
      • ExecuteNonQuery - executes a command that changes the data in the database, such as an update, delete, insert statement, or a stored procedure.
      • ExecuteScalar - returns a single value.
    • DataReader - provides fast forward-only, read-only stream of data, similar to a forward-only cursor. The Read method is used to load a row at a time of data into a buffer.

    • Transaction - a key feature offered by most databases for ensuring data integrity when related data modifications are made across multiple tables. Allows all updates to a database to be treated as a unit of work and either committed or rolled back in its entirety. Instead of tying transactions to a connection, ADO.NET associates a transaction with a number of commands which allows for finer control. See Transactions article for more information.

    • ParameterCollection - Stores all parameters related to a Command and the mappings for both table and column names to the DataSet columns.

    • Parameter - Defines parameters for parameterized SQL statements and stored procedures. Parameter names are not case sensitive.

  1. Disonnected Model
    • DataSet - is an in-memory representation of data. It can contain multiple DataTable objects which contain DataColumn and DataRow objects. It can contains relationships between tables and data constraints just like normal database tables.

    • DataTable - represents one table of in-memory data. When accessing DataTable objects, note that they are conditionally case sensitive. For example, if one DataTable is named "mydatatable" and another is named "Mydatatable", a string used to search for one of the tables is regarded as case sensitive. However, if "mydatatable" exists and "Mydatatable" does not, the search string is regarded as case insensitive.

    • DataColumn - represents the schema of a column in a DataTable. DataColumn is the fundamental building block for creating the schema of a DataTable. You build the schema by adding one or more DataColumn objects to the DataColumnCollection. Each DataColumn has a DataType property that determines the kind of data the DataColumn contains. Properties such as AllowDBNull, Unique, and ReadOnly put restrictions on the entry and updating of data, thereby helping to guarantee data integrity. You can also use the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties to control automatic data generation.

    • DataRow - represents a row of data in a DataTable. To create a new DataRow, use the NewRow method of the DataTable object. After creating a new DataRow, use the Add method to add the new DataRow to the DataRowCollection. Finally, call the AcceptChanges method of the DataTable object to confirm the addition. For more information about adding data to a DataTable, see Adding Data to a DataTable.
      You can delete a DataRow from the DataRowCollection by calling the Remove method of the DataRowCollection, or by calling the Delete method of the DataRow object. The Remove method removes the row from the collection. In contrast, Delete marks the DataRow for removal. The actual removal occurs when you call AcceptChanges method. By calling Delete, you can programmatically check which rows are marked for removal before actually deleting them.

    • DataRelation - represents a parent/child relationship between two DataTable objects. Relationships are created between matching columns in the parent and child tables. That is, the DataType value for both columns must be identical.
      Relationships can also cascade various changes from the parent DataRow to its child rows. To control how values are changed in child rows, add a ForeignKeyConstraint to the ConstraintCollection of the DataTable object. The ConstraintCollection determines what action to take when a value in a parent table is deleted or updated.
      When a DataRelation is created, it first verifies that the relationship can be established. After it is added to the DataRelationCollection, the relationship is maintained by disallowing any changes that would invalidate it

    • DataView - represents a databindable, customized view of a DataTable for sorting, filtering, searching, editing, and navigation. The DataView does not store data, but instead represents a connected view of its corresponding DataTable. Changes to the DataView’s data will affect the DataTable. Changes to the DataTable’s data will affect all DataViews associated with it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ConsoleApplication1
{
    class Program
    {
        private static string conString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        static void Main(string[] args)
        {
            string myString = "Kevin";

            // Get data from the table.
            DataSet ds = new DataSet();
            string sql = string.Format("SELECT * FROM table1 WHERE keyfield = '{0}'", myString);

            using (SqlConnection conn = new SqlConnection(conString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        da.Fill(ds, "MyTable");
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            // Get result fields.            
            DataRow dr = ds.Tables["MyTable"].Rows[0];
            string value1 = dr["field1"].ToString();
            string value2 = dr["field2"].ToString();
        }
    }
}


Retrieve Data from Disconnected DataRow