Database Connectivity in C#

DBMS Connectivity

Connect with an Oracle Database

Add the following Reference:
“System.Data.OracleClient” // Help is given at the end on how to add a reference

On top of the page, write down:
using System.Data.OracleClient;

To execute “select” type queries, use the following code:

DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(“QUERY”,@”Connection String”);
Da.Fill(ds,”abc”);
// Query Strings are given at the end

dataGridView1.DataSource = ds.Tables[“abc”]; // dataGridView1 is the name of the data grid where we display the result of the query

To execute “insert” type queries, use the following code:

OracleConnection con = new OracleConnection(@”Connection String”);
OracleCommand cmd = new OracleCommand(“QUERY”,con);
Con.Open();
cmd.ExecuteNonQuery();
con.Close();



Connect with a MySQL Database

For MySQL, replace the keyword ‘Oracle’ by ‘MySQL’ in all these statements, and perform the following additions:

Add the following reference:
“MySql.Data” from the dll file attached, which is “MySQL.Data.dll”
On top of the page write down:
Using MySql.Data.MySqlClient;

The remaining code will be the same as stated above.

Connection Strings

Connection String for Oracle
Server=localhost;Data Source=nameofdatasource;User Id=username;Password=password;
Connection String for MySQL
Server=localhost;Database=nameofdatasource;Uid=username;Pwd=password;


Adding a Reference
On the top right of the page there is a Solution Explorer
In the solution explorer, there is a folder named References.
Right click on References and click “Add Reference”
Scroll Down, select your reference and click OK

Creating Crystal Reports

Making a Crystal Report

Make a new windows application.

Click on Project -> Add new item ->Data -> DataSet
Open the DataSet
Right click on the empty space in the dataset -> Add -> Data Table

Write any name of that new Data Table
Right Click on that data table and add a new column
Make the columns which you want to display is the Crystal Report (e.g if your query will be “select name, roll from student” then only make two columns and name them “name” and “roll” and make their data types similar to that which are in the database)

Click on Project -> Add new item -> Reporting -> Crystal Report
Click Ok
Click on Project Data -> ADO.Net Data Sets -> Crystal_Report.DataSet1 -> ”Your Data Set Name” -> “>” -> Next -> “>>” -> Finish
The crystal report template will open. You can now format it according to your needs

Make a new dialog Box
Go to toolbox -> Reporting -> Crystal Report Viewer
Drag and drop this “CrystalReportViewer” onto the form
Go to its properties -> Dock -> Fill

Go to the on_Load event of this form
In the top right, there will be a solution explorer, over there right click on References -> Add Reference
Click the browse tab, and find the file “MySql.Data.dll” in your computer (You should download it, because this file is usually missing)
In the code, on top of the page, write “using MySql.Data.MySqlClient;”
Use the following code for database connectivity:

OracleDataAdapter da = new OracleDataAdapter(query, "Server=localhost;Data Source=nameofdatasource;User Id=username;Password=password;");
DataSet ds = new DataSet();
da.Fill(ds, "Name of your datatable that you made in the dataset");
CrystalReport1 rpt = new CrystalReport1();
rpt.SetDataSource(ds);
CrystalReportViewer1.ReportSource = rpt;

Now, open this dialog box from another dialog box, and as soon as this dialog will open, a crystal report would be displaying your result.

Note that the columns of the dataset that you made should be same as the columns that you display in your query e.g if your query says “select name, roll from student”, then your dataset should contain only two columns, that will be the “name” and “roll”, and there datatype should be similar to that of the name and roll of the database.
If there are any errors in the crystal report, then make it again, because the crystal report does have known issues that are handled be making the crystal reports again.