Siva Ganesh Anantharajan

Webservice for access database connection with insert , select all and select particular in Visual C#.

Service1.asmx

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.Xml.Linq;

using System.Data.OleDb;

using System.Xml;

namespace dbWebService1

{

///

/// Summary description for Service1

///

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[ToolboxItem(false)]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

// [System.Web.Script.Services.ScriptService]

public class Service1 : System.Web.Services.WebService

{

[WebMethod]

public string insertqry(string name,string roll,string dept)

{

OleDbConnection con1 = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;data source=~~use your's database location~~");

con1.Open();

string sqry="insert into ~~use your table name~~ values('"+name+"','"+roll+"','"+dept+"')";

OleDbCommand cmd = new OleDbCommand(sqry, con1);

cmd.ExecuteNonQuery();

con1.Close();

return "Inserted";

}

[WebMethod]

public XmlElement selectqry()

{

OleDbConnection con1 = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;data source=~~use your's database location~~");

con1.Open();

string sqry1 = "select * from ~~use your table name~~";

OleDbCommand cmd = new OleDbCommand(sqry1, con1);

cmd.ExecuteNonQuery();

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con1.Close();

XmlDataDocument xd = new XmlDataDocument(ds);

XmlElement xe = xd.DocumentElement;

return xe;

}

[WebMethod]

public XmlElement selectparticular(string roll)

{

OleDbConnection con1 = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;data source=~~use your's database location~~");

con1.Open();

string sqry1 = "select * from ~~use your table name~~ where ~~use your table field name~~='"+roll+"'";

OleDbCommand cmd = new OleDbCommand(sqry1, con1);

cmd.ExecuteNonQuery();

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con1.Close();

XmlDataDocument xd = new XmlDataDocument(ds);

XmlElement xe = xd.DocumentElement;

return xe;

}

Table:

Create a table with the following details

Name	Roll	Dept
Siva	12it001	IT

Web Application in asp

Default.aspx

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Xml;

namespace WebApplication1

{

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

string na, rol, dep;

na = TextBox1.Text;

rol = TextBox2.Text;

dep = TextBox3.Text;

localhost.Service1 fg = new WebApplication1.localhost.Service1();

Label2.Text = fg.insertqry(na,rol,dep);

}

protected void Button2_Click(object sender, EventArgs e)

{

localhost.Service1 fg = new WebApplication1.localhost.Service1();

DataSet ds = new DataSet();

XmlElement xe = fg.selectqry();

XmlNodeReader nodereader = new XmlNodeReader(xe);

ds.ReadXml(nodereader, XmlReadMode.Auto);

gvUserDetails.DataSource = ds;

gvUserDetails.DataBind();

}

protected void Button3_Click(object sender, EventArgs e)

{

string roll;

localhost.Service1 fg = new WebApplication1.localhost.Service1();

roll = TextBox4.Text;

DataSet ds = new DataSet();

XmlElement xe = fg.selectparticular(roll);

XmlNodeReader nodereader = new XmlNodeReader(xe);

ds.ReadXml(nodereader, XmlReadMode.Auto);

GridView1.DataSource = ds;

GridView1.DataBind();

}

Web page design in asp .net

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Untitled Page</title>

</head>

<body>

<p>

</p>

<p>

<asp:Label ID="Label1" runat="server" Text="Insert details"></asp:Label>

</p>

<p>

name :

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

</p>

<p>

roll :

<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

</p>

<p>

department:

<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

</p>

<div>

<asp:Button ID="Button1" runat="server" onclick="Button1_Click"

Text="Insert " Width="203px" />

<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>

</div>

<p>

click below button to select all from a table in database</p>

<p>

<asp:Button ID="Button2" runat="server" Text="select all from table"

Width="194px" onclick="Button2_Click" />

</p>

<p>

</p>

<table>

<tr><td>name</td><td>rollno</td><td>department</td></tr>

</table>

<asp:GridView ID="gvUserDetails" runat="server" EmptyDataText="No Record Found">

</asp:GridView>

<p>

Enter a roll number to display its details</p>

<p>

roll:<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>

</p>

<p>

<asp:Button ID="Button3" runat="server" Text="select" Width="156px"

onclick="Button3_Click" />

</p>

<table>

<tr><td>name</td><td>rollno</td><td>department</td></tr>

</table>

<asp:GridView ID="GridView1" runat="server" EmptyDataText="No Record Found">

</asp:GridView>

</form>

</body>

</html>

Tuesday, September 29, 2015

Webservice for access database connection with insert , select all and select particular in Visual C#.

Followers