ADO.NET connection to SQL Server

Hi, I thought I'd share this with you guys:

I looked for some examples on how to connect to SQL Server using ADO.NET but did not find much...

This worked for me:

Add an adodb Reference to your project.

Public Conn as ADODB.Connection
Public STR as String

Private Sub Connect (ByVal Server as String, ByVal UID as String, ByVal Pwd as String, ByVal DB as String)
Conn = New ADODB.Connection
Conn.ConnectionTimeout = 3000
STR = "Provider=SQLOLEDB.1;Initial Catalog=" & DB & ";Data Source=" & Server & ";User ID= & UID & ";password=" & Pwd
Conn.Open (STR)
End Sub


Now simply call the subroutine:

Connect <yourserver>, <userid>, <password>, <databasename>


any suggestions, remarks, etc... are more than welcome.

MartinLG

uh uh..bad. you dont use the old adodb stuff in VB .NET. You know have to use the classes provided in the System.Data namespace for maximum perfromance. Using that ADODB like you did uses COM interop and causes alot of over head and is slower because of the wrapper VS .NET has to create to make it work with .NET.

you need to look into
System.Data.SQLClient
System.Data.SQLDataAdpater
etc.

Yeah, I also came to that conclusion.... my apologies ;)
I just started with VB.NET and it is really confusing....but I guess you already knew that...

Anyone got some examples on how to connect to SQL Server, execute SP's, etc. using ADO.NET?

MartinLG

Code:

---

Dim conPubs as SqlConnection
                        conPubs = New SqlConnection("Server=111.11.11.1;uid=myname;pwd=mypassword;database=Candersen")
                        conPubs.Open()

                        Dim cmdSelectPeople As SqlCommand
                        Dim getpeople as SqlDataReader

                        cmdSelectPeople = New SqlCommand("Insert Into Snippets(catID, SnippetTitle, Snippet_Desc, Snippet) Values('" & strcatID & "','" & strsniptitle & "','" & strsnipdesc & "','" & strsnippet & "')" ,conPubs)

                        getpeople = cmdSelectPeople.ExecuteReader()

                        getpeople.close()
                        conPubs.close()

---

This code inserts a row to an sql table

This should be better:

Public Connection As SqlClient.SqlConnection
Public STR As String

Public Sub Connect(ByVal Server As String, ByVal UID As String, ByVal Pwd As String, ByVal Db As String)
Connection = New SqlClient.SqlConnection()
STR = "server=" & Server & ";database=" & Db & ";uid=" & UID & ";password=" & Pwd & ";"
Connection.ConnectionString = STR
Connection.Open()
End Sub


Then call the Connect subroutine

Connect <sqlserver>,<userid>,<password>,<database>



Still looking for example though on how to call SP's with ADO.NET

MartinLG