Here’s just the logic behind using stored procedure within your code. This can be a good code snippet in Visual Studio for easy access whenever you need to connect to your DB.
Dim cs as string = “Data Source=.\SQLExpress; Initial Catalog=DatabaseName; Integerated Security=True;”
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = con
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = “StoredProcedureName”
cmd.Parameters.Add(“@Param1″, Data.SqlDbType.Varchar, 50)
cmd.Parameters(“@Param1″).Value = AnyValue
Using reader As System.Data.SqlClient.SqlDataReader = cmd.Execute
GridView1.DataSource = reader
Gridview1.DataBind()
End Using
End Using
—–
Since a lot of developers, if not most store the connection string in the web.config file, another way of accomplishing the same task above is below: (forgive me if I’m missing any syntax as I hand-coded some of the codes here)
‘Dim con As New SqlConnection
Dim cs as string = ConfigurationManager.ConnectionStrings(“KeyedConnection”).ConnectionString
objConnectionString.ConnectionString = cs
objConnectionString.Open()
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = con
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = “StoredProcedureName”
cmd.Parameters.Add(“@Param1″, Data.SqlDbType.Varchar, 50)
cmd.Parameters(“@Param1″).Value = AnyValue
Using reader As System.Data.SqlClient.SqlDataReader = cmd.Execute
GridView1.DataSource = reader
Gridview1.DataBind()
End Using
End Using In the web.config file, you insert your <connectionstrings> section using the statement below (if using SQL Server):
<connectionStrings>
<add name=”KeyedConnection” connectionString=”
Server=yourserver.net;
Database=yourDBname;
User ID=user_name;
Password=password;
Trusted_Connection=False” providerName=”System.Data.SqlClient” />
<remove name=”LocalSqlServer”/>
<add name=”LocalSqlServer” connectionString=”
Server=yourserver.net;
Database=yourDBname;
User ID=user_name;
Password=password;
Trusted_Connection=False” providerName=”System.Data.SqlClient” />
</connectionStrings>
