Data Retrieval with stored procedure

Introduction
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.In this example I used the SqlDataReader.

Page Design


SQL Coding Part



C# coding for Button Event

How to write Stored procedure to Your Data Insert Application

Introduction:

In This article, you will understand what a stored procedure is and how to call one from SQL Sever Database.

Why Use Stored procedure?

There are several advantages of using stored procedures instead of standard SQL.

1. Stored procedures allow a lot more flexibility offering capabilities such as conditional Logic.

2. Because stored procedure 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.

3. Sql server pre-compiles stored procedures such that they execute optimally.

4. Client developer are abstracted from complex designs.They would simply need to know that stored procedure's name and the type of fata it returns.

Here I used ASP.NET application for demonstrate this tutorial.Also this is same for the C# desktop application too.

Creating Stored procedure:

First You have to create Table name called StudentInfo and insert stored procedure for that.You can see the SQL coding for that in below.


CREATE PROCEDURE [dbo].[tblStudentInfo] - In this code, we are telling SQL Server to create a new stored procedure with the name tblStudentInfo.We Specify the body of the stored procedure after this coding part.

Calling Stored procedure:
Create a new ASP.NET Page like in below and Double click on button and do the following coding.




Coding part


After completing the coding part you can build and Run you project.If you did the all the thing correctly you can insert the data to Table via stored proocedure.

ASP.NET Form with MS SQL database

In this tutorial I'm going to show you how to insert the data to SQL database in ASP.NET.It is very similar to the C# desktop application.You can see web form below .It has four fields.When I click insert button i want to insert the form data into table called StudentInfo.



In here also, I did the all the coding part for the insert button.

Working with DatagridView and SQL Database

This lesson will show you how to display data using .NET DataGridView control, C#.NET.The DataGridView control is a powerful tool and is simple to implement.

First you will need to import the System.Data.SqlClient namespace.The System.Data.Sqlclient namespace contain the sqlCommand and SqlConnection classes that we need in order to connect to our database and to send SQL command to it.

First we will look at our Simple Form design.


Here we will do the coding only for the button click event and here is the coding part for the Button click event.



And Here is final result.

Connect You C# Form Application with MS SQL server Database

Inserting data into SQL is pretty straightforward and simple. Using the SQL command string, you insert specific values for specific columns. The actual insertion is initiated using the cmd.Execute-NonQuery() command. This executes a command on the data when you don’t want anything in return.



using System.Data.SqlClient – This namespace defines a data provider for the SQL Server 7.0 or higher databse.It contains classes such as sqlconnection and sqlcommand.

ConnectionString - This property allows you to read or provide the connection string that should be used by the SqlConnection object.
CommandText – This read/write property allows you to set or retrieve either T-SQL statement or the name of the stored procedure.
Connection – This read /write property gets or sets the sqlConnection object that should be used by this command Object..
ExecuteNonQuery – This method executes the command specified and return the number of row affected.