How to access SQL Server from my WCF web service?
VS Express 2012, SQL Server Express 2012, Win 8.1
I have a (very) simple WCF hosted as a web service on IIS. I also have a SQL Server instance (with 1 table) installed on the same machine.
I need a step-by-step guide on how to connect to SQL from the WCF (VB) and retrieve a single record from the table (ie: "SELECT LAST NAME FROM MYTABLE WHERE PK = 1;"). That's it. I don't need a 1,200 page manual -- which is all Google keeps throwing at me.
Anyone know of a quick, clean resource?
Dim connStr = "Data Source=SQLServerName\InstanceName;Initial Catalog=DatabaseName;Integrated Security=SSPI" Using conn As New SqlConnection(connStr) conn.Open() Using cmd = conn.CreateCommand() cmd.CommandText = "SELECT LAST_NAME FROM MYTABLE WHERE PK = @pk" cmd.Parameters.AddWithValue("@pk", 1) Dim result = cmd.ExecuteScalar() If Typeof result Is DbNull Then ' Handle null value Else ' Otherwise End If End Using End Using
This sample assumes that you want to retrieve a single cell as in your statement. If you want to retrieve tabular data, have a look a the SqlDataReader or SqlDataAdapter class. Please note that - especially in server applications - it is important to dispose of the created instances properly.
There is no difference on using ADO.NET in a WCF service or in a normal application from the point of view of the classes required.
The first thing needed is a connection string that allows your SqlConnection object to find the host and the database that you want to use. Here examples on connection strings
Then you usually need a SqlCommand that encapsulates the SQL text and parameters needed to retrieve the data (Here you setup your SELECT statement and conditions)
Finally you need a SqlDataReader that get the result of the command execution and allows you to loop over the results.
Keep in mind that this is just a minimal todo-list and there are numerous other ways to work with data. Basic objects like SqlDataAdapter, Dataset, DataTable present different ways to load data from a database and use that data. Then on top of these there are technologies like Linq To Sql and Object Relational Mapper tools that abstract the data access and offer high level functionality on top of data.
That's probably the reason you get so much informations on data access technologies