M# Tutorials

Learn to build .NET applications with M#. Our step-by-step tutorials will get you up-to-speed rapidly.
If you already know ASP.NET and C#, you can master M# in a week or two.

DataAccessor

In this lesson we will learn how to perform manual operations in the database by writing a custom SQL query or stored procedure.
In the majority of your applications you will not have to use this, but in some specific cases where the construction of the SQL query is important and performance is needed DataAccessor provides the functionality .
Keep in mind that by using this class you bypass the cache system used by M#, which means that if your query changed any data, you will have to refresh the cache by calling the method

Database.Refresh()


Moreover, if you update data the standard M# data events like Validate() and OnSaving() will not be fired, be careful when using the following features.

ExecuteNonQuery

ExecuteNonQuery executes a Transact-SQL statement against the connection and returns the number of rows affected.

Overload 1: SQL query

This overload allows you to run your own SQL query.

The following example shows how to update records with a manually written SQL query.

result

will be equal to the number of employees who was not activated and with a date in the past.
Our code updates records in the database so we have to refresh the cache after executing the query.

Overload 2: Stored procedure

This overload allows you to run an existing stored procedure.

The following example shows how to update records using a SQL stored procedure. Let's create our stored procedure:

Now we can use ExecuteNonQuery method to run this procedure, we pass the name of the procedure, the type and parameters if needed.

This operation will produce the exactly same result as the first overload.

ExecuteReader

ExecuteReader executes the specified command text against the database connection of the context and builds an IDataReader.
Important: Make sure you close the data reader after finishing the required logic.

In the next code sample we are showing all employees and a text to show if they are activated or not.

First we create our query and execute it with

DataAccessor.ExecuteReader()

. Second we fetch all records returned by the function and with the help of the Read() method, cast each column, and then return each result with the yield keyword. To finish we close the data reader with Close().
Note: In this scenario we do not need to refresh the cache because we did not update data, just read.

ExecuteScalar

ExecuteScalar executes the specified command text against the database connection of the context, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

In the next example we return the number of activated employees.

ReadData

ReadData executes a database query and returns the result as a data set.


The following example returns the results in a DataSet.