ZennoPoster allows you to work with various types of databases such as Microsoft SQL, MySql, SQLite, etc. You will be able to connect to databases on a local computer or a remote server and load the necessary data for your web tasks.
How to add an action to a project?
Via the context menu Add action → Data → Databases
In order to connect to the database, you need to properly configure the connection. It will differ depending on the DBMS.
You can use the Connection manager to make it easier to create a connection string. Based on the data specified in it, a connection string will be generated.
Variable macros cannot be used in the Manager.
There are several data providers:
SqlClient - Provider for native connection to SQLServer;
MySqlClient - provider for native connection to MySQL;
OleDb - one of the standards that allows you to connect to various DBMS (including SQLServer);
Odbc is another standard for connecting to a DBMS.
The connection string specifies various login parameters (be it login and password). Examples of connection strings for different DBMS can be viewed at this link.
In order not to create this string manually, you can use the Connection Manager (described above).
There are several types of requests
Request is used for operations that do not return data from the database (for example, INSERT or DELETE). The response of such an operation is the number of records involved in the request.
Allows you to get a single value. For example, if you want to execute the aggregate function (select sum (price) from fruit).
Returns a table of data.
SQL request input field.
Use parameters in request
Parameters are used to make it easier to create a request. They will be substituted in the necessary places in the request text. There are named and unnamed parameters. In the first case, the name is important, in the second - the order of the variables. Which type of parameters to use depends on the DBMS.
When using parameters, the text inside them is automatically escaped
An example of a query with named parameters:
An example of a request with unnamed parameters:
In this section, you need to choose where to save the query result.
Save results to:
All rows and columns that are returned as a result of the query will be stored in one variable.
It is also necessary to select separators with the help of which the rows and columns will be separated from each other.
In order to save the results to a list, it is important to specify which separator to use for the columns. As a result, each line from the base will be entered into a new element of the list, and the corresponding separator will be inserted between the columns.
When writing data to a table, the cells will be filled in accordance with the request.
Line number - as a result of the query, several lines may be returned, so you must specify the number of the one that we will parse (numbering from zero!).
In the table below, you select the cell number in the row and the variable to which this cell will be saved (numbering from zero!).