VB.Net/Database ADO.net/SqlCommand Insert

Материал из VB Эксперт
Перейти к: навигация, поиск

Execute NonQuery To Insert Data and report how many rows affected

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient public class MainClass

  Shared Sub Main()
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")
     "Create Command object
     Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()
     Try
        " Open Connection
        thisConnection.Open()
        Console.WriteLine("Connection Opened")
        " Execute NonQuery To Create Table
        nonqueryCommand.rumandText = "CREATE TABLE MyTable (ID integer)"
        Console.WriteLine("Executing {0}", nonqueryCommand.rumandText)
        Console.WriteLine("Number of rows affected : {0}", nonqueryCommand.ExecuteNonQuery())
        " Execute NonQuery To Insert Data
        nonqueryCommand.rumandText = "INSERT INTO MyTable VALUES (37)"
        Console.WriteLine("Executing {0}", nonqueryCommand.rumandText)
        Console.WriteLine("Number of rows affected : {0}",nonqueryCommand.ExecuteNonQuery())
        " Execute NonQuery To Drop Table
        nonqueryCommand.rumandText = "DROP TABLE MyTable"
        Console.WriteLine("Executing {0}", nonqueryCommand.rumandText)
        Console.WriteLine("Number of rows affected : {0}", nonqueryCommand.ExecuteNonQuery())
     Catch ex As SqlException
        " Display error
        Console.WriteLine("Error: " & ex.ToString())
     Finally
        " Close Connection
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
     End Try
  End Sub

End Class

      </source>


Insert Data and Check result

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient public class MainClass

  Shared Sub Main()
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")
     "Create Command object
     Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()
     Try
        " Open Connection
        thisConnection.Open()
        Console.WriteLine("Connection Opened")
        "Create Command objects
        Dim scalarCommand As New SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection)
        " Execute Scalar Query
        Console.WriteLine("Before INSERT, Number of Employees = {0}", _
           scalarCommand.ExecuteScalar())
        " Set up and execute INSERT Command
        nonqueryCommand.rumandText ="INSERT INTO Employee(ID, FirstName,LastName) " & _
           "VALUES (9,"A","A")"
        Console.WriteLine("Executing {0}", nonqueryCommand.rumandText)
        Console.WriteLine("Number of rows affected : {0}", _
           nonqueryCommand.ExecuteNonQuery())
        " Execute Scalar Query
        Console.WriteLine("After INSERT, Number of Employees = {0}", _
           scalarCommand.ExecuteScalar())
     Catch ex As SqlException
        " Display error
        Console.WriteLine("Error: " & ex.ToString())
     Finally
        " Close Connection
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
     End Try
  End Sub

End Class

      </source>


Insert data using SqlCommand

<source lang="vbnet"> Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Windows.Forms Imports System.Data.SqlClient

Public Class MainClass

   Shared Dim WithEvents con As SqlConnection
   Shared Sub Main()
       con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI") 
       Dim cmd As New SqlCommand()
       cmd.rumandText = "Insert into Employee (ID, FirstName, LastName) values(" & _
                         "1, "Joe", "Yin")"
       cmd.Connection = con
       Try
           con.Open()
           cmd.ExecuteNonQuery()
       Finally
           con.Close()
       End Try
   End Sub

End Class

      </source>