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{

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

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

<span>using</span><span> System.Data;
</span><span>using</span><span> System.Data.SqlClient;
</span><span>using</span><span> System.Web.Configuration;

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

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

<span>[OperationContract]
</span><span>public</span> <span>void</span><span> DoWork()
{
  </span><span>return</span><span>;
 }</span>

为

<span>using</span><span> System.Data;
</span><span>using</span><span> System.Collections.Generic;

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

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

<span>using</span><span> System.Xml;
</span><span>using</span><span> System.IO;

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

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

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

<configuration>
    <system.web></system.web></configuration>

之间插入

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

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