[学习笔记]Silverlight+WCF对数据库进行操作

构建一个

silverlight命名为ContentSql，确定之后选择添加wcf服务。

右键ContentSql选择新建项-->silverlight WCF 服务。名字为UserService

using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;

namespace ContentSql.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class UserService
    {
        [OperationContract]
        public void DoWork()
        {
            // 在此处添加操作实现
            return;
        }
        [OperationContract]
        public string RetrieveUser(string sqlselect)//查询用户
        {
            try
            {
                string conString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString;
                SqlConnection _sqlConnection = new SqlConnection(conString);
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();
                //da.SelectCommand = new SqlCommand("SELECT * FROM Players", _sqlConnection);
                da.SelectCommand = new SqlCommand(sqlselect, _sqlConnection);
                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();
                return ds.GetXml();
            }
            catch (Exception ex)
            {
                return string.Empty;
            }
        }
       
        [OperationContract]
        public bool CreateUser(string CreateSql)//创建用户---->可以实现insert update delect
        {
            try
            {
                string conString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString;
                SqlConnection _sqlConnection = new SqlConnection(conString);
                _sqlConnection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = _sqlConnection;
                command.CommandType = CommandType.Text;
                command.CommandText = CreateSql;//创建
                command.ExecuteNonQuery();
                _sqlConnection.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        // 在此处添加更多操作并使用 [OperationContract] 标记它们
    }
}

在右键选择新建类-->命名为IUserService(接口类)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace ContentSql.Web
{
    [ServiceContract]
    public interface IUserService
    {
        [OperationContract]
        void DoWork();
        [OperationContract]
        string RetrieveUser(string sqlselect);//查询用户
        [OperationContract]
        bool CreateUser(string CreateSql);

    }
}

创建这两个文件之后。选择--->生成(右键项目第一个)(我自己使用的是中文版的)

////

右键ContentSql选择-->添加服务引用。

点发现---->点开服务目录---->选择UserService--->命名空间改为sqlText

在mainpage.xaml里面添加createButton按钮和

void createButton_Click(object sender, RoutedEventArgs e)
        {
            UserServiceClient userSqlClient = new UserServiceClient();
            string sqlselect = "SELECT * FROM Players where gid= '1'  ";
            userSqlClient.RetrieveUserAsync(sqlselect);           

 userSqlClient.CreateUserCompleted += new EventHandler<CreateUserCompletedEventArgs>(userSqlClient_CreateUserCompleted);

        }
        void userSqlClient_CreateUserCompleted(object sender, CreateUserCompletedEventArgs e)
        {
            //完成CreateUserAsync()方法后回调，这里象征性的提示是否完成
            if (e.Error == null)
            {
                textBlock1.Text = "创建用户成功！";
                textBlock1.Visibility = Visibility.Visible;
            }
            else
            {
                textBlock1.Text = e.Error.ToString();
                textBlock1.Visibility = Visibility.Visible;
            }
        }
        void retrieveButton_Click(object sender, RoutedEventArgs e)
        {
            string sqlselect = "SELECT * FROM Players where gid = '1'";
            userSqlClient.RetrieveUserAsync(sqlselect);
            userSqlClient.RetrieveUserCompleted+=new EventHandler<RetrieveUserCompletedEventArgs>(userSqlClient_RetrieveUserCompleted);
        }
        public List<UserInfo> usersList = new List<UserInfo>();
        void userSqlClient_RetrieveUserCompleted(object sender, RetrieveUserCompletedEventArgs e)
        {
            //XmlReader提供对 XML 数据的非缓存的并且只向前的只读访问
            XmlReader xReader = XmlReader.Create(new StringReader(e.Result));
            //XmlReader读取XML数据
            while (xReader.ReadToFollowing("Table"))
            {
                xReader.ReadToDescendant("gid");
                string id = xReader.ReadElementContentAsString();
                xReader.ReadToNextSibling("loginname");
                string username = xReader.ReadElementContentAsString();

                string come = SessionManager.Session["loginname"].ToString();

                textBlock1.Text = "" + come;
                //textBlock1.Text = "" + come;
                textBlock2.Text = "" + username;

                UserInfo use = new UserInfo() { gid = id, name = username };//注意UserInfo()是个方法
                usersList.Add(use);
            }
            this.dataGrid1.ItemsSource = usersList;//显示在dataGrid1中
        }

要建立一个用户实体类来存放select的数据

/// <summary>
    /// 用户实体类
    /// </summary>
    public class UserInfo
    {
        private string _gid;
        private string _name;

        public string gid
        {
            get { return _gid; }
            set { _gid = value; }
        }

        public string name
        {
            get { return _name; }
            set { _name = value; }
        }
    }