silverlight通过WCF访问SQLserver示例

VS2013+sql2012+win8.1测试通过 作者：一剑

1.创建sliverlight项目testWCFsql；
2.右击web项目添加新建项->类,命名为ClassDBserver,修改代码：

using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace testWCFsql.Web
{
    public class ClassDBserver
    {
        static public DataSet DBselect(string sql)
        {
            string conn;
            conn = "Data Source=none;Initial Catalog=comDB;User ID=sa;Password=123456";
            //conn = WebConfigurationManager.AppSettings.Get("DBappSettings");
            //conn = WebConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
            try
            {
                using (SqlConnection cnn = new SqlConnection(conn))
                {
                    cnn.Open();
                    using (SqlTransaction trans = cnn.BeginTransaction(IsolationLevel.Serializable))
                    {
                        try
                        {
                            using (SqlCommand cmd = new SqlCommand(sql, cnn))
                            {
                                cmd.Transaction = trans;
                                using (DataSet ds = new DataSet())
                                {
                                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                                    {
                                        da.Fill(ds);
                                        trans.Commit();
                                    }
                                    return ds;
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            return new DataSet();
                            throw ex;
                        }
                        finally
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return new DataSet();
                throw ex;
            }
        }
    }
}

3.右击testWCFsql.web项目添加新建项“启用了Silverlight的WCF服务”,命名为ServiceWCF；
4.修改

[OperationContract]
public void DoWork()
{
  return;
 }

为

using System.Data;
using System.Collections.Generic;

[OperationContract]
public List<ClassEmployee> getData()
{
    DataSet ds = ClassDBserver.DBselect("select * from employee");
    List<ClassEmployee> userList = new List<ClassEmployee>();//添加using System.Collections.Generic;
    foreach (DataRow dr in ds.Tables[0].Rows)
    {
        ClassEmployee user = new ClassEmployee();//userList是引用类型，所以这句不能放foreach外面
        user.ID = (int)dr["ID"];
        user.name = (string)dr["name"];
        user.departmentID = (int)dr["departmentID"];
        userList.Add(user);
    }
    return userList;
}
[DataContract]
public class ClassEmployee
{
    [DataMember]
    public int ID;
    [DataMember]
    public string name;
    [DataMember]
    public int departmentID;
}

5.生成一下；
6.右击客户端testWCFsql项目，添加服务引用->发现，默认ServiceReference1，确定；
7.双击页面文件MainPage.xaml.cs,从工具箱中拖入DataGrid控件,命名为datagrid1；
8.修改后台代码为：

using System.Xml;
using System.IO;

namespace testWCFsql
{
    public partial class MainPage : UserControl
    {
        public MainPage()
        {
            InitializeComponent();
            ServiceReference1.ServiceWCFClient myClient = new ServiceReference1.ServiceWCFClient();
            myClient.getDataCompleted += new EventHandler<ServiceReference1.getDataCompletedEventArgs >(myGetDataCompleted);
            myClient.getDataAsync();
        }

        void myGetDataCompleted(object sender, ServiceReference1.getDataCompletedEventArgs e)
        {
            System.Collections.ObjectModel.ObservableCollection<ServiceReference1.ServiceWCFClassEmployee> users = e.Result;
            datagrid1.ItemsSource = users;
        }
    }
}

9.此步可选：双击Weg.config修改，在

<configuration>
    <system.web>

之间插入

    <appSettings>
      <add key="DBappSetting" value="Data Source=none;Initial Catalog=comDB;User ID=sa;Password=123456"/>
    </appSettings>
    <connectionStrings>
      <add name="myConnectionString" connectionString="Data Source=none;Initial Catalog=comDB;User ID=sa;Password=123456" providerName="System.Data.SqlClient"/>
    </connectionStrings>

同时修改前面ClassDBserver.cs中的连接字符串为从配置文件中读取。
10.Now，just run it!!