AdoDotNet.Query

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function AdoDotNet.Query

The AdoDotNet.Query function is one of the many M functions available in Power Query. This function allows users to execute SQL queries against a variety of database systems, including Microsoft SQL Server, Oracle, and MySQL. In this article, we will explore the M code behind the AdoDotNet.Query function and how it can be used to access and manipulate data in a database.

Understanding AdoDotNet.Query

Before delving into the M code behind AdoDotNet.Query, it is helpful to understand the function itself. AdoDotNet.Query is a function that takes three arguments: connectionString, sqlStatement, and options. The connectionString argument is a string that defines the connection information for the database. The sqlStatement argument is a string that contains the SQL query to be executed. The options argument is an optional record that contains additional parameters for the query, such as the timeout value or the type of command to be executed.

When executed, the AdoDotNet.Query function returns a table that contains the results of the SQL query. This table can then be transformed and loaded into a variety of destinations using other Power Query functions.

The M Code Behind AdoDotNet.Query

The M code behind the AdoDotNet.Query function is relatively straightforward. At its core, the function is simply a wrapper around the .NET System.Data.SqlClient.SqlCommand class, which is used to execute SQL queries against a Microsoft SQL Server database.

Here is the M code for the AdoDotNet.Query function:

let AdoDotNet.Query = (connectionString as text, sqlStatement as text, optional options as record) =>

let

conn = AdoDotNetProvider.GetConnection(connectionString),

command = conn.CreateCommand(),

commandTimeout = if options <> null and options[CommandTimeout] <> null then options[CommandTimeout] else null,

commandType = if options <> null and options[CommandType] <> null then options[CommandType] else Sql.CommandType.Text,

resultTable = Table.FromRows(Sql.DataReader(command.ExecuteReader(CommandBehavior.CloseConnection), commandTimeout), command.ExecuteReader().GetSchemaTable())

in

resultTable

Let’s break down this code line by line:

– The first line defines the name of the function and its three arguments: connectionString, sqlStatement, and options.

– The second line creates a connection object using the AdoDotNetProvider.GetConnection function, which is a built-in Power Query function that returns a .NET System.Data.SqlClient.SqlConnection object.

– The third line creates a command object using the CreateCommand method of the connection object.

– The fourth and fifth lines check the options parameter to see if a command timeout or command type has been specified. If so, these values are used in the command object.

– The sixth line executes the SQL query using the ExecuteReader method of the command object and returns the results as a table using the Table.FromRows function.

Using AdoDotNet.Query in Power Query

Now that we understand the M code behind the AdoDotNet.Query function, let’s look at how it can be used in Power Query.

To use AdoDotNet.Query, we first need to create a new query in Power Query. We can do this by clicking the “New Source” button on the Home tab of the Power Query ribbon and selecting “Blank Query”.

Next, we need to enter the M code for the AdoDotNet.Query function. We can do this by clicking the “Advanced Editor” button on the View tab of the Power Query ribbon and pasting the following code:

let

connectionString = "Server=localhost;Database=MyDatabase;Trusted_Connection=True;",

query = "SELECT FROM MyTable",

results = AdoDotNet.Query(connectionString, query)

in

results

In this code, we define the connection string and SQL query to be executed, and then we call the AdoDotNet.Query function with these values. The results of the query are then returned as a table.

We can then use other Power Query functions to transform and load this data into a variety of destinations. For example, we could use the Table.SelectColumns function to select specific columns from the query results, or the Table.TransformColumnTypes function to change the data types of specific columns.

In this article, we have explored the M code behind the AdoDotNet.Query function in Microsoft Power Query. We have learned that this function is simply a wrapper around the .NET SqlCommand class and allows users to execute SQL queries against a variety of database systems. We have also seen how this function can be used in Power Query to access and manipulate data in a database. By understanding the M code behind this function, users can create custom data transformations and queries that meet their specific needs.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)