Thursday, 30 April 2015

STORED PROCEDURES


insert

create procedure stp_insert
@name varchar(50),
@age int,
@dob smalldatetime
as begin
insert into tbl3tier values(@name,@age,@dob)
end

select

create procedure stp_selectall
as begin
select * from tbl3tier
end

select specific

create procedure stp_selectcondition
@id int
as begin
select * from tbl3tier where id=@id
end

delete

create procedure stp_delete
@id int
as begin
delete from tbl3tier where id=@id
end

update

create procedure stp_update
@id int,
@name varchar(50),
@age int,
@dob smalldatetime
as begin
update tb3tier set name=@name,age=@age,dob=@dob where id=@id
end


Monday, 27 April 2015

LISTVIEW TO TEXTBOX IN WINDOWS FORMS

LISTIVIEW TO TEXTBOX

Private Sub EditToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditToolStripMenuItem.Click
        TextBox1.Text = ListView1.SelectedItems(0).SubItems(1).Text
        TextBox2.Text = ListView1.SelectedItems(0).SubItems(2).Text
        DateTimePicker1.Value = ListView1.SelectedItems(0).SubItems(3).Text
        g = ListView1.SelectedItems(0).SubItems(0).Text


    End Sub

Sunday, 26 April 2015

Fetch 3 Tier in VB

FETCH
dal:
Imports System.Data
Imports System.Data.SqlClient
Public Class fetchdl
    Dim obj As New connectiondal
    Dim cmd As New SqlCommand
    Dim ada As New SqlDataAdapter


    Public Function fetchdetails() As DataTable

        Dim dt As New DataTable

        cmd = New SqlCommand("stp_selectall", obj.Connection)
        Try


            ada = New SqlDataAdapter(cmd)
            ada.Fill(dt)
            Return dt

        Catch ex As Exception

            Throw ex

        End Try
    End Function

End Class
bal:

Imports System.Data

Imports System.Data.SqlClient

Public Class fetchbal
    Dim obj As New fetchdl

    Public Function fetchdetails() As DataTable
        Return obj.fetchdetails()

    End Function
End Class
PL:
 Public Sub fetch()
        Dim dt As New DataTable
        Dim lst As New ListViewItem
        dt = OBJf.fetchdetails()
        ListView1.Items.Clear()
        If (dt.Rows.Count > 0) Then
            For Each r As DataRow In dt.Rows
                lst = New ListViewItem(r("id").ToString())
                lst.SubItems.Add(r("name").ToString())
                lst.SubItems.Add(r("age").ToString())
                lst.SubItems.Add(r("dob").ToString())
                ListView1.Items.Add(lst)
            Next
        End If
    End Sub



Friday, 24 April 2015

INSERT DATAS TO SQL SERVER IN .NET USING THREE TIER ARCHITECTURE

INSERT

DAL:(Data Access layer)


Imports System.Data
Imports System.Data.SqlClient


Public Class insertdal
    Dim cmd As SqlCommand
    Dim conobj As New connectiondal

    Public Sub insertdetails(ByVal name As String, ByVal age As Integer, ByVal dob As Date)

        cmd = New SqlCommand("stp_insert", conobj.Connection)
        cmd.CommandType = CommandType.StoredProcedure
        Try


            cmd.Parameters.AddWithValue("@name", name)
            cmd.Parameters.AddWithValue("@age", age)
            cmd.Parameters.AddWithValue("@dob", dob)
            cmd.ExecuteNonQuery()

        Catch ex As Exception

            Throw ex
        End Try


    End Sub

End Class

BAL:(Business Access Layer)

Public Class insertbal
    Dim obj As New insertdal
    Public Sub insertdetails(ByVal name As String, ByVal age As Integer, ByVal dob As Date)
        obj.insertdetails(name, age, dob)
    End Sub
End Class


PL:(Presentation Layer)


Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
    Dim OBJ As New insertbal

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        OBJ.insertdetails(TextBox1.Text, TextBox2.Text, DateTimePicker1.Value)
      
    End Sub



Tuesday, 21 April 2015

SQL SELF ASSESSMENT TEST

                          

                                                                                                                         
                      .NET   SELF ASSESSMENT TEST 

1)Write an sql query to get an accurate count of  the number of records in a table.

2)What is difference between DELETE & TRUNCATE commands?


3)List few advantages of Stored Procedure.


4) The command to remove rows from a table 'CUSTOMER' is:
  (a) REMOVE FROM CUSTOMER ...
  (b) DROP FROM CUSTOMER ...
  (c) DELETE FROM CUSTOMER WHERE ...
  (d) UPDATE FROM CUSTOMER ...
5)The SQL WHERE clause:
  (a)  limits the column data that are returned.
  (b) limits the row data are returned.
  (c) Both A and B are correct.
  (d) Neither A nor B are correct.
6)  A view is which of the following?
(a) A virtual table that can be accessed via SQL commands
 (b) A virtual table that cannot    accessed via SQL commands
 (c) A base table that can be accessed via SQL commands
 (d) A base table that cannot be accessed via SQL commands

7)Write an SQL Query to find names of employee start with ‘S’.

(8)In which case would you use a FULL OUTER JOIN?


A) Both tables have NULL values.
B) You want all unmatched data from one table.
C) You want all matched data from both tables.
D) You want all unmatched data from both tables.
E) One of the tables has more data than the other.
F) You want all matched and unmatched data from only one table.

(10)Which two statements about views are true? (Choose two.)

A) A view can be created as read only.
B) A view can be created as a join on two or more tables.
C) A view cannot have an ORDER BY clause in the SELECT statement.
D) A view cannot be created with a GROUP BY clause in the SELECT statement.