DOT NET – Maintaining user information for Security & other requirements

– Create login.aspx in the root directory and design the page according to requirement. Code for this should be written after completing preperation of Entities, DL, BL and others…..

– Goto DB that the project is configured and create tables for storing user information. In general userinformation will have multiple tables, many columns but in our example
we will create users table with username(pk),password,role.

– Add some sample data in the created table and optionally write procedures for accessing the created tables.[In real time highly recommended is access tables with procedures]

– Once db is prepared we have to prepare front-end layers and as usual we will use EDM(O-R) for preparing our objects. prepare edm objects.

– once EDM objects are prepared we have create BL for users where we have to provide all functionality.

– Now we can write code for login button of login page. In this button we have to pass userdetails to bl and on successfull evaluation i.e when it returns true we must create forms identity.

class prjuser
string _uname,_password;



c1 obj=new c1();

class c2 : c1

Reference : Dilip Kumar Jena ( )



  1. ADO.NET is more than just a thin layer over some existing API. The similarity to ADO is fairly minimal- the classes and methods of accessing data are completely different.
  2. ADO is a library of COM components. ADO consists primarily of the connection, command, Recordset and field ohjects. Using ADO a connection is opened to the database, some data is selected into a recordset consisting of fields, that data is then manipulated and updated on the server and the connection is close. ADO also introduced a so-called disconnected recordset, which is used when keeping the connection open for long periods of time is not desirable.
  3. There were several problems that aDO did not address satisfactorily, most notabley the unwieldiness(in physical size) of a disconnected recordset. There are number of similarities between ADO.NET and ADO.
  4. ADO.NET ships with four database client namespaces: one for SQL server, another for Oracle, the third for ODBC datasources, and the fourth for any database exposed through OLEDB. If your database of choice is not SQL server or oracle then OLEDB route should be taken unless you have no other choice than to use ODBC.


System.Data  – All Generic data access classes

System.Data.Common – Classes shared (or overridden) by individual data providers

System.Data.Odbc – ODBC provider classes

System.Data.Oledb – OLEDB provider classes

System.Data.Oracle – Oracle provider classes

System.Data.SqlClient – SQL Server provider classes

System.Data.SqlTypes – SQL server data types.


Shared Classes:

ADO.NET contains a number of classes that are used regardless of whether you are using the SQL server classes or OLEDB classes.

The following classes are contained in the System.Data namespace


  1. Dataset – This object is designed for disconnected use and can contain a set of DataTables and include relationships between these tables.
  2. DataTable – A container of data that consists of one or more DataColumns and, when populated will have one or more DataRows containing data.
  3. DataRow – A number of values mapped to a row from a database table, or a row from a spreadsheet
  4. DataColumn – This object contains the definition of a column, such as the name and data type.
  5. DataRelation – A link between two Data Table classes within a Dataset class. Used for foreign key and master/detail relationship
  6. Constraint – This class defines a rule for a Datacolumn class(or set of data columns) such as unique values.


The following classes can be found in System.Data.Common namespace

  1. DataColumnMapping – Maps the name of a column from the database with the name of a column within a DataTable
  2. DataTableMapping – Maps a table name from the database to a DataTable within a Dataset.


Database Specific Classes:

These classes implement a set of standard interfaces defined within the System.Data namespace, allowing the classes to be used in a generic manner if necessary. For ex, SqlConnection and OleDbconnection classes implement the IdbConnection interface.


  1. SqlCommand, OledbCommand, OracleCommand and ODBCCommand – used as wrappers for SQL statements or stored procedure calls.
  2. SqlCommandBuilder, OleDbCommandBuilder, OracleCommandBuilder, and ODBCCommandBuilder – used to generate SQL Commands (such as insert, update, and delete statements) from a select statement
  3. SqlConnection, OleDBConnection, OracleConnection, ODBCConnection – used to connect to the database. Similar to ADO connection.
  4. SqlDataAdapter, OleDbDataAdapter, OracleDataAdapter, ODBCDataAdapter – used to hold select, insert, update and delete commands which are then used to populated a dataset and update the database.
  5. SqlDataReader, OleDbDataReader, OracleDataReader, ODBCDataReader – used as forward only connected data provider
  6. SqlParameter, OleDbParameter, OracleParameter, ODBCParameter – Used to define a parameter to a stored procedure
  7. SqlTransaction, OldDbTransaction, OracleTransaction, ODBCTransaction – used for a database transaction, wrapped in an object.

As seen above one for each of the providers that are part of .NET ver 1.1.

The most important new feature of the ADO.NET classes is that they are designed to work in a disconnected manner, which is important in today’s highly web-centric world.


Using Database Connections:

In order to access the database, you need to provide connection parameters, such as the machine that the database is running on, and possibly you login credentials.


System.Data.IdbConnection   O———————-System.Data.Odbc.OdbcConnection


System.Data.IdbConnection   O———————-System.Data.OleDb.OleDbConnection


System.Data.IdbConnection   O———————-System.Data.OracleClient.OracleConnection


System.Data.IdbConnection   O———————-System.Data.SqlClient.SqlConnection




Using System.Data.SqlClient;

String source = “server = (local)\\NetSDK; “ +

“integrated security = SSPI; “

“database = Northwind”;

SqlConnection conn = new SqlConnection(source);


// do something



Server = (local)\\NETSDK – This denotes the database server to connect to SQL server permits a number of separate database server processes to be running on the same machine, so here we are connecting to NetSDK process on local machine.


Intergrated Security == SSPI  – This used windows authentication to connect to the database which is highly recommended over using a username and password within the source code


  1. string connectionString; connectionString = “Provider=Microsoft.Jet.OLEDB.4.0; ” + “Data Source=C:\\BegASPNet11\\data\\Northwind.mdb”;
  2. OleDbConnection dbConnection = new OleDbConnection(connectionString); dbConnection.Open();
  3. System.Data.IDataReader GetProductsReader() { string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\\BEGASPNET11\\” + “data\\Northwind.mdb”; System.Data.IDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);


Using Connection Efficiently

When using scarce resources in .NET such as database connections, it is good practice to ensure that each resource is closed after use.  Keeping a connection open for slightly longer than necessary can affect other sessions.  They are two options to ensure the closing of connection


Option one : try .. catch .. finally.  – ensure that resources are cleaned up and open connections within the finally block.




open the connection


// do something useful


catch (Exception ex)


Do something





// ensure that the connection is freed



// within the finally block you can release any resources you have used. The only trouble with this method is that you have to ensure that you close the connection.



Option Two: The using block statement


With C# and other managed languages, there is no concept of automatic, deterministic destruction. Instead there is the garbage collector, which disposes of resources at some point in the future.  Fogetting to close a database connection could cause all sorts of problems for a .NET executable. To over come this


Using ( SqlConneciton conn = new SqlConnection (source) )



// open the connection

// do something


The using clause ensures that the database connection is closed regardless of how block is exited



Often when there is more than one update to be made to the database, these updates must be performed within the scope of a transaction. A transaction in ADO.NET is initiated by calling one of the BeginTransaction() methods on the database connection object. These methods return an object that implements the IdbTransaction Interface, define within System.Data.


String source = “server=(local)\\NetSDK;” +

“integrated security = SSPI;” +

“database = Northwind”;

SqlConnection conn = new SqlConnection(source)


SqlTransaction tx = conn.BeginTransaction();

// execute some commands , then commit the transaction






String source = “server=(local)\\NetSDK;” +

“integrated security = SSPI;” +

“database = Northwind”;

SqlConnection conn = new SqlConnection(source)


SqlCommand cmd = new SqlCommand(“select * from emp”,conn);


<provider> command classes have a property called commandtype, which is used to define whether the command is a sql clause, a call to stored procedure, or a full table statement.


CommandType              Example


Text(default)                  String select =m “select contactname from Customers”;

SqlCommand cmd = new SqlCommand(Select, conn);



StoredProcedure            Sqlcommand cmd = new sqlcommand(“CustOrderHist”,conn);

cmd.CommandType = CommandType.StoredProcedure;




TableDirect                    OleDbCommand cmd = new OleDbCommand(“Categories”,conn);



Executing Commands:

After you have defined the command, you need to execute it. There are no. of ways to issue the statement, depending on what you expect to be returned from that command. <provider> command classes provide the following execute methods:


ExecuteNonQuery() – Executes the command but does not return any output

ExecuteReader() – Executes the command and returns a typed IdataReader

ExecuteScalar() – Executes the command and returns a single value.


In additon to the above, SqlCommand class also exposes

ExecuteXmlReader() – Executes the command and returns XmlReader object




String source = “server=(local)\\NetSDK;” +

“integrated security = SSPI;” +

“database = Northwind”;

SqlConnection conn = new SqlConnection(source)



String select =”Update customers set contact name =’Bob’ where contactname = ‘Bill’ “

SqlCommand cmd = new SqlCommand(select, conn);

Int rowsReturned= cmd.ExecuteNonQuery();

Console.writeline (“{0} rows returned.” , rowsReturned);





On many occasion it is necessary to return a single result from a SQL statement, such as the count of records in a given table, or the current date/time on the server. The ExecuteScalar method can be used in such situations:


String source = “server=(local)\\NetSDK;” +

“integrated security = SSPI;” +

“database = Northwind”;

SqlConnection conn = new SqlConnection(source)


SqlCommand cmd = new SqlCommand(select, conn);

Object o – cmd.ExecuteScalar();

Console.WriteLine (o);


The method returns an object, which you can cast in the appropriate type if required.


Calling Stored procedures that returns nothing


Record Update:



@RegionDescription NCHAR(50)) AS


Update Region

SET RegionDescription = @RegionDescription

WHERE RegionID = @RegionID



SqlCommand acommand = new SqlCommand(“RegionUpdate”,conn);

aCommand.CommandType = CommandType.StoredProcedure;

aCommand.Parameters.Add (new SqlParameter (“@RegionID”,




aCommand.Parameter.Add(new SqlParameter(“@RegionDescription”,SqlDbType.NChar,



aCommand.UpdatedRowSource = UpdateRowSource.None;


aCommand.Parameters[0].Value = 999;

aCommand.Parameters[1].Value = “South Western England”;







Calling a stored procedure that returns output parameters

Reference : Dilip Kumar Jena ( )