Stored Procedures

Posted by Dotnetfundakumar under Regular Expressions on 9/3/2009 | Views : 2296 | Status : [Member] | Replies : 1
I am Create stored Procedure in Sql Insert,Delete,Update

How will access and process in vb.net

Regards
Kumar



Responses

Posted by: Vuyiswamb on: 9/3/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Dotnetfundakumar

Example if you have created a Stored Procedure like this

Create Proc sp_check_name

(
@Name varchar(30)
)
AS
SALECT NAME,LASTNAME,ID FROM TBL_USERS
WHERE NAME = @NAME


if you want to use this in vb.net, in your Data Layer you will will something like this


Public Function Get_Data(Name As [String]) As DataSet

'Create a Function that Accept a name string and return a Dataset
Dim con As New SqlConnection("User id= sa; Password=wow; Server=myserver; Database=MyDB")
'create a Connection Object and assign a connection string in the Constructor
Dim cmdselect As New SqlCommand()
' create a Command oject and initialize it at the same time
cmdselect.CommandText = "sp_check_name"
'Give the Command object what is about to be executed
cmdselect.Parameters.Add("@Name", SqlDbType.VarChar, 30).Value = Name
'Here you are creating a Parameter.It must be the same as the one created in the Stored Procedure and the datatype must be the same and the size must be the same
cmdselect.CommandType = CommandType.StoredProcedure
'Tell the Command object that it is going to execute a Stored Procedure
cmdselect.Connection = con
'Tell the Command object that it is going to use the assigned Connection to execute the command
Dim da As New SqlDataAdapter()
'Create and initialize the adapter object
da.SelectCommand = cmdselect
'Tell the Adapter that it is going to use the cmdselect that is defined eliar for the selectedCommand
Dim ds As New DataSet()
'create a Dataset and initialize it ata the same time
Try
'Always trap the Databases executed,because you dont know what might happen
con.Open()
'Open the Connection
'Fill the Dataset
da.Fill(ds)
Catch generatedExceptionName As SqlException
Throw
Finally
If con <> Nothing Then
'if the Connection is still open then close it
con.Close()
End If
End Try
Return ds
'return the Dataset
End Function


and in C#

public DataSet Get_Data(String Name) //Create a Function that Accept a name string and return a Dataset
{
SqlConnection con = new SqlConnection("User id= sa; Password=wow; Server=myserver; Database=MyDB"); //create a Connection Object and assign a connection string in the Constructor

SqlCommand cmdselect = new SqlCommand(); // create a Command oject and initialize it at the same time

cmdselect.CommandText = "sp_check_name"; //Give the Command object what is about to be executed

cmdselect.Parameters.Add("@Name", SqlDbType.VarChar,30).Value = Name; //Here you are creating a Parameter.It must be the same as the one created in the Stored Procedure and the datatype must be the same and the size must be the same

cmdselect.CommandType = CommandType.StoredProcedure; //Tell the Command object that it is going to execute a Stored Procedure

cmdselect.Connection = con; //Tell the Command object that it is going to use the assigned Connection to execute the command

SqlDataAdapter da = new SqlDataAdapter(); //Create and initialize the adapter object

da.SelectCommand = cmdselect;//Tell the Adapter that it is going to use the cmdselect that is defined eliar for the selectedCommand

DataSet ds = new DataSet(); //create a Dataset and initialize it ata the same time

try //Always trap the Databases executed,because you dont know what might happen
{
con.Open(); //Open the Connection

da.Fill(ds); //Fill the Dataset
}
catch (SqlException)
{
throw;
}
finally
{
if (con != null) //if the Connection is still open then close it
{
con.Close();
}
}
return ds; //return the Dataset
}



Use this website to Convert the code from C# to vb.net and virsa versa.

http://converter.telerik.com/

Please Dont Post one Question many times on the Forum.

Thank you for posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Dotnetfundakumar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response