_ASP.NET

3351603
 Assignment-3


1: List methods of Command object.

Answer:-

-The methods of Command object are given below :

1) ExecuteNonQuery() :-

-Data Manipulation tasks like Insert ,Update, Delete etc. are performed by this method. 

-this method is written as command.ExecuteNonQuery();

-It returns Integer type value.

-In this Command object no of Rows affected.

2) ExecuteScalar() :-

-This method is very useful to use with Aggregate functions like (Sum, count, min, max, avg, etc.).

-This command object returns single cell.

3) ExecuteReader() :-

-This Command object is used to retrive a single value from Database after the SQL statement execution.

-To create SqlDataReader we can only use ExecuteReader method 

2: What are return types of various methods of Command object?

Answer:-

-The ExecuteNonQuery() method returns an integer value.

-The ExecuteScalar()  returns only single cell value.

and The ExecuteReader() returns a SqlDataReader, SqlDataReader is a forward-only, Read-only retrieval of query results from data source  .

3: Write difference between DataSet and DataReader.

Answer:-

DataSet:-

-Dataset provides forward and backward  access to data.

-Dataset is a disconnected architecture.  

-We can do random access in Dataset.

-It is More expensive than DataReader because it stores multiple rows at the same time.



DataReader:-

-DataReader provides Forward-only and Read-only access to data.

-DataReader is a connected architecture.

we can do sequential access only in DataReader.

-It is less costly as it stores one Row at a time.

4: Write example ConnectionString for MS Access and SQL Server database connectivity (total 2 separate connection string).

Answer:- 

MS Access Connection String :- 

Protected Sub page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

        Dim con As New OleDb.OleDbConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\screen\anmol.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

        Dim ad As New OleDb.OleDbDataAdapter("select * from studentdata", con)

        Dim ds As New DataSet

        con.Open()

        ad.Fill(ds)

        con.Close()

        Gridview1.DataSource = ds.Tables(0)

        Gridview1.DataBind()

    End Sub

 

SQL Server Connection string:-

Protected Sub btn1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn1.Click

        Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\screen\anmol.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True")

        Dim ad As New SqlDataAdapter("select avg(marks) from studentdata", con)

         Dim ds As New DataSet

         con.Open()

        ad.Fill(ds)

        con.Close()

         Gridview1.DataSource = ds.Tables(0)

        Gridview1.DataBind()

    End Sub

End Class

 


5: List methods and properties of Connection object.

Answer:-

-The methods of connection object and its properties are given below:-

Open:- The open method opens a connection to a database for executing commands against data Source.

Syntax:-   object.Open();


Close:- The Close method is used to close a Connection object, to free the system Resources..

Syntax:-   Object.Open();


  


6: Write sample code for displaying data in GridView control of student table.

Answer:-

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     
        <asp:GridView ID="GridView1" runat="server" CellPadding="4"
                    ForeColor="#333333" GridLines="None">
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <EditRowStyle BackColor="#999999" />
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True"
                    ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True"
                    ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White"
                                           HorizontalAlign="Center" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True"
                   ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#E9E7E2" />
            <SortedAscendingHeaderStyle BackColor="#506C8C" />
            <SortedDescendingCellStyle BackColor="#FFFDF8" />
            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
        </asp:GridView>
     
    </div>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
public partial class _Default : System.Web.UI.Page
{
    SqlDataAdapter da = null;
    DataSet ds = null;
    SqlConnection cn = new SqlConnection(@"Data Source=.\sqlexpress;Initial
            Catalog=Student;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
    {
 
        da = new SqlDataAdapter("Select * From Studenttable", cn);
        ds = new DataSet();
        da.Fill(ds, "Studenttable");
        GridView1.DataSource = ds.Tables["Studenttable"];
        GridView1.DataBind();
 
    }
}

7: Write sample code for inserting data in student table using command object.

Answer:-

Public Class insert

    Inherits System.Web.UI.Page

    Protected Sub btn1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn1.Click

        Dim con As New OleDb.OleDbConnection("")

        Dim cmd As New OleDb.OleDbCommand("insert into tablename values (1, 'anmol')", con)

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

        Response.Write("inserted successfully")

    End Sub

End Class


8: Write sample code for using average marks using executeScalar method.

Answer:-

-Public Class insert

    Inherits System.Web.UI.Page

    Protected Sub btn1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn1.Click

        Dim con As New OleDb.OleDbConnection("")

        Dim cmd As New OleDb.OleDbCommand("select avg(marks) from studentdata", con)

        con.Open()

        Response.Write(cmd.ExecuteScalar() & " is the avg marks of 5 subjects")

        con.Close()

        Response.Write("inserted successfully")

    End Sub

End Class

 


9: Write sample code to read data from a customer table using DataReader object.

Answer:-

Public Class insert

    Inherits System.Web.UI.Page

    Protected Sub btn1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn1.Click

        Dim con As New OleDb.OleDbConnection("")

        Dim cmd As New OleDb.OleDbCommand("select avg(marks) from studentdata", con)

        Dim dr As OleDb.OleDbDataReader

        con.Open()

        dr = cmd.ExecuteReader

        While dr.Read

            Response.Write(dr("cid") & dr("cstmrname") & dr("total") & "</br>")

        End While

        con.Close()

    End Sub

End Class


10: Write sample code to show list of cities in a DropDownList using DataSet.

Answer:-

city.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="city.aspx.vb" Inherits="citydrop.city" %>

 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 <html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    Select Country<asp:DropDownList ID="Cty" runat="server" />

    </div>

    </form>

</body>

</html>

  

city.aspx.vb

Imports System.Data.SqlClient

 Public Class city

    Inherits System.Web.UI.Page

     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\screen\anmol.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

        Dim ad As New SqlDataAdapter("select * from ctable", con)

        Dim ds As New DataSet

         con.Open()

        ad.Fill(ds)

        con.Close()

         Cty.DataSource = ds.Tables(0)

        Cty.DataTextField = "cname"

        Cty.DataValueField = "cid"

        Cty.DataBind()

    End Sub

 End Class