-2

I am not an Expert of C#. Currently i am developing a desktop application that is used to build dynamic queries (for Reports) for a Data Warehouse. There are multiple tables with multiple columns in it. Nothing can be assumed in advance. My question is how should i generate dynamic sql statement (for multiple tables, columns, joins, where's, group by's etc.)

Harry
  • 11
  • 4

2 Answers2

0

Are you getting any input from the user on what they want? if so, use that to limit the columns/tables in the sql statements. Such as if they are looking for a customer's records from a specific date then you will do SELECT * FROM CustomerData WHERE CustomerID=@CustomerID AND Date = @Date where @CustomerID and @Dateare the input from the user.

jagler
  • 185
  • 1
  • 6
  • Thanks for the reply. Actually, i want to know that to query a DWH should i use the old string building method or some thing else? – Harry Oct 09 '15 at 17:06
  • In that case, then you would want to look into Linq. [MSDN Linq](https://msdn.microsoft.com/en-us/library/bb397906.aspx) Personally I use string building but I am using an older version of Visual Studio at work. – jagler Oct 09 '15 at 17:20
  • i am not sure but the string builder is for single table entity only. Am i right? – Harry Oct 09 '15 at 17:29
  • oh sorry, little mis understanding, I still feel that string building technique(VS 6.0) is tedious, but offers lot of control. – Harry Oct 09 '15 at 17:36
  • No, you can use string builder for getting data from multiple tables. It is tedious but they both can give you a good amount of control. With string building you need to make sure that the data from the user is not going to negatively impact your database, aka validate the data before trying to use it. Linq takes some getting used to, but can be just as good as string building. – jagler Oct 09 '15 at 17:47
  • I agree with you @jagler. linq but not linq to sql. Am i right here? – Harry Oct 09 '15 at 17:57
  • It is possible to join multiple tables with linq to sql. [Example](http://www.c-sharpcorner.com/UploadFile/54db21/inner-join-using-linq-for-multiple-tables-3-tables-now-wit/) Personally, if it were me, I would continue to use string builder because I am more comfortable with it but I would use SQLCommand to do it so you can take advantage of the parameters in which it will make any string coming from a control into something that wont effect the database (usually). – jagler Oct 09 '15 at 18:16
  • Yes, i agree with you. i was currently using linq to sql in my project, but i found out that i can not dynamically supply criteria like string building. for example join listbox1.text? you need to hardcode criteria before hand in linq to sql, according to my understanding. – Harry Oct 09 '15 at 18:40
  • Ok, so in the listbox1.text there is something that, if it is a table they should pull form the query will have to be pre-built portion in the background for it in linq. I'm pretty sure you can't directly add the text from the listbox1. – jagler Oct 09 '15 at 18:47
  • Thanks for the help @jagler – Harry Oct 09 '15 at 18:48
0

I think Linq will help you. It's a very flexible way to create queries against SQL that easily incorporates variables into the query. This seems as good a place to start as any: MSDN article on Linq

nycdan
  • 2,819
  • 2
  • 21
  • 33
  • Thanks for the reply. in Linq to Sql can i write var Query = from text1.text in db.customers or select text2.text (these textboxes contain multiple entries db.fieldnames) – Harry Oct 09 '15 at 17:20
  • You are mixing SQL tables with objects on the screen. Not sure you can do that in linq. However, you can user those values in the where clause like this: var x = db.table.where(w => w.field1 == Text1.text). Does that help? – nycdan Oct 09 '15 at 17:22
  • not much help, i tried hard to dynamically supply a tablename in from clause, but it did not work. – Harry Oct 09 '15 at 17:27
  • This article seems to address it: http://stackoverflow.com/questions/28099435/dynamic-table-name-in-linq – nycdan Oct 09 '15 at 17:50
  • Good links, but i must learn entity frame work before commenting here. I suspect that entity frame work may leads to some complex situtation, because i am making a DWH. – Harry Oct 09 '15 at 18:12
  • in this article i suspect last answer might be helpful stackoverflow.com/questions/8896843/querying-data-using-entity-framework-from-dynamically-created-table. Thanks for the help. – Harry Oct 09 '15 at 18:18