Is this method a good aproach to get SQL values from C#?
- by MadBoy
I have this little method that i use to get stuff from SQL. I either call it with varSearch = "" or varSearch = "something". I would like to know if having method written this way is best or would it be better to split it into two methods (by overloading), or maybe i could somehow parametrize whole WHERE clausule?
private void sqlPobierzKontrahentDaneKlienta(ListView varListView, string varSearch) {
varListView.BeginUpdate();
varListView.Items.Clear();
string preparedCommand;
if (varSearch == "") {
preparedCommand = @"
SELECT t1.[KlienciID],
CASE WHEN t2.[PodmiotRodzaj] = 'Firma' THEN
t2.[PodmiotFirmaNazwa] ELSE
t2.[PodmiotOsobaNazwisko] + ' ' + t2.[PodmiotOsobaImie] END AS 'Nazwa'
FROM [BazaZarzadzanie].[dbo].[Klienci] t1
INNER JOIN [BazaZarzadzanie].[dbo].[Podmioty] t2
ON t1.[PodmiotID] = t2.[PodmiotID]
ORDER BY t1.[KlienciID]";
} else {
preparedCommand = @"
SELECT t1.[KlienciID],
CASE WHEN t2.[PodmiotRodzaj] = 'Firma' THEN
t2.[PodmiotFirmaNazwa] ELSE
t2.[PodmiotOsobaNazwisko] + ' ' + t2.[PodmiotOsobaImie] END AS 'Nazwa'
FROM [BazaZarzadzanie].[dbo].[Klienci] t1
INNER JOIN [BazaZarzadzanie].[dbo].[Podmioty] t2
ON t1.[PodmiotID] = t2.[PodmiotID]
WHERE t2.[PodmiotOsobaNazwisko] LIKE @searchValue OR t2.[PodmiotFirmaNazwa] LIKE @searchValue OR t2.[PodmiotOsobaImie] LIKE @searchValue
ORDER BY t1.[KlienciID]";
}
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
using (SqlCommand sqlQuery = new SqlCommand(preparedCommand, varConnection)) {
sqlQuery.Parameters.AddWithValue("@searchValue", "%" + varSearch + "%");
using (SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader())
if (sqlQueryResult != null) {
while (sqlQueryResult.Read()) {
string varKontrahenciID = sqlQueryResult["KlienciID"].ToString();
string varKontrahent = sqlQueryResult["Nazwa"].ToString();
ListViewItem item = new ListViewItem(varKontrahenciID, 0);
item.SubItems.Add(varKontrahent);
varListView.Items.AddRange(new[] {item});
}
}
}
varListView.EndUpdate();
}
