Using SQL queries in scripts

This article describes receiving a user list by means of an SQL query.

Namespaces:

using System.Data.SqlClient;

Script text:

string ConString = "Server=Server_name;Database=name_base;Trusted_Connection=True;"; //string for connecting to the database with Windows authentication
 
           using (SqlConnection connection = new SqlConnection(ConString))
           {
               using (SqlCommand command = connection.CreateCommand())
               {
                   command.CommandText = "select UserName from [User]";//query text
                   connection.Open();
                   using (SqlDataReader reader = command.ExecuteReader())
                    {
                   while (reader.Read())
                       {
                           Console.WriteLine(reader[0].ToString());//display result in console
                       }
                    }
               } 
           }

When working with the database of a Firebird server you need to consider two cases: the query is made to the database on which the system server is running, or to the database that is not used in any current operations. Here is an example of an active database use:

You need the following assembly:

Namespaces:

using EleWise.ELMA.Services;
using EleWise.ELMA.Runtime.Db.Migrator.Framework;

Script text:

var transformationProvider = Locator.GetServiceNotNull<ITransformationProvider>();//initialize service
using (var reader = transformationProvider.ExecuteQuery(@"SELECT ""USERNAME"" FROM ""User"""))//specify SQL query for execution
{
while (reader.Read())
{
Console.WriteLine(reader[0].ToString());//display result in console
}
}

 In case of an inactive database, you need to initialize the connection. Also, you need an additional assembly:

using FirebirdSql.Data.FirebirdClient;

The script looks as follows:

FbConnection fb;
FbConnectionStringBuilder fb_con = new FbConnectionStringBuilder();
fb_con.Charset = "WIN1251"; //used coding
fb_con.UserID = "sysdba"; //login
fb_con.Password = "masterkey"; //password
fb_con.DataSource = "localhost"; // Specify the computer where the Firebird server is deployed
fb_con.Database = @"C:\ELMA3-Express\UserConfig\ELMA3-EXPRESS.FDB"; //path to database file
fb_con.ServerType = 0; //specify server type   
fb = new FbConnection(fb_con.ToString());//create connection
fb.Open(); //open database 
FbTransaction transaction = fb.BeginTransaction();// 
FbCommand command = new FbCommand(@"SELECT ""USERNAME"" FROM ""User""", fb, transaction);//Specify SQL query for execution 
using (FbDataReader reader = command.ExecuteReader())//Initialize Reader
{
    while (reader.Read())
    {
    Console.WriteLine(reader[0].ToString());//Display result in console
} 
reader.Close();//close connection
fb.Close();