How to Save, Update, Delete and Cancel in Asp.net
In article we will learn the code for save, update, delete and cancel in Asp.netBefore studying further lets study little bit about basic knowledge and requirement needed in this project
# Ado.net
ActiveX Data Object
It is an environment design by Microsoft for database.
*In Ado.net, we have two main component
- xml = license free, platform independent, fastest technique
for database
- Disconnected approach
*Environment/ Provider/ Driver
- ODBC old which are compatible with mostly all
- OLEDB
- SQLCLIENT = MSSSQL SERVER
-ORACLECLIENT = ORACLE
* Click on file > New project > Visual C#
>web>website >asp.net empty
website
# Application and Database connection
·
Connection String - To create connection b/w
application and database, the string which is used is called Connection String
Application ------------CS
----------DB
Web.config – We have to write
connection string in this file
* Why we write connection string in web.config
- It is xml based and k sensitive (caps)
- It is non complied file
- xml do not have complier, it have parcels
xml can be opened in browser
* Is web.config is
secured
Yes, because
web.config file cannot be opened with browser
* Can web.config be
multiple
Yes, But one folder
have one file
* Can application be made with web.config
Yes, It can but if required
then it can be used
* Web.config is used
for
It is used for
project configuration. For ex- Security setting, session setting for project
# Create Connection
String
* Click on file > New project > Visual C#
>web>website >asp.net empty
website
* Click on View >
Solution explorer > web.config
<configuration>
<connectionStrings>
<add name="ed" connectionString="server=*\sqlexpress;database=your
database name;integrated security= true"/>
</connectionStrings>
|
# Open webform > Design
Tool box> textbox
Toolbox> Button
1st button property – save
2nd button property – update
3rd button property – delete
4th button property – display
* Now run I.E or Chrome
#Now Double click on webform
using System.Web.UI.WebControls;
using System.Data; classes used for
database
using System.Data.SqlClient; classes used with mssql server
using System.Configuration; classes used with web.config
public partial class Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
|
Con- any (according to you as its object name)
Sqlconnection(); - contractor
After new no class will come
* Sqlconnection is used for
For mssql server connection for creating connection with it.
*. How to read/write web.config in connection
protected void Page_Load(object sender,
EventArgs e)
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["ed"].ConnectionString;
con.Open();
}
|
* When page is opened more and more, its performance will
decrease, to stop that
if(con.State==ConnectionState.Closed)
{
con.Open();
}
|
connectionstate- enumeration (constant)
# Double click on Save button
protected void Button1_Click(object sender,
EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "insert
employee values(@eno,@en,@ed,@es)";
cmd.Connection = con;
cmd.Parameters.Add("@eno", SqlDbType.Int).Value =
Convert.ToInt32(TextBox1.Text);
cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value =
TextBox2.Text;
cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value =
TextBox3.Text;
cmd.Parameters.Add("@es", SqlDbType.Int).Value =
Convert.ToInt32(TextBox4.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
Clear_Rec();
|
# Insert
insert employee values(1,'Amit','#Chd',5000)
employee is tables name
employee is tables name
When pass value to 2 or more column
insert employee (empno,ename) values (2,'Vikas')
# In sql parameters are made with @
Parameters – When pass at runtime
# How to pass parameters
cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);
cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value =
TextBox2.Text;
cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value =
TextBox3.Text;
cmd.Parameters.Add("@es", SqlDbType.Int).Value =
Convert.ToInt32(TextBox4.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
Clear_Rec();
|
# When code is used again and again
Select lines and then
Edit > Refactor> Extract Method
Clear_Rec();
private void Clear_Rec()
{
TextBox1.Text = String.Empty;
TextBox2.Text = String.Empty;
TextBox3.Text = String.Empty;
TextBox4.Text = String.Empty;
TextBox1.Focus();
}
|
# Now Display data
Toolbox > Standard> Listbox
Mostly used
|
Listbox
Dropdown list
Checkbox list
Radio button list
|
select * from tbemp
select empno,ename from tbemp
select datetime
|
* Sql data Reader – It is used for display data. It use forword
approach only. Fastest way to retrieve data from database. You must close data
reader
Data value field – internally used, not for display
Stag reference – When new does not comes with class
# Double click on display
protected void Button4_Click(object sender,
EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from employee";
cmd.Connection = con;
SqlDataReader dr;
dr =
cmd.ExecuteReader();
ListBox1.DataTextField
= "ename";
ListBox1.DataValueField = "empno";
ListBox1.DataSource =
dr;
ListBox1.DataBind();
dr.Close();
cmd.Dispose();
}
|
Edit > Refactor > extract method
Disp_Rec();
private SqlCommand Disp_Rec()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from employee";
cmd.Connection = con;
SqlDataReader dr;
dr =
cmd.ExecuteReader();
ListBox1.DataTextField = "ename";
ListBox1.DataValueField = "empno";
ListBox1.DataSource =
dr;
ListBox1.DataBind();
dr.Close();
return cmd;
}
}
}
|
# To display automatically after clicking save
Double click on save
Clear_Rec();
Disp_Rec();
# To display details when
clicked on name in listbox
Listbox > property> autopostback=true
#Double click on listbox
protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from employee where empno=@eno";
cmd.Connection = con;
cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(ListBox1.SelectedValue);
SqlDataReader dr =
cmd.ExecuteReader();
if(dr.HasRows)
{
dr.Read();
TextBox1.Text =
dr["empno"].ToString();
TextBox2.Text =
dr["ename"].ToString();
TextBox3.Text =
dr["eadd"].ToString();
TextBox4.Text =
dr["esal"].ToString();
}
dr.Close();
cmd.Dispose();
}
|
* Array of object
In data read , it is called array of object. It is called
collection because different data is stored
{
dr.Read();
TextBox1.Text =
dr["empno"].ToString();
TextBox2.Text =
dr["ename"].ToString();
TextBox3.Text =
dr["eadd"].ToString();
TextBox4.Text =
dr["esal"].ToString();
}
|
# Double Click on update
protected void Button2_Click(object sender,
EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "update employee set ename=@en,eadd=@ed,esal=@es where
empno=@eno";
cmd.Connection = con;
cmd.Parameters.Add("@eno", SqlDbType.Int).Value =
Convert.ToInt32(TextBox1.Text);
cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value =
TextBox2.Text;
cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value =
TextBox3.Text;
cmd.Parameters.Add("@es", SqlDbType.Int).Value =
Convert.ToInt32(TextBox4.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
Clear_Rec();
Disp_Rec();
}
|
#Double click on delete button
protected void Button3_Click(object sender,
EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "delete from employee where empno=@eno";
cmd.Connection = con;
cmd.Parameters.Add("@eno", SqlDbType.Int).Value =
Convert.ToInt32(TextBox1.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
Clear_Rec();
Disp_Rec();
}
}
|
# Truncate and delete
We can apply condition in delete
Truncate delete all data from table
No comments:
Post a Comment