Solved

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

Posted on 2008-10-20
11
422 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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