啦啦,写代码了
本项目参考以下博客:
http://www.cnblogs.com/iamlilinfeng/archive/2012/09/25/2700049.html
http://www.cnblogs.com/wuhuacong/archive/2011/09/29/2195528.html
项目结构如下:
下面我直接贴代码了,废话少说
小二,上菜
除了Service层是WCF应用程序外,其他的都是类库项目
DAL层
MySqlHelper.cs
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using MySql.Data;
using MySql.Data.MySqlClient;
/// <summary>
///MYSQLHelper 的摘要说明
/// </summary>
public abstract class MySqlHelper
{
//数据库连接字符串
public static string Conn = "Database='wcf';Data Source='localhost';User Id='root';Password='root';charset='utf8';pooling=true"; //XXX的为修改项
/// <summary>
/// 给定连接的数据库用假设参数执行一个sql命令(不返回数据集)
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 用现有的数据库连接执行一个sql命令(不返回数据集)
/// </summary>
/// <param name="connection">一个现有的数据库连接</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</returns>
public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
///使用现有的SQL事务执行一个sql命令(不返回数据集)
/// </summary>
/// <remarks>
///举例:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">一个现有的事务</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</returns>
public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 用执行的数据库连接执行一个返回数据集的sql命令
/// </summary>
/// <remarks>
/// 举例:
/// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>包含结果的读取器</returns>
public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程,因为如果这个方法产生一个异常我们要关闭连接,因为没有读取器存在,
//因此commandBehaviour.CloseConnection 就不会执行
try
{
//调用 PrepareCommand 方法,对 MySqlCommand 对象设置参数
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//调用 MySqlCommand 的 ExecuteReader 方法
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//清除参数
cmd.Parameters.Clear();
return reader;
}
catch
{
//关闭连接,抛出异常
conn.Close();
throw;
}
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns></returns>
public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程,因为如果这个方法产生一个异常我们要关闭连接,因为没有读取器存在,
try
{
//调用 PrepareCommand 方法,对 MySqlCommand 对象设置参数
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//调用 MySqlCommand 的 ExecuteReader 方法
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
//清除参数
cmd.Parameters.Clear();
conn.Close();
return ds;
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
/// </summary>
/// <remarks>
///例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
///<param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 用指定的数据库连接执行一个命令并返回一个数据集的第一列
/// </summary>
/// <remarks>
/// 例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个存在的数据库连接</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 准备执行一个命令
/// </summary>
/// <param name="cmd">sql命令</param>
/// <param name="conn">OleDb连接</param>
/// <param name="trans">OleDb事务</param>
/// <param name="cmdType">命令类型例如 存储过程或者文本</param>
/// <param name="cmdText">命令文本,例如:Select * from Products</param>
/// <param name="cmdParms">执行命令的参数</param>
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (MySqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
User.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
namespace WCF.Demo.DAL
{
public class User {
private static string Conn = "Database='wcf';Data Source='localhost';User Id='root';Password='root';charset='utf8';pooling=true";
//增加
public static bool add(Model.User user) {
string sql = string.Format("INSERT INTO User(Name,Password,Discribe,SubmitTime) VALUES('{0}','{1}','{2}','{3}')", user.UserName, user.Password, user.Discribe, user.SubmitTime);
int result = MySqlHelper.ExecuteNonQuery(Conn,CommandType.Text, sql);
if (result > 0)
{
return true;
}
else {
return false;
}
}
//修改
public static bool update(Model.User user) {
string sql = string.Format("UPDATE User SET Name = '{0}',Discribe = '{2}',SubmitTime = '{3}',Password = '{4}' WHERE UserID = {5}", user.UserName, user.Password, user.Discribe, user.SubmitTime,user.Password,user.UserID);
int result = MySqlHelper.ExecuteNonQuery(Conn,CommandType.Text,sql);
if (result > 0)
{
return true;
}
else {
return false;
}
}
//删除
public static bool delete(int userId) {
string sql = string.Format("DELETE FROM User WHERE UserId = {0}", userId);
int result = MySqlHelper.ExecuteNonQuery(Conn,CommandType.Text,sql);
if (result > 0)
{
return true;
}
else {
return false;
}
}
//获取用户
public static Model.User Get(int userId) {
Model.User user = new Model.User();
string sql = string.Format(" select * from User where UserId = {0}",userId);
DataSet ds = MySqlHelper.GetDataSet(Conn, CommandType.Text, sql);
if(ds!=null&&ds.Tables.Count>0){
foreach(DataRow dr in ds.Tables[0].Rows){
user.UserID = Convert.ToInt32(dr["UserId"]);
user.UserName = dr["Name"].ToString();
user.Password = dr["Password"].ToString();
user.Discribe = dr["Discribe"].ToString();
user.SubmitTime = Convert.ToDateTime(dr["SubmitTime"]);
}
}
return user;
}
//获取用户列表
public static List<Model.User> GetUserList() {
List<Model.User> list = new List<Model.User>();
string sql = string.Format("select * from User");
DataSet ds = MySqlHelper.GetDataSet(Conn,CommandType.Text,sql);
if(ds!=null&&ds.Tables.Count>0){
foreach(DataTable dt in ds.Tables){
foreach(DataRow dr in dt.Rows){
Model.User user = new Model.User();
user.UserID = Convert.ToInt32(dr["UserId"]);
user.UserName = dr["Name"].ToString();
user.Password = dr["Password"].ToString();
user.Discribe = dr["Discribe"].ToString();
user.SubmitTime = Convert.ToDateTime(dr["SubmitTime"]);
list.Add(user);
}
}
}
return list;
}
//用户登录验证
public static Model.User CheckLoginUser(string userName,string userPwd){
Model.User user = new Model.User();
string sql = string.Format("select * from User where Name='{0}' and Password ='{1}' ",userName,userPwd);
DataSet ds = MySqlHelper.GetDataSet(Conn, CommandType.Text, sql);
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
user.UserID = Convert.ToInt32(dr["UserId"]);
user.UserName = dr["Name"].ToString();
user.Password = dr["Password"].ToString();
user.Discribe = dr["Discribe"].ToString();
user.SubmitTime = Convert.ToDateTime(dr["SubmitTime"]);
}
}
return user;
}
}
}
Model层
User.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
namespace WCF.Demo.Model
{
//用户实体
[DataContract]
public class User {
[DataMember]
public int UserID{get;set;}
[DataMember]
public string UserName { get; set; }
[DataMember]
public string Password { get; set; }
[DataMember]
public string Discribe { get; set; }
[DataMember]
public DateTime SubmitTime { get; set; }
}
}
Service层
在Service层右键,添加,新建项,选择WCF服务,添加以下服务
Add.svc
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码、svc 和配置文件中的类名“Add”。
public class Add : IAdd
{
public bool add(Model.User user) {
return DAL.User.add(user);
}
}
}
IAdd.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码和配置文件中的接口名“IAdd”。
[ServiceContract]
public interface IAdd
{
[OperationContract]
bool add(Model.User user);
}
}
Get.svc
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码、svc 和配置文件中的类名“Get”。
public class Get : IGet
{
public Model.User DoWork(int userId)
{
return DAL.User.Get(userId);
}
}
}
IGet.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码和配置文件中的接口名“IGet”。
[ServiceContract]
public interface IGet
{
[OperationContract]
Model.User DoWork(int userId);
}
}
Save.svc
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码、svc 和配置文件中的类名“Save”。
public class Save : ISave
{
public bool DoWork(Model.User user)
{
return DAL.User.update(user);
}
}
}
ISave.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码和配置文件中的接口名“ISave”。
[ServiceContract]
public interface ISave
{
[OperationContract]
bool DoWork(Model.User user);
}
}
Remove.svc
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码、svc 和配置文件中的类名“Remove”。
public class Remove : IRemove
{
public bool DoWork(int userId)
{
return DAL.User.delete(userId);
}
}
}
IRemove.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码和配置文件中的接口名“IRemove”。
[ServiceContract]
public interface IRemove
{
[OperationContract]
bool DoWork(int userId);
}
}
Search.svc
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码、svc 和配置文件中的类名“Search”。
public class Search : ISearch
{
public List<Model.User> DoWork()
{
return DAL.User.GetUserList();
}
}
}
ISearch.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码和配置文件中的接口名“ISearch”。
[ServiceContract]
public interface ISearch
{
[OperationContract]
List<Model.User> DoWork();
}
}
CheckUserLogin.svc
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码、svc 和配置文件中的类名“CheckUserLogin”。
public class CheckUserLogin : ICheckUserLogin
{
public Model.User CheckLogin(string userName, string userPwd)
{
return DAL.User.CheckLoginUser(userName,userPwd);
}
}
}
ICheckUserLogin.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF.Demo.Service
{
// 注意: 使用“重构”菜单上的“重命名”命令,可以同时更改代码和配置文件中的接口名“ICheckUserLogin”。
[ServiceContract]
public interface ICheckUserLogin
{
[OperationContract]
Model.User CheckLogin(string userName,string userPwd);
}
}
web.config
本配置包含X.509证书的配置
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0" /> <!--发布是设为false-->
</system.web>
<system.serviceModel>
<!--配置服务和终结点开始-->
<serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
<services>
<service name="WCF.Demo.Service.Add" behaviorConfiguration="serviceBehavior">
<endpoint address="" binding="wsHttpBinding"
bindingConfiguration="CustomWsHttpBinding" contract="WCF.Demo.Service.IAdd">
<identity>
<dns value="UserLeverServer" />
</identity>
</endpoint>
</service>
<service name="WCF.Demo.Service.Save" behaviorConfiguration="serviceBehavior">
<endpoint address="" binding="wsHttpBinding"
bindingConfiguration="CustomWsHttpBinding" contract="WCF.Demo.Service.ISave">
<identity>
<dns value="UserLeverServer" />
</identity>
</endpoint>
</service>
<service name="WCF.Demo.Service.Search" behaviorConfiguration="serviceBehavior">
<endpoint address="" binding="wsHttpBinding"
bindingConfiguration="CustomWsHttpBinding" contract="WCF.Demo.Service.ISearch">
<identity>
<dns value="UserLeverServer" />
</identity>
</endpoint>
</service>
<service name="WCF.Demo.Service.Remove" behaviorConfiguration="serviceBehavior">
<endpoint address="" binding="wsHttpBinding"
bindingConfiguration="CustomWsHttpBinding" contract="WCF.Demo.Service.IRemove">
<identity>
<dns value="UserLeverServer" />
</identity>
</endpoint>
</service>
<service name="WCF.Demo.Service.Get" behaviorConfiguration="serviceBehavior">
<endpoint address="" binding="wsHttpBinding"
bindingConfiguration="CustomWsHttpBinding" contract="WCF.Demo.Service.IGet">
<identity>
<dns value="UserLeverServer" />
</identity>
</endpoint>
</service>
<service name="WCF.Demo.Service.CheckUserLogin" behaviorConfiguration="serviceBehavior">
<endpoint address="" binding="wsHttpBinding"
bindingConfiguration="CustomWsHttpBinding" contract="WCF.Demo.Service.ICheckUserLogin">
<identity>
<dns value="UserLeverServer" />
</identity>
</endpoint>
</service>
</services>
<!--绑定开始-->
<bindings>
<wsHttpBinding>
<binding name="CustomWsHttpBinding">
<!--X509证书服务配置-->
<security mode="Message">
<message clientCredentialType="Certificate"/>
</security>
<!--X509证书服务配置-->
</binding>
<!--绑定结束-->
</wsHttpBinding>
</bindings>
<!--配置服务和终结点结束-->
<!--配置行为开始-->
<behaviors>
<serviceBehaviors>
<behavior name="serviceBehavior">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
<!-- 配置运行时吞吐量设置,这些设置可以优化服务性能-->
<!-- maxConcurrentCalls:该值指定整个 ServiceHost 中正在处理的最多消息数-->
<!-- maxConcurrentInstances:获取或设置一个值,该值指定服务中可以一次执行的最多 InstanceContext 对象数-->
<!-- maxConcurrentSessions:获取或设置一个指定 ServiceHost 对象可一次接受的最多会话数的值-->
<serviceThrottling maxConcurrentCalls="1000" maxConcurrentInstances="1000" maxConcurrentSessions="1000"/>
<serviceCredentials>
<clientCertificate>
<authentication certificateValidationMode="None" />
</clientCertificate>
<serviceCertificate findValue="UserLeverServer" storeLocation="LocalMachine" storeName="My" x509FindType="FindBySubjectName" />
</serviceCredentials>
</behavior>
</serviceBehaviors>
</behaviors>
<!--配置行为结束-->
</system.serviceModel>
<system.webServer>
<modules runAllManagedModulesForAllRequests="true"/>
</system.webServer>
</configuration>
右键项目,点击发布到IIS网站目录即可。
至于客户端懒得写了。。。
参考最上面的链接就好。他写的不错
最后一篇就谈谈遇到的问题吧。。