About Microsoft SQL Server Data Tools – SSDT

SSDT is an attempt to bring all the SQL server design, development, testing and manipulation jobs which are usually done by developers, especially database developers under one umbrella – which is Visual Studio.

SQL Server Data Tools were code-named ‘Juneau” which was introduced with SQL Server code name “Denali”, which later became SQL Server 2012.

SQL Server Data Tools is FREE!!!

You will be able to use the same features you were enjoying while .NET language programming in database related development as well, like: intellisense, debugging, code navigation etc.

Even though I sounded like SSDT = BIDS + SSMS + various tools, thinking about a replacement for SSMS is not there. SSMS continues to be a powerful tool for both developers and administrators. I am in love with SSMS and I see there are many things SSMS only can do, or can do more user-friendly than SSDT.

SSDT makes below template types available:

  1. Analysis Services
  2. Integration Services
  3. Reporting Services

image

Supported SQL Versions:

  • All the versions above SQL Server 2005 and SQL Database (formerly SQL Azure). However MSDN uses the wording “any SQL Server platform”. Find the below screenshot of Visual Studio solution properties list.

image

Installation:

  1. Visual Studio 2013 (All editions including Express) – You are lucky as it SSD Tools are already available as an update. But if you do not find it, you can either manage it via Extensions and Update under Tools menu or download the SSDT-BI for Visual Studio 2013.
  2. Visual Studio 2012 – Download SSDT for Visual Studio 2012 and SSDT-BI for Visual Studio 2012 also
  3. Visual Studio 2010 – Forget about this. Microsoft is not bothered about this version anymore. Database tools features were available in this version but this was ported to 2012 version as well.

Features which I felt appealing:

  1. Declarative Database Development Model – Lets developer focus on the end result rather than the scripting pains
  2. Multi version targeting
  3. SQL Database (Azure) support
  4. Versioning and source control of objects (as files)
  5. Powerful SQL Server Object Explorer (which will remind you SQL Server Management Studio)
  6. Edit table data, compare schemas execute queries
  7. Project based development vs connected database development
  8. Incremental schema deployment
  9. Schema and Data comparison
  10. Custom static code analysis extensibility
  11. Project oriented offline/disconnected database development
  12. Schema and data synchronization
  13. SQL Server unit tests
  14. Snapshots saving
  15. Editor’s ability to highlight errors (similar to .NET development)

Reference URLs:

  1. http://msdn.microsoft.com/en-in/data/tools.aspx
  2. http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx
  3. http://msdn.microsoft.com/en-us/library/hh272702(v=vs.103).aspx

For me, there are still more features and bottlenecks to be experimented. I will write more later.

 

BigData – learn now! – from Pinal Dave


 

BigData – learn now! – from Pinal Dave


 

BigData – learn now! – from Pinal Dave


 

MongoDB C# – for beginners

Assuming you have MongoDB installed and configured already.

You have to download MongoDB C#/.NET Driver also – http://docs.mongodb.org/ecosystem/drivers/csharp/

You will get two dlls which you have to ‘add reference’ to your project.

  • MongoDB.Bson.dll
  • MongoDB.Driver.dll

Add the namespaces to your code first:

using MongoDB.Bson;
using MongoDB.Driver;

Table Structure

Define “table” structure. (I have used ‘table’ term here just to make a similarity with SQL tables. MongoDB as you know is a NoSQL database and the data stored is unstructured.

public class ADVT
{
    [BsonId]
    public ObjectId _Id { get; set; }

    public string advt { get; set; }
    public DateTime date { get; set; }
}

Note the [BsonId] and ObjectId. This is the _id reference in your physical database. You have to use namespace MongoDB.Bson.Serialization.Attributes to make this work.

Physical database looks like this if you use any management tool (I have used free version of MongoVUE tool)

image

Connecting to server, selecting database, selecting table (sorry, I mean Collection)

MongoClient mc = new MongoClient();
MongoServer ms = mc.GetServer();

MongoDatabase db = ms.GetDatabase(“myforum”);
MongoCollection mcollection = db.GetCollection(“posts”);

Insert/Write record to database

        ADVT advtrecord = new ADVT() {
            advt = “test advertizement”,
            date = DateTime.Now
        };

        mcollection.Insert(advtrecord);

Read records from database

MongoCursor<ADVT> cursor = mcollection.FindAllAs<ADVT>();

dataGridView1.DataSource = cursor.ToList();

 

A C I D properties of a database

ACID is a set of properties that guarantee that database transactions are processed reliably.

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Atomicity – The defined block of operations should left unchanged if any one operation fails. Transaction should be rolled back to its previous state.

Consistency – Any data written to the database in a transaction must be valid according to constraints, cascades, triggers etc.

Isolation – Each transaction must execute in total isolation. There should not be any dependency between two concurrent transactions.

Durability – Once the transaction is committed, it will remain permanent. Commited transactions will not be rolled back or cancelled in case of disasters like exceptions, power failure, crash etc.

Wiki page.

 

A C I D properties of a database

ACID is a set of properties that guarantee that database transactions are processed reliably.

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Atomicity – The defined block of operations should left unchanged if any one operation fails. Transaction should be rolled back to its previous state.

Consistency – Any data written to the database in a transaction must be valid according to constraints, cascades, triggers etc.

Isolation – Each transaction must execute in total isolation. There should not be any dependency between two concurrent transactions.

Durability – Once the transaction is committed, it will remain permanent. Commited transactions will not be rolled back or cancelled in case of disasters like exceptions, power failure, crash etc.

Wiki page.