ADO.NET Connection to SQL Server Code

[Adam Clark]

Here is what I have:

Dim strConn As String
Dim sqlCon As SqlClient.SqlConnection
Dim sqlCmd As SqlClient.SqlCommand
Dim sqlAdapt As SqlClient.SqlDataAdapter = New sqlclient.dataAdapter()
strConn = "data source=LHPSERVE;initial catalog=AssetTrakker;integrated
security=SSPI;persist security info=False;workstation id=LHPSERVE;packet
size=4096"

sqlCon = New SqlClient.SqlConnection(strConn)

sqlCon.Open()

dsAssets = New Data.DataSet("dsAssets")

sqlCmd = New SqlClient.SqlCommand("Select * from tblAssets")

sqlCmd.CommandType = CommandType.Text

sqlCmd.Connection = sqlCon

sqlAdapt.SelectCommand = sqlCmd

sqlAdapt.Fill(dsAssets, "tblAssets")





--------------------------------------------------------



Is there a way to make this simpler? DO I really need the sqlCmd ?



Thanks



Adam

 

[William Ryan eMVP]

Technically Yes but practically no. The overload for the adapter taeks a
command object but you can just pass in the sql string and specify a
connection object and you're good to go.

Also, don't open the connection yourself unless you have a good reason to
and if you do, use a try/catch/finally and make SURE you close it in the
finally..(Trust me on this!) You con't need to specify the CommandText if
you do use a command and you can overload it so you save a line of code

cmd = new SqlCommand("SQL STatemetn", connectionObject)
By taking advantage of a few of the overloads you can cut down about 4 lines
of code and if you pass in the SQL Statement, it will still create a
SqlCommand behind the scenes but you don't have to.

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[Cor Ligthert]

Hi Adam,

I have nothing to add to what Bill wrote only he did not answer your
question completly in my idea

sqlCmd = New SqlClient.SqlCommand("Select * from tblAssets")
sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlCon
sqlAdapt.SelectCommand = sqlCmd

sqlAdapt = new sqlClient.SqlDataAdapter("Select * from tblAssets", sqlCon)
And you can skip all what is above.

I think that this is the overloaded version Bill was talking about.

I hope this helps?

Cor