C# SQL List/Form views

[chrissdegrece]

Hi all, I am a new guy in C# and VS 2008 and trying to figure out how to do a few things.

Maybe I should let you know that I have been using Powerbuilder for some years now, and I am a used to doing DBMS related stuff quite quick and easy. As it seems up to now, things are totally different with C# and the "Quick & Easy" moto is already put aside.

Anyway, here are the "few" things I would like to find out how to implement in C# (VS 2008).

I am not asking for the code for this but for some directions on what controls (MS or others) I can use to achieve such functionality.

1) Generate a Table/List & Form view of data by issuing an SQL statement.

For Example:
SELECT name,surname,state,balance FROM Customers WHERE companyId = 1;

This should produce a Table/List view for the user to browse his customers;

2) Change the where clause in runtime to filter data.

After the user has viewed the customers he decided that he doesn't need a list of 30.000 customers and wants to filter out some of them. Thus I would like to change the "WHERE" clause in runtime like that: " WHERE companyID = 1 AND state = 'CA' " according to user's request.

This means that I must somehow have a way to identify the SQL statement table,fields,where clause in runtime and replace the SQL statement with the user's criteria.

3) Now the user wants to "Edit" one entry so he double clicks on one and then he enters a "Form View" mode which is created using another SQL and contains more fields than the simple list.

After completing his changes he "Saves" and goes back to "List mode"

4) Now the user wishes to export the list to some kind of file like CSV,Excel,PDF etc. Is that possible somehow?

----------

And finally the application must have some reports.

After writing our SQL statement we "design" the report layout and give that to our user too.

Then the user is not satisfied again and wants to filter out some records. Same logic should be applied: "replace the SQL where clause" with the user's criteria.

Finally export that report to Excel,PDF etc.


Sorry for the long post.

Thanks

[toraj58]

you need to use SqlCommand, SqlConnection, SqlDataAdapter, Dataset, DataTable and some contorls for representing data to user like GridView
for No. 4 and reporting you can use CrystalReport that can export to PDF, Excel and Ms Word.

[darwen]

Follow Toraj's suggestion and bear in mind that the C# application drives the sql and the database, not the other way around


"Form View" mode which is created using another SQL


The form isn't created by SQL - the form uses SQLCommand etc to connect to the database, execute an SQL statement and then retrieve the data.

Then the form takes the data and fills its controls with it.


After the user has viewed the customers he decided that he doesn't need a list of 30.000 customers and wants to filter out some of them. Thus I would like to change the "WHERE" clause in runtime like that: " WHERE companyID = 1 AND state = 'CA' " according to user's request.


and


This means that I must somehow have a way to identify the SQL statement table,fields,where clause in runtime and replace the SQL statement with the user's criteria.


As the C# app is building up the SQL statements it can change them at will. It doesn't identify the SQL statements within itself - it does the SQL statement construction and execution so it changes them when it's building them up.

The flow of a C# app connecting to a database is something like :


SqlConnection connection = new SqlConnection(...); // usually only one connection is used rather than connecting several times in the app

// using statements to dispose of objects when we're done with them
using (SqlCommand command = new SqlCommand("select my_value from my_table", connection))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int myValue = reader.GetInt32(reader.GetOrdinal("my_value"));

// add myValue to listbox/listview control etc
}
}
}


There are various other flavours of this approach using DataSets/DataTables/DataAdapters but the principle is the same.

A pointer of note : you should always use parameterized queries. See here ('http://www.uberasp.net/getarticle.aspx?id=46').

Darwen.

[JonnyPoet]

If you really want to parse a users sql Text line and creating an acceptable View in a Form only by evaluating the sql you will have to go a long way. At first as you told you are totally new I woul suggest you to read some easy C#books For example one of this step by step books ( for example MS Visul C# 2005 Step by step ) This willshow you some small examples how to do a cnnection to sql express database and how to bind controls to it so you are able to insert, edit, delete data. After you are able to easily handle this you may want to do some best practices books to learn how to really use C# in different design concepts. Aftr this you may be able to do a simple parser which is able to read and parse and evaluate sql statements. This needs reading and showing the full tables on a screen, having a designer View for the customer, so he is able to create useable sql statements - best way in a graphical step using drag and drop on the items. Maybe similar to the View designer in the 2008 SQL Server. :D

Good luck.
But why in th world, if your users are able to do sql code themselves they are not simple using the naked SQL 2008 Server and the management tool for it. ?

[toraj58]

consider this example:

it is just a begining:


const string CONNECTIONSTRING = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\spyMasterDataBase.mdb";
string selectQuery = "select * from agents";
OleDbConnection con = new OleDbConnection(CONNECTIONSTRING);
OleDbCommand getData = new OleDbCommand(selectQuery, con);
OleDbDataAdapter adapter = new OleDbDataAdapter(getData);
DataSet dsAgents = new DataSet();
try
{
con.Open();
adapter.Fill(dsAgents, "agents");
dataGridView1.DataSource = dsAgents;
dataGridView1.DataMember = "agents";
}
catch (OleDbException e1)
{
MessageBox.Show(e1.Message);
}
catch (Exception e2)
{
MessageBox.Show(e2.Message);
}
finally
{
con.Close();
}

[chrissdegrece]

Firstly I would like to to thank you all for your precious information.

Actually I have read a couple of Beginner's books which only provided basic info, but I can't seem to find anything that might help at what I am trying to do.

Let me post some pictures from a small Powerbuilder application to explain better:

1. We have "select id,trandate,username,amount etc.etc. from transactions_view"

This generates a "listview" for which we have to change the non user friendly fieldnames to something the user likes more.

Here is the result.

[img=http://img156.imageshack.us/img156/8193/listviewvs1.th.jpg] (http://img156.imageshack.us/my.php?image=listviewvs1.jpg)

2. User double clicks and enters "Form view" which is also generated by a more complicated SQL with joins and all.

[img=http://img105.imageshack.us/img105/8093/formviewvl3.th.jpg] (http://img105.imageshack.us/my.php?image=formviewvl3.jpg)

3. Search functionality is implemented using a custom control, which "behind the scenes" builds the "where clause", the user doesn't know that this is happening.The user only fills his criteria for searching and the fieldnames are "read" from the listview and displayed in a listbox for the user to select.

[img=http://img165.imageshack.us/img165/1646/searchlr4.th.jpg] (http://img165.imageshack.us/my.php?image=searchlr4.jpg)

Both 1 & 2 are stored as "definitions/layouts" inside the application in order to be able to use that "user friendly layout" in other forms too.

The whole form is used again & again throughout the application for every list/form need and only the "listview" & "form" are changed every time like this:

lvDataListView.layout = "transactions_list_view"
lvFormView.layout = "transaction_form_view"

(FYI Layouts are able to Insert/Update/Delete records, thus no additional code is necessary for this)


For a more descriptive demo on how these "layouts" are designed, you can take a look at:

http://www.sector-one.gr/dw/

Thanks a lot

[JonnyPoet]

Sorry you havn't done a question in your post so what is to answer ? Having userfriendly names can be achieved by different ways. One is to change the fieldnames in your sql query using 'as' like
SELECT MyTable.UID as UserIdentification, ....
The other way will be to use Localization as I have seen your tables have ( I think ) russian letters. And in doing the translation from database Names to what the user should read you dont need to be a correct translator so you an change the meaning of the expressions to what you want to let your users see. Google for 'Localization' or Satellite.dlls

[chrissdegrece]

The questions are:

1) The resulting table/view from the SQL needs to be stored somehow as a design and be re-used throughout the application.
(Imagine the user has re-arranged columns, sizes etc. , so we need to keep that somehow)

2) Can the SQL where clause be changed and data re-retrieved filtered by the new WHERE clause

3) Export the resulting data to a common file type.

4) Do all of the above with reports.

[chrissdegrece]

Thanks a lot everyone, it seems that I should have to use Datawindow .Net to achieve this kind of functionality.

[toraj58]

DataWindow .Net is a third party application(from sybase) that can be integrated with Visual Studio IDE; do you want to buy it?

[chrissdegrece]

Unfortunately I cannot find similar functionality in any other control or at least a combination of controls.

With datawindow .Net you write your sql and design your table/form/report/graph/treeview/grouping/crosstab etc. , this is stored in your application and you can use it everywhere with no additional code.

You can insert/delete/update data extremely easy (1 line of code).

You can export to csv,xls,pdf,txt,dbf etc.

Import of data is also very easy.

Same object can be used as view & as report.

SQL change is as easy as:

dwControl.SetSQLSelect(<SQL query>)
dwControl.Retrieve();

Parameters are supported for data retrieval.

That's actually what I asked for in the 1st place, this kind of functionality but if possible without using Datawindow .net

I also tried the example from:

http://www.devx.com/dotnet/Article/22655/0/page/1

And it really is amazing but it costs some $800 which is a bit expensive.

[JonnyPoet]

...
And it really is amazing but it costs some $800 which is a bit expensive. :D Prices are very relative. If you calculate to create that functionality yourself with 150 $ / hour it will be very cheap. If you want to code it without using a controllike this the costs of your development will per sure be more then 50 times of that.

[BigEd781]

:D Prices are very relative. If you calculate to create that functionality yourself with 150 $ / hour it will be very cheap. If you want to code it without using a controllike this the costs of your development will per sure be more then 50 times of that.

Exactly. I just bought a $300.00 ListView control at work. It may sound expensive, but it would have taken me at least a week to code (and it would not have been tested and would have lacked many features). At my salary, $300.00 was a steal.