Insert data through Stored Procedure in ASP.net

Here we will insert firstname in table tbl_test through dbo.insertfirstname Stored Procedure. Below are the steps to implement it.

Step1 : Add this in web.config file
-----appSettings------
add key="cn" value="workstation id=SQL8;packet size=4096;user id=sa;password=amit;data source=USER1;initial catalog=amit; Connection Timeout=120;"
----appSettings------

Step2 : Add with other namespaces
using System.Data.SqlClient;

Step3: Add under partial class
SqlConnection con;
SqlCommand cmd;

Step4: create connection object in page load event
con = new SqlConnection(ConfigurationManager.AppSettings["cn"]);

Step5: execute insertion stored procedure in database query
create procedure dbo.insertfirstname(@firstname varchar(50)) as insert into tbl_test values(@firstname)

Step6: call this code at add button's Onclick event
cmd = new SqlCommand("dbo.insertfirstname", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@firstname", TextBox1.Text.Trim());
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Explain Connection Object

The Connection object creates the connection to the database. Microsoft Visual Studio .NET provides two types of Connection classes: the SqlConnection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and the OleDbConnection object, which can provide connections to a wide range of database types like Microsoft Access and Oracle. The Connection object contains all of the information required to open a connection to the database.

The connection helps identify the data base server, the data base name, user name, password, and other parameters that are required for connecting to the data base.

Example:

SqlConnection conn = new SqlConnection( "Data Source=(local);Initial Catalog=xyz;Integrated Security=SSPI");

ADO.Net Primary Objects

1. Connection Object
2. Command Object
3. DataReader Object
4. DataSet Object
5. DataAdapter Object

Explain Command Object

It allows to manipulate database by executing stored procedure or sql statements.

A SqlCommand object allows us to specify what type of interaction we want to perform with a database.

For example, we can do select, insert, update, and delete commands on rows of data in a database table.

For Selecting data from datatable

// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();

Example:

For Inserting data in datatable

// prepare command string

string insertString = @"insert into Categories(CategoryName, Description) values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

// 1. Instantiate a new command with a query and connection

SqlCommand cmd = new SqlCommand(insertString, conn);

// 2. Call ExecuteNonQuery to send command

cmd.ExecuteNonQuery();

Explain DataReader Object

It provides a forward-only, read-only, connected recordset.

It is most efficient to use when data need not to be updated, and requires forward only traverse. In other words, it is the fastest method to read data.

Mostly Used:

1. Filling dropdownlistbox.
   
2. Comparing username and password in database

Example:

SqlDataReader rdr = cmd.ExecuteReader();

//Reading data

while (rdr.Read())
{

//Display data

string contact = (string)rdr["ContactName"];
string company = (string)rdr["CompanyName"];
string city = (string)rdr["City"];

}

Explain DataSet Object

Dataset is a disconnected, in-memory representation of data. It can contain multiple data table from different database.

They contain multiple Datatable objects, which contain columns and rows, just like normal data base tables. You can even define relations between tables to create parent-child relationships.

Example
DataSet dsEmp = new DataSet();

There are several ways of working with a DataSet, which can be applied independently or in combination. we can:

1. Programmatically create a DataTable, DataRelation, and Constraint within a DataSet and populate the tables with data.

2. Populate the DataSet with tables of data from an existing relational data source using a DataAdapter.

3. Load and persist the DataSet contents using XML.

For more understanding look for DataAdapter Object Article.

Explain DataAdapter Object

It populates dataset from data source. It contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database.

Example:
SqlDataAdapter daEmp = new SqlDataAdapter( "select EmpID, EmpName, Salary from Employees", conn);

Fill Method
It is used to populate dataset.
example: daEmp.Fill(dsEmp,"Employee");

Update Method
It is used to update database.
example: daEmp.Update(dsEmp,"Employee");

More about DataAdapter
DataAdapter object is like a bridge that links the database and a Connection object with the ADO.NET-managed DataSet object through its SELECT and action query Commands. It specifies what data to move into and out of the DataSet. Often, this takes the form of references to SQL statements or stored procedures that are invoked to read or write to a database.

Explain DataView

A DataView enables us to create different views of the data stored in a DataTable, a capability that is often used in data-binding applications. Using a DataView, we can expose the data in a table with different sort orders, and we can filter the data by row state or based on a filter expression.

It provides a means to filter and sort data within a data table.

Example:
DataView myDataView = new DataView(myDataSet.Tables["Customers"]);

// Sort the view based on the FirstName column
myDataView.Sort = "CustomerID";

// Filter the dataview to only show customers with the CustomerID of XYZ
myDataView.RowFilter = "CustomerID=XYZ";

Difference between ExecuteNonQuery, ExecuteScaler, ExecuteReader

Difference Angle	ExecuteNonQuery	ExecuteScaler	ExecuteReader
About	After Executing Sql statement Returns number of rows affected	Returns Single value, Executes first column first row only	Send the Command text to connection and builds a SqlDataReader.
When we use	ExecuteNonQuery is used to perform Catalog operation like Creating table in database or Changing of data in tables (without using Dataset) by using Insert, Update, Delete, create	Selecting only single fields like count field	When we manipulating with DataReader it used, Sending SQL Select statements to a database, Invoking Stored Procedures
Example	Example: SqlCommand cmd = new SqlCommand("Insert Into SampleTable Values('1','2')",con); //con is the connection object con.Open(); cmd.ExecuteNonQuery(); //The SQL Insert Statement gets executed This method returns no data at all. It is used majorly with Inserts and Updates of tables. It is used for execution of DML commands.	Example : select count(*) from emp ExecuteScalar is used to get aggregate value. ExecuteScalar will return only one value that is first column value of the first row in the executed query. Example 2: cmd.CommandText = "Select Name, DOB, from Emp where ID=1"; Dim strName As string = cmd.ExecuteScalar.ToString This returns one value only, no recordsets.	Example: SqlConnection con = new SqlConnection(constr); //constructor can be connection of string. SqlCommand cmd = new SqlCommand ("select * from emp", con); con.Open(); SqlDataReader dr = cmd. ExecuteReader (CommandBehavior. CloseConnection); while(dr.Read()) { Console.WriteLine (dr.GetString(0)); } dr.Close(); //Implicitly closes the connection because CommandBehavior. CloseConnection was specified. ExecuteReader is used to get set records by specified query