SQL Server is the most widely used database management system with Dot Net applications. Having the similar inherent architecture, these two form up a very good development environment and provide a feel of comfort for the programmer. The following steps will be quite helpful for you if you are new to the development of Dot Net applications or if you are going to develop data intensive Dot Net application for the first time. Programming structure of VB.NET is used in this article but similar steps can be followed if you are working with C#.

Defining Connection String

In order to access a database from your VB.NET code, the first step that you need to perform is to define a connection string which refers to the data source of your SQL Server database. So, define a public variable in your application which can be accessed from any form of the application requiring database interaction. An example connection string may be like this

Public ConnStr As String = “Data Source=MyServer;Initial Catalog=MyDB;User ID=sa; password=mypassword”

The first element of a connection string is information about the server. “MyServer” is the name of the server on which required database resides. Initial Catalog attribute refers to the database which you need to access. User Id and password attributes refer to the login credentials required to access this database.

Data Insertion

Data insertion is the most important data processing task. Following snippet of code will insert the data into the database.

Dim strsql = “insert into Logs(LoginID,Activity, Category,StartTime, EndTime) values (@loginid,@activity,@category,@starttime,@endtime)”

Using cn As New SqlConnection(ConnStr), _

cmd As New SqlCommand(strsql, cn)

cmd.Parameters.Add(“@loginid”, Data.SqlDbType.Int).Value = CurrentLoginID

cmd.Parameters.Add(“@activity”, Data.SqlDbType.NVarChar).Value = GameActivity

cmd.Parameters.Add(“@category”, Data.SqlDbType.NVarChar).Value = “Games”

cmd.Parameters.Add(“@starttime”, Data.SqlDbType.DateTime).Value =     GameStartTime

cmd.Parameters.Add(“@endtime”, Data.SqlDbType.DateTime).Value = DateTime.Now

cn.Open()

cmd.ExecuteNonQuery()

This code defines a string which holds the SQL statement to insert records into database. This SQL statement contains some parameters which are required for the execution of this statement. Here connection to the database has been established with the help of SqlConnection method which receives a connection string as a parameter. Then SqlCommand object is created by passing SQL statement and connection object as parameters. Parameters required for the SQL statement are added in the next step by defining the data type and assigning values to each variable. Then a connection is established and the SQL statement is executed.

You can execute update and delete commands by following the similar code.

Data Retrieval

Following piece of code shows how to retrieve data from the database.

Dim conn As New SqlConnection(ConnStr)

Dim strsql As String = “select max (id) from Students”

Dim cmd As New SqlCommand(strsql, conn)

Dim da As New SqlDataAdapter(cmd)

Dim dt As New DataTable

da.Fill(dt)

If dt.Rows.Count = 0 Then

// Code if no records exist

Else

// Code if records exist for example

IDTextBox.Text = dt.Rows(0).Item(0) + 1

End if

This code retrieves data with the help of SqlDataAdapter and fills the retrieved data into a DataTable object which can be accessed by code later.

The steps highlighted in this article will quickly get you started with SQL Server database interaction from your VB.NET applications and you can handle much complex data processing scenarios after following these instructions.

Learn more about vb.net applications here.