SQL Data Provider VB.NET Class By , published on 25 Feb 2006 | Filed in 0 Comments .NET ADO.NET SQL VB.NET SQL Server Page 2 of 3 Example Usage SQLDataProvider Class Documentation This class provides a fast and universal method for accessing SQL Server database. Create Instance At first you create an instance of SqlDatabase class. Dim sqldb As New SqlDatabase("Data Source=(local); Initial Catalog= ; UId = ; Pwd = ;") For more information about connection strings, visit ConnectionStrings.com. ExecuteNonQuery Method Executes a Transact-SQL statement against the connection and returns the number of rows affected. Dim params(0 To 1) As SqlParameter params(0) = New SqlParameter("@Firstname", SqlDbType.NVarChar, 120) params(0).Value = "Stefan" params(1) = New SqlParameter("@Lastname", SqlDbType.NVarChar, 120) params(1).Value = "Cameron" sqldb.ExecuteNonQuery("Insert Into dbo.Users(Firstname, LastName) Values(@FirstName, @LastName)", CommandType.Text, params) If you are using stored procedure,you can execute that without declaring parameters such as following code: sqldb.ExecuteNonQuery("dbo.CreateUser", Nothing, "Stefan", "Cameron") ExecuteScalar Method Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored. Dim count As Integer = sqldb.ExecuteScalar("Select Count(*) From dbo.Users", CommandType.Text)  MsgBox("Number of row(s): " & count) ExecuteReader Method Sends the CommandText to the Connection and builds a SqlDataReader. Dim FirstName As String = String.Empty  Dim LastName As String = String.Empty  Dim params(0) As SqlParameter  params(0) = New SqlParameter("@Id", SqlDbType.Int)  params(0).Value = 1  Dim dr As IDataReader = sqldb.ExecuteReader("Select * From dbo.Users Where (Id = @Id)", CommandType.Text, params)  While dr.Read()      FirstName = dr("Firstname")      LastName = dr("Lastname")  End While  dr.Close()  MsgBox(FirstName & " " & LastName, MsgBoxStyle.Information) There is a sample for using stored procedure: Create Procedure [dbo].[GetUserInfo]      (          @Id int      )  As  Begin      Select * From dbo.Users Where (Id = @Id)  End Dim FirstName As String = String.Empty  Dim LastName As String = String.Empty  Dim dr As IDataReader = sqldb.ExecuteReader("dbo.GetUserInfo", Nothing, 1)  While dr.Read()      FirstName = dr("Firstname")      LastName = dr("Lastname")  End While  dr.Close()  MsgBox(FirstName & " " & LastName, MsgBoxStyle.Information) Using Return Value Parameter If you are using stored procedure,you can get the value of 'return value parameter'. Create Procedure dbo.UserExists     (         @Firstname nvarchar(120),         @Lastname nvarchar(120)     ) As Begin     If Exists(Select * From dbo.Users Where (Firstname = @Firstname) And (Lastname = @Lastname))         Return 1 End Dim retval As Integer sqldb.ExecuteNonQuery("dbo.UserExists", retval, "Stefan", "Cameron") MsgBox("User Exists: " & IIf(retval = 1, "Yes", "No")) FillDataset Method Adds or refreshes rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table." Binding a DataGridView with FillDataset method. DataGridView1.DataSource = sqldb.FillDataset("Select * From dbo.Users", CommandType.Text).Tables(0) ExecuteDataset Method Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the System.Data.DataSet with the specified System.Data.DataTable name. ' Getting the System.Data.DataSet. Dim ds As DataSet = CType(DataGridView1.DataSource, DataTable).DataSet ' Declaring insert command object Dim inscmd As New SqlCommand("Insert Into dbo.Users(Firstname, Lastname) Values(@Firstname, @Lastname)") With inscmd     .CommandType = CommandType.Text     .Parameters.Add(New SqlParameter("@Firstname", SqlDbType.NVarChar, 120)).SourceColumn = "Firstname"     .Parameters.Add(New SqlParameter("@Lastname", SqlDbType.NVarChar, 120)).SourceColumn = "Lastname" End With ' Declaring update command object Dim updcmd As New SqlCommand("Update dbo.Users Set Firstname = @Firstname, Lastname = @Lastname Where (Id = @Id)") With updcmd     .CommandType = CommandType.Text     .Parameters.Add(New SqlParameter("@Id", SqlDbType.Int)).SourceColumn = "Id"     .Parameters.Add(New SqlParameter("@Firstname", SqlDbType.NVarChar, 120)).SourceColumn = "Firstname"     .Parameters.Add(New SqlParameter("@Lastname", SqlDbType.NVarChar, 120)).SourceColumn = "Lastname" End With ' Declaring delete command object Dim delcmd As New SqlCommand("Delete From dbo.Users Where (Id = @Id)") With delcmd     .CommandType = CommandType.Text     .Parameters.Add(New SqlParameter("@Id", SqlDbType.Int)).SourceColumn = "Id" End With ' Updating data source sqldb.ExecuteDataset(inscmd, updcmd, delcmd, ds, ds.Tables(0).TableName)

评论