WCF初见之SQL数据库的增删改查
WCF初见之SQL数据库的增删改查
1.首先要连接数据库,自然要有数据库啦,创建一个数据库表Login,并插入一个数据:
--创建数据库表login CREATE TABLE Login ( UName VARCHAR(20) PRIMARY KEY NOT NULL, UPassword VARCHAR(30) NOT NULL, UState INT DEFAULT'0' NOT NULL ) --查询Login表 SELECT * FROM Login SELECT UName,UPassword FROM Login --插入数据 INSERT INTO Login(UName,UPassword) VALUES('张三','123456')
2.下面就是WCF服务的创建了,直接创建一个WCF服务应用程序,项目名称为“ODataBase”,如下图:
3.创建服务契约和创建服务:
(1)IDataBase.cs (创建服务契约):
using System.Data; using System.ServiceModel; namespace ODataBase { [ServiceContract] public interface IService1 { //新增数据 [OperationContract] int InsertLogin(string strName,string strPwd); //删除数据 [OperationContract] int DeleteLogin(string strName); //修改数据 [OperationContract] int UpdataLogin(string strName, string strPwd); //查询数据 [OperationContract] DataSet SelectLogin(); } }
(2)DataBase.svc (创建服务)
using System.ServiceModel; using System.Data; using System.Data.SqlClient; using System; namespace ODataBase { public class DataBase : IService1 { //连接数据库 SqlConnection strCon = new SqlConnection(@"server=IT01\SQLEXPRESS;uid=sa;pwd=yc12369;database=yctest"); //添加数据 public int InsertLogin(string strName,string strPwd) { try { strCon.Open(); string strSql = "INSERT INTO Login(UName,UPassword) VALUES(@strName,@strPwd)"; SqlCommand cmd = new SqlCommand(strSql,strCon); SqlParameter parn = new SqlParameter("@strName",strName); cmd.Parameters.Add(parn); SqlParameter parp = new SqlParameter("@strPwd",strPwd); cmd.Parameters.Add(parp); //result接受受影响的行数,也就是说大于0的话表示添加成功 int result = cmd.ExecuteNonQuery(); cmd.Dispose(); return result; }catch(Exception ex) { throw ex; }finally { strCon.Close(); } } //删除数据 public int DeleteLogin(string strName) { try { strCon.Open(); string strSql = "DELETE FROM Login WHERE UName = @strName"; SqlCommand cmd = new SqlCommand(strSql,strCon); SqlParameter parn = new SqlParameter("@strName",strName); cmd.Parameters.Add(parn); int result = cmd.ExecuteNonQuery(); cmd.Dispose(); return result; }catch(Exception ex) { throw ex; }finally { strCon.Close(); } } //修改数据 public int UpdataLogin(string strName,string strPwd) { try { strCon.Open(); string strSql = "UPDATE Login SET UPassWord=@strPwd WHERE UName =@strName"; SqlCommand cmd = new SqlCommand(strSql,strCon); SqlParameter parn = new SqlParameter("@strName",strName); cmd.Parameters.Add(parn); SqlParameter parp = new SqlParameter("@strPwd",strPwd); cmd.Parameters.Add(parp); int result = cmd.ExecuteNonQuery(); cmd.Dispose(); return result; }catch(Exception ex) { throw ex; }finally { strCon.Close(); } } //查询数据 public DataSet SelectLogin() { try { strCon.Open(); string strSql = "SELECT UName,UPassword FROM Login "; DataSet ds = new DataSet(); SqlDataAdapter s = new SqlDataAdapter(strSql, strCon); s.Fill(ds); return ds; }catch(Exception ex) { throw ex; }finally { strCon.Close(); } } } }
4.然后新建一个名为Web4DataBase的Web客户端(用于测试),先引用创建的WCF服务,具体过程见WCF初见之HelloWorld,然后进行Web端的代码编写:
(1)Test4DataBase.aspx(Web界面代码)
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Test4DataBase.aspx.cs" Inherits="Web4DataBase.Test4DataBase" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>SQL数据库操作测试</title> </head> <body> <form id="DataBaseFrom" runat="server"> <h2>SQL数据库操作测试</h2> <br /> 用户名:<asp:TextBox ID="txbName" runat="server"></asp:TextBox> <br /><br /> 密 码:<asp:TextBox ID="txbPwd" runat="server" TextMode="Password"></asp:TextBox> <br /><br /> <asp:Button ID="btn_InsertLogin" runat="server" Text="新增数据" onclick="btn_InsertLogin_Click"/> <asp:Button ID="btn_DeleteLogin" runat="server" Text="删除数据" onclick="btn_DeleteLogin_Click"/> <asp:Button ID="btn_UpdataLogin" runat="server" Text="更新数据" onclick="btn_UpdataLogin_Click"/> <asp:Button ID="btn_ResetLogin" runat="server" Text="重置数据" onclick="btn_ResetLogin_Click"/> <br /><br /> <asp:GridView ID="gvLogin" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="UName" HeaderText="用户名" /> <asp:BoundField DataField="UPassword" HeaderText="密码" /> </Columns> </asp:GridView> </form> </body> </html>
(2)Test4DataBase.aspx.cs(功能实现代码)
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace Web4DataBase { public partial class Test4DataBase : System.Web.UI.Page { service.Service1Client host = new service.Service1Client(); protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindData(); }; } //绑定数据源 private void BindData() { gvLogin.DataSource = host.SelectLogin(); gvLogin.DataBind(); } //新增数据 protected void btn_InsertLogin_Click(object sender, EventArgs e) { string strName = txbName.Text.Trim(); string strPwd = txbPwd.Text.Trim(); host.InsertLogin(strName,strPwd); BindData(); } //删除数据 protected void btn_DeleteLogin_Click(object sender, EventArgs e) { string strName = txbName.Text.Trim(); host.DeleteLogin(strName); BindData(); } //更新数据 protected void btn_UpdataLogin_Click(object sender, EventArgs e) { string strName = txbName.Text.Trim(); string strPwd = txbPwd.Text.Trim(); host.UpdataLogin(strName, strPwd); BindData(); } // 重置数据 protected void btn_ResetLogin_Click(object sender, EventArgs e) { txbName.Text = ""; txbPwd.Text = ""; } } }
5.效果图如下:
原文地址:http://www.cnblogs.com/yc-755909659/archive/2012/07/10/2584703.html