Here I have described how to insert, delete and update data into database (SQL
Server) with the help of web service. Complete steps are given below.
Step 1: Open “ASP.NET Web Service”. For selecting “ASP.NET Web Service” option, must be select NET Framework 3.5. as below image. Complete path are given below
File > New > Project > Visual C# > Web >
ASP.NET Web Service
Step 2: When open “ASP.NET Web Service”. By default some code are prewritten in Service.cs page, as below image.
Step 3: Let’s go for creating web service. First set
your database connection string in web.config file.
Step 4: use three namespace for connection to database.
These namespaces provides all necessary libraries and classes that required for
insert, delete and update records to database. These namespaces are
System.Data.SqlClient, System.Data and System.Configuration.
Step 5: First create one table where we insert, delete
and update records. Below I have given table structure.
CREATE DATABASE Demo
USE Demo
CREATE TABLE [dbo].[tblEmployee]
(
[ID] INT IDENTITY
PRIMARY KEY,
[UserName] VARCHAR(50) NOT
NULL,
[Pass] VARCHAR(50) NOT
NULL,
[Email] VARCHAR(50)NOT NULL,
[Gender] VARCHAR(50) NOT
NULL
)
|
Step 6: Now establish connection from database. Before
writing code first remove default method of
service.cs page. Write below code for creating connection.
private string
()
{
string conString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
return conString;
}
|
Note: Form above
code “ConnectionState()” is a method, and “dbconnection” is the name of connection string which define into
web.config in
step 3.
Step 7: Now create method for inserting data into
database.
[WebMethod]
public int
insertRecord(string sUserName,
string sPass, string
sEmail, string sGender)
{
var con = new SqlConnection(ConnectionState());
con.Open();
var cmd = new SqlCommand("Insert
into tblEmployee(UserName,Pass, Email, Gender) values('" + sUserName +
"','" + sPass +
"','" + sEmail + "','" + sGender +
"')",con);
int row = cmd.ExecuteNonQuery();
return row;
}
|
Note: Here “[WebMethod]” is responsible for execute code on web. “insertRecord()” is a method that
take four argument and received from web page. Get references from given figure.
Insert Record
Step 7: Now,
execute Service.asmx page.
Copy the URL string for further use. Here you focus only “insertRecord” method.
Step 8: Click on “insertRecord” method and test your web service. That looks as below image.
After entry into textbox, click on button
Invoke. If everything is fine (no error in connection string and no
syntactical error) than you got a confirmation message as below image and
checked your database (refresh your table).
Step 9: In this step I tell you how to add web service
reference in our project. Right click on your project and select “Add Service Reference…” option as
below image.
Step 10: Click on button
“advanced…” as below image.
Step 11: Click on button “Add Web Reference…” as below image.
Step 12: “Add Web
Reference” window are open. Paste URL String (which we copy in
step 7) within URL Textbox. As shown in below image. Then click on button “go” (right hand side of URL textbox
with green color). After clicking button “go”, web service method is
populated!, you can see your method. Now
click on button “Add Reference”. Get reference
form below image.
Step 13: Added reference, you can see in solution Explorer as below image.
Step 14: Now time to create user interface. Add one
.aspx (Insert.aspx) page and write
below code.
<div>
<fieldset style="width: 250px;">
<legend>Insert</legend>
<table>
<tr>
<td>
<asp:Label
ID="lblUserName"
runat="server"
Text="User Name"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtUserName"
runat="server"></asp:TextBox>
<asp:RequiredFieldValidator
ID="rfvUserName"
runat="server"
ControlToValidate="txtUserName"
Display="Dynamic"
ErrorMessage="*"
ToolTip="User Name is
required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblPass"
runat="server"
Text="Password"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtPass"
runat="server"
TextMode="Password"></asp:TextBox>
<asp:RequiredFieldValidator
ID="rfvPass"
runat="server"
ControlToValidate="txtPass"
Display="Dynamic"
ErrorMessage="*"
ToolTip="Password is
required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblEmail" runat="server" Text="Email"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtEmail"
runat="server"></asp:TextBox>
<asp:RequiredFieldValidator
ID="refEmail"
runat="server"
ControlToValidate="txtEmail"
Display="Dynamic"
ErrorMessage="*"
ToolTip="Email id is
required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblGender"
runat="server"
Text="Gender"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtGender"
runat="server"></asp:TextBox>
<asp:RequiredFieldValidator
ID="rfvGender"
runat="server"
ControlToValidate="txtGender"
Display="Dynamic"
ErrorMessage="*"
ToolTip="Gender is
required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button
ID="btnInsert"
runat="server"
Text="Insert"
OnClick="btnInsert_Click"
/>
</td>
</tr>
</table>
</fieldset>
</div>
|
Step 15: Copy and paste below code on
insert.aspx.cs file.
using System;
public partial
class Transuction
: System.Web.UI.Page
{
protected void
Page_Load(object sender,
EventArgs e)
{
}
protected void
btnInsert_Click(object sender,
EventArgs e)
{
localhost.Service dbws =
new localhost.Service();
int row = dbws.insertRecord(txtUserName.Text, txtPass.Text, txtEmail.Text,
txtGender.Text);
if (row > 0)
{
Response.Write("<script>alert('Record insert
successfuly');</script>");
txtUserName.Text = txtPass.Text = txtEmail.Text = txtGender.Text =
"";
}
else { Response.Write("<script>alert('Record not
insert');</script>"); }
}
}
|
Note: In the above code “dbws” is an object of “local.Service”. Through object we
call web service method and pass data.
Step 16: Save and build application. Execute
Insert.aspx page and see view of
webpage. Fill textbox and click button “Insert”.
Note: Your web service is ready for
insert record into database. Now we move for delete and update record into data
base.
Delete Record
Step 18: For deleting record, add one
.aspx (Delete.aspx) page and design
web page. You can use below code.
<div>
<fieldset>
<legend>Delete</legend>
<table>
<tr>
<td>
<asp:Label
ID="lblSelect"
runat="server"
Text="Select"></asp:Label>
</td>
<td>
<asp:DropDownList
ID="ddlSelect"
runat="server"
AutoPostBack="True"
Width="127px"
onselectedindexchanged="ddlSelect_SelectedIndexChanged">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblUserName"
runat="server"
Text="User Name"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtUserName"
runat="server"
ReadOnly="True"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblPass"
runat="server"
Text="Password"
ReadOnly="True"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtPass"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblEmail"
runat="server"
Text="Email"
ReadOnly="True"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtEmail"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblGender"
runat="server"
Text="Gender"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtGender"
runat="server"
ReadOnly="True"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button
ID="btnDelete"
runat="server"
Text="Delete"
onclick="btnDelete_Click1"
/>
</td>
</tr>
</table>
</fieldset>
</div>
|
Step 19: Write below code on
Service.cs page that used for
deleting record.
[WebMethod]
public DataTable getRecord()
{
var con = new SqlConnection(ConnectionState());
var cmd = new SqlCommand("Select
ID From tblEmployee", con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable("Employee");
dt.Load(dr);
con.Close();
return dt;
}
[WebMethod]
public DataTable getDetails(int id)
{
var con = new SqlConnection(ConnectionState());
var cmd = new SqlCommand("Select
[UserName],[Pass],[Email],[Gender] From tblEmployee where ID = '" + id +
"'", con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable("Employee");
dt.Load(dr);
con.Close();
return dt;
}
[WebMethod]
public int
deleteRecord(int id)
{
var con = new SqlConnection(ConnectionState());
var cmd = new SqlCommand("delete
tblEmployee where ID='" + id + "'",
con);
con.Open();
int row = cmd.ExecuteNonQuery();
con.Close();
return row;
}
|
Step 20: Update Web Service as below image.
Step 21: write below code on
Delete.aspx.cs page.
using System;
using System.Data;
public partial
class Delete
: System.Web.UI.Page
{
protected void
Page_Load(object sender,
EventArgs e)
{
if (!IsPostBack)
{
getId();
}
}
public void
getId()
{
localhost.Service dbws =
new localhost.Service(); //
you need to create the object of the web service
DataTable dt = dbws.getRecord();
ddlSelect.DataTextField = "Id";
ddlSelect.DataValueField = "Id";
ddlSelect.DataSource = dt;
ddlSelect.DataBind();
}
protected void
btnDelete_Click1(object sender, EventArgs e)
{
localhost.Service dbws =
new localhost.Service(); //
you need to create the object of the web service
int row = dbws.deleteRecord(Convert.ToInt32(ddlSelect.SelectedItem.Text));
if (row > 0)
{
Response.Write("<script>alert('Record deleted
successfuly');</script>");
getId();
}
else { Response.Write("<script>alert('Record not
deleted');</script>"); }
}
protected void
ddlSelect_SelectedIndexChanged(object sender,
EventArgs e)
{
localhost.Service dbws =
new localhost.Service(); //
you need to create the object of the web service
DataTable dt = dbws.getDetails(Convert.ToInt32(ddlSelect.SelectedItem.Text));
txtUserName.Text = dt.Rows[0]["UserName"].ToString();
txtPass.Text = dt.Rows[0]["Pass"].ToString();
txtEmail.Text = dt.Rows[0]["Email"].ToString();
txtGender.Text = dt.Rows[0]["Gender"].ToString();
}
}
|
Note:
getId() : This is a method that get
value form database and populate into drop down for selecting record.
btnDelete_Click1(object sender,
EventArgs e) : This is an event that execute query for deleting
record from database.
ddlSelect_SelectedIndexChanged(object sender,
EventArgs e) : This is an
event of dropdown that populate data into textbox according dropdown value.
Step 22: Save and
build application. Execute Delete.aspx
page and delete record.
Update Record
Step 23: For updating record, add one
.aspx (Update.aspx) page and design
web page. You can use below code.
<div>
<fieldset>
<legend>Update</legend>
<table>
<tr>
<td>
<asp:Label
ID="lblSelect"
runat="server"
Text="Select"></asp:Label>
</td>
<td>
<asp:DropDownList
ID="ddlSelect"
runat="server"
AutoPostBack="True"
OnSelectedIndexChanged="ddlSelect_SelectedIndexChanged"
Width="127px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblUserName"
runat="server"
Text="User Name"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtUserName"
runat="server"></asp:TextBox>
<asp:RequiredFieldValidator
ID="rfvUserName"
runat="server"
ControlToValidate="txtUserName"
Display="Dynamic"
ErrorMessage="*"
ToolTip="User Name is
required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblPass"
runat="server"
Text="Password"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtPass"
runat="server"
TextMode="Password"></asp:TextBox>
<asp:RequiredFieldValidator
ID="rfvPass"
runat="server"
ControlToValidate="txtPass"
Display="Dynamic"
ErrorMessage="*"
ToolTip="Password is
required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblEmail"
runat="server"
Text="Email"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtEmail"
runat="server"></asp:TextBox>
<asp:RequiredFieldValidator
ID="refEmail"
runat="server"
ControlToValidate="txtEmail"
Display="Dynamic"
ErrorMessage="*"
ToolTip="Email id is
required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label
ID="lblGender"
runat="server"
Text="Gender"></asp:Label>
</td>
<td>
<asp:TextBox
ID="txtGender"
runat="server"></asp:TextBox>
<asp:RequiredFieldValidator
ID="rfvGender"
runat="server"
ControlToValidate="txtGender"
Display="Dynamic"
ErrorMessage="*"
ToolTip="Gender is
required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button
ID="btnUpdate"
runat="server"
Text="Update"
OnClick="btnUpdate_Click"
/>
</td>
</tr>
</table>
</fieldset>
</div>
|
Step 24: Write below code on
Service.cs page that used for
updating record. ConnectionState() (Step
6 ), getRecord() (step 19), getDetails() (step 19) method are necessary for
updating record.
[WebMethod]
public int
updateRecord(int id,
string sUserName, string sPass, string sEmail,
string sGender)
{
var con = new SqlConnection(ConnectionState());
var cmd = new SqlCommand("update
tblEmployee set UserName='" + sUserName + "',
Pass ='" + sPass + "', Email ='" +
sEmail + "' , Gender='" + sGender +
"' where ID='" + id +
"'", con);
con.Open();
int row = cmd.ExecuteNonQuery();
con.Close();
return row;
}
|
Step 25: Update Web Service as
step 20.
Step 26: Write below code on
Update.aspx.cs page.
using System;
using System.Data;
public partial
class Update
: System.Web.UI.Page
{
protected void
Page_Load(object sender,
EventArgs e)
{
if (!IsPostBack)
{
getId();
}
}
public void
getId()
{
localhost.Service dbws =
new localhost.Service(); //
you need to create the object of the web service
DataTable dt = dbws.getRecord();
ddlSelect.DataTextField = "Id";
ddlSelect.DataValueField = "Id";
ddlSelect.DataSource = dt;
ddlSelect.DataBind();
}
protected void
btnUpdate_Click(object sender,
EventArgs e)
{
localhost.Service dbws =
new localhost.Service(); //
you need to create the object of the web service
int row = dbws.updateRecord(Convert.ToInt32(ddlSelect.SelectedItem.Text),
txtUserName.Text, txtPass.Text, txtEmail.Text, txtGender.Text);
if (row > 0)
{
Response.Write("<script>alert('Record update
successfuly');</script>");
}
else { Response.Write("<script>alert('Record not
update');</script>"); }
}
protected void
ddlSelect_SelectedIndexChanged(object sender,
EventArgs e)
{
localhost.Service dbws =
new localhost.Service(); //
you need to create the object of the web service
DataTable dt = dbws.getDetails(Convert.ToInt32(ddlSelect.SelectedItem.Text));
txtUserName.Text = dt.Rows[0]["UserName"].ToString();
txtPass.Text = dt.Rows[0]["Pass"].ToString();
txtEmail.Text = dt.Rows[0]["Email"].ToString();
txtGender.Text = dt.Rows[0]["Gender"].ToString();
}
}
|
Note:
getId() : This is a method that get
value form database and populate into drop down for selecting record.
btnUpdate_Click(object
sender, EventArgs e) : This is an event
that execute query for updating record from database.
ddlSelect_SelectedIndexChanged(object sender,
EventArgs e) : This is an
event of dropdown that populate data into textbox according dropdown value.
Step 27: Save and build application. Execute Update.aspx
page and Update record.
Here this article ends. I hope you enjoyed this article.
No comments:
Post a Comment