Solved

C#: How to create report using a data table

Posted on 2003-11-11
14
787 Views
Last Modified: 2008-02-26
Hi,
I am using VS.NET 2003 and using the C# language to deploy crystal reports for .NET. I want to create a report using a dataset by deploying the push model, also I want to know how I can create this report using a data table ??

I want to know how to implement the use of a "data table" that I can create off of several tables and use that for my report. What I mean is that in my memory I can create a table what MS calls "data Table" right ? That's what I want to use to build my report.

any help would be greatly appreciated.

Chow.

__________________
Salmaan

When there is a bridge...There is a way
0
Comment
Question by:Salooo
  • 7
  • 6
14 Comments
 

Author Comment

by:Salooo
Comment Utility
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
0
 
LVL 10

Expert Comment

by:EBatista
Comment Utility
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
0
 

Author Comment

by:Salooo
Comment Utility
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.
0
 
LVL 20

Expert Comment

by:TheAvenger
Comment Utility
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.
0
 

Author Comment

by:Salooo
Comment Utility
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.
0
 
LVL 20

Accepted Solution

by:
TheAvenger earned 100 total points
Comment Utility
Well, the greates part of all this is made in the designer. From the designer, create a new OleDbAdapter for the Customer table. This will ask you for the connection to the DB, the table you want to use and the columns you want to include. Do the same for the Order and OrderDetails tables. If you never created an adapter with the designer, just select Data in the Toolbox and drag and drop OleDataAdapter to your form and follow the wizard.

Then right click on the adapter for the Customers table and select Generate Dataset. Select new dataset. Then select also the other adapters (in the second step of the wizard) and follow it to the end. Now you have a dataset in your project that contains the three tables. Open the dataset in design mode (double click on it in the solution explorer view) and you will see the tables. Using draging and droping link the tables (just drag the primary key of one table to the foreign key of the other and follow the wizard which is one step only). Save the dataset. Now you are ready with a dataset with 3 tables and two relations. You also have a varilble of the type of this dataset on your form. Set this variable as the datasource of your report.

What you are left with is to fill in the dataset with the data from the database. For this use OleDbAdapter.Fill method for ALL three adapters. Note that you need to do this in a specific order: child tables last. So it would be Customer, Order and then OrderDetails.

That's it. As you can see the only code you have to write yourself is the Fill methods for the adapters. Everything else is just designer work.
0
 

Author Comment

by:Salooo
Comment Utility
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 20

Expert Comment

by:TheAvenger
Comment Utility
What's PUSH and PULL model for building reports?
0
 

Author Comment

by:Salooo
Comment Utility
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.
   
0
 
LVL 20

Expert Comment

by:TheAvenger
Comment Utility
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).
0
 

Author Comment

by:Salooo
Comment Utility
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.
0
 
LVL 20

Expert Comment

by:TheAvenger
Comment Utility
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.
0
 

Author Comment

by:Salooo
Comment Utility
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.
0
 
LVL 20

Expert Comment

by:TheAvenger
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: Ivo
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now