Postawiłem sobie zadanie,  wywołania „czystego” zapytania SQL z interfejsu użytkownika.
Dodatkowo zwracany wynik nie zawsze będzie taki sam, czyli możemy napisać dowolne zapytanie łącznie z wykonywaniem funkcji i procedur. Można by skrócić treść zadania do „wykonaj pseudo managmenta”.

Zaintrygowała mnie możliwość wyświetlania dowolnej struktury.

Aktualnie w moich projektach wykorzystuje Entity Frameworka, który mapuje mi strukturę bazy na klasy. Ma on możliwość wykonywania własnych zapytań poprzez metodę ExecuteQuery, z tym, że trzeba pamiętać o tym aby wynik zmapować na dostępny typ w modelu. Nie da się zwrócić dowolnego typu z po za modelu.

Rozwiązanie z EF odpadło ale w linku zaprezentowanym na końcu posta jest podpowiedz, że można użyć ADO.net i obiektów DataTable lub DataSet i na tym rozwiązaniu postanowiłem się skupić.

WebFormsach mamy komponent DataGridView który we właściwościach datasource może przyjąć typ DataTable czyli nasz docelowy. Tak więc dzięki temu będziemy mogli wykonać dowolny kod sql i wyświetlić go na stronie (bez konieczności mapowania).

Żeby nie było tak łatwo postanowiłem jednak mieć pewną kontrolę nad zapytaniami, które chce wykonywać w aplikacji.
Zamiast wysyłania „czystego zapytania” wywołuje procedure składowaną, która to w parametrze przyjmuje zapytanie.
Dodatkowo w procedurze jest kontrola błędów, jeżeli powstanie błąd dostanę ładną tabelkę ze szczegółowym opisem błędu.

Procedura składowana pValidatingAndExecutegQery.
sp_executesql – pozwala na zbudowanie zapytanie i wykonanie go poprzez EXECUTE.
W bloku CATCH mamy funkcje, które zwracają opisy błędu w formie tabeli, dzięki zastosowanej frazie „Select”.

Create PROCEDURE [dbo].[pValidatingAndExecutegQery](
@nazwa nvarchar(1000)
)
AS
BEGIN
BEGIN TRY
EXECUTE sp_executesql @nazwa;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine;
END CATCH;
END

Powyżej kod na serwerze, teraz aplikacja.
W tym miejscu muszę jeszcze wspomnieć o funkcji SaveQueryToBase, która ma za zadanie przed wykonaniem zapytania zapisanie jego treści do loga.
Metoda ta jest prostym insertem do tabeli nie będę tutaj zamieszczał kodu.

public DataTable ExecuteQuery(string query, int idUser)
{
DataTable dtResult = new DataTable();
SqlConnection connect = new SqlConnection(conString);
try
{
_iqueryLog.SaveQueryToBase(query, idUser, „ExecuteQuery”);
connect.Open();
SqlCommand selectcommand2 = connect.CreateCommand();
selectcommand2.CommandText = „[dbo].[pValidatingAndExecutegQery]„;
selectcommand2.CommandType = CommandType.StoredProcedure;
selectcommand2.Parameters.Add(new SqlParameter(„nazwa”, query));
dtResult = getDataTableFromDataSet(selectcommand2);
connect.Close();
return dtResult;
}
catch (Exception ex)
{
_iqueryLog.SaveQueryToBase(ex.Message, idUser, „ExecuteQuery”);
return null;
}
}

W ramach dobrych praktyk uznałem, że część kodu przeniosę do osobnej metody, poniżej ona.

private DataTable getDataTableFromDataSet(SqlCommand selectcommand2)
{
using (SqlDataAdapter adapter = new SqlDataAdapter(selectcommand2))
{
DataTable dtResult = new DataTable();
DataSet dataset = new DataSet();
adapter.Fill(dataset);
if (dataset.Tables.Count > 0)
{
foreach (var i in dataset.Tables)
{
DataTable tmpTable = (DataTable)i;
if (tmpTable.Rows.Count > 0)
{
dtResult = tmpTable;
}
}
}
return dtResult;
}
}

Podsumowując
Złą praktyką jest pozwolić aby w aplikacji można było wykonać dowolne zapytanie. Jeżeli jednak z jakiś względów dopuścimy się takiej funkcjonalności należy pamiętać o jak największej kontroli nad tym procesem. Chyba że piszemy swojego „managmenta”.

Kolejną „wadą” (to zależy) tego rozwiązania jest fakt że trzeba wrócić do WebForms-ów, czytałem, że wielu uważa to narzędzie za antywzorzec , „narzędzie dinozaurów”.

Myślę, że nie zależnie od technologi, każde rozwiązanie  może stać się antywzorcem Jeżeli ktoś używa nowoczesnych technologi, a zapomina o dobrych praktykach to również stworzy „mityczny” antywzorzec. Technologia jest narzędziem i tylko od nas zależy jak tym narzędziem pracujemy.

Czy nowe zawsze jest lepsze od starego?

przydatne linki:
Execute custom sql with entity framework?
sp_executesql
Return multiple datasets from sql server stored procedure
Entity Framework wykonywanie własnych zapytań

Kategorie: ArchiwumInne