• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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

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
brian2k1
Asked:
brian2k1
  • 9
  • 2
1 Solution
 
talker2004Commented:
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
 
talker2004Commented:
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
 
talker2004Commented:
The above code was missing the declaration
dim dsRegistredUsers as Dataset
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
talker2004Commented:
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
 
talker2004Commented:
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
 
talker2004Commented:
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
 
brian2k1Author Commented:
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
 
talker2004Commented:
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
 
brian2k1Author Commented:
How do I assign individual values from the returned QueryDataset dataset?

As you did with old ASP Recordsets:

LName = objRS("LName")
0
 
talker2004Commented:

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
 
talker2004Commented:
the declaration mydatarow should have been

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now