C#: How to create report using a data table

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
SaloooAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SaloooAuthor Commented:
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
EBatistaCommented:
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
SaloooAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

TheAvengerCommented:
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
SaloooAuthor Commented:
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
TheAvengerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SaloooAuthor Commented:
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
TheAvengerCommented:
What's PUSH and PULL model for building reports?
0
SaloooAuthor Commented:
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
TheAvengerCommented:
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
SaloooAuthor Commented:
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
TheAvengerCommented:
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
SaloooAuthor Commented:
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
TheAvengerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.