Simple SQL Database in windows phone 7.5

Hi, guys this post is in regard for making a simple SQL DataBase in windows phone7.5, here I have demonstrated a sample database for an Quotes app, with its help you can store fav. Quotes of the user, display them on any page anytime and also delete them.

Well, I strictly recommend you all to please view windows_phone_geek page before going through it.
Lets Code…
download the CODE file
First Create Table:
// Take simple Class and use tags to make it Table Note :-

using System.Data.Linq.Mapping;

 

[Table]

    public class favorite

    {     

        [Column(IsPrimaryKey = true , IsDbGenerated= false,CanBeNull=true)]

        public int quoteID{ get; set;}

 

        //[Column]

        //public string title { get; set; }

 

        //[Column]

        //public string text { get; set; }

    }

 

Create DataContext Class for defining the table you just created:
using System.Data.Linq;

public class dataContext:DataContext

    {

        public Table<favorite> favoriteTable; //Table object to access it. 

        public dataContext(string connection) : base(connection)

        {

        }

    }

 

Now Create The DataBase for your app in App.xaml.cs

#region DataBase Created

        const string connectionString = “isostore:/quoteDB.sdf”;

        public dataContext dataContextObj { get; set; } 

        private void InitializeDataContext()

        {

            dataContextObj = new dataContext(connectionString);

            if (!dataContextObj.DatabaseExists())

            {

                dataContextObj.CreateDatabase();

            }

            dataContextObj.ObjectTrackingEnabled = true;

        }

        #endregion

 

After following above 3 steps you are to the end of creating a Simple Database for your app.
Here we have created a database for storing favorite quotes of the User for Quote app. Note that for using basic proposes database should not be used as it makes app a bit slow but easier in execution of query.

So, Now we are left only with creation of queries in LinqToSql .

Create DataHelper class for making queries and its functions to perform all the operations in database with the use of DataContext class object. Thus, we can use these functions throughout our app.

using System.Collections.Generic;

    public class dataHelper

    {

        private readonly dataContext quoteDB; // object of DataContext 

        public dataHelper()

        {

            quoteDB = (App.Current as App).dataContextObj; //important

        }

 

        public void Addfav(favorite quoteDataObj)  // function to add fav. quotes.

        { 

            try

            {

                quoteDB.favoriteTable.InsertOnSubmit(quoteDataObj);

                quoteDB.SubmitChanges(); 

            }

            catch (Exception e)

            {

                MessageBox.Show(“Quote not selected as favroite”);                          

            }

        } 

        public bool checkQuote(int id)

        {

            foreach (var item in quoteDB.favoriteTable)

            {

                if (item.quoteID==id)

                {

                    return true;                  

                }               

            }           

                return false;

        }

 

        public void deletefav(int id)

        {

            try

            {

                foreach (favorite quotes in quoteDB.favoriteTable)

                {

                    if (id == quotes.quoteID)

                    {

                        quoteDB.favoriteTable.DeleteOnSubmit(quotes);

                        quoteDB.SubmitChanges(); 

                    }

                }

            }

            catch

            {

            }

        }

 

        public List<favorite> GetfavList()

        {

            List<favorite> favQuotes = new List<favorite>();

            foreach (favorite item1 in quoteDB.favoriteTable)

            {

                favQuotes.Add(item1);

            }

 

            return favQuotes;

 

        }

    }

 

Now we are left using these functions via datahelper object.

dataHelper _dataHelper = new dataHelper();

 

//storing into DB

private void favorite(object sender, EventArgs e)

        {

            if (_dataHelper.checkQuote(i))

            {

            }

            else

            {

 

                favorite fav = new favorite

                {

                    quoteID = i

                    //title=Class1.myQuotes[i,0]

                    //text=Class1.myQuotes[i,1]

 

                };

 

                _dataHelper.Addfav(fav);

            }

 

 

        }

 

// retrieving List of quotes from DB
List<favorite> favList1 = new List<favorite>();

favList1 = _dataHelper.GetfavList();

//deleting from DB

_dataHelper.deletefav(a[j]);

 

 

Further you can make any kind of queries as needed and more complex DataBase as making relation between two tables and all.

Well, hope you enjoy the sample for creating a basic single Table DataBase in windows phone for Quotes app for storing fav. Quotes and displaying them and deleting them.

 

Happy coding!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s