几步走，教你创建简单访问数据库方法(1)(2)

第七步：我们在SQL SERVER2008中创建一个示例数据库名为SLRiaTest。在这个数据库里面新增一个UserInfo表，在里面添加字段如下：

第八步：编写一个类，来连接SLRiaTest数据库，获取DataSet数据集。

DB.cs数据库访问类   
    /// <summary> 
    /// DB 的摘要说明  
    /// </summary> 
    public   class  DB  
    {  
        private static IsolationLevel m_isoLevel = IsolationLevel.ReadUncommitted;  
        private static string connStr = string.Empty;  
        private DB()  
        {  
        }  
        #region DB2 Access Functions  
        static public IsolationLevel IsolationLevel  
        {  
            get  
            {  
                return m_isoLevel;  
            }  
        }  
        /// <summary> 
        /// Gets Connection out of Web.config  
        /// </summary> 
        /// <returns>Returns SqlConnection</returns> 
        public static SqlConnection GetConnection()  
        {  
            if (connStr == string.Empty)  
            {  
                AppSettingsReader configurationAppSettings = new AppSettingsReader();   
                connStr = "Data Source=CXL-DC6F5F6CA80;  
                POOLING=FALSE;database=SLRiaTest;  
                User ID=sa;Password=123456";  
            }  
            SqlConnection conn = new SqlConnection(connStr);  
            conn.Open();  
            return conn;  
        }  
        /// <summary> 
        /// Gets data out of database using a plain text string command  
        /// </summary> 
        /// <param name="sql">string command to be executed</param> 
        /// <returns>DataTable with results</returns> 
        static public DataSet Getdata(string sql)  
        {  
            using (SqlConnection conn = GetConnection())  
            {  
                using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))  
                {  
                    try  
                    {  
                        using (SqlCommand cmd = conn.CreateCommand())  
                        {  
                            if (sql != null)  
                            {  
                                sqlsql = sql.ToLower().Replace("<;", "");  
                                //.Replace(Convert.ToChar(32).ToString(), "&nbsp;").Replace(" ", "&nbsp;").Replace("%32", "&nbsp;").Replace("%20", "&nbsp;");  
                                sqlsql = sql.Replace(">;", "");  
                                sqlsql = sql.Replace("script", "");  
                                sqlsql = sql.Replace("object", "");  
                                sqlsql = sql.Replace("applet", "");  
                                sqlsql = sql.Replace("[", "");  
                                sqlsql = sql.Replace("]", "");  
                                sqlsql = sql.Replace("execute", "");  
                                sqlsql = sql.Replace("exec", "");  
                                sqlsql = sql.Replace("union", "");  
                                sqlsql = sql.Replace("drop", "");  
                                sqlsql = sql.Replace("delete", "");  
                                //  sqlsql = sql.Replace("chr", "");  
                                //sqlsql = sql.Replace("mid", "");  
                                sqlsql = sql.Replace("truncate", "");  
                                sqlsql = sql.Replace("nchar", "");  
                                //  sqlsql = sql.Replace("varchar", "");  
                                //sqlsql = sql.Replace("char", "");  
                                sqlsql = sql.Replace("alter", "");  
                                // sqlsql = sql.Replace("cast", "");  
                                sqlsql = sql.Replace("exists", "");  
                                sqlsql = sql.Replace("update", "");  
                            }  
                            cmd.Transaction = trans;  
                            cmd.CommandType = CommandType.Text;  
                            cmd.CommandText = sql;  
                            using (DataSet ds = new DataSet())  
                            {  
                                using (SqlDataAdapter da = new SqlDataAdapter())  
                                {  
                                    da.SelectCommand = cmd;  
                                    da.SelectCommand.Connection = conn;  
                                    da.Fill(ds);  
                                    return ds;  
                                }  
                            }  
                        }  
                    }  
                    finally  
                    {  
                        trans.Commit();  
                    }  
                }  
            }  
        }  
        #endregion  
    }  
 

第九步：鼠标右键点击SLGetDataFromWCFRIA项目“添加服务引用”即引用刚才我们编写的WCF服务。

第十步：添加一个UserInfo的实体类集合。代码如下：

/// <summary> 
/// 用户实体类  
/// </summary> 
public class UserInfo  
{  
    private string _ID;  
    private string _UserName;  
    private string _UserAddr;  
    private string _UserTel;  
    public string ID  
    {  
        get { return _ID; }  
        set { _ID = value; }  
    }  
    public string UserName  
    {  
        get { return _UserName; }  
        set { _UserName = value; }  
    }  
    public string UserAddr  
    {  
        get { return _UserAddr; }  
        set { _UserAddr = value; }  
    }  
    public string UserTel  
    {  
        get { return _UserTel; }  
        set { _UserTel = value; }  
    }  
}  

第十一步：点击MainPage.xaml文件，添加一个DataGird控件命名为grShow，在MainPage.xaml.cs文件中编写以下代码获取WCF RIA services读取到的数据库数据：

public partial class MainPage : UserControl  
{  
    public MainPage()  
    {  
        InitializeComponent();  
        //创建一个代理类的实例  
        SLWCFRIAClient client = new SLWCFRIAClient();  
        //调用GetData方法并加载事件  
        client.GetDataAsync();  
        client.GetDataCompleted += new EventHandler<GetDataCompletedEventArgs>(client_GetDataCompleted);  
    }  
    public  List<UserInfo> userList = new List<UserInfo>();  
    void client_GetDataCompleted(object sender, GetDataCompletedEventArgs e)  
    {  
        using (XmlReader xReader = XmlReader.Create(new StringReader(e.Result)))  
        {  
            //XmlReader读取XML数据  
            while (xReader.ReadToFollowing("Table"))  
            {  
                xReader.ReadToDescendant("ID");  
                string id = xReader.ReadElementContentAsString();  
                xReader.ReadToNextSibling("UserName");  
                string username = xReader.ReadElementContentAsString();  
                xReader.ReadToNextSibling("UserAddr");  
                string useraddr = xReader.ReadElementContentAsString();  
                xReader.ReadToNextSibling("UserTel");  
                string usertel = xReader.ReadElementContentAsString();  
                //实例化类并添加进实体类List<> 
                UserInfo uinfo = new UserInfo(){ID=id, UserName=username,UserAddr=useraddr,UserTel=usertel};  
                userList.Add(uinfo);  
            }  
            this.grShow.ItemsSource = userList;  
        }  
    }  
}  

最后我们看最后的效果图，本实例采用VS2010+Silverlight 4.0+MS SQL SERVER 2008，复制下述网址 http://files.cnblogs.com/chengxingliang/SLGetDataFromWCFRIA.rar）即可下载本实例源码和示例数据库