C#: How to create report using a data table

Hi again,

I think I have not explained my question right. Let me give you an example of what I am looking for. Here is a VB example of similar process to update a ListView item. But I am using C# so please give me examples in C#.

*******************
Dim dr As DataRow
cn = New SqlConnection("Server = Saloo\VsDotNET;database=Test_DB;trusted_connection = yes")
ds = New DataSet
ds.Tables.Add("TaskTable")
da = New SqlDataAdapter

'your sql statement
sql ="................................."

cmd = New SqlCommand(sql, cn)

da.SelectCommand = cmd
cn.Open()
ds.Clear()
Try
da.Fill(ds, "TaskTable")
Catch eFill As SqlException
MessageBox.Show(eFill.Message, "Data Fill Error")
cn.Close()
Exit Sub
End Try
'Read the records from the virtual recordset "TaskTable"
For Each dr In ds.Tables("TaskTable").Rows
'dr(0) is the first column and dr(1) is second column...
Dim Str2 As String() = {dr(0), dr(1), dr(2), dr(3), dr(4), dr(5)}
ListView1.Items.Add(New ListViewItem(Str2))
Next
da.Dispose()
cn.Close()
ds.Clear()
**********************************
Now my question is how can I use a similar way to buil my report????
What would be the steps I need to take to construct my report off of a data table like the one used in the example?

Anticipating help soon :-)
__________________
Salmaan

When there is a bridge...There is a way

Is this what are you trying to do?:

CrystalReport1 oRpt=new CrystalReport1(); //Report object

SqlConnection cnn=new SqlConnection(yourConnectionString);
SqlCommand cmdSelect=new SqlCommand(yourCommandText,cnn);
      
DataTable dt=new DataTable(); //Datatable to be used in the report

SqlDataAdapter da=new SqlDataAdapter(); //DataAdapter to fill the datatable
da.SelectCommand=cmdSelect;

cnn.Open();
da.Fill(dt); //You can fill the datatable just like a dataset
cnn.Close();

oRpt.SetDataSource(dt); //set the datasource as the DataTable object


EB

Hi EBatista,
Thank you for your response. Well you are close but this is not quite what I am looking for. What I need is the following:

1) I want to create a dataset in memory  with the tables I need from the database
2) I want to be able to set the relationshipd between the tables in step 1
3) I want to create another data table in memory built from the tables in the dataset (step 1)
4) Then I want to be able to update my report from the data table by specifying the criteria I want my report to be built on.

In brief this is what I want to accomplish. I would really appreciate if you or anyone reading this thread can compose a brief code example of how can I am accomplish the tasks that I just mentioned.

Thanks.

Salmaan.

Actually EBatista gave you the major points for what you are asking for. Using the designer or a manual command as EBatista showed, create a dataset and put inside the initial tables (step 1). In the designer you can also build the relations (step 2). You shoul create a separate dataset with one table (you can do this also through the designer) for your "memory" data table and fill it manually with data (step 3). And then you should set this last dataset as the datasource of the report, as also shown by EBatista (step 4).

This solution includes several different areas of knowledge and is too large to explain everything in details. Have a look at the following classes:
- OleDbAdapter
- OleDbCommand
- OleDbConnection
- DataSet
- DataTable
- DataRelation

If you have never worked with databases in .NET you should read a tutorial or something. You can find a lot of these in the internet. Go to Google and search for .net, c#, dataset, dataadapter, wizard.... These are all keywords that can lead you to the solution.

If you have more questions for details, post them here.

Hi,

Thank you so much for your response. I really appreciate it. I am pretty much aware with
- OleDbAdapter
- OleDbCommand
- OleDbConnection
- DataSet
But I have really not dealt with the concepts of - DataTable and - DataRelation

I tried to follow your guidlines but there are a few problems. First, when I create my report I use a dataset to fill in the fields required in my report. In the example above there is no use of the dataset anywhere!!!

Secondly, I do understand that this problem requires a long explanation, but I would really appreciate if you can give me a brief code example of  building a report by using the steps described and using only 3 tables i.e., Customer, Order, and OrderDetails. I would truly appreciate your help. I have been trying to solve this problem but I have not been able to find a single example that really expalins what I am trying to do here.

Again thank you for your help and cooperation.

Salmaan.

Hi,

I really appreciate your quick response my friend. Thanks a bunch. But there is one thing that you must have overlooked. I am trying to deploy the "PUSH MODEL" for building the reports. You have explained the "PULL MODEL" where I have to type in very little code. But as I said thank you for describing it to me. I really do appreciate it.

Now, lf possible can you  please describe how I would accomplish the same tasks in the "PUSH MODEL"

I would be very thankfull for your kind help.

Thanks a lot.

Salmaan.

What's PUSH and PULL model for building reports?

Hi,

Well the PULL Model is what you have just described. Its a way of building reports where you don't type too much code and this process has a drawback. It keeps a continous connection to your database. Hence if you have too many users using the same application at the same time it will sloww down your program's response and also put too mcuh load on the database itself.

The PUSH model is where you use an ADO dataset and load your information into the memory. This allows you to connect to the database, pull the required data, and then disconnect. Hence you do not have a continous connection to the databse.

Check out the following link for details about the PUSH model:
http://support.crystaldecisions.com/communityCS/TechnicalPapers/crnet_adonet.pdf.asp

I tried to use a similar style explained in this document, but apparently is not working in my situation. Possibaly its because the database has some sort of screwed up relationships. But let me know if you need some more info on this model.

Thanks.

Salmaan.
   

Sorry, this is not correct. I told you to fill in the dataset and then set the dataset as the data source of the report. When you fill the dataset, it is in the memory and is not connected to the database any more. It is exactly what you explain that you need.

Another point: the report connects to the database only during loading the data. It does not stay connected all the time, but maybe it takes more time than you loading the data in a dataset.

Once again: what I explained is totally database disconnected way to fill in your report. The call to OleDbAdapter.Fill fills the data in the dataset and disconnects immediately (even closes the connection).

Yes you are right I think I might have described it in wrong "terms". What I wanted to say was it allows "connection-sharing". Please visit the following website for more details on both models:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/crystlmn/html/crconreportdataaccess.asp

Thanks.

Salmaan.

What I described you is exactly the push model. Whenever I said designer, I meant Visual Studio IDE, not the crystal reports desinger. If you implement the pull model, you will make the database connection directly from the report, which is NOT what I explained.

Hi,

I understand what you mean now. I really did take designer for the crystal designer. I have an application with 2 tables working now. I will try to work on a couple more and let you know if I have any further questions. But still for suture references I would like to know the implementation of dataTables in building reports. If you get some time, I would really appreciate if you can come up with an example using the same 3 tables mentioned earlier.

Other than that have a great weekend and Thanks a bunch for your continued cooperation.

Thanks.

Salmaan.

Your dataset contains 3 tables (the way I explained it). You make them in the designer, but at the backend these are DataTables (DataSet = n x DataTable + m x DataRelation). So you know already how to use them and so on - they are just the building blocks of the data set.