Stored Procedures with parameters in c#

Stored Procedures Outline:

* Why Use Stored Procedures?
* Creating a Stored Procedure
* Calling a Stored Procedure
* Specifying Parameters
* Data Retrieval
* Inserting Data Using Parameters

Why Use Stored Procedures?

There are several advantages of using stored procedures instead of standard SQL. First, stored procedures allow a lot more flexibility offering capabilities such as conditional logic. Second, because stored procedures are stored within the DBMS, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements. Third, SQL Server pre-compiles stored procedures such that they execute optimally. Fourth, client developers are abstracted from complex designs. They would simply need to know the stored procedure's name and the type of data it returns.

Creating a Stored Procedure

Enterprise Manager provides an easy way to create stored procedures. First, select the database to create the stored procedure on. Expand the database node, right-click on "Stored Procedures" and select "New Stored Procedure...". You should see the following:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS


Substitute OWNER with "dbo" (database owner) and PROCEDURE NAME with the name of the procedure. For example:

CREATE PROCEDURE [dbo].[GetProducts] AS


So far, we are telling SQL Server to create a new stored procedure with the name GetProducts. We specify the body of the procedure after the AS clause:

CREATE PROCEDURE [dbo].[GetProducts] AS
SELECT ProductID, ProductName FROM Products


Click on the Check Syntax button in order to confirm that the stored procedure is syntactically correct. Please note that the GetProducts example above will work on the Northwind sample database that comes with SQL Server. Modify it as necessary to suite the database you are using.

Now that we have created a stored procedure, we will examine how to call it from within a C# application.

Calling a Stored Procedure

A very nice aspect of ADO.NET is that it allows the developer to call a stored procedure in almost the exact same way as a standard SQL statement.

1. Create a new C# Windows Application project.

2. From the Toolbox, drag and drop a DataGrid onto the Form. Resize it as necessary.

3. Double-click on the Form to generate the Form_Load event handler. Before entering any code, add "using System.Data.SqlClient" at the top of the file.

Enter the following code:

private void Form1_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("Data
Source=localhost;Database=Northwind;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("GetProducts", conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = "Products";
}


As you can see, calling a stored procedure in this example is exactly like how you would use SQL statements, only that instead of specifying the SQL statement, you specify the name of the stored procedure. Aside from that, you can treat it exactly the same as you would an ordinary SQL statement call with all the advantages of a stored procedure.

Specifying Parameters

Most of the time, especially when using non-queries, values must be supplied to the stored procedure at runtime. For instance, a @CategoryID parameter can be added to our GetProducts procedure in order to specify to retrieve only products of a certain category. In SQL Server, parameters are specified after the procedure name and before the AS clause.

CREATE PROCEDURE [dbo].[GetProducts] (@CategoryID int) AS
SELECT ProductID, ProductName FROM Products WHERE CategoryID = @CategoryID


Parameters are enclosed within parenthesis with the parameter name first followed by the data type. If more than one parameter is accepted, they are separated by commas:

CREATE PROCEDURE [dbo].[SomeProcedure] (
@Param1 int,
@Param2 varchar(50),
@Param3 varchar(50)
) AS
...


For our GetProducts example, if @CategoryID was supplied with the value 1, the query would equate to:

SELECT ProductID, ProductName FROM Products WHERE CategoryID = 1


Which would select all the products that belong to CategoryID 1 or the Beverages category. To call the stored procedure, use Query Analyzer to execute:

exec GetProducts X


where X is the @CategoryID parameter passed to the stored procedure. To call the stored procedure from within a C# application using 1 as the @CategoryID parameter value, use the following code:

SqlConnection conn = new SqlConnection("Data
Source=localhost;Database=Northwind;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("GetProducts", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = "Products";


Note that you must now specify the CommandType property of the SqlCommand object. The reason we did not do this in the first example was that it is not required if the stored procedure does not accept parameters. Of course, specifying the CommandType property even if it is not needed may improve readability. The next line actually combines two lines in one:

command.Parameters.Add("@CategoryID", SqlDbType.Int);
command.Parameters["@CategoryID"].Value = 1;


The first line of this segment specifies that the command object (which calls the GetProducts stored procedure) accepts a parameter named @CategoryID which is of type SqlDbType.Int. The type must be the same as the data type specified by the stored procedure. The second line of this code segment gives the parameter the value 1. For simplicity, especially when using more than one parameter, I prefer to combine to two lines into a single line:

command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1;


The rest of the code is the same as in the previous example without parameters. As illustrated in the previous examples, ADO.NET takes a lot of pain out of database programming. Calling a stored procedure uses virtually the same code as using standard SQL and specifying parameters is a painless process.

Data Retrieval

Data Retrieval with stored procedures is the same (surprise!) as if using standard SQL. You can wrap a DataAdapter around the Command object or you can use a DataReader to fetch the data one row at a time. The previous examples have already illustrated how to use a DataAdapter and fill a DataSet. The following example shows usage of the DataReader:

SqlConnection conn = new SqlConnection("Data
Source=localhost;Database=Northwind;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("GetProducts", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1;
conn.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["ProductName"]);
}
conn.Close();


Again, using either a DataAdapter or a DataReader against a query from a stored procedure is the same as specifying the SQL from within the code.

Inserting Data Using Parameters

Using other SQL statements such as INSERT, UPDATE or DELETE follow the same procedure. First, create a stored procedure that may or may not accept parameters, and then call the stored procedure from within the code supply the necessary values if parameters are needed. The following example illustrates how to insert a new user in a users table that has a username and password field.

CREATE PROCEDURE [dbo].[InsertUser] (
@Username varchar(50),
@Password varchar(50)
) AS
INSERT INTO Users VALUES(@Username, @Password)


string username = ... // get username from user
string password = ... // get password from user
SqlConnection conn = new SqlConnection("Data
Source=localhost;Database=MyDB;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("InsertUser", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;
conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();


First, we retrieve the username and password information from the user. This information may be entered onto a form, through a message dialog or through some other method. The point is, the user specifies the username and password and the applicaton inserts the data into the database. Also notice that we called the ExecuteNonQuery() method of the Connection object. We call this method to indicate that the stored procedure does not return results for a query but rather an integer indicating how many rows were affected by the executed statement. ExecuteNonQuery() is used for DML statements such as INSERT, UPDATE and DELETE. Note that we can test the value of rows to check if the stored procedure inserted the data successfully.

if (rows == 1)
{
MessageBox.Show("Create new user SUCCESS!");
}
else
{
MessageBox.Show("Create new user FAILED!");
}


We check the value of rows to see if it is equal to one. Since our stored procedure only did one insert operation and if it is successful, the ExecuteNonQuery() method should return 1 to indicate the one row that was inserted. For other SQL statements, especially UPDATE and DELETE statements that affect more than one row, the stored procedure will return the number of rows affected by the statement.

DELETE FROM Products WHERE ProductID > 50


This will delete all products whose product ID is greater than 50 and will return the number of rows deleted.

Conclusion

Stored procedures offer developers a lot of flexibility with many features not available using standard SQL. ADO.NET allows us to use stored procedures in our applications seamlessly. The combination of these two allows us to create very powerful appliations rapidly.

Difference between Stored procedure vs User Functions in Sql server

In many situation you can do the same task using either a stored procedure or a function.
Fundamental difference between Stored procedure vs User Functions:
•Procedure may return none or more values.Function must always return one value either a scalar value or a table.

•Procedure have input,output parameters.Functions have only input parameters.
•Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.

•Functions can be called from procedure.Procedures cannot be called from function.

•Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)

•Transaction management possible in procedure but not in function.

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

Coding to swap two variables without using third variable

swap two variables without using third variable

a=a+b;
b=a-b;
a=a-b;

Program for Queue implementation through Array in C language

Program for Queue implementation through Array in C language

#include "stdio.h"
#include "ctype.h"
# define MAXSIZE 200

int q[MAXSIZE];
int front, rear;
void main()
{
void add(int);
int del();
int will=1,i,num;
front =0;
rear = 0;

clrscr();

printf("Program for queue demonstration through array");

while(will ==1)
{
printf("
MAIN MENU:
1.Add element to queue
2.Delete element from the queue
");
scanf("%d",&will);

switch(will)
{
case 1:
printf("
Enter the data... ");
scanf("%d",&num);
add(num);
break;
case 2: i=del();
printf("
Value returned from delete function is %d ",i);
break;
default: printf("Invalid Choice ... ");
}

printf("

Do you want to do more operations on Queue ( 1 for yes, any other key to exit) ");
scanf("%d" , &will);
} //end of outer while
} //end of main

void add(int a)
{

if(rear>MAXSIZE)
{
printf("

QUEUE FULL

");
return;
}
else
{
q[rear]=a;
rear++;
printf("
Value of rear = %d and the value of front is %d",rear,front);
}
}

int del()
{
int a;
if(front == rear)
{
printf("QUEUE EMPTY");
return(0);
}
else
{
a=q[front];
front++;
}
return(a);
}

Stack Coding in C language

Program for Stack implementation through Array in C language

#include "stdio.h"
#include"ctype.h"
# define MAXSIZE 200

int stack[MAXSIZE];
int top; //index pointing to the top of stack
void main()
{
void push(int);
int pop();
int will=1,i,num;
clrscr();

while(will ==1)
{
printf("
MAIN MENU:
1.Add element to stack
2.Delete element from the stack
");
scanf("%d",&will);

switch(will)
{
case 1:
printf("
Enter the data... ");
scanf("%d",&num);
push(num);
break;
case 2: i=pop();
printf("
Value returned from pop function is %d ",i);
break;
default: printf("Invalid Choice . ");
}

printf(" Do you want to do more operations on Stack ( 1 for yes, any other key to exit) ");
scanf("%d" , &will);
} //end of outer while
} //end of main


void push(int y)
{

if(top>MAXSIZE)
{
printf(" STACK FULL");
return;
}
else
{
top++;
stack[top]=y;
}
}

int pop()
{
int a;
if(top<=0)
{
printf(" STACK EMPTY ");
return 0;
}
else
{
a=stack[top];
top--;
}
return(a);

}

Vb.net Connectivity using ADO.NET - Updating Record royalarun.blogspot.com

Vb.net Connectivity using ADO.NET - Updating Record

Updating Records

We will update a row in Authors table. Drag a button onto the form and place the following code.

Imports System.Data.SqlClient
Public Class Form4 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra as Integer
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Update Authors Set city='Oakland' where city=_
'San Jose' ",myConnection)
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records affected" & ra)
myConnection.Close()
End Sub
End Class

//royalarun.blogspot.com

Vb.net Connectivity using ADO.NET - Deleting Record royalarun.blogspot.com

Vb.net Connectivity using ADO.NET - Deleting Record
Deleting a Record

We will use Authors table in Pubs sample database to work with this code. Drag a button onto the form and place the following code.

Imports System.Data.SqlClient
Public Class Form3 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra as Integer
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Delete from Authors where city='Oakland'",_
myConnection)
'since no value is returned we use ExecuteNonQuery
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records affected" & ra)
myConnection.Close()
End Sub
End Class