How to edit and delete rows of a database using ASP?

Question

I currently have a website that displays all the data within the database

        Dim dcSQL As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("College").ConnectionString)
        Dim dbAdapt As New System.Data.SqlClient.SqlDataAdapter()
        Dim cmd As New SqlCommand("SELECT * FROM [College].[dbo].[Class]", dcSQL)
        dbAdapt.SelectCommand = cmd


        Dim ds As New DataSet
        dbAdapt.Fill(ds)

        If dbAdapt IsNot Nothing Then
            gvStudents0.DataSource = ds.Tables(0)
            gvStudents0.DataBind()
        End If
    Catch ex As Exception

    End Try
End Sub'

But I want to create/edit and delete the database, I am aware of how to do this in EF but I am currently not aware in SQL, can someone help?

Answer 1

The following snippet is from SqlCommand.Parameters Property .

Updating

    Dim commandText As String = _
     "UPDATE Sales.Store SET Demographics = @demographics " _
     & "WHERE CustomerID = @ID;"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(commandText, connection)

        ' Add CustomerID parameter for WHERE clause.
        command.Parameters.Add("@ID", SqlDbType.Int)
        command.Parameters("@ID").Value = customerID

        ' Use AddWithValue to assign Demographics.
        ' SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml)

        Try
            connection.Open()
            Dim rowsAffected As Integer = command.ExecuteNonQuery()
            Console.WriteLine("RowsAffected: {0}", rowsAffected)

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using 

Deleting

For deleting the command could be as follows.

Dim commandText As String = _
     "DELETE FROM Sales.Store WHERE CustomerID = @ID;"

(...)

command.Parameters.Add("@CustomerID ", SqlDbType.Int).Value = xxx;

Inserting

For inserting the sql will be something like the following

Dim commandText As String = _
     "INSERT INTO tablename(column1,column2,column3)" _
     & " VALUES(@column1,@column2,@column3);"

and then

command.Parameters.Add("@column1", SqlDbType.Int).Value = x;
command.Parameters.Add("@column2", SqlDbType.Int).Value = y;
command.Parameters.Add("@column3", SqlDbType.Int).Value = z;

or

command.Parameters.AddWithValue("@column1", x);
command.Parameters.AddWithValue("@column2", y);
command.Parameters.AddWithValue("@column3", z)

Please note that this is not ASP.NET specific. A console app could use this code as well.

Answer 2

You can use this class that I make and don’t care about SQL (in some cases). You can just pas lists (Of String) as parameter and the methods do the tasks.

Imports System.Data.SqlClient

Friend Class SqlDB

    Friend Property _SConessione As String = ""
    Private _NomeDB As String = ""
    Private _SQLConnection As SqlConnection
    Private _SQLCommand As SqlCommand
    Private _SQLAdapter As SqlDataAdapter
    Private _SQLReader As SqlDataReader



    Friend Enum Version
        SQL_SERV_2005 = 90
        SQL_SERV_2008 = 100
        SQL_SERV_2012 = 110
    End Enum

    Friend Structure Inputs
        Const _CHAR As String = "CHAR"
        Const _VARCHAR As String = "VARCHAR"
        Const _TEXT As String = "TEXT"
        Const _NCHAR As String = "NCHAR"
        Const _NVARCHAR As String = "NVARCHAR"
        Const _NTEXT As String = "NTEXT"
        Const _BIT As String = "BIT"
        Const _BINARY As String = "BINARY"
        Const _VARBINARY As String = "VARBINARY"
        Const _IMAGE As String = "IMAGE"
        Const _TINYINT As String = "TINYINT"
        Const _SMALLINT As String = "SMALLINT"
        Const _BIGINT As String = "BIGINT"
        Const _DECIMAL As String = "DECIMAL(10,2)"
        Const _NUMERIC As String = "NUMERIC(10)"
        Const _FLOAT As String = "FLOAT"
        Const _DATE As String = "DATE"
        Const _DATETIME As String = "DATETIME"
    End Structure

    Friend Structure NULLS
        Const NULL = "NULL"
        Const NOT_NULL = "NOT NULL"
    End Structure

    Friend Structure Cols
        Dim Nome As String
        Dim TInput As String
        Dim Length As Integer
        Dim Null As String
    End Structure

    Private _Versione As Version = Version.SQL_SERV_2008



    Friend Function SqlDate(dateStr As String) As String
        If IsDate(dateStr) Then
            Dim sDate As String = ""
            dateStr = CDate(dateStr).ToShortDateString
            sDate = " CONVERT(DATE, '" & dateStr & "', 0) "
            Return sDate
        Else
            Throw New Exception("Formato data non valido")
        End If
        Return ""
    End Function

    Friend Property Versione As Version
        Get
            Return _Versione
        End Get
        Set(value As Version)
            _Versione = value
        End Set
    End Property

    Friend Enum TypeIndex
        CLUSTER = 0
        NONCOLUSTER = 1
    End Enum

    Friend Sub New(ByVal ConString As String,
                    ByVal VersionServer As Version)

        Try

            Versione = VersionServer
            If ConString.Length > 0 Then
                _SConessione = ConString
            End If

        Catch ex As Exception

#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If

        End Try

    End Sub


    Friend Sub New(ByVal ConString As String)

        Try

            Versione = _Versione
            If ConString.Length > 0 Then
                _SConessione = ConString
            End If

        Catch ex As Exception

#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If
        End Try

    End Sub



    Friend Function ExecuteRestore(Optional ByVal pathBackup As String = "", Optional NameDb As String = "") As Boolean

        Dim res As Boolean = False


        If NameDb.Length > 0 Then _NomeDB = NameDb
        If _NomeDB.Length = 0 Then
            Debug.WriteLine("Set the name of DB")
            Return False
        End If

        Dim Sql = " USE [master]; " & vbCrLf
        Sql &= " RESTORE DATABASE [" & _NomeDB & "]" & vbCrLf
        Sql &= " FROM DISK = '" & pathBackup & "'" & vbCrLf
        Sql &= " WITH FILE = 1, NOUNLOAD, REPLACE" & vbCrLf  ' 

        Try

            Using SQLConnection = New SqlConnection(_SConessione)

                Open(SQLConnection)

                Dim command As New SqlCommand(Sql, SQLConnection)
                res = CBool(command.ExecuteNonQuery())
                res = True

            End Using

        Catch ex As Exception

#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If
        End Try

        Return res
    End Function


    Friend Function ExecuteQuery(sql As String) As Boolean

        Dim res As Boolean = False

        If sql.Length > 0 Then
            sql = "USE [" & _NomeDB & "]; " & vbCrLf & sql
        End If

        Try

            Using SQLConnection = New SqlConnection(_SConessione)
                Open(SQLConnection)
                Dim command As New SqlCommand(sql, SQLConnection)
                command.ExecuteNonQuery()
            End Using

            Return True

        Catch ex As Exception

#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If

        End Try

        Return res

    End Function


    Friend Function ReadTheFirstResult(sql As String) As Object

        Dim sRet As Object = ""
        If sql.Length > 0 Then

            sql = "USE [" & _NomeDB & "]; " & vbCrLf & sql
        End If

        Try

            Dim SQLConnection = New SqlConnection(_SConessione)
            Open(SQLConnection)
            Dim command As New SqlCommand(sql, SQLConnection)
            Dim reader As SqlDataReader = command.ExecuteReader()

            While reader.Read
                sRet = reader(0)
                Exit While
            End While

        Catch ex As SqlException


#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If
        End Try


        Return sRet

    End Function

    Friend Function GetDataAsDataReader(Sql As String, Optional ByRef RowsCount As Integer = -1) As SqlDataReader
        Dim reader As SqlDataReader = Nothing

        If Sql.Length > 0 Then
            Sql = "USE [" & _NomeDB & "]; " & vbCrLf & Sql
        End If

        Try

            Dim SQLConnection = New SqlConnection(_SConessione)
            Open(SQLConnection)

            If RowsCount > -1 Then
                Dim DtTable As New DataTable
                Dim mSqlAdapter As New SqlClient.SqlDataAdapter With {
                    .SelectCommand = New SqlCommand(Sql, SQLConnection)
                }
                mSqlAdapter.Fill(DtTable)
                RowsCount = DtTable.Rows.Count
            End If

            Dim command As New SqlCommand(Sql, SQLConnection)
            reader = command.ExecuteReader()


        Catch ex As SqlException


#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If
        End Try

        Return reader
    End Function

    Friend Function CheckOrCreateDB(ByVal dbName As String) As Boolean

        Try

            Dim query As StringBuilder = New StringBuilder

            query.Append(" USE [master]; ")
            query.Append("IF NOT EXISTS(SELECT * FROM sys.databases Where name = '" & dbName & "') ")
            query.Append(" CREATE DATABASE [" & dbName & "] ")

            query.Append(" ALTER DATABASE [" & dbName & "] SET  COMPATIBILITY_LEVEL = " & Versione & " ;")
            query.Append(" ALTER DATABASE [" & dbName & "] SET  READ_WRITE ;")
            query.Append(" ALTER DATABASE [" & dbName & "] SET  MULTI_USER ;")


            Using SQLConnection = New SqlConnection(_SConessione)
                Open(SQLConnection)
                Dim command As New SqlCommand(query.ToString, SQLConnection)
                command.ExecuteNonQuery()
                _NomeDB = dbName
            End Using

            Return True

        Catch ex As Exception

#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If

            _NomeDB = ""
            Return False

        End Try

    End Function

    Friend Function CheckOrCreateTable(ByVal tableName As String, ByVal columns As List(Of Cols)) As Boolean
        Try

            If _NomeDB IsNot Nothing AndAlso _NomeDB.Length > 0 Then

                Dim query As StringBuilder = New StringBuilder

                query.Append(" USE [" & _NomeDB & "]; ")
                query.Append(" IF NOT EXISTS ( SELECT * FROM sys.tables WHERE name = '" & tableName & "' ) ")
                query.Append(" CREATE TABLE " & tableName & " ")
                query.Append(" ( IndexRow INT IDENTITY(1,1) NOT NULL ); ")


                Using SQLConnection = New SqlConnection(_SConessione)
                    Open(SQLConnection)
                    Dim command As New SqlCommand(query.ToString, SQLConnection)
                    command.ExecuteNonQuery()
                End Using

                query.Clear()

                If columns IsNot Nothing AndAlso columns.Count > 0 Then
                    For i As Integer = 0 To columns.Count - 1
                        Dim TipoInput As String = columns(i).TInput & " "

                        If columns(i).Length > 0 Then
                            CreateColumn(tableName, columns(i).Nome, TipoInput, columns(i).Null, CStr(columns(i).Length))
                        Else
                            CreateColumn(tableName, columns(i).Nome, TipoInput, columns(i).Null)
                        End If
                    Next
                End If

                Return True

            End If
        Catch ex As Exception

#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If
        End Try

        Return False
    End Function

    Friend Function CreateColumn(ByVal tableName As String,
                                 ByVal columnName As String,
                                 ByVal TipoInput As String,
                                 ByVal nullOrNot As String,
                                 Optional ByVal Lunghezza As String = "-1") As Integer


        Dim i As Integer = 0

        Try

            Dim mTipoInput As String = TipoInput
            If CInt(Lunghezza) > -1 Then mTipoInput = mTipoInput & "(" & Lunghezza & ") "

            Dim query As StringBuilder = New StringBuilder

            query.Append(" USE [" & _NomeDB & "]; ")
            query.Append(" IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID('" & tableName & "') AND name = '" & columnName & "' )")
            query.Append(" ALTER TABLE " & tableName)
            query.Append(" ADD " & columnName & "  " & mTipoInput & "  " & nullOrNot)

            Using SQLConnection = New SqlConnection(_SConessione)
                Open(SQLConnection)
                Dim command As New SqlCommand(query.ToString, SQLConnection)
                i = command.ExecuteNonQuery()
            End Using

        Catch ex As Exception

#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If

        End Try


        Return i
    End Function

    Friend Function CheckOrCreateColumn(ByVal tableName As String,
                                 ByVal columnName As String,
                                 Optional ByVal inputType As String = Inputs._VARCHAR,
                                 Optional ByVal nullOrNot As String = "NULL",
                                 Optional ByVal Lunghezza As String = "-1") As Integer


        Dim i As Integer = 0

        Try

            Dim mTipoInput As String = inputType
            If CInt(Lunghezza) > -1 Then mTipoInput = mTipoInput & "(" & Lunghezza & ") "

            Dim query As StringBuilder = New StringBuilder

            query.Append(" USE [" & _NomeDB & "]; ")
            query.Append(" IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID('" & tableName & "') AND name = '" & columnName & "' )")
            query.Append(" ALTER TABLE " & tableName)
            query.Append(" ADD " & columnName & "  " & mTipoInput & "  " & nullOrNot)

            Using SQLConnection = New SqlConnection(_SConessione)
                Open(SQLConnection)
                Dim command As New SqlCommand(query.ToString, SQLConnection)
                i = command.ExecuteNonQuery()
            End Using

        Catch ex As Exception

#If DEBUG Then
            Debug.WriteLine(ex.ToString)
#End If

        End Try


        Return i
    End Function

    Friend Function CheckOrCreateIndex(tipo As TypeIndex,
                                       indexName As String,
                                       tableName As String,
                                       columnsIndexes() As String,
                              Optional includedColumns() As String = Nothing) As String

        Dim res As String = ""

        Try

            ' CREATE INDEX IX_TableName_Col1
            ' ON dbo.TableName
            ' (column_1)

            If columnsIndexes.Length > 0 Then
                Dim query As StringBuilder = New StringBuilder

                query.Append(" USE [" & _NomeDB & "]; ")
                query.Append(" IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = '" & indexName & "')")

                Select Case tipo
                    Case TypeIndex.CLUSTER
                        query.Append(" CREATE CLUSTERED INDEX " & indexName)
                    Case TypeIndex.NONCOLUSTER
                        query.Append(" CREATE NONCLUSTERED INDEX " & indexName)
                End Select

                query.Append(" ON [" & tableName & "] (" & JoinParams(columnsIndexes, False).Trim & ")")

                If includedColumns IsNot Nothing AndAlso includedColumns.Length > 0 Then
                    query.Append(" Include (" & JoinParams(includedColumns, False) & ")")
                End If



                Using SQLConnection = New SqlConnection(_SConessione)

                    Open(SQLConnection)
                    Dim command As New SqlCommand(query.ToString, SQLConnection)
                    command.ExecuteNonQuery()
                    res = indexName

                End Using

            End If



        Catch ex As Exception

#If DEBUG Then

            Debug.WriteLine(ex.ToString)
            'Debugger.Break()

#End If
        End Try

        Return res

    End Function

    Friend Function InsertInto(tableName As String, listOfCols As List(Of String), listOfValues As List(Of String)) As Boolean

        Try

            Dim Sql As String = " USE [" & _NomeDB & "]; "
            Sql &= " INSERT INTO " & tableName

            If listOfCols.Count > 0 AndAlso listOfValues.Count > 0 Then

                Sql &= " (" & Join(listOfCols.ToArray, ",") & ")"
                Sql &= " VALUES (" & JoinParams(listOfValues.ToArray) & ")"

                Using SQLConnection = New SqlConnection(_SConessione)

                    Open(SQLConnection)
                    Dim command As New SqlCommand(Sql, SQLConnection)
                    command.ExecuteNonQuery()

                End Using

                Return True

            End If

        Catch ex As Exception

#If DEBUG Then
            Console.WriteLine(ex.ToString)
            'Stop
#End If
        End Try

        Return False

    End Function

    Friend Function UpdateInto(tableName As String, listOfCols As List(Of String), listOfValues As List(Of String), WhereClausola As String) As Integer

        Dim ret As Integer = 0

        Try

            Dim Sql As String = " USE [" & _NomeDB & "]; "
            Sql &= " UPDATE " & tableName & " SET "

            If listOfCols.Count > 0 AndAlso listOfValues.Count > 0 Then


                If listOfCols.Count > 0 AndAlso listOfValues.Count > 0 Then
                    '               UPDATE Customers
                    'SET 
                    'ContactName ='Alfred Schmidt', 
                    'City ='Hamburg'
                    'WHERE CustomerName ='Alfreds Futterkiste'; 

                    For i As Integer = 0 To listOfCols.Count - 1
                        Sql &= " " & listOfCols(i) & "=" & CStr(IIf(listOfValues(i) IsNot Nothing, listOfValues(i), "NULL")) & CStr(IIf(i < listOfCols.Count - 1, ",", ""))
                    Next

                    If WhereClausola IsNot Nothing AndAlso WhereClausola.Length > 0 Then
                        WhereClausola = WhereClausola.ToLower.Replace("where", " ")
                        Sql &= " WHERE " & WhereClausola

                        Using SQLConnection = New SqlConnection(_SConessione)
                            Open(SQLConnection)
                            Dim command As New SqlCommand(Sql, SQLConnection)
                            ret = command.ExecuteNonQuery()
                        End Using

                    End If

                End If

            End If

            Return ret

        Catch ex As Exception

#If DEBUG Then
            Console.WriteLine(ex.ToString)
            'Stop
#End If
        End Try

        Return ret

    End Function



    Private Function JoinParams(ByVal params() As String, Optional ByVal UseVbcrlf As Boolean = True) As String
        Dim s As String = ""
        Dim mVbCrlf As String = CStr(IIf(UseVbcrlf, vbCrLf, " "))
        For i As Integer = 0 To params.Length - 1
            If params(i) IsNot Nothing Then
                If i = params.Length - 1 Then
                    s &= params(i) & " " & mVbCrlf
                Else
                    s &= params(i) & ", " & mVbCrlf
                End If

            End If
        Next
        Return s

    End Function

    Friend Sub Open(sqlConn As SqlConnection)

        Try
            _SQLConnection = sqlConn
            Select Case sqlConn.State
                Case <> ConnectionState.Connecting
                    sqlConn.Close()
                    sqlConn.ConnectionString = _SConessione
                    sqlConn.Open()
            End Select

        Catch ex As Exception

        End Try
    End Sub


    Friend Sub Close()
        Try
            If _SQLConnection IsNot Nothing Then
                _SQLConnection.Close()
            End If
        Catch ex As Exception
            Debug.WriteLine(ex.ToString)
        End Try

    End Sub





End Class




Module SQLUtil

    Function InLIKE(s As String) As String
        '%email pippo%
        If s IsNot Nothing AndAlso s.Length > 0 Then
            Return "%" & s & "%"
        End If
        Return "%%"
    End Function

    Function InApice(value As String) As String
        If value Is Nothing Then Return "''"
        If value.Length = 0 Then Return "''"

        Dim appice As String = "'" '"`"

        value = value.Replace("'", "''")
        Return " " & appice & value & appice & " "

    End Function

    Function ToDate(dateStr As String) As String

        If IsDate(dateStr) Then
            Dim sDate As String = CDate(dateStr).ToString
            Return sDate
        End If

        Return dateStr

    End Function

    Function ToDbDate(dateStr As String) As String
        dateStr = New Date(dateStr)

        If IsDate(dateStr) Then
            Dim mDate As Date = CDate(dateStr)

            Dim anno As Integer = mDate.Year
            Dim mese As Integer = mDate.Month
            Dim giorno As Integer = mDate.Day
            Dim ora As Integer = mDate.Hour
            Dim minuto As Integer = mDate.Minute

            If Not IsNumeric(anno) Then anno = 0
            If Not IsNumeric(mese) Then mese = 0
            If Not IsNumeric(giorno) Then giorno = 0
            If Not IsNumeric(ora) Then ora = 0
            If Not IsNumeric(minuto) Then minuto = 0


            anno = Strings.Format(anno, "0000")
            mese = Strings.Format(mese, "00")
            giorno = Strings.Format(giorno, "00")
            ora = Strings.Format(ora, "00")
            minuto = Strings.Format(minuto, "00")

            Dim sDate As String = CStr(anno) & CStr(mese) & CStr(giorno) & CStr(ora) & CStr(minuto)

            Return sDate
        End If

        Return ""

    End Function

    Function FromDbDate(s As String, Optional ancheOraEMinuti As Boolean = False) As String
        If s Is Nothing Then Return ""
        If s.Length = 0 Then Return ""
        If s.Length > 12 Then
            Dim data As String = ""

            Dim anno As String = s.Substring(0, 4)
            Dim mese As String = s.Substring(5, 2)
            Dim giorno As String = s.Substring(7, 2)

            Dim ora As String = s.Substring(9, 2)
            Dim minuti As String = s.Substring(11, 2)

        End If

        Return ""


    End Function


End Module