Solved

Example or Tutorial Links on Programatically handling Insert, Update, and Delete using ADO.NET

Posted on 2008-10-20
11
397 Views
Last Modified: 2009-06-04
Example or Tutorial Links on Programatically handling Insert, Update, and Delete using ADO.NET

I do not want to use a GridView or other databound rad controls

I simply need a good example of loading a value from SQL server using ADO.NET to a Textbox and then updating it.

Basically a simple CRUD example all in code behind using VB.NET.
0
Comment
Question by:brian2k1
  • 9
  • 2
11 Comments
 
LVL 7

Expert Comment

by:talker2004
ID: 22764267
Here is a simple class i wrote for an example project.

I will add in some of the usage as well as a delete example.


Imports System.Data

Imports System.Data.SqlClient

 

Public Class dbLib

    Dim strConn As String = System.Configuration.ConfigurationManager.ConnectionStrings("PersonalPortalConnectionString").ConnectionString.ToString
 

    Public Function addUsers(ByVal LName As String, ByVal FName As String, _

                             ByVal Email As String, ByVal City As String, _

                             ByVal State As String, ByVal Phone As String, _

                             ByVal Gender As Boolean, ByVal Age As Integer, _

                             ByVal FeedBack As String, ByVal UserGuid As String) As String
 
 

        Dim sqlConn As New SqlConnection

        sqlConn = New SqlConnection(strConn)

        sqlConn.Open()

        Dim cmdSQL As New SqlCommand

        cmdSQL.Connection = sqlConn
 

        Try

            Dim sql As String = ""
 

            cmdSQL.Parameters.AddWithValue("@LName", LName)

            cmdSQL.Parameters.AddWithValue("@FName", FName)

            cmdSQL.Parameters.AddWithValue("@Email", Email)

            cmdSQL.Parameters.AddWithValue("@City", City)

            cmdSQL.Parameters.AddWithValue("@State", State)

            cmdSQL.Parameters.AddWithValue("@Phone", Phone)

            cmdSQL.Parameters.AddWithValue("@Gender", Gender)

            cmdSQL.Parameters.AddWithValue("@Age", Age)

            cmdSQL.Parameters.AddWithValue("@FeedBack", FeedBack)

            cmdSQL.Parameters.AddWithValue("@UserGuid", UserGuid)
 

            cmdSQL.CommandText = _

            "INSERT INTO RegisteredUsers (LastName, FirstName, EmailAddress, " & _

            "City, State, Phone, Gender, Age, FeedBack, UserGuid)" & _

            " " & _

            "Values(@LName, @FName, @Email, @City, @State, " & _

            "@Phone, @Gender, @Age, @FeedBack, @UserGuid)"
 

            cmdSQL.ExecuteNonQuery()

            addUsers = UserGuid

        Catch ex As SqlException

            addUsers = False

            'User already exists in the database

            'We are going to query their unique identifier

            cmdSQL = New SqlCommand

            cmdSQL.Connection = sqlConn

            cmdSQL.Parameters.AddWithValue("@Email", Email)

            cmdSQL.CommandText = "SELECT UserGuid FROM RegisteredUsers WHERE EmailAddress = @Email"

            Try

                Dim objResult As Object = cmdSQL.ExecuteScalar()

                addUsers = objResult.ToString

            Catch

            End Try

        Catch ex As Exception

            addUsers = ""

            ' Log something to the servers event log

        Finally

            cmdSQL.Dispose()

            cmdSQL = Nothing

            sqlConn.Close()

            sqlConn = Nothing

        End Try
 
 

    End Function
 

    Public Function LogVisit(ByVal TargetPage As String, ByVal UserKey As String, ByVal IPAddress As String) As Boolean
 
 
 

        Dim sqlConn As New SqlConnection

        sqlConn = New SqlConnection(strConn)

        sqlConn.Open()
 

        Try

            Dim sql As String = ""

            Dim cmdSQL As New SqlCommand

            cmdSQL.Connection = sqlConn
 

            cmdSQL.Parameters.AddWithValue("@csWebPage", TargetPage)

            cmdSQL.Parameters.AddWithValue("@csUserKey", UserKey)

            cmdSQL.Parameters.AddWithValue("@csClickTime", Now)

            cmdSQL.Parameters.AddWithValue("@csIPAddress", IPAddress)
 

            cmdSQL.CommandText = _

            "INSERT INTO ClickStream (csWebPage, csUserKey, csClickTime, csIPAddress)" & _

            " " & _

            "Values(@csWebPage, @csUserKey, @csClickTime, @csIPAddress)"
 

            cmdSQL.ExecuteNonQuery()

            LogVisit = True

        Catch ex As SqlException

            LogVisit = False

            ' Log somehting to the servers event log

        Catch ex As Exception

            LogVisit = False

            ' Log something to the servers event log

        Finally
 

        End Try
 

        sqlConn.Close()

        sqlConn = Nothing
 

    End Function
 

    Public Function LogEmail(ByVal emailDateSent As Date, _

                             ByVal emailIdentifier As String, _

                             ByVal emailUserKey As String) As Boolean

        Dim sqlConn As New SqlConnection

        sqlConn = New SqlConnection(strConn)

        sqlConn.Open()
 

        Try

            Dim sql As String = ""

            Dim cmdSQL As New SqlCommand

            cmdSQL.Connection = sqlConn
 

            cmdSQL.Parameters.AddWithValue("@emailDateSent", emailDateSent)

            cmdSQL.Parameters.AddWithValue("@emailIdentifier", emailIdentifier)

            cmdSQL.Parameters.AddWithValue("@emailUserKey", emailUserKey)
 

            cmdSQL.CommandText = _

            "INSERT INTO EmailData " & _

            "      (emailDateSent, emailIdentifier, emailUserKey) " & _

            " " & _

            "Values " & _

            "      (@emailDateSent, @emailIdentifier, @emailUserKey)"
 

            cmdSQL.ExecuteNonQuery()

            LogEmail = True

        Catch ex As SqlException

            LogEmail = False

            ' Log somehting to the servers event log

        Catch ex As Exception

            LogEmail = False

            ' Log something to the servers event log

        Finally
 

        End Try
 

        sqlConn.Close()

        sqlConn = Nothing
 

    End Function
 

    Public Function MarkEmailRead(ByVal emailIdentifier As String) As Boolean
 

        Dim sqlConn As New SqlConnection

        sqlConn = New SqlConnection(strConn)

        sqlConn.Open()
 

        Try

            Dim sql As String = ""

            Dim cmdSQL As New SqlCommand

            cmdSQL.Connection = sqlConn
 

            cmdSQL.Parameters.AddWithValue("@emailIdentifier", emailIdentifier)

            cmdSQL.Parameters.AddWithValue("@emailDateOpened", Now)
 

            cmdSQL.CommandText = _

            "UPDATE EmailData SET emailDateOpened = @emailDateOpened " & _

            " " & _

            "WHERE emailIdentifier = @emailIdentifier"
 

            cmdSQL.ExecuteNonQuery()

            MarkEmailRead = True

        Catch ex As SqlException

            MarkEmailRead = False

            ' Log somehting to the servers event log

        Catch ex As Exception

            MarkEmailRead = False

            ' Log something to the servers event log

        Finally
 

        End Try
 

        sqlConn.Close()

        sqlConn = Nothing
 

    End Function
 

    Public Function getUsers() As DataSet

        Dim ds As New DataSet

        Dim sqlConn As New SqlConnection

        Try

            sqlConn = New SqlConnection(strConn)

            sqlConn.Open()

            Dim sql As String = "Select * From RegisteredUsers"

            Dim cmdSQL As New SqlCommand

            Dim daSql As New SqlDataAdapter

            cmdSQL = New SqlCommand(sql, sqlConn)

            daSql.SelectCommand = cmdSQL

            daSql.Fill(ds, "RegisteredUsers")

        Catch ex As SqlException

            ds = Nothing

        Catch ex As Exception

            ds = Nothing

        Finally

            sqlConn.Close()

            sqlConn = Nothing

        End Try

        Return ds

    End Function
 
 

    Public Function QueryDataset(ByVal cmdSQl As SqlCommand, ByVal SourceTable As String) As DataSet

        Dim ds As New DataSet

        Dim sqlConn As New SqlConnection

        Try

            sqlConn = New SqlConnection(strConn)

            sqlConn.Open()

            cmdSQl.Connection = sqlConn

            Dim daSql As New SqlDataAdapter

            daSql.SelectCommand = cmdSQl

            daSql.Fill(ds, SourceTable)

        Catch ex As SqlException

            ds = Nothing

        Catch ex As Exception

            ds = Nothing

        Finally

            sqlConn.Close()

            sqlConn = Nothing

        End Try

        Return ds

    End Function
 

End Class

Open in new window

0
 
LVL 7

Expert Comment

by:talker2004
ID: 22764288
These imports are key

Imports System.Data
Imports System.Data.SqlClient

Here is a nice way to do a query with the above library

        Dim objData As New dbLib
        Dim cmdSQL As New System.Data.SqlClient.SqlCommand

        Dim sql As String = _
            "Select LastName, FirstName, EmailAddress, Phone, FeedBack FROM RegisteredUsers"
        cmdSQL.CommandText = sql

        dsRegisteredUsers = objData.QueryDataset(cmdSQL, "RegisteredUsers")
0
 
LVL 7

Expert Comment

by:talker2004
ID: 22764293
The above code was missing the declaration
dim dsRegistredUsers as Dataset
0
 
LVL 7

Expert Comment

by:talker2004
ID: 22764307
Dim sqlConn As New SqlConnection
        sqlConn = New SqlConnection(strConn)
        sqlConn.Open()
        Dim cmdSQL As New SqlCommand
        cmdSQL.Connection = sqlConn
 
        Try
 
            cmdSQL.CommandText ="DELETE FROM RegisteredUsers"
 
            cmdSQL.ExecuteNonQuery()
        Catch ex As SqlException
            addUsers = False
        Catch ex As Exception
            addUsers = ""
            ' Log something to the servers event log
        Finally
            cmdSQL.Dispose()
            cmdSQL = Nothing
            sqlConn.Close()
            sqlConn = Nothing
        End Try
0
 
LVL 7

Expert Comment

by:talker2004
ID: 22764346
Here is an example connection string utilizing integrated security

dim connectionString="Data Source=MyServer;Initial Catalog=PersonalPortal;Integrated Security=True"

I am pasting an sql script that you can execute in sql server, this will allow you to create the database on your server to test the libraries.

The entire application sent out emails to users, logged to the sql server database that the user opened the email, and displayed data in a datagrid as well as an on the fly grid throuhg a response.write in an asp.net application.

Those are entirely different topics.
USE [master]

GO

/****** Object:  Database [PersonalPortal]    Script Date: 10/08/2008 16:08:34 ******/

CREATE DATABASE [PersonalPortal] ON  PRIMARY 

( NAME = N'PersonalPortal', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PersonalPortal.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'PersonalPortal_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PersonalPortal_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'PersonalPortal', @new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [PersonalPortal].[dbo].[sp_fulltext_database] @action = 'disable'

end

GO

ALTER DATABASE [PersonalPortal] SET ANSI_NULL_DEFAULT OFF 

GO

ALTER DATABASE [PersonalPortal] SET ANSI_NULLS OFF 

GO

ALTER DATABASE [PersonalPortal] SET ANSI_PADDING OFF 

GO

ALTER DATABASE [PersonalPortal] SET ANSI_WARNINGS OFF 

GO

ALTER DATABASE [PersonalPortal] SET ARITHABORT OFF 

GO

ALTER DATABASE [PersonalPortal] SET AUTO_CLOSE OFF 

GO

ALTER DATABASE [PersonalPortal] SET AUTO_CREATE_STATISTICS ON 

GO

ALTER DATABASE [PersonalPortal] SET AUTO_SHRINK OFF 

GO

ALTER DATABASE [PersonalPortal] SET AUTO_UPDATE_STATISTICS ON 

GO

ALTER DATABASE [PersonalPortal] SET CURSOR_CLOSE_ON_COMMIT OFF 

GO

ALTER DATABASE [PersonalPortal] SET CURSOR_DEFAULT  GLOBAL 

GO

ALTER DATABASE [PersonalPortal] SET CONCAT_NULL_YIELDS_NULL OFF 

GO

ALTER DATABASE [PersonalPortal] SET NUMERIC_ROUNDABORT OFF 

GO

ALTER DATABASE [PersonalPortal] SET QUOTED_IDENTIFIER OFF 

GO

ALTER DATABASE [PersonalPortal] SET RECURSIVE_TRIGGERS OFF 

GO

ALTER DATABASE [PersonalPortal] SET  ENABLE_BROKER 

GO

ALTER DATABASE [PersonalPortal] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

GO

ALTER DATABASE [PersonalPortal] SET DATE_CORRELATION_OPTIMIZATION OFF 

GO

ALTER DATABASE [PersonalPortal] SET TRUSTWORTHY OFF 

GO

ALTER DATABASE [PersonalPortal] SET ALLOW_SNAPSHOT_ISOLATION OFF 

GO

ALTER DATABASE [PersonalPortal] SET PARAMETERIZATION SIMPLE 

GO

ALTER DATABASE [PersonalPortal] SET  READ_WRITE 

GO

ALTER DATABASE [PersonalPortal] SET RECOVERY FULL 

GO

ALTER DATABASE [PersonalPortal] SET  MULTI_USER 

GO

ALTER DATABASE [PersonalPortal] SET PAGE_VERIFY CHECKSUM  

GO

ALTER DATABASE [PersonalPortal] SET DB_CHAINING OFF 
 
 
 
 
 

USE [PersonalPortal]

GO

/****** Object:  Table [dbo].[RegisteredUsers]    Script Date: 10/08/2008 16:12:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[RegisteredUsers](

	[userKey] [bigint] IDENTITY(1,1) NOT NULL,

	[LastName] [nvarchar](50) NOT NULL,

	[FirstName] [nvarchar](50) NOT NULL,

	[EmailAddress] [nvarchar](100) NOT NULL,

	[City] [nvarchar](50) NOT NULL,

	[State] [nvarchar](2) NOT NULL,

	[Phone] [nchar](14) NOT NULL,

	[Gender] [bit] NOT NULL,

	[Age] [int] NOT NULL,

	[Feedback] [text] NOT NULL,

	[UserGuid] [nvarchar](100) NOT NULL,

 CONSTRAINT [PK_RegisteredUsers] PRIMARY KEY CLUSTERED 

(

	[userKey] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],

 CONSTRAINT [IX_RegisteredUsers] UNIQUE NONCLUSTERED 

(

	[EmailAddress] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 

GO

ALTER TABLE [dbo].[RegisteredUsers]  WITH CHECK ADD  CONSTRAINT [FK_RegisteredUsers_RegisteredUsers] FOREIGN KEY([userKey])

REFERENCES [dbo].[RegisteredUsers] ([userKey])

GO

ALTER TABLE [dbo].[RegisteredUsers] CHECK CONSTRAINT [FK_RegisteredUsers_RegisteredUsers]
 
 
 

USE [PersonalPortal]

GO

/****** Object:  Table [dbo].[ClickStream]    Script Date: 10/08/2008 16:12:24 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ClickStream](

	[csKey] [bigint] IDENTITY(1,1) NOT NULL,

	[csWebPage] [nvarchar](256) NOT NULL,

	[csUserKey] [nvarchar](100) NOT NULL,

	[csIPAddress] [nvarchar](100) NOT NULL,

	[csClickTime] [datetime] NULL,

 CONSTRAINT [PK_ClickStream] PRIMARY KEY CLUSTERED 

(

	[csKey] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
 
 

USE [PersonalPortal]

GO

/****** Object:  Table [dbo].[EmailData]    Script Date: 10/08/2008 16:12:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[EmailData](

	[emailKey] [bigint] IDENTITY(1,1) NOT NULL,

	[emailDateSent] [datetime] NULL,

	[emailIdentifier] [nvarchar](100) NOT NULL,

	[emailDateOpened] [datetime] NULL,

	[emailUserKey] [nvarchar](100) NOT NULL,

 CONSTRAINT [PK_EmailData] PRIMARY KEY CLUSTERED 

(

	[emailKey] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Expert Comment

by:talker2004
ID: 22764406
After reading over the examples one thing not covered in the example is the ability to use the sqldataadpaters fill method to perform the updates, deletes, and inserts. I only utilized the fill method for the select statement in the example above.

But it is really easy, you can build yourself an sql command object for insert, update, and delete all in the same function. Set your sqldataadapter objects to each of your command objects. Pass your dataset through the daSQL.Filll method and everything will be executed.
 

The concept is that your dataset keeps track of changes Once you pass the dataset through the fill method of the sql data adapter it looks at any new rows in the dataset and runs them against your insert statements, then it takes your updated data in the dataset and runs it against your update query, and it checks for any deleted data from the dataset and deletes it from the datasource.

After the fill you will call the acceptchanges method of your dataset and all of the changes will be commited to your dataset.

Here is a good example i found using the above mentioned method.

http://webgraphics.web108.discountasp.net/HandH/AdapterUpdate.htm
 
0
 
LVL 1

Author Comment

by:brian2k1
ID: 22768554
thank you! this is a lot of code and I'm reviewing and playing around with it now. as soon as I have something working I will comment and award points.
0
 
LVL 7

Expert Comment

by:talker2004
ID: 22768598
cool my suggestion is to run that sql script on your sql server to get the database created. The learning curve with ADO.Net is getting used to the disconnected architecture. When you are used to recordsets it gets confusing at first.

what i like the best is it's ability to do parametrized queries.
0
 
LVL 1

Author Comment

by:brian2k1
ID: 22768694
How do I assign individual values from the returned QueryDataset dataset?

As you did with old ASP Recordsets:

LName = objRS("LName")
0
 
LVL 7

Accepted Solution

by:
talker2004 earned 500 total points
ID: 22768758

dim ds as dataset

LName = ds.tables("TableNameOrIndex").rows(RowIndex)('LName')

or

dim mydatarow as datarow = ds.tables(0).rows(0)
LName = dr("LastName")
FName = dr(3) 'Use index as opposed to column name


loop through the rows of a dataset
for each dr as datarow in ds.tables(0).rows
  debug.writeline( dr("LastName"))
next
0
 
LVL 7

Expert Comment

by:talker2004
ID: 22768772
the declaration mydatarow should have been

dim dr as datarow = ds.tables(0).rows(0)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now