BlueGroup.ClsDBSupport.SQLServer 1.0.7

BlueGroup.ClsDBSupport.SQLServer

BlueGroup.ClsDBSupport.SQLServer is an open-source library used to access and operate on SQL Server DataBases. It is a wrapper for the BlueGroup.ClsDBSupport.Base package, specific for SQL Server DataBases.

How To Use

Just add the package to your project via NuGet Package Manager.
The DataBase Connection String must be provided as a parameter of the constructor. Note that by default the Encryption setting of the connection string is set to true, so if an error regarding non-authorized certificates appears when creating the SQLServerDBClient object consider adding Encryption=false to the connection string.

Methods

Query

This method is used to retrieve multiple records from any DB Table. Only SELECT statements should be used as queries. Results of the query are returned as a DataSet inside a specific structure.

Query<T>

This method is used to retrieve multiple records from any DB Table. Only SELECT statements should be used as queries. Results of the query are returned as a List<T> inside a specific structure. Note that, in this case, the DataBase columns must have the same name of the object's properties, or the object's properties must be marked with the attribute DbColumn("COLUMN_NAME") or DbNullableColumn("COLUMN_NAME") for nullable columns.

ExecCommand

This method is used to perform all the operations on DB objects and records (INSERT, DELETE, UPDATE, ALTER TABLE, etc...).

ScalarQuery

This method is used to retrieve a single value from a DB Table (the value of a single column of a Table for a specific record); the value is returned as a string inside a specific structure.

ScalarQuery<T>

This method is used to retrieve a single record from a DB Table; the value is returned as an object of type T inside a specific structure. Note that, in this case, the DataBase columns must have the same name of the object's properties, or the object's properties must be marked with the attribute DbColumn("COLUMN_NAME") or DbNullableColumn("COLUMN_NAME") for nullable columns.

ExecCommandList

This method is used to perform several commands in a single transaction. If all commands have successful response, the transaction is committed, while if one (or more) fail, the transaction is rolled back. Commands must be passed to the method as a List<string> and they must be enlisted in the desired order of execution.

Return Values

All methods return a specific return structure, which inherits from a generic ReturnStruct class. The properties of the generic ReturnStruct class (which are inherited by ALL specific return structures) are:

• RetVal: it can assume either SUCCESS or ERROR values, according to the result of the query/command;
• Message: if the result is ERROR, it contains the error message;
• LastCommand: contains the last executed command. If you are trying to execute a list of commands and something goes wrong, it contains the command that resulted in the error.

The specific return structures are the following (with the additional specific properties):

ReturnStructQuery

For the Query method.

• RetDS: a DataSet containing the results of the query;
• RowsFetched: the number of rows fetched by the query. It is equal to RetDS.Tables[0].Rows.Count.

This class also has a CheckExistsResult() method, that returns true if the RetDS object is not null, contains at least one table with at least one row, false otherwise.

ReturnStructQuery<T>

For the Query<T> method.

• RetList: a List<T> containing the results of the query;
• RowsFetched: the number of rows fetched by the query. It is equal to RetList.Count.

This class also has a CheckExistsResult() method, that returns true if the RetList object is not null and contains at least one element, false otherwise.

ReturnStructExecCommand

For the ExecCommand method.

• RowsAffected: the number of rows affected by the executed command.

ReturnStructScalarQuery

For the ScalarQuery method.

• ResultValue: a string containing the single result of the query.

ReturnStructScalarQuery<T>

For the ScalarQuery<T> method.

• ResultValue: a T object which is the result of the query.

ReturnStructExecCommandList

For the ExecCommandList method.

• RowsAffected: a List<int> containing the number of rows affected by each executed command in the corresponding list.
• TotalRowsAffected: the total number of rows affected by all executed commands.