ASP.NET – How to work with GridView

Today we are going to see how to work with Grid View Control.

Here is an example how are we going to get a repeater after we finished coding.

Here I am using AdventureWorks in SQL SERVER which can be downloaded from codeplex.

The table I am using is DimCustomer and fields are :

1)FirstName

2)MiddleName

3)LastName

4)BirthDate

5)CustomerKey

Step 1 :  Add the control in the <body> tag in page in .aspx side let it be grdView.aspx

</pre>
<form id="frmExmGrdView" runat="server">
    <asp:GridView ID="grd" runat="server" CssClass="gdview" HeaderStyle-CssClass="hd" AutoGenerateColumns="false" BorderStyle="None">
    <Columns>
    <asp:BoundField DataField="FirstName" HeaderText="Login" ItemStyle-Width="40" ItemStyle-HorizontalAlign="Left" ></asp:BoundField>
    <asp:BoundField DataField="MiddleName" HeaderText="Initial" ItemStyle-Width="20" ItemStyle-HorizontalAlign="Left" ></asp:BoundField>
    <asp:BoundField DataField="LastName" HeaderText="Name" ItemStyle-Width="60" ItemStyle-HorizontalAlign="Left" ></asp:BoundField>
    <asp:BoundField DataField="BirthDate" HeaderText="DateAdded" ItemStyle-Width="40" ItemStyle-HorizontalAlign="Left" HtmlEncode="false" DataFormatString={0:d}></asp:BoundField>
    </Columns>
    </asp:GridView>
    </form>
<pre>

NOTE: Dont forget to make the form tag as runat = “server “

otherwise an error will occur because you are trying to insert the data from .cs

side but u can only access it when u make runat = “server “

STEP 2:  Now insert the data from the code behind (.cs side) in Page_Load event

 

protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Server=localhost;UID=sa;PWD=dilip;Database=AdventureWorksDW");
        string sSQL = "Select top 5 * from dbo.DimCustomer";
        SqlCommand cmd = new SqlCommand(sSQL, con);
        con.Open();
        SqlDataReader dtrClient = cmd.ExecuteReader();
        grd.DataSource = dtrClient;
        grd.DataBind();
        con.Close();
    }

The last 2 lines binding the data to the repeater and starting remaining lines is to establish connection to database.

the output will come as shown above .

Reference : Dilip Kumar Jena ( https://mstechexplore.wordpress.com )

Advertisements

ASP.NET How to work with Repeater

Today we are going to see how to work with Repeater Control.

Here is an example how are we going to get a repeater after we finished coding.

Here I am using AdventureWorksDW2008R2 in SQL SERVER which can be downloaded from http://www.codeplex.com.

The table I am using is DimCustomer and fields are :

1)FirstName

2)MiddleName

3)LastName

4)BirthDate

5)CustomerKey

Step 1 :  Upload the control in the page in .aspx side let it be default.aspx

<form id=”frmExample” runat=”server”>

<asp:Repeater ID=”rptList” runat=”server” OnItemDataBound=”rptList_ItemDataBound”>

<HeaderTemplate>

<table border=”1″ cellpadding=”0″ cellspacing=”0″>

12px; color:White;”>

<th>

<asp:Label ID=”Label14″ Text=”Login” runat=”server”></asp:Label>

</th>

<th>

<asp:Label ID=”Label1″ Text=”First Name” runat=”server”></asp:Label>

</th>

<th>

<asp:Label ID=”Label2″ Text=”Last Name” runat=”server”></asp:Label>

</th>

<th>

<asp:Label ID=”Label3″ Text=”Date Added” runat=”server”></asp:Label>

</th>

<th>

<asp:Label ID=”Label6″ Text=”Delete” runat=”server”></asp:Label>

</th>

</tr>

</HeaderTemplate>

<ItemTemplate>

<tr>

<td>

<asp:LinkButton ID=”lbnLogin” Text='<%# Eval(“FirstName”) %>’ runat=”server”</asp:

OnClick=”lnkClient_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

LinkButton>

</td>

<td>

<asp:LinkButton ID=”lbnFirstName” Text='<%# Eval(“MiddleName”).ToString() %>'</asp:

runat=”server”

OnClick=”lnkClient_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

LinkButton>

</td>

<td>

<asp:LinkButton ID=”LinkButton2″ Text='<%# Eval(“LastName”).ToString() %>'</asp:

runat=”server”

OnClick=”lnkClient_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

</asp:LinkButton>

</td>

<td>

<asp:LinkButton ID=”LinkButton1″ Text='<%# string.Format(“{0:d}”, Eval(“BirthDate”)) %>’ runat=”server”

OnClick=”lnkClient_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

</asp:LinkButton>

</td>

<td>

<asp:LinkButton ID=”lbnDelete” Text=”delete” runat=”server”

OnClick=”lbnDelete_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

</asp:LinkButton>

</td>

</tr>

</ItemTemplate>

<FooterTemplate>

</table>

</FooterTemplate>

</asp:Repeater>

</form>

NOTE: Dont forget to make the form tag as runat = “server “

otherwise an error will occur because you are trying to insert the data from .cs

side but u can only access it when u make runat = “server “

STEP 2:  Now insert the data from the code behing

int Page_Load event

SqlConnection con = new SqlConnection(“Server=localhost;UID=xx;PWD=xx;

Database=AdventureWorksDW2008R2″);

string sSQL = “Select top 5 * from dbo.DimCustomer”;

SqlCommand cmd = new SqlCommand(sSQL, con);

con.Open();

SqlDataReader dtrClient = cmd.ExecuteReader();

rptList.DataSource = dtrClient;

rptList.DataBind();

con.Close();

/*along with it*/ write these dummy function later you can implement them as you like .

protected void lnkClient_Click(object sender, EventArgs e)

{ }

protected void lbnDelete_Click(object sender, EventArgs e)

{ }

protected void rptList_ItemDataBound(object sender, RepeaterItemEventArgs e)

{ }

The last 2 lines binding the data to the repeater and starting remaining lines is to establish connection to database.

the output will come as shown above .

Reference : Dilip Kumar Jena ( https://mstechexplore.wordpress.com )

DOT NET 2.0 – Features of C# 2.0

New features in C# for the .NET SDK 2.0 (corresponding to the 3rd edition of the ECMA-334 standard) are:

  • Partial classes allow class implementation across more than one source file. This permits splitting up very large classes.
  • Generics or parameterized types.
  • Static classes that cannot be instantiated, and that only allow static members. This is similar to the concept of module in many procedural languages.
  • Anonymous delegates.
  • The accessibility of property accessors can be set independently.
  • Nullable value types which provides improved interaction with SQL databases.
  • Coalesce operator: (??) returns the first of its operands which is not null (or null, if no such operand exists):

Reference : Dilip Kumar Jena ( https://mstechexplore.wordpress.com )

C #(SHARP).NET – ADO.NET

  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.

Namespaces:

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

 

Eg:

 

Using System.Data.SqlClient;

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

“integrated security = SSPI; “

“database = Northwind”;

SqlConnection conn = new SqlConnection(source);

Conn.Open();

// do something

Conn.Close();

 

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.

 

Try

{

open the connection

conn.Open();

// do something useful

}

catch (Exception ex)

{

Do something

}

finally

{

 

// ensure that the connection is freed

 

conn.close()

// 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

conn.open()

// do something

}

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

 

Transactions:

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)

Conn.Open();

SqlTransaction tx = conn.BeginTransaction();

// execute some commands , then commit the transaction

tx.Commit();

conn.Close();

 

Commands:

 

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

“integrated security = SSPI;” +

“database = Northwind”;

SqlConnection conn = new SqlConnection(source)

Conn.Open();

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;

cmd.Parameters.Add(“@customerid”,”quick”);

 

 

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

cmd.CommandType=CommandType.TableDirect;

 

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

 

ExecuteNonQuery():

 

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

“integrated security = SSPI;” +

“database = Northwind”;

SqlConnection conn = new SqlConnection(source)

Conn.Open();

 

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);

Conn.close();

 

ExecuteScalar()

 

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)

Conn.Open();

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:

 

CREATE PROCEDURE RegionUpdate(@RegionID INTEGER,

@RegionDescription NCHAR(50)) AS

SET NOCOUNT OFF

Update Region

SET RegionDescription = @RegionDescription

WHERE RegionID = @RegionID

GO

 

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

aCommand.CommandType = CommandType.StoredProcedure;

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

SqlType.Int,

0,

“RegionID”));

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

50,”RegionDescription”));

 

aCommand.UpdatedRowSource = UpdateRowSource.None;

 

aCommand.Parameters[0].Value = 999;

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

acommand.ExecuteNonQuery();

 

 

aCommand.Parameters[“@RegionID”].Value=999;

aCommand.ExecuteNonQuery();

 

Calling a stored procedure that returns output parameters

Reference : Dilip Kumar Jena ( https://mstechexplore.wordpress.com )