The quickest way to interface with a database from a web-based application is using direct data connections. Rather than using DataSets, DataAdapters, DataTables and all that confusing stuff, go straight to the data! The following is an easy way to perform all the data manipulation you need.
1.) Declaire a connection to you database in your web.config
file.
<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=SERVER\SQLINSTANCE;Initial Catalog=databaseName;Integrated Security=True;Pooling=False" providerName="System.Data.SqlClient"/>
</connectionStrings>
More information on Connection Strings can be found here:
http://msdn2.microsoft.com/en-us/library/bf7sd233.aspx
2.) Declare your connetion string in your code-behind page.
Dim connStr As String = ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString
3.) Declare a new SqlConnection
using the connStr
Dim conn As New System.Data.SqlClient.SqlConnection(connStr)
4.) Declare a new SqlCommand
. The first element of the constructor is your SQL statement, the second element is the SqlConnection
, conn
Dim cmd As New System.Data.SqlClient.SqlCommand("TRUNCATE TABLE [customer]", conn)
5.) Use a using statement to instantiate your connection. This will automatically create and destroy your connection when used.
Using conn
conn.Open()
cmd.ExecuteNonQuery()
End Using
That’s it! Using this method your data access and manipulation will be much quicker and cleaner!
It’s also very easy to use this method to bind standard ASP controls to data from a database using connected data access.
If your SQL was: “SELECT [name], [date_of_birth], [eye_color] FROM [people]
“, you can bind that to DropDownLists, GridViews, Repeaters, FormViews, etc.
DropDownLists:
Using conn
conn.Open()
dim r as Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
While r.Read
DropDownList1.Items.Add(r(0).ToString)
End While
End Using
GridViews:
Using conn
conn.Open()
GridView1.DataSource = cmd.ExecuteReader()
GrieView1.DataBind()
End Using