Tuesday, August 30, 2011

Using SQL Server Compact 4.0 with Entity Framework.

In previous section we have seen, how we can use a use SQL Server Compact 4.0 with visual studio. In this section we would see how to use this with entity framework. We would use sample database provided with the SQL Server Compact 4.0 , i.e. Northwind.sdf database for demonstrating the ideas. Created a sample asp.net project for any purpose program, For how think that we are going to build an application based on Northwind database. Go ahead and add the file in App_Data folder.

image

Ado.net Entity Data Model

In this section we will see how we can add the Data model, frankly speaking its pretty simple but still explaining it. Bellow I have put down the steps.

  1. Add Ado.net Entity Data Model by right click on the project then select Add New Item, from the data page on item category select “ADO.NET Entity Data Model”. Click Add This will bring up Entity Data Model wizard.
  2. Select “Generate from Database” In the first page and click Next.
  3. In “Choose your data connection” page all connection should be prepopulated since you added a database and opened in “Data Connections”. If not Manually do it by clicking “New Connection”.
  4. In “Choose your database object” page select the all objects. and click finish.
  5. Your Data Model will be Generated.

image

Sample Codes

Bellow I have put down few simple code, just to demonstrate few sample code examples. We have added a data manager class, and added few simple methods to manipulate the data.

public class OrderData
{
public List<Order> GetAllOrder()
{
NorthwindEntities entities = new NorthwindEntities();
List<Order> orders = entities.Orders.ToList();
return orders;
}

public void AddOrder(Order order)
{
NorthwindEntities entities = new NorthwindEntities();
entities.AddToOrders(order);
entities.SaveChanges();
entities.AcceptAllChanges();
}

public Order GetOrderById(int orderId)
{
NorthwindEntities entities = new NorthwindEntities();
Order order = entities.Orders.ToList().Where(p => p.Order_ID == orderId).FirstOrDefault();
return order;
}

public void UpdateOrder(Order order)
{
NorthwindEntities entities = new NorthwindEntities();
Order updatingorder = entities.Orders.ToList().Where(p => p.Order_ID == order.Order_ID).FirstOrDefault();
if (updatingorder != null)
{
updatingorder.Order_Date = order.Order_Date;
//other changes
}
entities.SaveChanges();
}
}

Section endings


In this section we have seen a simple code and demonstration how we can use Entity Framework with SQL Server Compact 4.0. In next section we will see how we can use SQL Server Compact 4.0 with linq to SQL. Best of luck and happy programming.

Sunday, August 28, 2011

SharePoint 2010 WSS 3.0 is now SharePoint Foundation 2010

SharePoint 2010 has rich leaning support for the developers, by learning resource I mean video resource particularly, I my self is very excited to explorer the whole set of video to learn SharePoint, quickly. At least I will have a clear idea about the product and how to develop custom solution based on SharePoint.

Note that we don’t have WSS anymore it now SharePoint Foundation 2010. Bellow I have share a link from the Microsoft SharePoint developer center.

Get Started Developing on SharePoint 2010

http://msdn.microsoft.com/en-us/sharepoint/ee513147.aspx

image

Every thing is explain via a rich video tutorials,

“nopCommerce”, an open source e-commerce solution, Build your store’s site in minutes.

I was working on a ecommerce site for reliant-rehab when I first get to know nopCommerce, its a true open source ecommerce site, build using asp.net. Pretty handy and easy to customizable, I am not going to explain each and every detail, the installations steps are pretty simple and you can download the from bellow site.

http://www.nopcommerce.com

image

You can customize the theme and controls and bend it as you like it. There is a documentation for nopCommerce and users can buy it paying a small about of money 20$. This part is pretty strange, if its true open source ecommerce site the documentation should be also free Open-mouthed smile. Any way they have there own policy, but as developers if we explore a little bit its easy to customize the site. Bellow I have added a screen shot for one of my work.

image

http://www.dbcigarcorner.com

Start building awesome web applications, best of luck happy programming. 

Thursday, August 25, 2011

Design SQL Server Compact Database with SQL Server management studio.

In previous section we have seen how we can use SQL Server Compact 4.0 Database. In this section we would see how we can create and edit and SQL Server Compact Database with SQL Server management studio. Lets start with SQL Server management studio, By default when you open the Management studio you would have two option for Server type and the options are follows

  • Database Engine
  • SQL Server Compact

We would select SQL Server Compact as our server type and click on database file combo, and it will bring up browse or create new option for the user. Select what ever suits your condition. For explanation I will choose create new for the time being.

image

After selecting the option you would get a dialog like the bellow screen shot. It is the database creation wizard. I am sure the options are pretty understandable to all. Browse and select the location where you wanted to create the database, if you have an existing database you can choose to override the existing database. Keep the destination Database Properties as follows. Next step is to provide a password for your database. you can choose to keep the password empty, in that case you would see a warning before created. Please choose the Encryption type and select ok. 

image

That’s it you would see a your database option created in object explorer and you are good to go for going ahead and design your database.

image

Let’s move ahead with the database table  creation wizard. The table creating wizard is pretty intuitive and a develop can easily understand. Right click on table node on object explorer and select new table option. Give a suitable name for your database table name, then fill the table columns, note that almost all the renowned data types are available, You can set you tables primary key from Primary key combo. And if you want to set the primary key as identity column just modify the column detail at bottom of the wizard and set Identity to True. Click ok to finish creating your table.

image

Next step is to create relationship among the tables. for this you have to right click on table note and select table properties. This will bring up your table property window. There are four sections of this window, we would concentrate on Add Relations for now. Click on add relation page, next fill primary key table and columns that you want to map, next give a name to the relation and click add columns, this will add relation columns to relation section. click ok to finish and close the dialog.

image

Now verify that the relation actually added by opening table properties for a table again and visit the Manage relation page on Table properties window. In Manage Relations page you will find your table relation ship in relation name combo. if you wish to modify or delete the relation, you can do it from this window.

image

Note that while creating database with SQL Server management studio you will eventually create a SQL Server Compact 3.5 database, we would talk about SQL Server Compact 4.0 in near future. Till then happy programming and best of luck.

Wednesday, August 24, 2011

Using SQL Server Compact 4.0 Local database with visual studio 2010

If we consider using Embedded database and the environment is Microsoft development platform, there are couple of choices.

  • SQL Server compact 4.0
  • SQLite 3.0
  • Firebird
  • Vista DB

There could be more options for embedded database but considering embedded database those four is most popular in the web. 

I have posted few blog post on how to use SQLite with visual studio to gain good amount of development ease. In this post we are going to see how to use SQL Server Compact 4.0 with Visual studio 2010. SQL Server compact 4.0 is a file based database and has native visual studio 2010 support. It’s a simple way of data store and off Couse pretty handy in few development scenario.

Adding the database is pretty simple. Right click on the project folder and select add new item, from the add new item wizard select Data category, and you would find the item template named “SQL Server Compact 4.0 Local Database”, which is your target template. Now select the item and give a suitable name. Now click add.

image

If you are trying to add the database file in a asp.net project you would be presented with a message box dialog like bellow. And you should select “Yes”. By Asp.net convention any data related stuff should go to App_Data Folder which is a special folder for asp.net project.

image

Now after that you should find a file with the same name of your given database name in app_data folder and as well as a data connection would be added to the server explorer. That’s it you are good to go.

image

Now then if we try to use the same database with WPF Project you would see the warning like Bellow screen shot. It simply says that you can not use this version of database with dataset designer. I mean if you thinking if using Type Dataset and data adapter to fill your data, you shouldn’t probably use this. Visual studio 2010 dataset designer does not have native support for this.

 

image

In this case you should use local Database template rather that version 4.0. And after selecting the local database template click add, a data source configuration wizard will be lunched the finish the steps of the wizard you will get the data file added in you project.

image

In this post we have see how we can add the SQL Server Compact4.0 Local Database in visual studio 2010. In my next post we would see how we can design a effective database quickly using visual studio or perhaps SQL Server management studio.

Wednesday, August 3, 2011

Using LINQ to SQL with SQLite database

In my previous short article we have discussed about how to use Ado.net Entity Framework with SQLite database. And we have witnessed that its almost as like as SQL server in context of ease and usability when you have the right provider.

Today we are going to see how we can use LINQ to SQL with SQLite database. We always looks for ways that would make our life easy and most important no more re-inventing the wheel. By default you can not drag and drop tables from server explorer or from data connections. Its because we don’t have any built in provider that support LINQ and if you try to do so you would certainly encounter with the following error.

AddRelation

Now, I know what am I going to show you wont please you but it certainly serve our purpose to use LINQ with SQLite. There is no magic or rocket engineering behind this, we have a DBLinq open source distribution that come with various sort of database support. For this particular case lets stick to SQLite. The binaries can be found here,  http://code.google.com/p/dblinq2007/, please download latest binaries from this site.

Now its time to generate the OurDatabase.dbml file. the steps are not that much complex, Located the DBLinq package that you downloaded from Google code, then unzip in a suitable folder location where you can access it easily.

Note that you need to copy the System.Data.SQLite dll in the DBLinq directory. 

image

Step 1: Create the dbml file

DbMetal /provider:Sqlite /conn "Data Source=File.db3" /dbml:File.dbml

Step 2: Create the code file for the dbml file

DbMetal /code:File.cs File.dbml

Copy those files to the files. That’s it you are good to go.


Generated DBML


Make sure that every thing compiles okay by hitting build. Bellow I have put done a simple code snippet to demonstrate how to use this dbml to retrieve data.

 protected void Page_Load(object sender, EventArgs e)
{
string ConStr = "Data Source=" + HttpContext.Current.Server.MapPath("App_Data\\File.s3db") + ";Version=3;";
var connection = new SQLiteConnection(
ConStr
);
connection.Open();
var db = new Main(connection,new SqliteVendor());
var users = db.User;
var item = users.Take(10).ToList();
}



I hope this above code is self explanatory. Best of luck and happy coding.