ADo.net

ADO.
Net
Pretty much every application deals with data in some manner, whether that data comes from memory,
databases, XML files, text files, or something else. The location where we store the data can be called as a Data
Source or Data Store where a Data Source can be a file, database, or indexing server etc.
Programming Languages cannot communicate with Data Sources directly because each Data Source
adopts a different Protocol (set of rules) for communication, so to overcome this problem long back Microsoft has
introduced intermediate technologies like JET, Odbc and Oledb which works like bridge between the Applications
and Data Sources to communicate with each other.
The Microsoft Jet Database Engine is a database engine on which several Microsoft products have been
built. A database engine is the underlying component of a database, a collection of information stored on a
computer in a systematic way. The first version of Jet was developed in 1992, consisting of three modules which
could be used to manipulate a database. JET stands for Joint Engine Technology, sometimes being referred to as
Microsoft JET Engine or simply Jet. Microsoft Access and Excel uses Jet as their underlying database engine. Over
the years, Jet has become almost synonymous with Microsoft Access, to the extent where many people refer to a
Jet database as an "Access database". MS developed Jet database system, a C-based interface allowing applications
to access that data, and a selection of driver DLLs that allowed the same C interface to redirect input and output to
databases. However, Jet did not use SQL; the interface was in C and consisted of data structures and function calls.
ODBC (Open Database Connectivity) is a standard C programming language middleware API for accessing
database management systems (DBMS). ODBC accomplishes DBMS independence by using an ODBC driver as a
translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC
driver manager with which it is linked, and the driver passes the query to the DBMS. An ODBC driver will be
providing a standard set of functions for the application to use, and implementing DBMS-specific functionality. An
application that can use ODBC is referred to as "ODBC-compliant". Any ODBC-compliant application can access any
DBMS for which a driver is installed. Drivers exist for all major DBMSs as well as for many other data sources like
Microsoft Excel, and even for Text or CSV files. ODBC was originally developed by Microsoft during the early 1990s.
OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB), an API
designed by Microsoft, allows accessing data from a variety of sources in a uniform manner. The API provides a set
of interfaces implemented using the Component Object Model (COM). Microsoft originally intended OLE DB as a
higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-
relational databases, such as object databases and spreadsheets that do not necessarily implement SQL. OLE DB is
conceptually divided into consumers and providers. The consumers are the applications that need access to the
data, and the providers are the software components that implement the interface and thereby provide the data to
the consumer. An OLE DB provider is a software component enabling an OLE DB consumer to interact with a data
source. OLE DB providers are alike to ODBC drivers or JDBC drivers for Java. OLE DB providers can be created to
access such simple data stores as a text file and spreadsheet, through to such complex databases as Oracle,
Microsoft SQL Server, and many others. It can also provide access to hierarchical data stores.
DAO’s, RDO’s and ADO’s in Visual Basic Language:

Visual Basic Language used DAO’s, RDO’s and ADO’s for data source communication without having to deal
with the comparatively complex JET or ODBC or OLEDB API.
12
ADO.Net:
It is a set of types that expose data access services to the .NET programmer. ADO.NET provides
functionality to developers writing managed code similar to the functionality provided to native COM developers
by ADO. ADO.NET provides consistent access to data sources such as Microsoft SQL Server, as well as data sources
exposed through OLE DB and XML. Data-sharing consumer applications can use ADO.NET to connect to these data
sources and retrieve, manipulate, and update data. It is an integral part of the .NET Framework, providing access to
relational data, XML, and application data. ADO.NET supports a variety of development needs, including the
creation of front-end database clients and middle-tier business objects used by applications or Internet browsers.
ADO.Net provides libraries for Data Source communication under the following namespaces:
 System.Data
 System.Data.Odbc
 System.Data.Oledb
 System.Data.SqlClient
 System.Data.OracleClient
Note: System.Data, System.Data.Odbc, System.Data.Oledb and System.Data.SqlClient namespaces are under the
assembly System.Data.dll whereas System.Data.OracleClient is under System.Data.OracleClient.dll assembly.
System.Data: types of this namespace are used for holding and managing of data on client machines. This
namespace contains following set of classes in it: DataSet, DataTable, DataRow, DataColumn, DataView,
DataRelation etc.
System.Data.Odbc: types of this namespace can communicate with any Data Source using Un-Managed Odbc
Drivers.
System.Data.Oledb: types of this namespace can communicate with any Data Source using Oledb Providers (Un-
Managed COM Providers).
System.Data.SqlClient: types of this namespace can purely communicate with Sql Server database only using
SqlClient Provider (Managed .Net Provider).
System.Data.OracleClient: types of this namespace can purely communicate with Oracle database only using
OracleClient Provider (Managed .Net Provider).
All the above 4 namespaces contains same set of types as following: Connection, Command, DataReader,
DataAdapter, CommandBuilder etc, but here each class is referred by prefixing with their namespace before the
class name to discriminate between each other as following:
OdbcConnection OdbcCommand OdbcDataReader OdbcDataAdapter OdbcCommandBuilder

OledbConnection OledbCommand OledbDataReader OledbDataAdapter OledbCommandBuilder
SqlConnection SqlCommand SqlDataReader SqlDataAdapter SqlCommandBuilder
OracleConnection OracleCommand OracleDataReader OracleDataAdapter OracleCommandBuilder
12
Performing Operations on a DataSource: Each and every operation we perform on a Data Source involves in 3
steps, like:
 Establishing a connection with the data source.
 Sending a request to the data source in the form of an SQL Statement.
 Capturing the results given by the data source.
Establishing a Connection with Data Source:

It's a process of opening a channel for communication between Application and Data Source that is
present on a local or remote machine to perform any operations. To open the channel for communication we use
the Connection class.
Constructors of the Class:

Connection() Connection(string ConnectionString)
Note: ConnectionString is a collection of attributes that are required for connecting with a DataSource, those are:
 Provider
 Data Source
 User Id and Password
 Database or Initial Catalog
 Integrated Security
 DSN
Provider: as discussed earlier provider is required for connecting with any data source, so we have a different
provider available for each data source.
Oracle: Msdaora Sql Server: SqlOledb
MS-Access or MS-Excel: Microsoft.Jet.Oledb.4.0 MS-Indexing Server: Msidxs
Data Source: it is the name of target server to which we want to connect with but it is optional when the data
source is on a local machine and in case of a file we need to specify path of the file.
User Id and Password: as db's are secured places for storing data, to connect with them we require a valid user id
and password.
Oracle: Scott/tiger Sql Server: Sa/<pwd>
Database or Initial Catalog: these attributes are used while connecting with Sql Server Database to specify the
name of database we want to connect with.
Integrated Security: this attribute is also used while connecting with Sql Server Database only to specify that we
want to connect with the Server using Windows Authentication. In this case we should not again use User Id and
Password attributes.
DSN: this attribute is used to connect with data sources by using Odbc Drivers.
Connection String for Oracle: "Provider=Msdaora;User Id=Scott;Password=tiger[;Data Source=<server name>]"
Connection String for Sql Server: "Provider=SqlOledb;User Id=Sa;Password=<pwd>;Database=<db name>[;Data

Source=<server name>]"
Note: in case of Windows Authentication in place of User Id and Password attributes we need to use Integrated
Security = SSPI (Security Support Provider Interface).
12
Members of Connection class:
1. Open(): a method which opens a connection with data source.
2. Close(): a method which closes the connection that is open.
3. State: an enumerated property which is used to get the status of connection.
4. ConnectionString: a property which is used to get or set a connection string that is associated with the
connection object.
The Object of class Connection can be created in any of the following ways:
Connnection con = new Connection(); con.ConnectionString = "<connection string>";
or
Connection con = new Connection("<connection string>");
Testing the process of establishing a connection: open a new project of type Windows Forms Application and
name it as DBOperations. Place 2 buttons on the form and set their caption as “Connect with Oracle using OLEDB
Provider” and “Connect with Sql Server using OLEDB Provider”. Now go to code view and write the following code:
using System.Data.OleDb;
Declarations: OledbConnection ocon, scon;
Under Connect with Oracle using OLEDB Provider:
ocon = new OleDbConnection(“Provider=Msdaora;User Id=Scott;Password=tiger;Data Source=<server name>");
ocon.Open(); MessageBox.Show(ocon.State.ToString()); ocon.Close(); MessageBox.Show(ocon.State.ToString());
Under Connect with Sql Server using Oledb Provider:
scon = new OleDbConnection(); scon.ConnectionString =
"Provider=SqlOledb;User Id=Sa;Password=<pwd>;Database=Master;Data Source=<server name>";
scon.Open(); MessageBox.Show(scon.State.ToString()); scon.Close(); MessageBox.Show(scon.State.ToString());
Sending request to Data Source as a Sql Statement: In this process we send a request to Data Source by specifying
the type of action we want to perform using a Sql Statement like Select, Insert, Update, and Delete or by calling a
Stored Procedure. To send and execute those statements on data source we use the class Command.
Constructors of the class: Command() Command(string CommandText, Connection con)

Note: CommandText means it can be any Sql Stmt like Select or Insert or Update or Delete Stmts or Stored
Procedure Name.
Properties of Command Class:

1. Connection: sets or gets the connection object associated with command object.
2. CommandText: sets or gets the sql statement or SP name associated with command object.
The object of class Command can be created in any of the following ways:
Command cmd = new Command(); cmd.Connection = <con>; cmd.CommandText = "<Sql Stmt or SP Name>";
or
Command cmd = new Command("<Sql Stmt or SP Name>", <con>);
Methods of Command class:

 ExecuteReader() -> DataReader
 ExecuteScalar() -> object
 ExecuteNonQuery() -> int
Note: after creating object of Command class we need to call any of these 3 methods to execute that statement.
12
Use ExecuteReader() method when we want to execute a Select Statement that returns data as rows and
columns. The method returns an object of class DataReader which holds data that is retrieved from data source in
the form of rows and columns.
Use ExecuteScalar() method when we want to execute a Select Statement that returns a single value
result. The method returns result of the query in the form of an object.
Use ExecuteNonQuery() method when we want to execute any SQL statement other than select, like
Insert or Update or Delete etc. The method returns an integer that tells the no. of rows affected by the statement.
Note: The above process of calling a suitable method to capture the results is our third step i.e. capturing the
results given by data source.
Accessing data from a DataReader: DataReader is a class which can hold data in the form of rows and columns, to
access data from DataReader it provides the following methods:
1. GetName(int columnindex) -> string
Returns name of the column for given index position.
2. Read() -> bool
Moves record pointer from the current location to next row and returns a bool value which tells whether the row
to which we have moved contains data in it or not, that will be true if data is present or false if data is not present.
3. GetValue(int coloumnindex) -> object
Used for retrieving column values from the row to which pointer was pointing by specifying the column index
position. We can also access the row pointed by pointer in the form of a single dimensional array also, either by
specifying column index position or name, as following:
<DR>[column index] -> object
<DR>[column name] -> object
Add a new Windows Form under the project and design it as following:
using System.Data.OleDb;
Declarations: OleDbConnection con; OleDbCommand cmd; OleDbDataReader dr;
Under Form Load: con=new OleDbConnection(“Provider=Msdaora;User Id=Scott;Password=tiger”);
cmd = new OleDbCommand(“Select Deptno, Dname, Loc From Dept”, con); con.Open(); dr = cmd.ExecuteReader();
label1.Text = dr.GetName(0); label2.Text = dr.GetName(1); label3.Text = dr.GetName(2); ShowData();
private void ShowData() {
if(dr.Read()) {
textBox1.Text = dr.GetValue(0).ToString(); textBox2.Text = dr[1].ToString(); textBox3.Text = dr[“Loc”].ToString();
}
else { MessageBox.Show(“Last record of the table.”); }
}
Under Next Button: ShowData();
Under Close Button: if(con.State != ConnectionState.Closed) { con.Close(); } this.Close();
12
Working with Sql Server
Sql Server is a collection of databases, where a database is again collection of various objects like tables,
views, procedures etc.; users of Sql Server can be owner of 1 or more databases at a time, so while connecting with
sql server from a .net application within the connection string we need to specify name of the database we want to
connect either by using Database or Initial Catalog attributes.
Sql Server provides 2 different modes of authentication for connecting with the DB Server those are:
1. Windows Authentication
2. Sql Server Authentication
When a user connects through windows authentication, Sql Server validates the account name and
password using the windows principal token in the operating system; this means that the user identity is confirmed
by windows, Sql Server does not ask for the password and does not perform the identity validation. When using Sql
Server authentication, logins are created in sql server that is not based on windows user accounts, both the user
name and password are created by using sql server and stored in sql server database. Users connecting with sql
server authentication must provide their credentials every time they connect with DB Server.
Note: if we want to connect using windows authentication mode, within the connection string in the place of User
Id and Password attributes use “Integrated Security=SSPI” attributes.
Connecting String for Sql Server Authentication:

“Provider=SqlOledb;User Id=Sa;Password=<pwd>;Database=<dbname>[;Data Source=<server name>]”
Connecting String for Windows Authentication:

“Provider=SqlOledb;Integrated Security=SSPI;Database=<dbname>[;Data Source=<server name>]”
Creating a database on Sql Server:

Go to Start Menu -> Programs -> MS Sql Server -> Sql Server Management Studio, open it and provide the
authentication details to login. Once the studio is opened in the LHS we find a window “Object Explorer”, in that
right click on the node Databases, select “New Database” that opens a window asking for the name, enter the
name as: <DB Name>, click ok which adds the database under databases node. Now expand the database node,
right click on Tables node and select “New Table” which opens a window asking for column names and data types
enter the following:
Eno (Int), Ename (Varchar), Job (Varchar), Salary (Money), Photo (Image), Status (Bit)
Now select Eno Column, right click on it and select the option “Set Primary Key” and make it as an identity
or key column of the table. Select Status column, go to its properties in the bottom and set “Default value or
Binding” property as 1, which takes the default value for status column as true. Click on the save button at top of
the studio which will prompt for table name enter name as “Employee” and click Ok which adds the table under
tables node. Now right click on the table created and select “Edit” which opens a window, in that enter the data we
want ignoring Photo and Status columns. Close the studio.
Note: We can connect with Sql Server from .net applications either by using Oledb or SqlClient classes also. If using
SqlConnection or OracleConnection classes to connect with databases then connection string doesn’t require
Provider attribute to be specified as these classes are designed specific for those databases.
12
Add a new form in the project and design it as following:
Set Read-only Property as True
Set Enabled Property as False
using System.Data.SqlClient;
Declarations: SqlConnection con; SqlCommand cmd; SqlDataReader dr; string SqlStr;
Under Form Load: con = new SqlConnection("User Id=Sa;Password=<Pwd>;Database=<DB Name>;Data
Source=<Server Name>"); cmd = new SqlCommand(); cmd.Connection = con; con.Open(); LoadData();
private void LoadData() {
cmd.CommandText = "Select Eno, Ename, Job, Salary, Status From Employee Order By Eno";
dr = cmd.ExecuteReader(); ShowData();
}
if (dr.Read()) {
textBox1.Text = dr[0].ToString(); textBox2.Text = dr[1].ToString(); textBox3.Text = dr[2].ToString();
textBox4.Text = dr[3].ToString(); checkBox1.Checked = Convert.ToBoolean(dr[4]);
}
else { MessageBox.Show("No data exists."); }
}
Under Next Button:
ShowData();
Under New Button:
textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = ""; checkBox1.Checked = false;
dr.Close(); cmd.CommandText = "Select IsNull(Max(Eno), 1000) + 1 From Employee";
textBox1.Text = cmd.ExecuteScalar().ToString(); btnInsert.Enabled = true; textBox2.Focus();
private void ExecuteDML() {
DialogResult d = MessageBox.Show(“Are you sure of executing the below Sql Statement?\n\n" + SqlStr,
"Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (d == DialogResult.Yes) {
cmd.CommandText = SqlStr; int count = cmd.ExecuteNonQuery();
if (count > 0) { MessageBox.Show("Statement executed successfully"); }
else { MessageBox.Show("Statement failed execution"); }
LoadData();
}
}
Under Close Button: if (con.State != ConnectionState.Closed) { con.Close(); } this.Close();
12
Under Insert Button:
SqlStr = "Insert Into Employee (Eno, Ename, Job, Salary, Status) Values(" + textBox1.Text + ", '" + textBox2.Text + "', '"
+ textBox3.Text + "', " + textBox4.Text + ", " + Convert.ToInt32(checkBox1.Checked) + ")";
or
SqlStr = String.Format("Insert Into Employee (Eno, Ename, Job, Salary, Status) Values({0}, '{1}', '{2}', {3}, {4})",
textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, Convert.ToInt32(checkBox1.Checked));
or
SqlStr = $"Insert Into Employee (Eno, Ename, Job, Salary, Status) Values ({textBox1.Text}, '{textBox2.Text}',
'{textBox3.Text}', {textBox4.Text} , {Convert.ToInt32(checkBox1.Checked)})";
or
SqlStr="Insert Into Employee (Eno, Ename, Job, Salary, Status) Values (@Eno, @Ename, @Job, @Salary, @Status)";
cmd. Parameters.Clear(); cmd.Parameters.AddWithValue("@Eno", textBox1.Text);
cmd.Parameters.AddWithValue("@Ename", textBox2.Text);
cmd.Parameters.AddWithValue("@Job", textBox3.Text);
cmd.Parameters.AddWithValue("@Salary", textBox4.Text);
cmd.Parameters.AddWithValue("@Status", Convert.ToInt32(checkBox1.Checked));
ExecuteDML(); btnInsert.Enabled = false;

Under Update Button:
SqlStr = "Update Employee Set Ename='" + textBox2.Text + "', Job='" + textBox3.Text + "', Salary=" + textBox4.Text +
", Status=" + Convert.ToInt32(checkBox1.Checked) + " Where Eno=" + textBox1.Text;
or
SqlStr = String.Format("Update Employee Set Ename='{0}', Job='{1}', Salary={2}, Status={3} Where Eno={4}",
textBox2.Text, textBox3.Text, textBox4.Text, Convert.ToInt32(checkBox1.Checked), textBox1.Text);
or
SqlStr = $"Update Employee Set Ename='{textBox2.Text}', Job='{textBox3.Text}', Salary={textBox4.Text},
Status={Convert.ToInt32(checkBox1.Checked)} Where Eno={textBox1.Text}";
or
SqlStr = "Update Employee Set Ename=@Ename, Job=@Job, Salary=@Salary, Status=@Status Where Eno=@Eno";
cmd. Parameters.Clear(); cmd.Parameters.AddWithValue("@Ename", textBox2.Text);
cmd.Parameters.AddWithValue("@Status", Convert.ToInt32(checkBox1.Checked));
cmd.Parameters.AddWithValue("@Eno", textBox1.Text);
dr.Close(); ExecuteDML();
Under Delete Button:
SqlStr = "Delete From Employee Where Eno=" + textBox1.Text; or
SqlStr = String.Format("Delete From Employee Where Eno={0}", textBox1.Text); or
SqlStr = $"Delete From Employee Where Eno={textBox1.Text}"; dr.Close(); ExecuteDML();
or
SqlStr = "Delete From Employee Where Eno=@Eno";
cmd. Parameters.Clear(); cmd.Parameters.AddWithValue("@Eno", textBox1.Text);
dr.Close(); ExecuteDML();
12
DataReader: it's a class designed for holding the data on client machines in the form of Rows and Columns.
Features of DataReader:
1. Faster access to data from the data source as it is connection oriented.
2. Can hold multiple tables in it at a time. To load multiple tables into a DataReader pass multiple select
statements as arguments to command separated by a semi-colon.
E.g.: Command cmd = new Command("Select * From Student;Select * From Teacher", con);
DataReader dr = cmd.ExecuteReader();
Note: use NextResult() method on data reader object to navigate from current table to next table.
E.g.: dr.NextResult(); -> bool
Drawbacks of DataReader:
1. As it is connection oriented requires a continuous connection with data source while we are accessing the
data, so there are chances of performance degradation if there are more no. of clients accessing data at
the same time.
2. It gives forward only access to the data i.e. allows going either to next record or table but not to previous
record or table.
3. It is a read only object which will not allow any changes to data that is present in it.
Dis-Connected Architecture: ADO.Net supports 2 different models for accessing data from Data Sources:
1. Connection Oriented Architecture
2. Disconnected Architecture
In the first case we require a continuous connection with the data source for accessing data from it, in this
case we use DataReader class for holding the data on client machines, where as in the 2nd case we don’t require a
continuous connection with data source for accessing of the data from it, we require the connection only for
loading the data from data source and here DataSet class is used for holding the data on client machines.
Working with DataSet
DataSet: It's a class present under System.Data namespace designed for holding and managing of the data on client
machines apart from DataReader. DataSet class provides the following features:
1. It is designed in disconnected architecture which doesn't require any permanent connection with the data
source for holding of data.
2. It provides scrollable navigation to data which allows us to move in any direction i.e. either top to bottom
or bottom to top.
3. It is updatable i.e. changes can be made to data present in it and those changes can be sent back to DB.
4. DataSet is also capable of holding multiple tables in it.
5. It provides options for searching and sorting of data that is present under it.
6. It provides options for establishing relations between the tables that are present under it.
Using DataSet's: The class which is responsible for loading data into DataReader from a DataSource is Command, in
the same way DataAdapter class is used for communication between DataSource and DataSet.
DataReader <= Command => DataSource
DataSet <=> DataAdapter <=> DataSource
Constructors of DataAdapter class:

DataAdapter() DataAdapter(Command SelectCommand) DataAdapter(string SelectCommand, Connection con)
Note: Select Command means it can be a Select Stmt or a Stored Procedure which contains a Select Stmt.
12
Instance of DataAdapter class can be created in any of the following ways:
Command cmd = new Command(“<Select Stmt or SPName>”, <instance of conn. class>);
DataAdapter da = new DataAdapter(); da.SelectCommand = cmd;
or
Command cmd = new Command(“<Select Stmt or SPName>”, <instance of conn. class>);
DataAdapter da = new DataAdapter(cmd);
or
DataAdapter da = new DataAdapter(“<Select Stmt or SPName>”, <instance of conn. class>);
Methods of DataAdapter:
Fill(DataSet ds, string tableName) Update(DataSet ds, string tableName)
Note: Internally DataAdapter is a collection of 4 commands that are associated with a single table, those are:
-Select Command -Insert Command -Update Command -Delete Command
When we call Fill method on DataAdapter following actions takes place internally:
 Opens a connection with the Data Source.
 Executes the SelectCommand present under it on the DataSource and loads data from table to DataSet.
 Closes the connection.
As we are discussing DataSet is updatable, we can make changes to the data that is loaded into it like adding,
modifying and deleting of records, after making changes to data in DataSet if we want to send those changes back
to DataSource we need to call Update method on DataAdapter, which performs the following:
 Re-opens a connection with the Data Source.
 Changes that are made to data in DataSet will be sent back to corresponding table, where in this process it
will make use of Insert, Update and Delete commands of DataAdapter.
 Closes the connection.
Accessing data from DataSet: Data Reader’s provides pointer based access to the data, so we can get data only in a
sequential order whereas DataSet provides index based access to the data, so we can get data from any location
randomly. DataSet is a collection of tables where each table is represented as a class DataTable and identified by its
index position or name. Every DataTable is again collection of Rows and collection of Columns where each row is
represented as a class DataRow and identified by its index position and each column is represented as a class
DataColumn and identified by its index position or name.
 Accessing a DataTable from DataSet: <dataset>.Tables[index] or <dataset>.Tables[name]
E.g.: ds.Tables[0] or ds.Tables["Employee"]
 Accessing a DataRow from DataTable: <datatable>.Rows[index]
E.g.: ds.Tables[0].Rows[0]
 Accessing a DataColumn from DataTable: <datatable>.Columns[index] or <datatable>.Columns[name]
E.g.: ds.Tables[0].Columns[0] or ds.Tables[0].Columns["Eno"]
 Accessing a Cell from DataTable: <datatable>.Rows[row][col]
E.g.: ds.Tables[0].Rows[0][0] or ds.Tables[0].Rows[0]["Eno"]
12
Add a new form in the project, design it as below, and then add reference of Microsoft.VisualBasic
assembly from Framework tab of add reference window and write the code:
Set Read-only Property as True
using System.Data.SqlClient; using static Microsoft.VisualBasic.Interaction;

Declarations: SqlConnection con; SqlDataAdapter da; SqlCommandBuilder cb; DataSet ds; int rno = 0;
Under Form Load:
con = new SqlConnection("User Id=Sa;Password=<pwd>;Database=<DB Name>;Data Source=<Server Name>");
da = new SqlDataAdapter("Select Eno, Ename, Job, Salary From Employee Order By Eno", con);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
ds = new DataSet(); da.Fill(ds, "Employee"); ShowData();
if (ds.Tables[0].Rows[rno].RowState != DataRowState.Deleted) {
textBox1.Text=ds.Tables[0].Rows[rno][0].ToString(); textBox2.Text=ds.Tables[0].Rows[rno][1].ToString();
textBox3.Text=ds.Tables[0].Rows[rno][2].ToString(); textBox4.Text=ds.Tables[0].Rows[rno][3].ToString();
}
else
MessageBox.Show("Deleted row data cannot be accessed.");
}
Under First Button: rno = 0; ShowData();
Under Prev Button:
if (rno > 0) {
rno -= 1; ShowData();
}
else
MessageBox.Show("First record of the table.");
Under Next Button:
if (rno < ds.Tables[0].Rows.Count - 1) {
rno += 1; ShowData();
}
else
MessageBox.Show("Last record of the table.");
Under Last Button: rno = ds.Tables[0].Rows.Count - 1; ShowData();
Under New Button:
textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = “”;
int Index = ds.Tables[0].Rows.Count - 1; //Finding the index position of last record.
int Eno = Convert.ToInt32(ds.Tables[0].Rows[Index][0]) + 1; //Finding the Eno of last record and adding 1 to it.
textBox1.Text = Eno.ToString(); textBox2.Focus();
12
Adding a DataRow to DataTable of DataSet:
To add a DataRow to the DataTable of DataSet adopt the following process:
1. Create a new row by calling the NewRow() method on DataTable.
2. Assign values to the new row by treating it as a single dimensional array.
3. Call the Rows.Add method on DataTable and add the row to DataRowCollection.
DataRow dr = ds.Tables[0].NewRow();
dr[0] = textBox1.Text; dr[1] = textBox2.Text; dr[2] = textBox3.Text; dr[3] = textBox4.Text;
ds.Tables[0].Rows.Add(dr); rno = ds.Tables[0].Rows.Count - 1;
MessageBox.Show("DataRow added to DataTable of DataSet.");
Updating a DataRow in DataTable of DataSet:
To update an existing DataRow in DataTable of DataSet we need to re-assign the modified values back to
the DataRow in data table, so that the old values get modified with new values.
ds.Tables[0].Rows[rno][1] = textBox2.Text; ds.Tables[0].Rows[rno][2] = textBox3.Text;
ds.Tables[0].Rows[rno][3] = textBox4.Text; MessageBox.Show("DataRow updated in DataTable.");
Deleting a DataRow in DataTable of DataSet:
To delete an existing DataRow in DataTable of DataSet call Delete() method pointing to the row that has to
be deleted on DataTable.
ds.Tables[0].Rows[rno].Delete(); MessageBox.Show("DataRow deleted in DataTable of DataSet.");
Saving changes made in DataTable of DataSet back to DataBase:
If we want to save changes made in DataTable of DataSet back to Database we need to call Update
method on DataAdapter by passing the DataSet which contains modified values as a parameter. If Update method
of DataAdapter has to work it should contain 3 commands under it i.e. Insert, Update and Delete, these 3
commands have to be written by the programmers explicitly or can be generated implicitly with the help of
CommandBuilder class. CommandBuilder class constructor if given with DataAdapter that contains a
SelectCommand in it will generate the remaining 3 commands.
Note: CommandBuilder can generate update and delete commands for a given select command only if the
database table contains Primary Key Constraints on it.
Under Save To DB Button:
cb = new SqlCommandBuilder(da); da.Update(ds, "Employee");
MessageBox.Show("Data saved to DB Server");
Under Close Button: this.Close();
Searching for a DataRow in DataTable of DataSet:
To search for a DataRow in DataTable of DataSet call Find method on DataRowCollection this searches for
the DataRow on Primary Key Column(s) of table and returns a Row.
Find(Object key) -> DataRow Find(Object[] keys) -> DataRow
Use the first method if the primary key constraint is present on a single column or else use the second
method if it is a composite primary key.
Note: if the Find method has to work we need to first load the Primary Key information of database table into
DataSet by setting the property value as “AddWithKey” for MissingSchemanAction of DataAdapter.
12
Under Search Button: int Eno = 0;
string value = Interaction.InputBox("Enter Employee No. to search", "Employee Search", "", 150, 150);
if (int.TryParse(value, out Eno)) {
DataRow dr = ds.Tables[0].Rows.Find(Eno);
if (dr != null) {
rno = ds.Tables[0].Rows.IndexOf(dr);
textBox1.Text = dr[0].ToString(); textBox2.Text = dr[1].ToString();
}
else { MessageBox.Show("Employee does not exists for given Employee No."); }
}
else
MessageBox.Show("Enter integer value as Employee No.");
Configuration Files
While developing applications if there are any values in application which requires changes in future,
should not be hard coded i.e. should not be maintained as static values within the application, because if any
changes are required for those values in future client will not be able to make changes because they will not have
the source code of application for modification. To overcome this problem we need to identify those values and put
them under a special file known as Configuration File, it’s an XML file which stores values in it in the form of
Key/Value pairs. The values that are present under configuration file can be read from applications in runtime. We
store values like Company Name, Address, Phone No, Fax No, connection strings etc., in these files. When an
application is installed on the client machines along with it the configuration file also will be installed there and
because the configuration file is a text file clients can edit those files and make modification to the values under
them at any time and those values will be taken into the application for execution.
Storing values under configuration file: By default the file comes with a tag <configuration></configuration>, all
the values must be present under that tag only by maintaining them under different sections as following:
<appSettings>
<add key="Cname" value="Naresh I Technologies"/>
<add key="Address" value="Ameerpet, Hyderabad - 38"/>
<add key="Phone" value="23746666"/>
<add key="Email" value="m.bangarraju@gmail.com"/>
</appSettings>
Reading configuration file values from applications: to read configuration file values from applications we are
provided with a class ConfigurationManager within the namespace System.Configuration present under the
assembly System.Configuration.dll. To consume the class we need to first add reference of the assembly using the
“Add Reference” window and we find the assemblies under Framework Tab, after adding the reference of assembly
import the namespace and read the values as following:
ConfigurationManager.AppSettings.Get(string key) -> string (returns the value for given key as string)
To test the above process add a configuration file in the project and store values in it as shown above
within the <configuration>/<configurations> tags. Now add a new form in the project place a button on it setting
the Text as “Read Configuration Values” and write the following code in code view:
12
using System.Configuration;
Under Read Configuration Values Button:
string cname = ConfigurationManager.AppSettings.Get("Cname");
string addr = ConfigurationManager.AppSettings.Get("Address");
string phone = ConfigurationManager.AppSettings.Get("Phone");
string email = ConfigurationManager.AppSettings.Get("Email");
MessageBox.Show(cname + “\n” + addr + “\n” + phone + “\n” + email);
Storing Connection Strings under configuration files:
We can store the connection string values also in configuration files so that we don’t require to specify the
connection strings in all forms and whenever we want to change it we can make the change directly under the
configuration file. To store connection strings in the configuration file we are provided with a tag
<connectionStrings> same as <appSettings> tag, so we can maintain the connection string values in the
configuration file under <configuration> tag as following:
<connectionStrings>
<add name="OConStr" connectionString="User Id=Scott;Password=tiger;Data Source=<Server Name>"
providerName="Msdaora"/>
<add name="SConStr" connectionString ="User Id=Sa;Password=<your password>;
Database=<Your DB Name>;Data Source=<Server Name>" providerName="SqlOledb"/>
</connectionStrings>
Note: we can read the connection string values from our application as following:
string oconstr = ConfigurationManager.ConnectionStrings["OConStr"].ConnectionString;
string oprovider = ConfigurationManager.ConnectionStrings["OConStr"].ProviderName;
string sconstr = ConfigurationManager.ConnectionStrings["SConStr"].ConnectionString;
string sprovider = ConfigurationManager.ConnectionStrings["SConStr"].ProviderName;
DataGridView: this control is used for displaying the data in the form of a table i.e. rows and columns. To display
data in the control first we need to bind the DataTable of DataSet to the DataGridView control by using its
DataSource property as following: dataGridView1.DataSource = <DataTable>
DataGridView control has a specialty i.e. changes performed to data in it gets reflected directly to the data
of DataTable to which it was bound, so that we can update dataset back to database directly.
To test this process add a new Form in the project and place a DataGridView control on it setting its dock
property as top. Now place 2 buttons on the form setting the text as Save and Close and write the code:
using System.Data.SqlClient; using System.Configuration;

Declarations: SqlConnection con; SqlDataAdapter da; SqlCommandBuilder cb; DataSet ds; int rno = 0;
12
Under Form Load:
string constr = ConfigurationManager.ConnectionStrings["SConStr"].ConnectionString;
con = new SqlConnection(constr); ds = new DataSet();
da = new SqlDataAdapter("Select Eno, Ename, Job, Salary From Employee Order By Eno", con);
da.Fill(ds, "Employee"); dataGridView1.DataSource = ds.Tables[0];
Under Save Button:
cb = new SqlCommandBuilder(da); da.Update(ds, "Employee"); MessageBox.Show("Data saved to DB Server.");
Loading multiple tables into DataSet:
A DataSet can hold any no. of tables in it; if we want to load multiple tables into a Dataset we have 2 different
approaches.
1. Using a single DataAdapter we can load any no. of tables into the DataSet by changing the
SelectCommand of adapter each time after calling Fill and loading a table into DataSet. In this approach if
we want to make any changes to the data of dataset and send it back to database we can do it on last
table of the dataset only because an adapter can hold all the 4 commands only for a single table i.e. for
the last SelectCommand we have given only the required insert, update and delete commands will be
generated by CommandBuilder class.
2. Using a separate DataAdapter for each table being loaded into DataSet we can load multiple tables. In this
approach it will be possible to update all the tables data, back to the database because each table is using
an individual DataAdapter that will hold the required insert, update and delete commands for a table and
more over here each table can also be loaded from a different data source i.e. 1 table from Oracle 1 table
from Sql Server etc.
DataView: Just like we have Views in SQL, we have DataView object in ADO.Net. A DataView object represents a
customized view of DataTable object. Operations like Sorting; Searching can be performed on a DataView object. In
scenarios like retrieval of a subset of data from a DataTable, we can make use of DataView to get this data. Note
that the DefaultView property of a DataTable returns the default data view for the DataTable.
Using a DataView for filtering or sorting the data under DataTable:

Step1: Create a reference of class DataView by calling DefaultView property on the DataTable which will return a
DataView reference with same structure of the table on which the property is called.
E.g: DataView dv = ds.Tables[“Emp”].DefaultView;
Step2: Specify a condition for filter by making use of the RowFilter property or specify a column for sorting the data
using Sort property of DataView class.
E.g: dv.RowFilter = “Job = ‘Manager’”; dv.RowFilter = “Sal > 2500”;
dv.RowFilter = “Job = ‘Manager’ And Sal > 2500”; dv.RowFilter = “Job = ‘Manager’ Or Sal > 2500”;
dv.Sort = “Sal”; or dv.Sort = “Sal Desc”; or dv.Sort = “Sal, Comm”; or dv.Sort = “Sal, Comm Desc”;
Loading multiple tables into a DataSet using single DataAdapter and filtering the data using DataView:
Add a new form in the project, place a ComboBox control at top center, place a DataGridView control
below and write the following code after adding the reference of System.Data.OracleClient.dll assembly.
using System.Data.OracleClient; using System.Configuration;
Declarations: OracleConnection con; OracleDataAdapter da; DataSet ds; bool flag = false;
Under Form Load: string constr = ConfigurationManager.ConnectionStrings["OConStr"].ConnectionString;
con = new OracleConnection(constr); ds = new DataSet();
da = new OracleDataAdapter(“Select * from Dept”, con); da.Fill(ds, “Dept”);
da.SelectCommand.CommandText = “Select * From Emp”; da.Fill(ds, “Emp”);
12
comboBox1.DataSource = ds.Tables[“Dept”]; comboBox1.DisplayMember = “Dname”;
comboBox1.ValueMember = “Deptno”; comboBox1.SelectedIndex = -1; comboBox1.Text = “Select a Department.”;
dataGridView1.DataSource = ds.Tables[“Emp”]; flag = true;
Under ComboBox SelectedIndexChanged:
If (flag) {
DataView dv = ds.Tables[“Emp”].DefaultView; dv.RowFilter = “Deptno=” + comboBox1.SelectedValue;
dv.Sort = “Sal” or dv.Sort = “Sal Desc”; or dv.Sort = “Sal, Comm”; or dv.Sort = “Sal, Comm Desc”;
}
Note: Just like we can bind a DataTable to DataGridView control in the same way it can also be bound to ComboBox
and ListBox controls using DataSource property, but these controls even if bound with the table they can display
only a single column. So using DisplayMember property of the controls we need to specify which column has to be
displayed. We can also bind another column of the table using ValueMember property of the controls but that
column values will not be visible to end user where as we can access them in code using SelectedValue property of
control for the selected DisplayMember.
Loading multiple tables into a DataSet from different DataSources using different DataAdapter’s:
Add a new form in the project, place a SplitContainer on it which comes with 2 panels in it. Now place a
button on each panel and set the dock property of button as top. Set the caption of the first button as “Save Data
to Sql Server” and caption of second button as “Save Data to Oracle”. Then add a DataGridView control on each
panel and set their dock property as Fill. Then write the following code:
using System.Data.SqlClient; using System.Data.OracleClient; using System.Configuration;
Declarations:
SqlConnection scon; SqlDataAdapter sda; SqlCommandBuilder scb;
OracleConnection ocon; OracleDataAdapter oda; OracleCommandBuilder ocb; DataSet ds;
Under Form Load:
string sconstr = ConfigurationManager.ConnectionStrings["SConStr"].ConnectionString;
string oconstr = ConfigurationManager.ConnectionStrings["OConStr"].ConnectionString;
scon=new SqlConnection(sconstr); sda=new SqlDataAdapter("Select Eno,Ename,Job,Salary From Employee", scon);
ocon = new OracleConnection(oconstr); oda = new OracleDataAdapter("Select * From Salgrade", ocon);
ds = new DataSet(); sda.Fill(ds, "Employee"); oda.Fill(ds, "Salgrade");
dataGridView1.DataSource = ds.Tables["Employee"]; dataGridView2.DataSource = ds.Tables["Salgrade"];
Under Save Data to Sql Server Button:
scb=new SqlCommandBuilder(sda);sda.Update(ds, "Employee");MessageBox.Show("Data saved to Sql Server DB.");
Under Save Data to Oracle Button:
ocb=new OracleCommandBuilder(oda);oda.Update(ds, "Salgrade");MessageBox.Show("Data saved to Oracle DB.");
DataRelation:
DataRelation is used to relate two DataTable objects to each other through DataColumn objects. For example,
in a Dept/Emp relationship, the Dept table is the parent and the Emp table is the child of the relationship. This is
similar to a primary key/foreign key relationship. Relationships are created between matching columns in the
parent and child tables. That is, the DataType value for both columns must be identical.
To use foreign key constraint we need a parent table that contains master data and a child table that contains
detailed data corresponding to master data. Parent table should contain a Reference Key Column with a Primary
Key or Unique Key Constraints imposed on it, and child table should contain a Foreign Key Column with Foreign Key
Constraint imposed on it which refers into the values of Reference Key Column. If relationships are established
between tables following rules comes into picture:
12
1. Cannot store a value in the foreign key column of child table, provided the given value is not present in
reference key column of parent table.
2. Cannot delete a row from parent table provided the given reference key value of the record being deleted
has child records in the child table without addressing what to do with the corresponding child records.
3. Cannot update reference key value of the parent table provided the given reference key value being
updated has child records in the child table without addressing what to do with the corresponding child
records.
If we want to establish same type of relations between tables of a DataSet also, we can do it with the help
of DataRelation class. DataRelation (string relname, DataColumn RKcol, DataColumn FKcol)
After creating object of DataRelation it has to be added explictly to the DataSet under which tables were
present using Relations.Add method of DataSet. <dataset>.Relations.Add(DataRelation dr)
For deleting or updating reference key values in parent table if the reference key value has an child records
in the child table some rules comes into picture for delete and update known as DeleteRules and UpdateRules,
those are:
1. None: Cannot delete or update reference key value of parent table when corresponding child records exists in
child table, this rule is applied by default under DB's.
2. Cascade: In this case we can delete or update reference key values of parent table, but the corresponding child
records in child table will also be deleted or updated, this rule is applied by default in case of DataSet's.
3. Set Null: In this case also we can delete or update reference key values of parent table but the corresponding
child records foreign key value changes to null.
4. Set Default: This is same as Set Null, but in this case the corresponding child records foreign key value changes
to default value of the column.
Note: we need to apply required rule for delete or update on DataRelation using following statements:
<datarelation>.ChildKeyConstraint.DeleteRule = Rule.<rule>;
<datarelation>.ChildKeyConstraint.UpdateRule = Rule.<rule>;
To use SetDefault rule first we need to set a default value for foreign column using the following statement:
<datatable>.Columns[name].DefaultValue=<value>
Loading multiple tables into a DataSet and establishing relation between the tables:
Add a new form in the project, place a SplitContainer on it and change the Orientation property of the
control as Horizontal so that the panels under the SplitContainer will be horizontally aligned (default is vertical).
Place a DataGridView control on each panel and set their Dock property as Fill. Now write the following code:
Declarations: OracleConnection con; OracleDataAdapter da1, da2; DataSet ds; DataRelation dr;
Under Form Load: string constr = ConfigurationManager.ConnectionStrings[OSConStr"].ConnectionString;
con = new OracleConnection(constr); ds = new DataSet();
da1 = new OracleDataAdapter(“Select * From Dept”, con); da1.Fill(ds, "Dept");
da2 = new OracleDataAdapter(“Select * From Emp”, con); da2.Fill(ds, "Emp");
dr = new DataRelation("EmpDept", ds.Tables[“Dept”].Columns["Deptno"], ds.Tables[“Emp”].Columns["Deptno"]);
ds.Relations.Add(dr);
dr.ChildKeyConstraint.DeleteRule = Rule.None; dr.ChildKeyConstraint.UpdateRule = Rule.None;
dataGridView1.DataSource = ds.Tables["Dept"]; dataGridView2.DataSource = ds.Tables["Emp"];
Note: Use the following statement if required for setting a default value to foreign key column:
ds.Tables["Emp"].Columns["Deptno"].DefaultValue = 40;
Establishing relations between Controls of Form and displaying data in Parent/Child view using BindingSource:
12
It is possible to establish relations between controls of a Form and display the data in Parent/Child view
with the help of a class known as BindingSource to test this add a new form in the project, place a SplitContainer
on it and change the Orientation property as Horizontal. Place a DataGridView control on each panel and set their
Dock property as Fill. Now write the following code:

Declarations: OracleConnection con; OracleDataAdapter da1, da2; DataSet ds;
con = new OracleConnection(constr);
da1=new OracleDataAdapter("Select * From Dept", con); da2=new OracleDataAdapter("Select * From Emp", con);
ds = new DataSet(); da1.Fill(ds, "Dept"); da2.Fill(ds, "Emp");
ds.Relations.Add("MasterDetail",ds.Tables[0].Columns["Deptno"],ds.Tables[1].Columns["Deptno"]);
BindingSource bsDept = new BindingSource(); bsDept.DataSource = ds; bsDept.DataMember = "Dept";
BindingSource bsEmp = new BindingSource(); bsEmp.DataSource = bsDept; bsEmp.DataMember = "MasterDetail";
dataGridView1.DataSource = bsDept; dataGridView2.DataSource = bsEmp;
Displaying data in Parent/Child view using DataGrid Control:
It is possible to display the data in Parent/Child view with the help of a control known as DataGrid which is
available before 2.0 version of .net framework, but doesn’t support data manipulations like DataGridView control.
To test this add a new form in the project, open the toolbox right click on “Data Tab” and select “Choose Items”, in
the window opened select the control “DataGrid” and click ok which adds the control under “Data Tab”. Now place
a DataGrid control on form and write the following code:
Declarations: OracleConnection con; OracleDataAdapter da1, da2; DataSet ds;
con = new OracleConnection(constr);
da1=new OracleDataAdapter("Select * From Dept", con); da2=new OracleDataAdapter("Select * From Emp", con);
ds = new DataSet(); da1.Fill(ds, "Dept"); da2.Fill(ds, "Emp");
ds.Relations.Add("MasterDetail",ds.Tables[0].Columns["Deptno"],ds.Tables[1].Columns["Deptno"]);
dataGrid1.DataSource = ds;
Major Differences between DataSet and DataReader:
 DataSet is disconnected architecture while DataReader has live connection while reading data.
 If we want to cache data and pass to a different tier DataSet’s will be the best choice.
 If we want to move back while reading records, DataReader does not support this functionality.
 One of the biggest drawbacks of DataSet is speed as DataSet carry considerable overhead because of
relations; multiple tables’ etc speed is slower than DataReader, so always try to use DataReader where
ever possible as it’s meant especially for speedy performance.
Communication with Data Sources using ODBC Drivers
In the case of Oledb, SqlClient and OracleClient classes we use providers for Data Source communication
where as in case of Odbc classes we use drivers for Data Source communication. Providers will be on server side so
within the connection string if we specify the provider name along with other details like server name, user id,
password we can communication with data sources, whereas drivers will be sitting on client machines so to use
them first we need to configure the appropriate driver with data source and then from our application using Odbc
classes we should communicate with drivers, which will in turn communicate with the data sources.
Odbc offers different drivers for connecting with different data sources which comes along with OS, to use
them first we need to configure an appropriate driver to its specific data source by supplying all the required
12
connection details and they are stored internally with a name known as DSN (Data Source Name), this should be
specified by us only while configuring with the driver. After configuring the DSN, we can use that DSN in our .net
app so that Odbc Classes can talk with the Data Source making use of the driver that has been configured as
following:
OdbcConnection con = new OdbcConnection("Dsn=<name of the dsn we have created>");
Configuring a DSN: to configure DSN go to Control Panel -> Administrative Tools -> Data Sources (ODBC), click on it
to open 'ODBC Data Source Administrator' window. In the window opened click on Add button -> choose a driver
for Oracle or Sql Server or MS Excel etc., and click Finish, which opens a window in that first enter a name for DSN
and then supply connection details like User Id, Password, Database, Server etc., and Click finish, which adds DSN in
Data Source Administrator window.
Configuring a DSN with Sql Server and Oracle Databases:
Open ODBC Data Source Administrator window, click on Add button, select a driver for Sql Server and click
Finish button, which opens a window, in it enter the following details, Name: SqlDsn, Description: Connects with Sql
Server Database, Server: <Server Name>, click on Next button, select the RadioButton “Using Sql Server
Authentication”, enter the Login ID: <User Name>, Password: <Pwd>, click on Next button, select the CheckBox
“Change the default database to”, and select the Database to which we want to configure with below, click on Next
button and Click on Finish button which displays a window showing the connection details, click on Ok button
which adds the DSN under ODBC Data Source Administrator window.
Again click on Add button, select a driver for Oracle and click Finish button, which opens a window, in it
enter the following details, Data Source Name: OraDsn, Description: Connects with Oracle Database, TNS Service
Name: <Server Name>, User ID: Scott/tiger, click on Ok button which adds the DSN under ODBC Data Source
Administrator window.
Now add a new form in the Project place 2 buttons on it and set their caption as “Connect with Oracle
using ODBC Driver” and “Connect with Sql Server using ODBC Driver”. Go to code view and write the following
code:
using System.Data.Odbc;
Declarations: OdbcConnection ocon, scon;
Under Connect with Oracle using ODBC Driver: ocon = new OdbcConnection(“Dsn=OraDsn”);
ocon.Open(); MessageBox.Show(ocon.State.ToString()); ocon.Close(); MessageBox.Show(ocon.State.ToString());
Under Connect with Sql Server using ODBC Driver: scon = new OdbcConnection(“Dsn=SqlDsn”);
scon.Open(); MessageBox.Show(scon.State.ToString()); scon.Close(); MessageBox.Show(scon.State.ToString());
Accessing MS Excel data from .Net Application
MS Excel is a file system which stores data in the form of rows and columns same as a database table. An
Excel document is referred as Work Book that contains Work Sheets in it, work books are considered as databases
and work sheets are considered as tables. First row of work sheet can store column names.
Creating an Excel document:

Go to Start Menu -> Programs -> Microsoft Office -> Microsoft Office Excel, click on it to open, by default
the document contains 3 work sheets in it. Now in the first row of the sheet1 enter column names for Student table
as Sno, Sname, Class, Fees and from the second row enter few records in it. Now in bottom of the document
change the sheet name Sheet1 as Student and select ‘Save As’ choose ‘Excel 97 – 2003 Workbook’, name the
document as School.xls in your desired location.
Connecting with Excel document from .Net Application:
12
We can connect with Excel documents from .Net application by using Drivers or Providers also. To connect
with drivers first we need to configure ODBC driver for Excel. To configure driver go to Start Menu => Control Panel
=> Administrative Tools => Data Sources (ODBC), click on it to open ODBC Data Source Administrator window, Click
Add button, select Microsoft Excel (*.xls) driver, Click finish and Enter the following details, Data Source Name:
ExcelDsn, Description: Connects with Excel document, and click on Select Workbook button to choose the
School.xls document from it physical location and click on the Ok button which adds the DSN under ODBC Data
Source Administrator window. Now add a new windows form in the project and design it as following:
using System.Data.Odbc;
Declarations: OdbcConnection con; OdbcCommand cmd; OdbcDataReader dr; string SqlStr;
Under Form Load:
con = new OdbcConnection("Dsn=ExcelDsn;ReadOnly=0");
cmd = new OdbcCommand(); cmd.Connection = con; con.Open(); LoadData();
label1.Text=dr.GetName(0); label2.Text=dr.GetName(1); label3.Text=dr.GetName(2); label4.Text=dr.GetName(3);
private void LoadData() {
cmd.CommandText = "Select * From [Student$]"; dr = cmd.ExecuteReader(); ShowData();
}
if (dr.Read()) {
}
else
MessageBox.Show("No data exists.”);
}
Under Clear Button: textBox1.Text=textBox2.Text=textBox3.Text=textBox4.Text = ""; textBox1.Focus();
private void ExecuteDML() {
dr.Close(); cmd.CommandText = SqlStr;
if (cmd.ExecuteNonQuery() > 0) MessageBox.Show("Insert Or Update operation was successful.");
else MessageBox.Show("Insert or Update operation failed.");
LoadData();
}
Under Insert Button: sqlstr = String.Format(“Insert Into [Student$] Values ({0}, ‘{1}’ {2}, {3})”, textBox1.Text,
textBox2.Text, textBox3.Text, textBox4.Text); dr.Close(); ExecuteDML();
Under Update Button: sqlstr = String.Format(“Update [Student$] Set Sname='{0}’, Class={1}, Fees={2} where
Sno={3}”, textBox2.Text, textBox3.Text, textBox4.Text, textBox1.Text); ExecuteDML();
Under Close Button: if (con.State != ConnectionState.Closed) { con.Close(); } this.Close();
Connecting with Excel using OLEDB Provider:
12
To connect with Excel documents using OLEDB Provider, Connection String should be as following:
"Provider=Microsoft.Jet.Oledb.4.0;Data Source=<path of excel file>;Extended Properties=Excel 8.0"
Note: OdbcConnection class opens connection with Excel Document in read only mode so if we want to perform
any manipulations to data in the document we need to open it in read/write mode by setting the attribute
“ReadOnly=0” under the connection string, whereas OledbConnection will open the document in read/write mode
only so no need of using readonly attribute there.
Stored Procedures
Whenever we want to interact with a database from an application we use sql stmts. When we use sql
statements within the application we have a problem i.e. when the application runs sql Statements will be sent to
db for execution where the stmts will be parsed (compile) and then executed. The process of parsing takes place
each time we run the application, because of this performance of our application decreases. To overcome the
above drawback write sql statements directly under db only, with in an object known as Stored Procedure and call
them for execution. As a SP is a pre-compiled block of code that is ready for execution will directly execute the
statements without parsing each time.
Syntax to define a Stored Procedure:

Create Procedure <Name> [ (<parameter definitions) ]
As
Begin
<Stmts>
End
 SP’s is similar to a method in our language.
public void Test() //Method
Create Procedure Test() //Stored Procedure
 If required we can also define parameters but only optional. If we want to pass parameters to a Sql Server
SP prefix the special character “@” before parameter name.
public void Test(int x) //CSharp
Create Procedure Test(x number) //Oracle
Create Procedure Test(@x int) //Sql Server
 A SP can also return values, to return a value we use out clause in Oracle and Output clause in Sql Server.
public void Test(int x, ref|out int y) //CSharp
Create Procedure Test(x number, y out number) //Oracle
Create Procedure Test(@x int, @y int out|output) //Sql Server
Creating a Stored Procedure:
We can create a SP in Sql Server either by using Sql Server Management Studio or Visual Studio.Net also. To
create a SP from Visual Studio first we need to configure our Database under Server Explorer, to do this go to view
menu, select Server Explorer which gets launched on LHS of the studio. To configure it right click on the node “Data
12
Connections”, select “Add Connection” which opens a window asking to choose a Data Source select “MS Sql
Server”, click ok, which opens “Add Connection” window and under it provide the following details:
1. Server Name: <Name of the Server>
2. Authentication: Windows or Sql Server (provide User Name and Password)
3. Database: <DB Name>
Click on the OK button which adds the DB under Server Explorer, expand it, and right click on the node Stored
Procedures, select “Add New Stored Procedure” which opens a window and write the following code:
Create Procedure Employee_Select
As
Select Eno, Ename, Job, Salary, Status From Employee Order by Eno;
-Now right click on the document window and select execute which will create the procedure on Database Server.
Calling a SP from .Net application: if we want to call a SP from .net application we use DataAdapter class if it is a
Select SP to load data into a DataSet or else we can use Command class if the SP is of any operation but here if it is
Select SP we can load data either into a DataReader or DataSet also. To call the SP we adopt the below process:
Step 1: Create an object of class Command or DataAdapter by passing SP name as argument to their constructor.
DataAdapter da = new DataAdapter(“Employee_Select”, con);
or
Command cmd = new Command (“Employee_Select”, con);
Step 2: Change the CommandType property of SelectCommand of DataAdapter object or CommandType property
of Command object as StoredProcedure because by default CommandType property is configured to execute Sql
Statements only after changing the property we can call Stored Procedures.
da.SelectCommand.CommandType = CommandType.StoredProcedure;
or
cmd.CommandType = CommandType.StoredProcedure;
Step 3: If the SP has any parameters we need to pass values to those parameters by adding the parameters with
their corresponding values under SelectCommand of DataAdapter or Command.
Step 4: To call the Select SP using DataAdapter we can directly call Fill method on DataAdapter object and load data
into DataSet. If we want to call Select SP using Command call the ExecuteReader() method on Command object so
that data gets loaded into a DataReader or else if we want to load the data into a DataSet create object of
DataAdapter by passing this command object as a parameter and then call Fill method on DataAdapter. If the SP
contains any non-query operations like Insert or Update or Delete then call ExecuteNonQuery method on
command to execute the SP.
Calling above Stored Procedure using DataAdapter: In a new form place a DataGridView and write below code:
using System.Configuration; using System.Data.SqlClient;
Declarations: SqlConnection con; SqlDataAdapter da; DataSet ds;
Under Form Load: string constr = ConfigurationManager.ConnectionStrings["SConStr"].ConnectionString;
con = new SqlConnection(constr); da = new SqlDataAdapter(“Employee_Select”, con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
ds = new DataSet(); da.Fill(ds, “Employee”); dataGridView1.DataSource = ds.Tables[0];
Calling the above Stored Procedure using Command: take a new form and design it as following.
12
Declarations: SqlConnection con; SqlCommand cmd; SqlDataReader dr;
con = new SqlConnection(constr); cmd = new SqlCommand("Employee_Select", con);
cmd.CommandType = CommandType.StoredProcedure; con.Open(); dr = cmd.ExecuteReader(); ShowData();
label1.Text = dr.GetName(0); label2.Text = dr.GetName(1);
label3.Text = dr.GetName(2); label4.Text = dr.GetName(3);
if (dr.Read()) {
}
else
MessageBox.Show("Last record of the table");
}
Parameters to Stored Procedures:
SP can be defined with parameters either to send values for execution or receiving values after execution.
While calling a SP with parameters from .net application for each parameter of the SP we need to add a matching
parameter either under Command or DataAdapter i.e. for input parameter matching input parameter has to be
added and for output parameter a matching output parameter has to be added. Every parameter that is added to
Command or DataAdapter has 5 attributes to it like Name, Value, DbType, Size and Direction which can be Input(d)
or Output or InputOutput (in case of oracle only).
 Name refers to name of the parameter that is defined in SP.
 Value refers to value being assigned in case of input or value we are expecting in case of output.
 DbType refers to data type of the parameter in terms of the DB where the SP exists.
 Size refers to size of data.
 Direction specifies whether parameter is Input or Output or InputOutput.
If a SP has Input or Output parameters we need to specify the following attributes while adding the parameters:
Input Output InputOutput
Name Yes Yes Yes
Value Yes No Yes
DbType No Yes Yes
Size No Yes Yes [Only in case of variable length types]
Direction No Yes Yes
Adding Input Parameter under .Net Application:
da.SelectCommand.Parameters.AddWithValue(string <pname>, object <pvalue>);
12
or
cmd.Parameters.AddWithValue(string <pname>, object <pvalue>);
Adding Output Parameter under .Net Application:
da.SelectCommand.Parameters.Add(string <pname>, DbType <type>).Direction = ParameterDirection.Output;
or
cmd.Parameters.Add(string <pname>, DbType <type>).Direction = ParameterDirection.Output;
Adding Output parameter with size if it is a variable length type:
da.SelectCommand.Parameters.Add(string <pname>, <dbtype>, int <size>).Direction = ParameterDirection.Output;
or
cmd.Parameters.Add(string <pname>, DbType <type>, int <size>).Direction = ParameterDirection.Output;
After executing the SP we can capture Output parameter values as following:
Object obj = da.SelectCommand.Parameters[<pname>].Value;
or
Object obj = cmd.Parameters[<pname>].Value;
Performing Select and DML Operations using Stored Procedures:
To perform select, insert, update and delete operations using SP’s first define the following procedures in
Database.
Alter PROCEDURE Employee_Select (@Eno Int=Null, @Status bit=Null)
As
Begin
If @Eno Is Null And @Status Is Null --Retrieves all records
Select Eno, Ename, Job, Salary, Photo, Status From Employee;
Else If @Eno Is Null And Status Is Not Null --Retrieves all records based on status
Select Eno, Ename, Job, Salary, Photo, Status From Employee Where Status=@Status;
Else -- Retrieves single record based on Eno and Status
Select Eno, Ename, Job, Salary, Photo, Status From Employee Where Eno=@Eno And Status=@Status;
End
Create Procedure Employee_Insert(@Ename Varchar(50), @Job Varchar(50), @Salary Money, @Photo Image,
@Eno Int Output)
As
Begin
Begin Transaction
Select @Eno = IsNull(Max(Eno), 1000) + 1 From Employee;
Insert Into Employee (Eno, Ename, Job, Salary, Photo) Values (@Eno, @Ename, @Job, @Salary, @Photo);
Commit Transaction;
End;
Create Procedure Employee_Update(@Eno Int, @Ename Varchar(50), @Job Varchar(50), @Salary Money, @Photo
Image)
As
Update Employee Set Ename=@Ename, Job=@Job, Salary=@Salary, Photo=@Photo Where Eno=@Eno;
CREATE PROCEDURE Employee_Delete (@Eno Int)
As
Update Employee Set Status=0 Where Eno=@Eno;
Take a new form, design it as following by adding an OpenFileDialog control and then write below code:
12
using System.Configuration; using System.IO; using System.Data.SqlClient; using Microsoft.VisualBasic;
Declarations: SqlConnection con; SqlCommand cmd; SqlDataAdapter da; DataSet ds;
string ImgPath=””; byte[] ImgData = null;
con = new SqlConnection(constr); cmd = new SqlCommand();
cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure;
Under Select Button:
int Eno;
string value = Interaction.InputBox("Enter an Employee No."); bool Status = int.TryParse(value, out Eno);
if (Status) {
cmd.Parameters.Clear(); cmd.CommandText = "Employee_Select"; ds = new DataSet();
cmd.Parameters.AddWithValue("@Eno", Eno); cmd.Parameters.AddWithValue("@Status", true);
da = new SqlDataAdapter(cmd); da.Fill(ds, "Employee");
if (ds.Tables[0].Rows.Count > 0) {
ImgData = null; ImgPath = “”;
textBox1.Text = ds.Tables[0].Rows[0][0].ToString(); textBox2.Text = ds.Tables[0].Rows[0][1].ToString();
textBox3.Text = ds.Tables[0].Rows[0][2].ToString(); textBox4.Text = ds.Tables[0].Rows[0][3].ToString();
if (ds.Tables[0].Rows[0][4] != System.DBNull.Value) {
ImgData = (byte[])ds.Tables[0].Rows[0][4];
MemoryStream ms = new MemoryStream(ImgData);
pictureBox1.Image = Image.FromStream(ms);
}
else { pictureBox1.Image = null; ImgData = null; }
}
else
MessageBox.Show("Employee doesn't exist, please check the given employee number.", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
MessageBox.Show("Enter an integer value as Employee No.", “Warning”, MessageBoxButtons.OK,
MessageBoxIcon.Warning);
Under Clear Button:
textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = "";
pictureBox1.Image = null; ImgData = null; ImgPath = ""; textBox2.Focus();
12
try {
cmd.Parameters.Clear(); cmd.CommandText = "Employee_Insert";
if(ImgPath.Trim().Length > 0) {
ImgData = File.ReadAllBytes(ImgPath); cmd.Parameters.AddWithValue("@Photo", ImgData);

}
else {
cmd.Parameters.AddWithValue("@Photo", DBNull.Value);
cmd.Parameters["@Photo"].SqlDbType = SqlDbType.Image;
}
cmd.Parameters.Add("@Eno", SqlDbType.Int).Direction = ParameterDirection.Output;
con.Open(); cmd.ExecuteNonQuery();
textBox1.Text = cmd.Parameters["@Eno"].Value.ToString(); ImgPath = “”; ImgData = null;
}
catch (Exception ex)
{ MessageBox.Show(ex.Message, "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally { con.Close(); }
try {
cmd.Parameters.Clear(); cmd.CommandText = "Employee_Update";
if (ImgPath.Trim().Length == 0 && ImgData == null) {
cmd.Parameters.AddWithValue("@Photo", DBNull.Value);
cmd.Parameters["@Photo"].SqlDbType = SqlDbType.Image;
}
else if (ImgPath.Trim().Length > 0) {
ImgData = File.ReadAllBytes(ImgPath);
cmd.Parameters.AddWithValue("@Photo", ImgData);
}
else if (ImgData != null) {
cmd.Parameters.AddWithValue("@Photo", ImgData);
}
con.Open(); cmd.ExecuteNonQuery();
MessageBox.Show("Record updated under the database table.", "Information Message", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
finally { con.Close();}
12
try {
cmd.Parameters.Clear(); cmd.CommandText = "Employee_Delete";
con.Open(); cmd.ExecuteNonQuery(); btnClear.PerformClick();
MessageBox.Show("Record deleted under the database table.", "Sql Message", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
finally { con.Close(); }
Note: when we call PerformClick() method on a button internally the Click Event of that button occurs.
Under Load Image Button:
openFileDialog1.Filter = "Jpeg Images (*.jpg)|*.jpg|Bitmap Images (*.bmp)|*.bmp|All Files (*.*)|*.*";
DialogResult dr = openFileDialog1.ShowDialog();
if (dr == DialogResult.OK) {
ImgPath = openFileDialog1.FileName; pictureBox1.ImageLocation = ImgPath;
}
Create a new form as below for accessing all, active and in-active records from the table using select SP.
DataGridView

Declarations: SqlConnection con; SqlDataAdapter da; DataSet ds;
con = new SqlConnection(constr); da = new SqlDataAdapter("Employee_Select", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure; comboBox1.SelectedIndex = 0;
Under ComboBox SelectedIndexChanged: da.SelectCommand.Parameters.Clear();
if (comboBox1.SelectedIndex == 1) { da.SelectCommand.Parameters.AddWithValue("@Status", true); }
else if(comboBox1.SelectedIndex == 2) { da.SelectCommand.Parameters.AddWithValue("@Status", false); }
ds = new DataSet(); da.Fill(ds, "Employee"); dataGridView1.AutoGenerateColumns = false;
dataGridView1.AllowUserToAddRows = false; dataGridView1.DataSource = ds.Tables[0]; CreateColumns();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{ dataGridView1.Rows[i].Height = 200; }
12
private void CreateColumns() {
DataGridViewTextBoxColumn EnoColumn = new DataGridViewTextBoxColumn();
EnoColumn.DataPropertyName = "Eno"; EnoColumn.HeaderText = "Eno"; EnoColumn.Width = 65;
DataGridViewTextBoxColumn EnameColumn = new DataGridViewTextBoxColumn();

EnameColumn.DataPropertyName = "Ename"; EnameColumn.HeaderText = "Ename"; EnameColumn.Width = 100;
DataGridViewTextBoxColumn JobColumn = new DataGridViewTextBoxColumn();

JobColumn.DataPropertyName = "Job"; JobColumn.HeaderText = "Job"; JobColumn.Width = 120;
DataGridViewTextBoxColumn SalaryColumn = new DataGridViewTextBoxColumn();

SalaryColumn.DataPropertyName = "Salary"; SalaryColumn.HeaderText = "Salary"; SalaryColumn.Width = 130;
DataGridViewImageColumn PhotoColumn = new DataGridViewImageColumn();

PhotoColumn.DataPropertyName = "Photo"; PhotoColumn.HeaderText = "Photo"; PhotoColumn.Width = 200;
PhotoColumn.ImageLayout = DataGridViewImageCellLayout.Stretch;
DataGridViewCheckBoxColumn StatusColumn = new DataGridViewCheckBoxColumn();

StatusColumn.DataPropertyName = "Status"; StatusColumn.HeaderText = "Status"; StatusColumn.Width = 75;
dataGridView1.Columns.Add(EnoColumn); dataGridView1.Columns.Add(EnameColumn);
dataGridView1.Columns.Add(JobColumn); dataGridView1.Columns.Add(SalaryColumn);
dataGridView1.Columns.Add(PhotoColumn); dataGridView1.Columns.Add(StatusColumn);
}
Crystal Reports
Crystal Reports is a business intelligence application used to design and generate reports from a wide
range of data sources. Microsoft Visual Studio bundles an OEM version of Crystal Reports as a general purpose
reporting tool which became the de facto standard report writer when Microsoft released it with Visual Studio.
The product was originally created by Crystal Services Inc. as Quik Reports when they could not find a
suitable commercial report writer for their accounting software. After producing versions 1.0 through 3.0, the
company was acquired in 1994 by Seagate Technology. Crystal Services was combined with Holostic Systems to
form the Information Management Group of Seagate Software, which later rebranded as Crystal Decisions, and
produced versions 4.0 through 9.0. Crystal Decisions was acquired in December 2003 by Business Objects, which
has so far produces versions 10, 11 and current version 12. Business Objects was acquired by SAP on Oct 8, 2007.
To add report under a project open "Add New Item" window, select Reporting in LHS panel, then select
Crystal Report from RHS panel, which adds the report file, with an extension.rpt. A report file by default has 5
sections in it when opened. Those are:
1. Report Header 2. Page Header 3. Details 4. Report Footer 5. Page Footer
Report Header: content placed under this section gets displayed on top of the report i.e. on top of the first page in
report. We use it for placing contents like Company Name, Logo, and Address etc.
Page Header: content placed under this section gets displayed on top of every page into which the report extends.
We use it for placing contents like Column Names, Date and Time when the report is generated etc.
12
Details: content into this section generally comes from Databases; this was the actual information that has to be
presented or displayed to the clients.
Report Footer: this was same as Report Header but gets displayed on bottom of the report. We use it for placing
contents like Date, Place, and Signatures etc.
Page Footer: this was same as Page Header but gets displayed on bottom of every page into which the report
extends. We use it for placing contents like Page No's, Phone No’s, and Fax No’s etc.
Creating a Report:
Open a new project of type Windows, name it as “ReportsProject”, open add new item window, select
Reporting on LHS, choose Crystal Report on RHS, name the report as Employee.rpt and click Add button which
opens “Crystal Report Gallery” window asking to choose how we want to design the report select “As a Blank
Report” Radio Button and click Ok. Once the report is added to the Project all the required assembly references are
also implicitly added, check them in solution explorer under references node.
As the report has to get its information from DB first we need to configure the report with appropriate
Data Source using the "Field Explorer" window that gets added along with your Report.
Configuring the Report with DB: open the Field Explorer, right click on DB Fields Node, select 'Database Expert'
which opens a window, expand Create New Connection node, double click on 'OLE DB (ADO)' node that displays a
list of providers, choose a provider for Sql Server, click next, specify the connection details like server name, user id,
password and DB, click Finish which adds a node under OLE DB (ADO) with your Server Name expand it and under
it expand your database, “dbo", “Tables”, double click on the table we want to consume, e.g.: Employee which adds
the table under selected tables list on RHS, click ok which adds Table under DB Fields node of Field Explorer.
Designing the Report:
Go to Report Header section right click in it and select Insert Text Object that adds an object like a Label,
you can enter content in it and do the required alignments using the format toolbar on top.
Go to Page Header section right click on it, select Insert, Special Field, “DataDate” place it on LHS of the
section, right click on it select format object and choose the format in which we want to display the date, in the
same way select “DataTime” and place it on RHS.
Now open Field Explorer and expand the table Employee we have selected, that displays the list of
columns below, drag and drop each column on to details section as per your design which will create 1 Field on the
Page Header (Column Name) and 1 Field on the Details (Data), do all the required alignments to header and details.
Now go to Report Footer and provide the option for users to enter Date, Place and Signature using the
Text Objects and Insert Line Options.
Now go to Page Footer section right click Insert, Special Field, select Page Number and place it on the
Center of the section.
Launching the Report:

To display report in a windows application use “CrystalReportViewer” control. You find the control in
reports tab of ToolBox. To show a report under the control we need to assign path of the report to controls
“ReportSource” Property. Now place a CrystalReportViewer control on form which by default uses dock property
value as fill and write the following code under Form Load Event:
crystalReportViewer1.ReportSource = <path of the report>;
Note: to get the path of your report go to Solution Explorer, right click on report file and select properties, copy the
value under “FullPath” property and use it.
12
Run the project to watch the output but first it will prompt for the password confirmation so enter
password. The reason why it asks for password is because right now when we launch the report Crystal Report
Viewer needs to connect with the Database for getting the information, so it needs all connection details. If we
want to launch the Report without prompting the end user for connection details we need to supply all that
information to Crystal Report Viewer control explicitly from code with the help of 3 classes ConnectionInfo,
TableLogOnInfo and TableLogOnInfos which are present under the namespace CrystalDecisions.Shared. Now
rewrite the code again in Form1 under Load Event as following:
using CrystalDecisions.Shared;
ConnectionInfo ci = new ConnectionInfo();
ci.ServerName = "<Server Name>" ci.UserID = "Sa";ci.Password = <pwd>;ci.DatabaseName = "<DB Name>";
TableLogOnInfo ti = new TableLogOnInfo(); ti.ConnectionInfo = ci; ti.TableName="Employee";
TableLogOnInfos tis = new TableLogOnInfos(); tis.Add(ti);
crystalReportViewer1.ReportSource = "<path of the report>"; crystalReportViewer1.LogOnInfo = tis;
Note: Maintain the Server Name, User Id, Password and Database Name attributes under the Configuration File
and then read them into the application to make it more dynamic.
Selection Based Reports: these are reports which get generated based on the selections made by users which may
get the data from single or multiple tables. Creating a selection based report using EMP and Dept tables of Oracle.
Step 1: Add a new form in the project “Form2.cs” and add reference of System.Data.OracleClient.dll to the project.
Design the form as following and write the below code.
using System.Data.OracleClient;
Declarations: OracleConnection con; OracleDataAdapter da; DataSet ds;
Under Form Load: con = new OracleConnection("User Id=Scott;Password=tiger");
da = new OracleDataAdapter("Select Empno From Emp", con); ds = new DataSet(); da.Fill(ds, "Emp");
comboBox1.DataSource = ds.Tables[0]; comboBox1.DisplayMember = "Empno";
Under Show Report Button: write this code after creation of second form i.e. Form3.
Form3 f = new Form3(); f.Empno = int.Parse(comboBox1.Text); f.ShowDialog();
Step 2: Designing the Report, add a new Blank Crystal Report under the project and configure the report with DB:
Open the field explorer, right click on database fields, database expert, create new connection, OLE DB (ADO),
Microsoft Oledb Provider for Oracle, click next, provide the connection information, click Finish. Adds a node under
OLE DB (ADO) as Connection or Server Name, double click on "Add Command" node under connection or Server
Name, opens a window where we can write a Select Statement on LHS TextArea.
Parameter Fields: these are used for sending values to a report for execution to make the report more dynamic
where those values can be used for displaying title, address, phone no’s and still values to queries to execute etc.
Our select stmt should execute basing on the Empno selected under ComboBox of above Form which
should be sent as a parameter to report, so first we need to create a parameter and then use it under the select
12
stmt. To create a parameter click on "Create" button which opens a window under which we need to specify
following details:
1. Parameter Name: Empno
2. Prompting Text: Enter Employee No.
3. Value Type: Number
4. Default Value: 7566 (optional)
Click ok which adds the parameter on RHS ListBox, now in LHS TextBox write following Sql Stmt:
Select E.Empno, E.Ename, E.Job, E.Mgr, E.HireDate, E.Sal, E.Comm, D.Deptno, D.Dname, D.Loc From Emp E
Inner Join Dept D On E.Deptno=D.Deptno Where E.Empno = {?Empno}
Click Ok, Ok, Ok which will add the Command under Database Fields Node, if we expand it will display the
columns we mentioned under Select Stmt.
Designing the Report:

We can also Add Parameters using Field Explorer window, to create a Parameter right click on the node
Parameters and Select Add which will prompt for Name of Parameter, enter a name and click ok. Following the
above process create 2 parameters CompanyName and Address, now drag and drop them on ReportHeader and do
the necessary alignments.
Place DataDate and DataTime Fields on PageHeader section and do all the necessary alignments by
settings the formats.
Drag and drop Columns under Command into the details section and pull back column names from Page
Header to Details section.
Right click on the ReportFooter secion and select supress, so that our report doesn't have report footer.
Any section can be suppressed like this if not required.
Place a PageNumber Special Field on PageFooter section and do the necessary alignments, if required we
add any special fields or parameters fields here also.
Step 3: to launch the above report add a New Form under the project (Form3), place a CrystalReportViewer control
on the form. Go to "Application Configuration File" i.e. app.config and add the following code inside configuration
tag.
<appSettings>
<add key ="Cname" value ="NIT Technologies"/>
<add key ="Address" value="Ameerpet, Hyderabad"/>
</appSettings>
In this example we need to launch the report as well as send few parameter values to the report for
execution, to send values to parameters of report we need to use the class ReportDocument which is present
under CrystalDecisions.CrystalReports.Engine namespace. Now write the following code under Form:
using System.Configuration;using CrystalDecisions.CrystalReports.Engine; using CrystalDecisions.Shared;
Declarations: internal int Empno;
Under Form2 Load:
string Cname = ConfigurationManager.AppSettings.Get("Cname");
string Addr = ConfigurationManager.AppSettings.Get("Address");
ci.ServerName = "<Server Name>"; ci.UserID = "Scott"; ci.Password = "tiger";
TableLogOnInfo ti = new TableLogOnInfo(); ti.ConnectionInfo = ci; ti.TableName="Command";
ReportDocument obj = new ReportDocument(); obj.Load("<path of the report>");
crystalReportViewer1.ReportSource = obj; crystalReportViewer1.LogOnInfo = tis;
12
//Sending values to parameters of report:
obj.SetParameterValue("CompanyName", Cname);
obj.SetParameterValue("Address", Addr);
obj.SetParameterValue("Empno", Empno);
Note: using the SetParameterValue method of ReportDocument class we can pass values to Parameter Fields of
report: SetParameterValue(string pname, object value) or SetParameterValue(int index, object value)
Cross Tab Reports:
These are reports that are specially provided for summarizing the data or analysis of data. To create this
type of report add a new report under the project and when the Crystal Report Gallery window opens select the
Radio Button “Using the Report Wizard”, now in the bottom we find and option “Choose an Expert” Select “Cross
Tab” in it and click ok, which opens “Cross-Tab Report Creation Wizard”.
In the wizard expand the node “Create New Connection”, double click on the node OLE DB (ADO), which
opens a new window with a list or providers, select the provider for Oracle, Click Next, which opens a new window
for Connection Information, enter the connection details in it and click finish which adds a new node under OLE DB
(ADO) with the name as Server or Connection, expand it we will find a list of users, expand the user “Scott”, expand
the node tables, double click on the “Emp” table node which adds the table under “Selected Tables” List Box on
RHS, Click Next which displays all the columns corresponding to the Emp table asking for the columns to be added
into the report, select Job and add to Columns List Box, select Deptno and add to Rows List Box, select Sal and add
to Summary Fields List Box, now in the below Combo Box select Sum and click Next which will ask for including a
Chart into the report for summarizing the report, select Pie Chart Radio Button, provide a title to the chart or leave
the same, under “On change of” select the column Deptno, under “Subdivided by” select Job, under “Show
Summary” select Sum of Emp.Sal and click next which will ask for “Record Selection” using which we can set filters
to reports data or else it is optional, so leave it and click Next which displays for Grid Style, select the style we want
and click Finish which display the report with sample data, make any changes if required or add any required
special fields into the report.
Now to display the report in our application add a new windows form in the project “Form4.cs”, place a
Crystal Report Viewer Control on it and write the following code under load event:
using CrystalDecisions.Shared;
ci.ServerName = "<Server Name>"; ci.UserID = "Scott"; ci.Password = "tiger";
TableLogOnInfo ti = new TableLogOnInfo(); ti.ConnectionInfo = ci; ti.TableName="Emp";
crystalReportViewer1.ReportSource = "<path of the crystal report>"; crystalReportViewer1.LogOnInfo = tis;
12

ADo.net

Uploaded by

Copyright:

Available Formats

ADo.net

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ADo.net

Uploaded by

Copyright:

Available Formats

ADO.

DAO’s, RDO’s and ADO’s in Visual Basic Language:

OdbcConnection OdbcCommand OdbcDataReader OdbcDataAdapter OdbcCommandBuilder

Establishing a Connection with Data Source:

Constructors of the Class:

Connection String for Sql Server: "Provider=SqlOledb;User Id=Sa;Password=<pwd>;Database=<db name>[;Data

Constructors of the class: Command() Command(string CommandText, Connection con)

Properties of Command Class:

Methods of Command class:

Connecting String for Sql Server Authentication:

Connecting String for Windows Authentication:

Creating a database on Sql Server:

Set Read-only Property as True

Set Enabled Property as False

ExecuteDML(); btnInsert.Enabled = false;

Constructors of DataAdapter class:

Set Read-only Property as True

using System.Data.SqlClient; using static Microsoft.VisualBasic.Interaction;

using System.Data.SqlClient; using System.Configuration;

Using a DataView for filtering or sorting the data under DataTable:

using System.Data.OracleClient; using System.Configuration;

Creating an Excel document:

Syntax to define a Stored Procedure:

ImgData = File.ReadAllBytes(ImgPath); cmd.Parameters.AddWithValue("@Photo", ImgData);

using System.Configuration; using System.Data.SqlClient;

DataGridViewTextBoxColumn EnameColumn = new DataGridViewTextBoxColumn();

DataGridViewTextBoxColumn JobColumn = new DataGridViewTextBoxColumn();

DataGridViewTextBoxColumn SalaryColumn = new DataGridViewTextBoxColumn();

DataGridViewImageColumn PhotoColumn = new DataGridViewImageColumn();

DataGridViewCheckBoxColumn StatusColumn = new DataGridViewCheckBoxColumn();

Launching the Report:

Designing the Report:

You might also like