Database Accessing In Silverlight:-

We can’t access database directly in Silverlight.The most comman ways to get database access is 1.Wcf services.2.Wcf RIA services.

1.Using first way we have to create wcf service and add database accessing logic in wcf service.

2.The best way is wcf RIA(Rich internet applications).

->Create Silverlight project and check the enable WCF RIA Services checkbox.

->For this example I have created a simple database table like below.

->Click Silverlight web project and add ADO.NET Entity Data Model file

and select  connection and table from you database.

.->Your model should look like this when you are done.

->Build solution.

-> Click Silverlight web project and add Domain Service Class.Check your table,

    Enable editing check boxes and Generate associated classes for metadata.

Now you can put all your server side data access code and logic into the UserService.cs class.

->Build solution.

->Goto Silverlight application properties select you web project in Wcf RIA Services link dropdown.

Here we have to use context for calling methods in UserService.

In MainPage add data grid.

<sdk:DataGrid  Name="dgUsers"/>

private void UserControl_Loaded(object sender, RoutedEventArgs e)

{

      //The DomainContext corresponding to the 'UserService' DomainService.

      DatabaseAccess.Web.UserContext objContext = new Web.UserContext();

      //Set the item source.

      dgUsers.ItemsSource = objContext.Users;

      //Query the users.

      objContext.Load(objContext.GetUsersQuery());

}

Custom Methods:-

We can add our own methods in UserService.cs class.

Add CustomGetUser function in UserService.cs class.

[Invoke]

public List<User> CustomGetUsers()

{

            string connectionString = "you connection string…";

            SqlConnection sqlConnection = new SqlConnection(connectionString);

            DataSet objSet = new DataSet();

            SqlCommand sqlCommand = new SqlCommand();

            sqlCommand.Connection = sqlConnection;

            sqlCommand.CommandText = "SELECT [iUserId],[vcUserName],[vcCity],[vcCountry],[vcMobile] FROM [Blog].[dbo].[User]";

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

            sqlDataAdapter.SelectCommand = sqlCommand;

            sqlDataAdapter.Fill(objSet);

            List<User> lstResult = new List<User>();

            User item;

            if (objSet.Tables.Count > 0)

            {

                foreach (DataRow dr in objSet.Tables[0].Rows)

                {

                    item = new User();

                    item.iUserId = Convert.ToInt32(dr["iUserId"].ToString());

                    item.vcUserName = dr["vcUserName"].ToString();

                    item.vcCity = dr["vcCity"].ToString();

                    item.vcCountry = dr["vcCountry"].ToString();

                    item.vcMobile = dr["vcMobile"].ToString();

                    lstResult.Add(item);

                }

            }

            return lstResult;

}

Call this function from main page as

private void UserControl_Loaded(object sender, RoutedEventArgs e)

{

            //Call the function.

            objContext.CustomGetUsers(CallBack, null);

}

//Callback.

void CallBack(InvokeOperation<List<User>> e)

{

  if (!e.HasError)

     {

         //User list.

         List<User> lstUsers = new List<User>();

         //Get the users list.

         lstUsers = e.Value;

          //Set the item source.

          dgUsers.ItemsSource = lstUsers;

        }

   }

Let me know, if you have any feedback. Mail me for source code. Enjoy reading my articles…

sekhartechblog@gmail.com