Is this method a good aproach to get SQL values ​​from C #?

Question

I have this little method that I use to get information from SQL. I either call it with varSearch = "" or varSearch = "something" . I would like to know if it is better to use a method written this way, or would it be better to split it into two methods (by overloading), or maybe I could parameterize the whole WHERE clause in some way?

 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(); } 

Answer 1

A better approach would be to actually use a stored procedure instead of hardcoding SQL in your application. You can pass the where where parameter to the stored procedure and process the logic on the database side.

This approach also gives the advantage that if you need this logic in another application (for example, a JAVA application, for example), the logic is centralized in the database, so you do not have to rewrite it again.

Answer 2

Sludge at the MULTIPLE levels:

• NO DAL in general - this means that your SQL code is plastered in all forms. Horrible service - put at least all the SQL processing in one class.

• A lot of hand-written code, as this is poor performance (as in: programmer performance). Take a look at BLToolkit, how you can use all GENERATED RUNTIME code (from an attribute with SQL and an abstract method - a subclass with a real method is generated by bytecode).

However, if I cannot convince you to use a real level of data access / ORM, for example NHibernate.

FOr 1 I would fire you as a programmer (welcome as an intern). Having SQL in forms is not my idea to spend time reworking the database — as such, it is not amenable to testing and painful to maintain. This is by the way., Not ".net specific" - SQL isolation is what I did 20 years ago (almost) in smalltalk and C ++ already;)

For 2, I would ... well - this will not happen because of the recommendations;)

Answer 3

I'm sure there are more efficient approaches to this, for example, using a stored procedure, but if you really need to do this, you need to:

  string preparedCommandTemplate = @" 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] {0} ORDER BY t1.[KlienciID]"; string whereClause="WHERE t2.[PodmiotOsobaNazwisko] LIKE @searchValue OR t2.[PodmiotFirmaNazwa] LIKE @searchValue OR t2.[PodmiotOsobaImie] LIKE @searchValue" if (string.Emtpy.Equals(varSearch )) { preparedCommand = string.Format(preparedCommandTemplate,string.Empty) } else { preparedCommand = string.Format(preparedCommandTemplate,whereCaluse) } 

Answer 4

If you do not want to use the stored procedure, at least parameterize the where clause. This code can quickly get out of hand. I would also like to use a free text index or something like Lucene.NET to implement this type of "like" search in multiple fields.

Answer 5

Given that you want to continue using hard-coded SQL statements in your code (rather than switching to LINQ2SQL or Entity Framework or another ORM tool), the one thing you definitely do not want to do is add the where clause as a parameter to your method (if that’s what you mean by parameterizing the where clause). This is done by clients who use this method, depending on the technology of data access (in this case, the SQL database).

Compare the following two calls:

 sqlPobierzKontrahentDaneKlienta(lv, "something"); 

and

 sqlPobierzKontrahentDaneKlienta(lv, "WHERE t2.[PodmiotOsobaNazwisko] LIKE '%something%' OR " + " t2.[PodmiotFirmaNazwa] LIKE '%something%' OR " + " t2.[PodmiotOsobaImie] LIKE '%something%'") 

Which one looks better?