none
Insert, update, select, delete from SQL with Visual Studio 2010

    Question

  • Hi,

    I'm experienced in PHP but new to Visual Studio. Whilst I'm finding the experience very user-friendly so far, one thing I simply cannot get my head around is the way the database works. Even googling a solution doesn't get simple basic tutorials that are easily inserted into applications.

    I need a local database that is packaged within the application.

    I have set up a dataset from server-based database, added it to my project etc, set up the database name, set up the table and some data.

    I've tried 3-4 different methods to insert/select/delete/update it but nothing works and it makes no sense at all. I want to try to use normal SQL request formatting as used in PHP for getting data (ie select * from mytable where id='1' etc).

    Has anyone got any really basic code that will work with every type of SQL query?

    Thanks.

    Thursday, September 26, 2013 10:22 PM

Answers

  • When you use SQL Server, then for best performance and support you should use SqlClient namespace and not OleDB.

    For simple insert/update/delete command you can use SqlCommand, similar to your first example. Here a basic code snippet for run an INSERT statement. And note, for security reasons always use parameterized queries; don't create SQL statements dynamically, because that SQL Injections can happen.

    Imports System.Data.SqlClient
    
    Module Test
    
        Sub Main()
    
            Dim connStr = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True"
            Using con = New SqlConnection(connStr)
    
                Dim sql = "INSERT INTO myTable (forename, surname) " &
                          "VALUES (@forename, @surname)"
                Using cmd As New SqlClient.SqlCommand(sql, con)
                    cmd.Parameters.AddWithValue("@forename", "Bob")
                    cmd.Parameters.AddWithValue("@surname", "Smith")
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
        End Sub
    
    End Module
    


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by RobertyBob Friday, September 27, 2013 3:22 PM
    Friday, September 27, 2013 8:00 AM

All replies

  • Have a look at this video:

    MSSQL - Simple CRUD Example


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Thursday, September 26, 2013 10:39 PM
  • Hello,

    Which programming language (C#/VB.NET/..) are you using? What have you tried up to now?

    An option could be to use Entity Framework, an O/R mapper; see Getting Started (Entity Framework)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 27, 2013 4:14 AM
  • Thanks for the responses guys.

    Saeid - I've checked out the video and whilst it shows how to create a database and make queries it doesn't seem to show any VB code. From PHP I know all the methods for querying the db table but I can't find an easy way to write a runtime command in VB. Thanks

    Olaf - I'm writing in VB.net. Below is some sample code that needs tweaking or some further explanation to deal with insert/select/update/delete. This seems to be a straightforward method and seems easier than sqlserver method but I can't work out what to put in the data source bit, whether I need to add a resource for something and how to assign the username/password. I'm not sure if this is even a local internal database access code.

    Imports System
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim thisid = 1
            Dim objconnection As OleDbConnection = Nothing
            Dim objcmd As OleDbCommand = Nothing
            Dim strconnection As String
            Dim strSQL As String
            strconnection = "provider=SQlOLEDB;data source=???????????????;database=myTable;user ID=????;password=?????;"
            objconnection = New OleDbConnection(strconnection)
            objconnection.ConnectionString = strconnection
            objconnection.Open()
            strSQL = "insert into myTable(forename,surname)values(Bob,Smith)"
            objcmd = New OleDbCommand(strSQL, objconnection)
            objcmd.ExecuteNonQuery()
            objconnection.Close()
            TextBox1.Text = "Success"
        End Sub
    
    End Class

    Then there is the SQLServer code I tried but I can't get it to work with all insert/select/update/delete requests.

    Dim connection As New SqlClient.SqlConnection
    Dim command As New SqlClient.SqlCommand
    Dim adaptor As New SqlClient.SqlDataAdapter
    Dim dataset As New DataSet
    connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True")
    command.CommandText = "SELECT * FROM [myTable] WHERE forename!=''"
    connection.Open()
    command.Connection = connection
    adaptor.SelectCommand = command
    adaptor.Fill(dataset, "0")

    Not sure which is best or whether either works.

    Thanks

    Friday, September 27, 2013 7:30 AM
  • When you use SQL Server, then for best performance and support you should use SqlClient namespace and not OleDB.

    For simple insert/update/delete command you can use SqlCommand, similar to your first example. Here a basic code snippet for run an INSERT statement. And note, for security reasons always use parameterized queries; don't create SQL statements dynamically, because that SQL Injections can happen.

    Imports System.Data.SqlClient
    
    Module Test
    
        Sub Main()
    
            Dim connStr = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True"
            Using con = New SqlConnection(connStr)
    
                Dim sql = "INSERT INTO myTable (forename, surname) " &
                          "VALUES (@forename, @surname)"
                Using cmd As New SqlClient.SqlCommand(sql, con)
                    cmd.Parameters.AddWithValue("@forename", "Bob")
                    cmd.Parameters.AddWithValue("@surname", "Smith")
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
        End Sub
    
    End Module
    


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by RobertyBob Friday, September 27, 2013 3:22 PM
    Friday, September 27, 2013 8:00 AM
  • Many thanks, Olaf.

    This is working great though hard to view properly in debug mode but that's a minor issue at my end.

    I have a working insert/delete/update but I wonder if you can give me an example of a method to display a set of rows via looping through the results from a select query? Say, 'select surname from myTable where id<5'.

    Thanks again

    Friday, September 27, 2013 12:01 PM
  • Below is a parameterized example based from Olaf's code with a loop to retrieve results from a SqlDataReader.

    Using con = New SqlConnection(connStr) Dim sql = "SELECT surname FROM dbo.myTable WHERE id < @id;" Using cmd As New SqlClient.SqlCommand(sql, con)

    cmd.Parameters.Add("@id", SqlDbType.Int).Value = 5 con.Open() Dim reader = cmd.ExecuteReader While reader.Read Console.WriteLine(reader.GetString(reader.GetOrdinal("surname"))) End While reader.Close() con.Close()

    End Using

    End Using



    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, September 27, 2013 12:16 PM
  • can give me an example of a method to display a set of rows via looping through the results from a select query? Say, 'select surname from myTable where id<5'.

    Sure. In your first sample you used DataAdapter+DataSet; with this all rows from the result set will be transfered from SQL Server to the client into the DataSet, with that you can then work "offline" without an existing connection to SQL Server, but for large results it will use a lot of memory on the client.

    Other option is to use a DataReader, it works like a server cursor and you can process the data row by row.

    The following example queries the names from the system view "sys.objects", which exists in every database, so you can run it without any modification. The printout is done to a Console.

    Imports System.Data.SqlClient
    
    Module Test
    
        Sub Main()
    
            Dim connStr = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True"
            Using con = New SqlConnection(connStr)
    
                Dim sql = "SELECT name " &
                          "FROM sys.objects " &
                          "WHERE object_id < @id"
                Using cmd As New SqlClient.SqlCommand(sql, con)
                    cmd.Parameters.AddWithValue("@id", 10000)
                    con.Open()
                    Dim reader = cmd.ExecuteReader()
    
                    While reader.Read()
                        Console.WriteLine(reader.GetString(0))
                    End While
                    reader.Close()
    
                    con.Close()
                End Using
            End Using
    
            ' Wait for key stroke
            Console.ReadKey()
        End Sub
    
    End Module
    


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 27, 2013 12:22 PM
  • >I've tried 3-4 different methods to insert/select/delete/update

    You should develop stored procedures to do those tasks and call the sp-s from the application.

    QUOTE: "CREATE PROCEDURE dbo.sp_Students_INS_byPK
           @student_id                     INT                      ,
           @password                       VARCHAR(15)      = NULL  ,
           @active_flg                     TINYINT                  ,
           @lastname                       VARCHAR(30)      = NULL  ,
           @birth_dttm                     DATETIME         = NULL  ,
           @gpa                            INT              = NULL  ,
           @is_on_staff                    TINYINT                  
    AS
    BEGIN
         SET NOCOUNT ON

         INSERT INTO dbo.Students
              (
                student_id                   ,
                password                     ,
                active_flg                   ,
                lastname                     ,
                birth_dttm                   ,
                gpa                          ,
                is_on_staff                 
              )
         VALUES
              (
                @student_id                   ,
                @password                     ,
                @active_flg                   ,
                @lastname                     ,
                @birth_dttm                   ,
                @gpa                          ,
                @is_on_staff                 
              )

    END "

    Link: http://www.sqlinfo.net/sqlserver/sql_server_stored_procedure_INSERT.php


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Friday, September 27, 2013 1:26 PM
  • Dan and Olaf - I can see the logic in your code and can obtain some data however with both your examples I get the following error on the Console.Writeline - 'Unable to cast object of type 'System.Int32' to type 'System.String''.

    Not sure if that is an error on your part but since it happens with both code snippets I think it must be something to do with the way I've set things up initially. Any ideas?

    Friday, September 27, 2013 2:46 PM
  • 'Unable to cast object of type 'System.Int32' to type 'System.String''.

    I tested my code, of course, and for me it works. Are you using a different code? The easiest way to cast a data type to string is to use the .ToString() method. If you e.g. use GetInt32() instead of GetString(), then you can write it as

    Console.WriteLine(reader.GetInt32(0).ToString())


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, September 27, 2013 2:59 PM
  • Thanks Olaf - I don't fully understand the workings of all this but I have managed to get the data to display by messing with the available options for the reader and reader.GetValue(0) etc seems to do the trick so all is great now.

    Can you just confirm that using a local database within the application like this will store the data even when the application is closed then re-opened later on?

    Friday, September 27, 2013 3:09 PM
  • The data will be stored in the database (file), so yes.

    But note, you are using a "user instance" of SQL Server and the database file will be attached during runtime (see your connection string). During debugging a copy of the database file will be used (from "Debug" subfolder) and if you clean/rebuild your solution, may a new copy of the database file MDF will we created for debugging purpose and this is may empty.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 27, 2013 3:16 PM
  • No problem - understood. Obviously will have to deal with upgrade downloads affecting the stored database etc but will deal with that later.

    Thanks so much for all the help here - has been a very efficient and useful tutorial.

    All the best.

    Friday, September 27, 2013 3:21 PM
  • Your are welcome; please fell free to come back if you have further questions.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 27, 2013 3:24 PM