Android developer check if database exists Videos

I did not find the .XPO file in my Visual Studio 2012 Ultimate... I want to know what I'm doing wrong. Thanks

Okay So... Alright...hi Good morning everybody.... today we have this seminar in here. Today we gonna see some of the technique like webSQL techniquae for local database, and in this next 1 hour I will be taking you through one of technique “webSQL database” and how we can use this in your browser to store your data at the local end of the client.and then I will be also taking you through the SQLite, the basic for web SQL database technique and then we will be having our demo application and then we will be doing some synchronization part from your client end to the server end. So till now we have created a demo application and we have taken that offline and we have also seen some of the techniques like.....Okay so till now we have made our demo application part 1 and we put that offline and in part 2 we saw saw some techniques like index db,local storage and session storage, which can be used to store data at the local end. And we have one of techniques. So this is web SQL technique and this is all the previous 3 technique which we use like web SQL, sorry, session storage, local storage and index db so they were not relational. Actually we were using some key value pair and storing data here. So it wasn't relational model but this web SQL model is relational model and you are finding relational type of database. We have already worked with maybe MySQL, SQL or oracle. So this is based on that. Today we will start with SQLite, then we will see how web SQL databases and synchronization and demo application. So starting with SQLite, basically this is your relational database management. Its not just dedicated software that is running. It can be understood like, We use JQuery but its not a software, you are using some JQuery library, we will use those in your project and this is not a piece of software running there. We are using those JQueries and using those. So web SQL lite is somewhat similar to JQuery here we use some library returning C. use those library and unlike SQL server which is a piece of software which is running in the background and processing your queries. This is not a software, this is a library. This is like, ACID compliant, all you know that is ACID. Now basically web SQL, so web SQL isn't actually a part of HTML 5 specification but its a separate specification. This introduces a set of APIs. SQLite is implemented in C. Actually what web SQL is doing, using some sort of JavaScript or JQuery library. Its kind of manipulating those C library functions and which is allowing you to interact with SQLite. So web SQL is nothing but kind of library which allows interaction with SQLite libraries. So there are some core methods using in web SQL. Basically there are 3 methods used in web SQL. There is opendatabase(), you can use this method to create a database or connect to an existing database. Then you have transaction. You do everything inside transaction. Then you have execute SQL. So execute SQL is like whenever you need to execute some query, then you need this function execute SQL. And if you look at the structure for execute SQL, its more like, we are going to create a transaction over db and then on that transaction object. Here is like open database. You gonna.. it will show how you gonna open your database. So open database function is more like.. something like this. It will take 5 parameter. It will take your database name, version of database, so when you need to make any changes in database you need to change the version. Like you were doing in the ndex database and then whenever there's a version change its gonna call a function. You can just create or modify whatever you wanna do. Now test database, this is nothing, this is description. Then we are gonna provide size. Here I am using 2KB size is allotted to this database. So first parameter is database name,then version, description and size and then you are creating call back. So last 2 parameter, they will be only used when the database is being created for the first time. So this open database function is used for both purpose. If you had to create database or you needed to open some database. When you are creating for the first time, then the last 2 parameter, like size of database and creation call back will be fired. Otherwise we add database object. Now you have transaction and execute SQL. Transaction.. everything that we do inside SQLite has to be done inside this transaction object and inside transaction we can use this execute SQL. So I have my database object here which I just created, var database. Inside this db I am using my transaction and on the call back I am passing this transaction object here and I am using this transaction object as I am executing some sort of query. So this is the function, execute SQL and everyone succeed then the transaction will succeed or else it will roll back. Question:Is this the transaction used here is same as that of transaction we use in SQL. Response: Yeah, I am just coming to that part. We will be seeing it in demo. Then we have synchronization. Now you have your offline application, you have offline database and now when the server actually comes you wanna do some sort of synchronization with your local copy of database with server copy or vice versa. Actually there is no predefined API for synchronization in hTML 5. So you need to write JavaScript or you need to use some kind of services to implement this functionality. This is navigator.online this is the property of a browser which you can use to see if your browser is online or not. So whenever you have internet connectivity so it returns true or else false. And you can periodically check this thing. So if your browser is online you can do some stuff with server or else you have to do it in local end. Generally we use services and pass data as json or XML. So this is all about it. Lets see the implementation of this. Question:In open database function. It was said that it uses five parameters, so what is the last parameters, and when its gonna be used? Response: Ok, just coming to this. I will just show you. Okay? Actually that’s a callback, thats not a parameter, that's a callback will be called whenever you are creating a. database for first time, I mean the open database function is used to connect to database and at the same time its also used to create the database. So if you already have a database then that callback will not fire, if you don't have database then that call back will fire. Question:What we have inside that call back? Response: Inside that call back you can do. if you are trying to get a database object, If you don't have it then you would like to create that database object. So that call back will create a database object and you will return the database object here. Question:What if we already have that database?Will still the call back gets fired? Response: It will just return the object of that database. Question:well we be able to access the database of different clients? Response: Actually this is the client side implementation so every client will be adding its own copy of database. Question:If we have same database for different sites, lets say we have 10 different sites with same database name? will it cause any conflict? Response: The space which is allotted to a particular site is allocated to that site only, so if you have 10 different sites, then all will add their own database so it won't conflict with each other. So we have a simple page here. We will use web SQL instead of index database. So if you just open that console here and its web SQL. So I have a database in employee and for this dbemployee I have a table and if you see its relation kind of thing. So you have a table whose format is not like what you have in index database. So here in index database you have an object form. So here was a key and for that there was value. But in web SQL you have table like structure so its divided into columns and rows. Lets see how we are achieving the functionality. Lets just start with the code. So its visible I believe. So this part is nothing but for just showing notifications. Lets just start here. So if you see this query here, its more like SQL query. So nothing to explain here, so lets start with structure of table, I have some columns here starting with ID, making it primary key. Employee ID, name, designation and salary. And the reason why I am not making my employee ID as primary key is because in your server maybe you have employee ID and … so I have just another ID. This at my local end will server the purpose of primary key. So that the conflict doesn't occur. Starting with, I have 2 variables here. So it will be only my database object which I will use in entire file. Then I have employee data. Here I will hold the data of each and every employee that I have in database and then I will passing this array to server for synchronization purpose. Now starting here, before doing any operation I have to see if my browser supports this web SQL or not. So starting with storage object and then I am going to check my open database property of my browser. So if it's there then I am just gonna check by storage to that thing. Otherwise I am gonna show you that your browser is not supporting webSQL. For the time being its only supported in Chrome, Safari, and Opera. Now SQL database, here is where I will be creating and opening my database. I will start with this function. Before creating my database I have to see if actually my browser is supporting or not. So I will check for SQL storage and if I don't have that stuff I will check it from here. Or I will try to open database here. So right now I am trying to open a database dbemployee and this is my version 1 and this is the just for information part. This is the size, 10kb. I am not using any callback here. If I have my database object here, I can start with transaction, passing transaction object and creating table. So using this transaction object I am trying to execute my SQL and if you see the way SQL is being executed so this is basically divided into 4 parts. So it will take 4 parameters. So my create SQL, which is declared here. If I am going to create a table if it doesn't exist, then there is empty array. Actually you can use parameterized queries. I will show in a while, while deleting or updating or you can pass parameters. So if you wanna use parameterized query, you can pass parameters here. Third parameter is your success callback. In case this SQL executes successfully then this function will get executed otherwise if it gives some error this error part will get executed. So this is the basic way how you can execute web SQL. So you have to pass 4 parameters starting with query, maybe you wanna do insertion, updation, deletion whatever you wanna do and pass query, then you need to use parameters. If you are using parameterized query, otherwise you can just specify the parameters here also. This is your success call back which will execute once everything gets okay with this query. If some error occurs, then this error call back will execute. For insertion purpose, I will start from here. Again the database object will be global variable. If my browser is not supporting this web SQL kind of storage, I won't be doing any stuff. Otherwise just grabbing the elements and values. So this is not … so I am just getting the name, designation, ID, salary. Question:what is this frmWebSQL? Response: Actually its my ID for this form. This is my ID. So getting this stuff. Getting all the values and if do have the values then this part is here. So again using transaction on this database, this is my transaction object. Again if you see this query here, its more of like insert into this thing, table and then these are the columns. And then we start our values. I am using parameterized query here so just question mark. Its going to match these values to those in the array. Its going to replace first question mark with the first value in array for ID. Then for name, designation, salary this will replace question marks here. Then again the success call back, how many rows are affected, it will return me the rows. So this is transaction object and this is returning rows which are affected. Again this is my error call back. So this is the general way how you will create or execute or SQL. So you have 4 statement starting with queries, then if have any parameters, then success call back and then your error call back. So once I have this I can edit in the C. here is how its working. Question:So you are doing everything that can be done at the server Response: everything can be done at the client end, this is one of the technique. Question: So isn't it duplication of the code? Response: No, no, its not duplicacy, see, so you are creating an offline application, you wanna do everything that a person can do while online. Now you are on offline mode, you wanna do some insertion deletion and all that you can do online. Now when you are offline, this is offline substitute. Question:okay so we are creating the same db structure in the offline mode and implementing all the functionalities. Response: you need to create the same structure so.. which is on the server we need to create exact copy of that on your client end and then you can do all the operations which you can do in the online mode as offline mode. Ok, so getting all the stuff here. Actually I need to put my debugger here. So it will return me the SQL results, this is insert ID and one row was affected. Also if you see in the resources, it will get my .. here. So for a guy who have worked on relational database. So easy for him to migrate to web SQL kind of storage instead of index database. So in case of fetch I will be using employee id. So you will get employee ID on the basis of which you will get the data and then again your query here and this time I am using non parameterized query. I will just specify the ID in the query itself. So this is the query and I am not using any parameter's here. If this succeeds, then we gonna populate the form with the object which was returned to me So this way we can implement all these functionality. If you wanna delete, then same sql kind of query. Delete from table where Id equal to some Id. And same for Update also. This is just pretty same. So this is all about CRUD operation which you can perform. Now once we are done with CRUD operation, we will see the synchronization part. I am not going to do the whole stuff like closing my connection because there are some guys on GTM. So I will be just trying to do all these on button click and we can do this by catching the event also. Question:Are all these the inbuilt functions of the SQL Lite. Response: actually SQLite is not your... its just a you know, you go to SQLite this is basically a library written in C. So you need to implement all those. Is this all the job a relational database does. You can just use this in standalone application and use this sqlLite functions to do all the operations that you do in a relational DB. Question:How you use synchronization? Response: I am just showing you this part. So for the synchronization part, so I have a kind of web service here and this data operation I will just pass my employee data as a JSON string. Just simple .. service here. So after passing my entire data as json string, and then I have a class in here, employee class and with this deserializing the data. Then once I have the object you can do the insertion of json, so this is pretty easy to do insertion operation. Now we are going to do this. I am doing this on button click. You can do this also by .. the event. Which is if Windows is online or offline. First I am just going to retrieve from my local database. For retrieving same SQL query is there, select all from tEmployee, this is the data, this is the query. Once you get the data we will call a create json from that data. So again creating transaction on the database and this passing the queries. On success creating the json string from that data. So before starting I am just entering my data, I have some previous data so we get the data and push into the array. I have an array of employee data. I am just showing you all the stuff. Once I have employee data, I am just in my doSynchronization() if I have my employee data then just creating json string from that data. And so this is my web service, synchronization. And I am passing the json data. This is my success callback. So if I am able to successfully pass the employee data then this functionality will execute. Question:How we gonna distinguish data which we need to update on the server? Response: actually here you need to implement some business logic. When we are online.. Question: Where we gonna distinguish in Web Service or in JavaScript? Response: You know this is how you gonna implement this. What you can do, when you are getting online you can just do a synchronization. If data is synchronized you can just truncate all data from your local database and then get the fresh copy. If there is some updation, what you can actually do, actually Joginder is working on this and what actually they are doing. When the first time they are connecting to the server, they are getting the data. If there is any modification they have created a different table or data structure. So when there is some modification they are getting the data from their original database and putting that stuff in different database. So whenever they are again connecting to the server so if they have some data in the database which is modified database, they are just synchronizing that with the server. And one synchronization is over, they are again deleting their modified data. So they have the fresh copy of data which is there on server and client end. If any modification is there they are taking some data from that database to some different data and they synchronize this data with server. Once synchronization is over they are again truncating the stuff. So when you have some stuff and you know that you have to do synchronization they you are synchronizing this stuff. So this is somewhat there is business logic. Question:Is there any Synchronization API? Response: There is no synchronization API, as I already said. So you need to create your business logic here. You can't create your database and do that stuff. Ok so first I am going to try from my database. Actually its a sync process so its passing down. for the time being my arrays are empty and my result contains data here,so 3 records are there. I will create my arra y. So now in my employee data I have data in here. I am just passing it to the server, So I have my JSON string, and after serialization I have list of employees. so insertion part is quite easy so you can do this stuff. Again for synchronization purpose I can again send my employee data back. so you can get the data from database from the server. For the time being I am returing the same string. So this is how you can send the data to the server and get the data from the server. So this was all about the synchronization part. Question:How long SQLite can reside the data. Response: How long SQLite can .. data. So as long as you are not gonna delete your local database copy it will retain the data. Question:So if I close my browser it will have the data. Response: It will have the data. Only the session storage will lose once you close the browser or all 3 other techniques like local database,Indexed db and websql they are gonna hold the data as long as you don't delete data from your local end. So this was it, if you have any questions. Question:Is it possible to update the data in multiple tables using transaction objects? Response: Using transaction objects? Yes its possible. Question: okay so lets say in one tab I am udating the database and in other tab I am selecting the database from the same table, So will it allow me to do this? Response:Okay so in one tab you are updating and using other tab you are selecting... Question:there is NoLock in SQL, so I can do in SQL using NoLock.Is there anything like NoLock in SQLite. Response: I have to check if SQLite supports NoLock or not. If SQLite supports NoLock then you have the lock functionality also. Actually web SQL is using all the wrapper classes of SQLite. So if SQLite supports lock mechanism, then you must have lock in this.