Execute a SQL Stored Procedure and process the results

Question

In VB NET  how do I do the following    Execute a Stored Procedure Read through the DataTable returned

User · Answer

Simplest way  It works          Dim queryString As String    Stor Proc Name    amp  data1  amp       amp  data2     Try         Using connection As New SqlConnection ConnStrg              connection Open               Dim command As New SqlCommand queryString  connection              Dim reader As SqlDataReader   command ExecuteReader               Dim DTResults As New DataTable              DTResults Load reader              MsgBox DTResults Rows 0  0  ToString           End Using     Catch ex As Exception         MessageBox Show  Error while executing       amp  ex Message          Finally     End Try

User · Answer

From MSDN  To execute a stored procedure returning rows programmatically using a command object  Dim sqlConnection1 As New SqlConnection  Your Connection String   Dim cmd As New SqlCommand Dim reader As SqlDataReader  cmd CommandText    StoredProcedureName  cmd CommandType   CommandType StoredProcedure cmd Connection   sqlConnection1  sqlConnection1 Open    reader   cmd ExecuteReader     Data is accessible through the DataReader object here    Use Read method  true false  to see if reader has records and advance to next record   You can use a While loop for multiple records  While reader Read       End While  If reader Read   Then   someVar   reader 0    someVar2   reader 1    someVar3   reader  NamedField   End If      sqlConnection1 Close

User · Answer

At the top of your  vb file   Imports System data sqlclient   Within your code    Setup SQL Command Dim CMD as new sqlCommand  StoredProcedureName   CMD parameters   Parameter1   sqlDBType Int  value   Param 1 value  Dim connection As New SqlConnection connectionString  CMD Connection   connection CMD CommandType   CommandType StoredProcedure  Dim adapter As New SqlDataAdapter CMD  adapter SelectCommand CommandTimeout   300   Fill the dataset Dim DS as DataSet     adapter Fill ds  connection Close        Now  read through your data  For Each DR as DataRow in DS Tables 0  rows     Msgbox  The value in Column   ColumnName1       amp  cstr DR  ColumnName1     next     Now that the basics are out of the way    I highly recommend abstracting the actual SqlCommand Execution out into a function    Here is a generic function that I use  in some form  on various projects        lt summary gt Executes a SqlCommand on the Main DB Connection  Usage  Dim ds As DataSet   ExecuteCMD CMD  lt  summary gt          lt param name  CMD  gt The command type will be determined based upon whether or not the commandText has a space in it  If it has a space  it is a Text command   select     from               otherwise if there is just one token  it s a stored procedure command lt  param gt      Function ExecuteCMD ByRef CMD As SqlCommand  As DataSet     Dim connectionString As String   ConfigurationManager ConnectionStrings  main   ConnectionString     Dim ds As New DataSet        Try         Dim connection As New SqlConnection connectionString          CMD Connection   connection           Assume that it s a stored procedure command type if there is no space in the command text  Example   sp Select Customer  vs   select   from Customers          If CMD CommandText Contains      Then             CMD CommandType   CommandType Text         Else             CMD CommandType   CommandType StoredProcedure         End If          Dim adapter As New SqlDataAdapter CMD          adapter SelectCommand CommandTimeout   300           fill the dataset         adapter Fill ds          connection Close        Catch ex As Exception           The connection failed  Display an error message          Throw New Exception  Database Error     amp  ex Message      End Try      Return ds End Function     Once you have that  your SQL Execution   reading code is very simple    ----------------------------------------------------------------------  Dim CMD As New SqlCommand  GetProductName   CMD Parameters Add   productID   SqlDbType Int  Value   ProductID Dim DR As DataRow   ExecuteCMD CMD  Tables 0  Rows 0  MsgBox  Product Name     amp  cstr DR 0     ----------------------------------------------------------------------

User · Answer

My Stored Procedure Requires 2 Parameters and I needed my function to return a datatable here is 100  working code  Please make sure that your procedure return some rows  Public Shared Function Get BillDetails AccountNumber As String  As DataTable     Try         Connection Connect           debug print  Look up account number    amp  AccountNumber          Dim DP As New SqlDataAdapter  EXEC SP GET ACCOUNT PAYABLES GROUP      amp  AccountNumber  amp           amp  08 28 2013  amp      connection Con          Dim DST As New DataSet         DP Fill DST          Return DST Tables 0            Catch ex As Exception         Return Nothing     End Try End Function

User · Answer

Dim sqlConnection1 As New SqlConnection  Your Connection String   Dim cmd As New SqlCommand  cmd CommandText    StoredProcedureName  cmd CommandType   CommandType StoredProcedure cmd Connection   sqlConnection1  sqlConnection1 Open    Dim adapter As System Data SqlClient SqlDataAdapter Dim dsdetailwk As New DataSet  Try    adapter   New System Data SqlClient SqlDataAdapter    adapter SelectCommand   cmd    adapter Fill dsdetailwk   delivery      Catch Err As System Exception End Try  sqlConnection1 Close    datagridview1 DataSource   dsdetailwk Tables 0

[vb.net] Execute a SQL Stored Procedure and process the results

Examples related to vb.net

Examples related to tsql

Examples related to sql-server-2008

Examples related to stored-procedures