Diwakar's Blog

Conditional Creation of Tables and Columns (The Old Way)

If your application has numerous deployment versions and you want your script to adapt and be able to either install in a fresh database or into a prior version of the schema, then you have lots of work to do.  First you create the latest version of the table, if it does not exist.  If it does already exist, then you check for the missing columns added since your initial deployment and add them as needed.  You may also need to drop columns that have been removed over time.  Here is an example of an old script where I have done this.
Please excuse the name prefixes. I used to prefix table and stored procedure names with a prefix indicating a kind of namespace. I’ve since changed over to using different schemas. This script also checks for an existing table with sysobjects since this was written for SQL Server 2000 and then updated to use SQL server 2005. It has since been replaced, however sysobjects still works for backwards compatibility. I discourage you from using this in any current or future projects.

Creating a Database Project in Visual Studio 2012 (The New Way)

You can reverse engineer a database project from an existing database, or create a new project from scratch.  This article will focus on how to create one from scratch.  Open Visual Studio and either create a new solution or open an existing one to which this new database should be a part of.

Adding the Project to a Solution

Select the Other Languages, SQL Server template group on the left.  Then choose the SQL Server Database Project type.  Enter a project name and press OK.  I usually pick a project name matching the class library that will contain the business layer or data layer that will interact with this database, and then append Database to the end of that name.  It may make more sense to also put SQL in the name; just in case you use another type of database in the future.

Update the Project Properties

You should check out the project properties and see what options are available.  On the main Project Settings tab page, there is a ‘Database Settings’ button that lets you specify any metadata to be applied to the database as a whole.  The defaults have worked for me, but if you need a specific database collation, file group, or need certain flags like ANSI_PADDING then check that out.
I tend to override the default output type, by checking the ‘Create script (.sql file)” option as shown here.  I do not change the default schema from ‘dbo’; even knowing that below I want most of my tables, functions, and procedures in a specific schema.

Import a Database (Optional)

If you already have a database to start with; you can import from the current schema.  Then you can follow the other sections below for making changes and publishing those changes.  To import a schema, just right click on the project node in solution explorer and select menu “Import” -> “Database”.  Then configure the database connection and pick the options for things you want to import.

I prefer the Folder structure of “Schema\Object Type”.  This is what I will assume for the following sections; It is also the default selection for the Import dialog.   I don’t normally change any of the import setting defaults.  If you need permissions of any specific database settings from your existing database then select those import options.  You can modify the database settings in the project properties as noted in the previous section.

Creating a Schema

Before I create any tables, I usually define a schema in which I will place all my database objects for this project.  This allows you to have simpler names for your tables, since the schema scopes them similar to a namespace in .Net code.
It may not really matter where you put the schema file, however I follow the convention used when reverse engineering a database.  Create a folder in the database project of the same name that you will name your schema.  Then add the schema file to that folder using that same name.

This seems so much better to me than the old way above where I showed a prefix on a table name to facilitate grouping of related tables.  Having different schemas for loosely coupled or unrelated sets of tables also helps me think of ways tables could be segmented into different database shards.  You can either go the route of one database project per schema, or one database project for all your schemas.  I usually make that decision based on how I want to deploy the database.  One database project equals a deployment to one database instance.

Add a Table

When reverse engineering a database into a database project it creates folders under the schema folder for Tables, Functions, and Stored Procedures.  I follow the same convention when creating these items manually.  I just create a table by right clicking on the Tables folder under the schema and selecting the ‘Add Table’ menu item.

Table Designer Overview

The table designer gives you options as to how you want to design your table.  It has a design pane which has a columns grid and keys overview with right click support for adding new keys.  It also has a raw text pane with the sql required to create the table as defined in the design pane.  As you type in the raw text pane the changes appear in the design pane; and as you change details on the design pane it updates the raw text pane.  On my 5 year old laptop, I have not experienced any performance issues either to open the file or in having updates sync between the panes.

As you use this designer all the keys and constraints are added in the table definition sql file.

Deploying the Database – Publish

Publishing the database changes is very simple.  Just right click on the database project in solution explorer, and select “Publish”.  A dialog appears for connection details.

Assuming you followed the steps above during project setup, this will just generate a script file.  I prefer script files so that I have them ready for promotion to the next environment.  If your project properties default to do an automatic publish instead of generating a script, you can override this by just pressing the ‘Generate Script’ button.

Deploying the Database – Schema Compare

You can also create a deployment script with the schema compare command.  This is also available as a right click menu item on the database project node in solution explorer.  This gives you more flexibility.  You pick the database target to compare the database project to and it tells you what is changed.  Then you can choose which items are included in the generated script.  If you leave all changes selected, then this generates the same script as if you followed the ‘Publish’ option in the previous section.
To begin a comparison to a database, pick in the ‘Select Target’ dropdown to select a database connection.  In the screenshot above that dropdown has my connection name “.\SQLExpress.CandorMail”.  Then press the Compare button (Or use shortcut Shift-Alt-C) to see the changes.  If you have changes, then press the “generate script button” (Or use shortcut Shift-Alt-G).
If you actually want to deploy these changes to the target database now, then press the ‘Update Target” button (next to generate script).  This has no shortcut, thankfully.  I personally wouldn’t want a possible accidental key press of the wrong combination to publish a database change to a production database.

Build Errors

One of the great advantages of a database project is the continuous ‘compilation’ of the database project objects.  If you have invalid definitions or reference other objects that do not exist, then you will see compilation errors.  This is a great development enhancement over parsing scripts and manually running them against a local database instance on a regular basis.
As you type you will see problem areas highlighted in the raw text pane as shown here.  If you hover over it, you’ll see the error message.

Also if you view the errors list the detail will be shown.  If you double click on the error it will navigate you to the table designer where the error is located.

Database References

You may have multiple database projects in your solution that have some level of dependency.  Maybe one of the projects is a set of customizations to a base database product defined in another project.  Or maybe you just want each schema defined in a separate project.
Without a reference the project that depends on external database objects will not compile (generate a script) if it cannot find the referenced database object.  To fix this you can create a reference to the other database project.  Just right click on the ‘references’ node of the database project and select “Add Database Reference”.  Then you can pick another database project in the solution, or a system database, or a dacpac file exported from another database.

Requirements

This works on my machine.  I didn’t research if it works with less features installed than I have, but it probably does.  I have the following Microsoft development tools installed.

1. Visual Studio 2012 Professional with update 1 (Full Install , lots of ‘features’.  Not all are listed below)
   • Now includes: SQL Server Data Tools 11.1.20627.00 (separate install for VS 2010)
   • Now includes: Microsoft SQL Server Data Tools (yes, this is different from the previous item)
2. SQL Server 2012 Express with all features including SQL Server Management Studio (ENU\x64\SQLEXPRWT_x64_ENU.exe, 669.9 MB).  Obviously pick the 32 bit version instead if your OS is 32 bit.  http://www.microsoft.com/en-us/download/details.aspx?id=29062

This topic describes how to create a Transact-SQL stored procedure by using Object Explorer in SQL Server Management Studio and provides an example that creates a simple stored procedure in the AdventureWorks database.
To create a stored procedure


In Object Explorer, connect to an instance of Database Engine and then expand that instance.


Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.


Right-click Stored Procedures, and then click New Stored Procedure.


On the Query menu, click Specify Values for Template Parameters.


In the Specify Values for Template Parameters dialog box, the Value column contains suggested values for the parameters. Accept the values or replace them with new values, and then click OK.


In the query editor, replace the SELECT statement with the statements for your procedure.


To test the syntax, on the Query menu, click Parse.


To create the stored procedure, on the Query menu, click Execute.


To save the script, on the File menu, click Save. Accept the file name or replace it with a new name, and then click Save.
To create a stored procedure example


In Object Explorer, connect to an instance of Database Engine and then expand that instance.


Expand Databases, expand the AdventureWorks database, and then expand Programmability.


Right-click Stored Procedures, and then click New Stored Procedure.


On the Query menu, click Specify Values for Template Parameters.


In the Specify Values for Template Parameters dialog box, enter the following values for the parameters shown.
Parameter
Value

Author
Your name

Create Date
Today's date

Description
Returns employee data.

Procedure_name
HumanResources.uspGetEmployees

@Param1
@LastName

@Datatype_For_Param1
nvarchar(50)

Default_Value_For_Param1
NULL

@Param2
@FirstName

@Datatype_For_Param2
nvarchar(50)

Default_Value_For_Param2
NULL



Click OK.


In the query editor, replace the SELECT statement with the following statement:
SELECT FirstName, LastName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
WHERE FirstName = @FirstName AND LastName = @LastName;


To test the syntax, on the Query menu, click Parse. If an error message is returned, compare the statements with the information above and correct as needed.


To create the stored procedure, on the Query menu, click Execute.


To save the script, on the File menu, click Save. Enter a new file name, and then click Save.


To run the stored procedure, on the toolbar, click New Query.


In the query window, enter the following statements:
USE AdventureWorks;
GO
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Diane', @LastName = N'Margheim';
GO


On the Query menu, click Execute.

Introduction

The time you start developing your web application until you finish the application, you will more often use the Web.config file not only for securing your application but also for wide range of other purposes which it is intended for. ASP.NET Web.config file provides you a flexible way to handle all your requirements at the application level. Despite the simplicity provided by the .NET Framework to work with web.config, working with configuration files would definitely be a task until you understand it clearly. This could be one of the main reasons that I started writing this article.
This article would be a quick reference for the professional developers and for those who just started programming in .NET. This article would help them to understand the ASP.NET configuration in an efficient way. The readers may skip the reading section "Authentication, Authorization, Membership Provider, Role Provider and Profile Provider Settings", as most of them are familiar with those particular settings.

Background

In this article, I am going to explain about the complete sections and settings available in the Web.config file and how you can configure them to use in the application. In the later section of the article, we will see the .NET classes that are used to work with the configuration files. The contents of the articles are summarized below:

1. Web.config sections/settings
2. Reading Web.config
3. Writing or manipulating Web.config
4. Encrypting the Web.config and
5. Creating your own Custom Configuration Sections

Points to be Remembered

ASP.NET Web.config allows you to define or revise the configuration settings at the time of developing the application or at the time of deployment or even after deployment. The following are brief points that can be understood about the Web.config file:

• Web.config files are stored in XML format which makes us easier to work with.
• You can have any number of Web.config files for an application. Each Web.config applies settings to its own directory and all the child directories below it.
• All the Web.config files inherit the root Web.config file available at the following location systemroot\Microsoft.NET\Framework\versionNumber\CONFIG\Web.config location
• IIS is configured in such a way that it prevents the Web.config file access from the browser.
• The changes in Web.config don’t require the reboot of the web server. 

Source: http://www.codeproject.com/Articles/301726/Web-config-File-ASP-NET

Download demo website configuration - 1.34 MB

Display Image from blob
PHP & MySQL

<img src="data:image/jpeg;base64,<?php echo base64_encode($blobimg); ?>"/>

C# is an object oriented programming language. In Object Oriented Programming methodology a program consists of various objects that interact with each other by means of actions. The actions that an object may take are called methods. Objects of the same kind are said to have the same type or, more often, are said to be in the same class.
For example, let us consider a Rectangle object. It has attributes like length and width. Depending upon the design, it may need ways for accepting the values of these attributes, calculating area and display details.
Let us look at an implementation of a Rectangle class and discuss C# basic syntax, on the basis of our observations in it:

using System;
namespace RectangleApplication
{
    class Rectangle
    {
        // member variables
        double length;
        double width;
        public void Acceptdetails()
        {
            length = 4.5;    
            width = 3.5;
        }
        public double GetArea()
        {
            return length * width;
        }
        public void Display()
        {
            Console.WriteLine("Length: {0}", length);
            Console.WriteLine("Width: {0}", width);
            Console.WriteLine("Area: {0}", GetArea());
        }
    }
    
    class ExecuteRectangle
    {
        static void Main(string[] args)
        {
            Rectangle r = new Rectangle();
            r.Acceptdetails();
            r.Display();
            Console.ReadLine();
        }
    }
}

When the above code is compiled and executed, it produces following result:

Length: 4.5
Width: 3.5
Area: 15.75

If you are using Visual Studio.Net for compiling and executing C# programs, take the following steps:

• Start Visual Studio.
• On the menu bar, choose File, New, Project.
• Choose Visual C# from templates, and then choose Windows.
• Choose Console Application.
• Specify a name for your project, and then choose the OK button.
• The new project appears in Solution Explorer.
• Write code in the Code Editor.
• Click the Run button or the F5 key to run the project. A Command Prompt window appears that contains the line Hello World.

You can compile a C# program by using the command line instead of the Visual Studio IDE:

• Open a text editor and add the above mentioned code.
• Save the file as helloworld.cs
• Open the command prompt tool and go to the directory where you saved the file.
• Type csc helloworld.cs and press enter to compile your code.
• If there are no errors in your code the command prompt will take you to the next line and would generate helloworld.exe executable file.
• Next, type helloworld to execute your program.
• You will be able to see "Hello World" printed on the screen.