Scribd
Upload
9 views5 pages

SQL Manager for Nested Queries

The document defines a class named SqlManagerNested that implements the ISqlManager interface for managing SQL database operations. It includes methods for executing queries, reading data, executing non-query commands, handling transactions, and returning data in various formats such as lists and DataTables. The class utilizes a connection string to connect to the database and supports parameterized queries to enhance security and flexibility.

Uploaded by

codandocerto
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views5 pages

SQL Manager for Nested Queries

The document defines a class named SqlManagerNested that implements the ISqlManager interface for managing SQL database operations. It includes methods for executing queries, reading data, executing non-query commands, handling transactions, and returning data in various formats such as lists and DataTables. The class utilizes a connection string to connect to the database and supports parameterized queries to enhance security and flexibility.

Uploaded by

codandocerto
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

using [Link].

Infraestructure;
using System;
using [Link];
using [Link];
using [Link];
using [Link];
using [Link];

namespace [Link]
{
public class SqlManagerNested : ISqlManager
{
private String _query;

private String _connString;

public SqlManagerNested(String connString)


{
_connString = connString;
}

private T ExecuteQuery<T>(Func<DbCommand, T> callBack, DbParameter[]


parameters = default(SqlParameter[]))
{
T returnValue = default(T);

using (var connection = new SqlConnection(_connString))


{
[Link]();

using (var cmd = new SqlCommand(_query, connection))


{
if (parameters != default(SqlParameter[]))
[Link](parameters);

returnValue = [Link](cmd);
}
[Link]();
}

return returnValue;
}

public IEnumerable<T> ExecuteReader<T>(DbParameter[] parameters, String


query = default(String)) where T : new()
{
Initialize(query);

List<T> list = ExecuteQuery((command) =>


{
var reader = [Link]();

var nestedLines = new NestedQuery().BuildEntities(reader);


var firstEntityName = [Link]().LevelName(1);

var nested = new Nested();


var nestedEntities = [Link](nestedLines,
firstEntityName, null, 1);
return [Link](nestedEntities).Cast<T>().ToList();

}, parameters);

return list;
}

public IEnumerable<T> ExecuteReader<T>(String query = default(String))


where T : new()
{
return ExecuteReader<T>(default(SqlParameter[]), query);
}

public Int32 ExecuteNonQuery(String query = default(String))


{
Initialize(query);

return ExecuteQuery((comm) => { return [Link](); });


}

public Int32 ExecuteNonQuery(DbParameter[] parameters, String query =


default(String))
{
Initialize(query);

return ExecuteQuery((comm) => { return [Link](); },


parameters);
}

public T ExecuteScalar<T>(String query = default(String))


{
return ExecuteScalar<T>(null, query);
}

public T ExecuteScalar<T>(DbParameter[] parameters, String query =


default(String))
{
Initialize(query);

return ExecuteQuery((comm) =>


{
var returnValue = [Link]();

if (returnValue == [Link] || returnValue == null) return


default(T);

try
{
return (T)returnValue;
}
catch
{
return (T)[Link](returnValue, typeof(T));
}
}, parameters);
}

private void Initialize(String query)


{
if (query != default(String))
_query = query;
}

public ISqlManager WithConnString(String connString)


{
_connString = connString;

return this;
}

public ISqlManager WithQuery(String query)


{
_query = query;

return this;
}

public void ExecuteTransaction(String[] querys, DbParameter[] parameters =


null)
{
SqlTransaction trans = null;

try
{
SqlConnection connection = new SqlConnection(_connString);

[Link]();

trans = [Link]();

foreach (var query in querys)


{
SqlCommand command = new SqlCommand(query, connection, trans);

if (parameters != null)
[Link](parameters);

[Link]();

[Link]();
}

[Link]();
}
catch (Exception ex)
{
try
{
if (trans != null) [Link]();
}
catch (Exception)
{

throw ex;
}
}

public void ExecuteTransaction(Dictionary<String, DbParameter[]> commands)


{
SqlTransaction trans = null;

try
{
SqlConnection connection = new SqlConnection(_connString);

[Link]();

trans = [Link]();

foreach (var command in commands)


{
SqlCommand sqlCommand = new SqlCommand([Link], connection,
trans);

if ([Link] != null)
[Link]([Link]);

[Link]();

[Link]();
}

[Link]();
}
catch (Exception ex)
{
try
{
if (trans != null) [Link]();
}
catch (Exception)
{

throw ex;
}
}

public DataTable ExecuteDataTable(DbParameter[] parameters =


default(DbParameter[]))
{
SqlConnection conn = new SqlConnection(_connString);

SqlCommand cmd = new SqlCommand(_query, conn);

if (parameters != default(DbParameter[]))
[Link](parameters);

[Link]();

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable table = new DataTable();


[Link](table);

[Link]();
[Link]();

return table;
}
}
}

Common questions

Powered by AI

Both methods handle exceptions by rolling back transactions in the event of an error. The method accepting a string array sequentially executes each query and rolls back if any execution fails. The method with a dictionary of commands handles name-value pairs and adds flexibility by associating specific parameters with each command. While error control is similar in both approaches, the dictionary permits more granular control over parameters, reducing risk associated with parameter passing .

Using default parameter values allows methods to be called with fewer arguments, simplifying the method use cases where certain parameters are not needed. In SqlManagerNested, default parameters like SqlParameter[] provide flexibility, enabling methods to be invoked with or without custom parameters. This is significant for enhancing usability, as it reduces developer overhead when default operations suffice and allows focus on essential input .

Potential risks include resources being left open longer than necessary, leading to connection pool exhaustion or transaction locks. The class mitigates these risks using using blocks for automatic disposal of connections and ensuring transactions are committed or rolled back within the catch block in case of errors. This minimizes the possibility of lingering active transactions and aids in maintaining database stability .

The SqlManagerNested class manages database transactions using the ExecuteTransaction method, which accepts a list of queries and optional parameters for a batch execution. Transactions are initiated using SqlConnection and SqlTransaction objects. If an exception occurs during transaction execution, the transaction is rolled back using trans.Rollback(), ensuring data integrity. The use of try-catch blocks around transaction operations allows the rollback to occur before throwing the exception up the call stack .

The SqlManagerNested class provides flexibility through methods like ExecuteReader, ExecuteNonQuery, and ExecuteScalar, which can execute queries and return results as needed. Developers can customize query execution by using either version of ExecuteReader—one accepting parameters and another without to suit different database operations. Furthermore, methods such as WithConnString and WithQuery allow setting or updating the connection string and query, enabling dynamic query customization .

The ExecuteScalar method in SqlManagerNested handles return values by checking for DBNull.Value or null, returning a default value of type T in such cases. This prevents exceptions when attempting type conversion on null equivalents. It then attempts a direct cast to type T, and if that fails, it converts the value using Convert.ChangeType to ensure type compatibility. This sequence of operations ensures both type safety and appropriate handling of null values, thus avoiding runtime exceptions .

DbParameter arrays are utilized in the SqlManagerNested class to safely pass parameters to the SQL queries. This approach allows for parameterized queries, which are critical in preventing SQL injection attacks and ensuring type safety. Methods like ExecuteQuery and ExecuteNonQuery use these parameter arrays to insert values into queries dynamically, reducing risks associated with hardcoded query fragments .

The class design promotes reusability through its generic methods like ExecuteQuery and ExecuteReader, which work with any data type that can be constructed with new(). This generic approach, combined with methods for setting connection strings and queries separately, allows the same class instance to be repurposed for different contexts without rewriting functionality. The ability to execute transactions with different sets of parameters using the same structural methods adds agility in modifying or extending database interactions .

The nested query execution within ExecuteReader enhances data handling by building entity hierarchies from complex result sets. This approach allows handling of relationships in data without requiring separate database calls for each level of data. By extracting nested lines and then building entities with levels, it facilitates efficient data retrieval and manipulation within applications, providing a more sophisticated data management structure directly from query results .

The ExecuteDataTable method retrieves data from the database by first creating a SqlConnection with the connection string and a SqlCommand using the query. If parameters are present, they are added to the command. The connection is then opened, and a SqlDataAdapter is used to fill a DataTable with the results of the query. This approach abstracts the complexity of manually iterating over a data reader, providing a data structure directly usable in applications. Finally, resources like the connection and data adapter are closed and disposed of to free up resources promptly .

You might also like