Solved

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

Posted on 2008-10-20
11
384 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 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

22 Experts available now in Live!

Get 1:1 Help Now