Difference Between Where and Having Clause

The WHERE clause does not work with aggregates like SUM,Count etc.

The SQL above will not work, because the where clause doesn’t work with aggregates – like sum, avg, max, etc.. Instead, what we will need to use is the having clause. The having clause was added to sql just so we could compare aggregates to other values – just how the ‘where’ clause can be used with non-aggregates. Now, the correct sql will look like this:

Many time i have seen code where Having and where clause are misused see Examples Below -

BAD SQL:
select employee, sum(bonus) from emp_bonus
group by employee where sum(bonus) > 1000;

GOOD SQL:
select employee, sum(bonus) from emp_bonus
group by employee having sum(bonus) > 1000;


Difference between having and where clause

So we can see that the difference between the having and where clause in sql is that the where clause can not be used with aggregates, but the having clause can. One way to think of it is that the having clause is an additional filter to the where clause.

Sql Standard Theory says WHERE Clause restricts the result set before returning rows and HAVING restricts the result set after retrieving all records.That's why WHERE is faster as compared to Having and that's why having is not used more commonly or always as that of Having so it should used in circumstances where it is necessary to use having and nothing else is possible like as in case we are using aggregate functions

what are CTE? When and Why To Use CTE in SQL ?

What are CTE in SQL ?

CTE are Common Table Expressions first introduced in Sql Server 2005. It is basically a result set or result holder or separate plugin / extension that can hold some complex query separately from main query and supply the named result set or result holder name in place of that complex query in main query.

It really improves complexity of complex queries. It is specially used in queries that contains complex sub-queries in them. Using them makes our query more concise, improves readability of query and makes it easy for maintainable for long time for future use.

CTE always start with Semicolon preceeded by 'with' keyword and CTE are temporary result holders and are removed form memory after query executed its life is only during query is executing.

When To Use CTE 

CTE is helpful to be used in Below two Conditions -

1.  In order to reduce complexity of query with complex subqueries that will make code easy to read and maintain.
   
2. Very helpful to create Recursive query if required in some cases.

you can read more about recursive queries with CTE on link below -
Read More About How To Use Recursive Query With CTE

Why We use ; ( Semicolon ) in start of CTE

It is actually to ensure previous statement before with / CTE are terminated. Most of times
we use semicolon before with keyword in CTE as we don't know what is before CTE.

Example - Without semicolon may create problem in below code in some StoredProcedure

DECLARE @foo int
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate from Orders;
)

Above query will create error in Stored Procedure as DECLARE @foo int is not ended with
semicolon and CTE's with keyword is not ended with ; ( Semicolon ). Better way will  to write this query in stored procedure or anywhere is below -


DECLARE @foo int
;WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate from Orders;
)

Now in this case semicolon before WITH Keyword will terminate previous statement first and then execute CTE

Table Valued Functions in Sql

What are Table Valued Functions in Sql ?

There are two types of “User Defined” fucntions in sql server

1. scalar function ( that returns single value in response to function call )

     2. inline table valued ( that returns function result in tabulated form but query can single lined e.g.

     3. return (select * from table_name).

1. Inline Table Valued Functions
If function is made to execute a select query with or without parameters and return result as table type it comes in category of Inline table valued functions.

Sql Code Example -


CREATE FUNCTION GetAuthorsByState( @state char(2) )
RETURNS table AS
RETURN (SELECT au_fname, au_lname FROM Authors WHERE state=@state)
GO


2. Multi-statement table valued Functions
Multi-statement table valued that returns function result in tabulated form but query may have multiple statements that can make changes to query and return the tabulated data.

If functions is made to execute multiple sql statements including insert, update statements etc and return result as table type then it comes in category of Multistatement table valued functions

Example

CREATE FUNCTION GetAuthorsByState( @state char(2) )
RETURNS
@AuthorsByState table (
au_id Varchar(11),
au_fname Varchar(20)
)
AS
BEGIN
INSERT INTO @AuthorsByState
SELECT au_id,au_fname FROM Authors WHERE state = @state
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @AuthorsByState VALUES ('','No Authors Found')
END
RETURN
END
GO

SQL Server Public Preview available on Ubuntu

Microsoft has just announced its public preview of the next release of SQL Server, and Canonical is delighted to announce that this preview is available for Ubuntu.

Moreover, with SQL Server on Ubuntu, there are significant cost savings, performance improvements, and the ability to scale & deploy additional storage and compute resources easier without adding more hardware.

Read Complete Details Here -

Full Article details Sql Server Public Preview Available on Ubuntu

Download Sql Server on Linux 

Convert Date Format To dd/mm/yy in Sql Server

If you Mssql Server is Using Some Other Date Format For Insert Date in Date Column Then Possibly Default Date format is set to US Date Format ( mm/dd/yy ) and if you want to insert date in dd/mm/yy . In order to insert date in this format you need to set Date Format to French / British ( dd/mm/yy )

So first I recommend you to Use any of Jquery Date Picker and pass the selected date / datetime as below to Insert Query as Below -

insert Table_Name (Date_Column)  values (convert(datetime,'18-06-12 10:34:09 PM',103));

Here second parameter 103 is For French Date Format dd/mm/yyyy .If you want to have dd/mm/yy then use 3 in that parameter

Different Types of Date Format Code as As Below - 

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100 (1,2)	Default for datetime and smalldatetime	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	ANSI	2 = yy.mm.dd 102 = yyyy.mm.dd
3	103	British/French	3 = dd/mm/yy 103 = dd/mm/yyyy
4	104	German	4 = dd.mm.yy 104 = dd.mm.yyyy
5	105	Italian	5 = dd-mm-yy 105 = dd-mm-yyyy
6	106 (1)	-	6 = dd mon yy 106 = dd mon yyyy
7	107 (1)	-	7 = Mon dd, yy 107 = Mon dd, yyyy
8	108	-	hh:mi:ss
-	9 or 109 (1,2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	10 = mm-dd-yy 110 = mm-dd-yyyy
11	111	JAPAN	11 = yy/mm/dd 111 = yyyy/mm/dd
12	112	ISO	12 = yymmdd 112 = yyyymmdd
-	13 or 113(1,2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces) Note: When the value for milliseconds (mmm) is 0, the millisecond value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) Note: When the value for milliseconds (mmm) is 0, the milliseconds value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
-	130 (1,2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM In this style, mon represents a multi-token Hijri unicode representation of the full month's name. This value will not render correctly on a default US installation of SSMS.
-	131 (2)	Hijri (5)	dd/mm/yyyy hh:mi:ss:mmmAM

Create News Feed In PHP

Code To Create A Simple News Feed In PHP

Use Ajax & Jquery To Fetch & Populate News Feed . Make Ajax Call From Page Control Will go to specified PHP page through ajax call then fetch data in that PHP page and send data to callback to Ajax Call

See A simple Example below :-

HTML PAGE CODE

<html>

<head>

//Required CSS  / JSS Files

</head>

<body>

<div id="news"></div>

//Click Button Below To Fetch News Feeds

<input type="button" onclick="GetNews()" value="Get News Feed" />

<script>

    function GetNews()

    {

        $("#news").load("path_to/AjaxFetchData.php")

    }

</script>

</body>

</html>

AjaxFetchData.php File Code

//Sql Query To Fetch News Feed From Database

$con= Initialize it with connection Details for database

$sql = "Select Top 50 from TblNews";

$result = $result=mysqli_query($con,$sql);

while($row =  $result->fetch_assoc())

{

    echo("<div class='NewsFeed'>");

    echo("<div class='title'>" . $row['title'] . "</div>");

    echo("<div class='body'>" . $row['body'] . "</div>");

    echo("</div>");

}

What is POCO in Entity Framework

POCO stands for "Plain Old CLR Object" Here CLR means Common Language Rutime that includes dotnet supported languages like C#, VB etc.

A Plain Old CLR Objects (POCO) is a class that doesn't depend on any framework-specific base class. It is like any other normal .Net class; that is why they are called “Plain Old CLR Objects”. These POCO entities support most of the same LINQ queries as Entity Object derived entities.

POCO allows you to write your own entity classes in a persistence ignorant fashion. POCO is also called as Persistence ignorant objects .

Persistence ignorance means that, as much as possible, anything in your code operating at the business logic layer or higher knows nothing about the actual design of the database, what database engine you're running, or how or when objects get retrieved from or persisted to the database. In the case of the EF, persistence ignorance is attained by working on POCO's and using LINQ to perform queries (i.e., not requiring the user to create any SQL queries to retrieve the desired objects).

There is still the need for you to “plug in” persistence and EF  so that your POCO entities can be take from the database and updated back to the database. In order to do this, you will still need to either create an Entity Data Model using the Entity Framework Designer

Codeigniter open_basedir_restriction in effect

Error :

Message: mkdir(): open_basedir restriction in effect. File() is not within the allowed path(s): (/home/thelazyppl/:/home/thelazyppl:/tmp:/usr/local/lib/php/)

Filename: drivers/Session_files_drivers.php

Details About Error :

CodeIgniter installation is trying to save its session files to somewhere that is unwritable .  I would like to recommend you store sessions in database . You need to do this settings in config.php file Under Application -> Config Folder in Your CodeIgniter Project Default Structure

In My Case I was using CodeIgniter3 Version . You Can Use The Below User Guide Link To Make Configurations Required For Solving this error properly 

User Guide For CodeIgniter 3 :User Guide CodeIgniter3 Open_basedir_restriction in effect

Sure Short Solution For CodeIgniter 3 :- 

• Navigate to your project folder - Under Application folder in Codeigniter go Under Config Folder
  
• Then Open Config.php file Search For below line of code$config['sess_save_path'] = NULL;
  
• And Replace this line of code with line of code below -
  $config['sess_save_path'] = '/tmp';

The better option would be to use the database driver, for which the details are just below that.

If you were having these issues on CodeIgniter 2, you would need definitely need to enable the database. To do this you would change the $config['sess_use_database'] option to TRUE and then run the SQL shown in the CodeIgniter 2 user guide.

Difference Between Stored Procedure and Functions Sql

Stored Procedure and Functions

 

1. Function Must Return Value

   For Stored Procedure it's Not Must to return value it's optional 

2. Stored Procedures are pre-compile objects which are compiled for 

first time and its compiled format is saved which executes (compiled code) whenever it is called 

But Function is compiled and executed every time when it is called

3. Function can Accept only Input Parameters

   But Stored Procedure Can Accept Both Input and Output Parameters

4. Functions can be called from Procedure whereas Procedures cannot be called from Function

5. Functions Like Views only Accept Data Select Statements It does not Allow the Permanent Storage of data so it does not allows DML statements like Insert , Update , Delete

Whereas Store Procedure Allows All type of DML statements like Insert , Update and also Select Statements.

6. Funtions can be used in Select Statements like views to View data 

But Stored Procedure Cannot be Embedded in Select Statements

7.

 

Difference Between Views And StoredProcedure Sql

Stored Procedure Vs Views

1. Stored Procedure are collection of pre-executed sql Statements that accepts parameters as input and depending on input parameters passed gives the Output Result

Views Act like Virtual Tables That Contains set of Rows and Columns from multiple original table of Database according to defined Query Commands

2. Stored Procedure Cannot be Used as Large Building Block or we can simply say It cannot be treated as tables in large queries 

But Views Can be treated like tables we can Use Views similar to table and execute select statements on Views Similar to Tables

3. Stored Procedures Allow the Use of Data Manipulation Command like Insert Data , Update data on Table or Schemas To Manipulate Data

But Views Cannot have Data Manipulation Command It can only give list of data or View of data based on set of Queries in it . Views cannot be used to Permanently store data in database it allows just to view the data.

4. Views  can have  only one Select Statement Allowed To Use

Store Procedure Can Use Various Set of Statements That also Include If-Else Statements

5. View is a virtual table that only exists when you use the view in a query. Its considered virtual table because it acts like a table, and the same operations that can be performed on a table can be performed on a view. Virtual table doesn't stay in the database, it gets created when we use the view and then delete it. 

But Stored Procedure Physically Exist 

6. Views are Used for Providing Security to data So that by using the Views we can View only Particular view of the schema to the user Not the Full View with required columns. Their is no physical presence of view data anywhere in database.

Stored Procedure allows to perform any type of data Manipulation operation But We can also Put Security Permission on Store Procedure In order to Restrict its Access From Unauthorized User

7. Code Sample For Stored Procedure and Views

/*
This Stored procedure is used to Insert value into the table tbl_students. 
*/

Create Procedure InsertStudentrecord
(
 @StudentFirstName Varchar(200),
 @StudentLastName  Varchar(200),
 @StudentEmail     Varchar(50)
) 
As
 Begin
   Insert into tbl_Students (Firstname, lastname, Email)
   Values(@StudentFirstName, @StudentLastName,@StudentEmail)
 End

/*

View Example

*/

CREATE VIEW [Category Sales For 1997] AS

SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales

FROM [Product Sales for 1997]

GROUP BY CategoryName 

Crud Operation in AngularJS With Bootstrap

In My Previous Article I explored about How to setup angularjs in Visual Studio and also Explored concepts of What , why and when To use AngularJS . In this article I am going to do CRUD Operation in AngularJS . CRUD operations means Insert , view , Update and Delete Data In Database . CRUD operations are most important thing that you must practice to create Applications either simple or complex in AngularJS or in any other Language or Technology . I am giving a simple code and easy sample that you can use to Perform CRUD operations in AngularJS using .NET technology 

Download the Sample Code from Link Below .

Download Code



How to setup/Run this sample 


1. First Download the code 
2. Then Open Web.config file and update the connection string to your sqlserver connection string by first making database in you sql server .
3. You need to Deal with CODE First Data migration Commands you can see these command in  link below :-

Data Migration

Snapshots

View Data From Database AngularJS

Insert Data In Database AngularJS

Delete Data from Database AngularJS

Update Data From Database AngularJS

Nopcommerce Developer Guide

Internship - Day 1

Today was first day of my internship Period of 6 months . I am very happy to start my internship with Abax Technologies . Abax technologies is a recognized IT company Situated in Noida . It deals with Desktop , web and Mobile application development and providing solutions to market .

Mr. Rohit Jain is CEO of Company and I am doing Internship under their guidance .

Now came to work -- On 2nd January - 8:23 am Rohit Sir posted my first task on skype . My first task was first to learn more about Nopcommerce , Its Architecture , Plugin development , Module Development , Theme development and widget development .

First i need to download the source code for Nopcommerce form Nopcommerce office webiste then using that source code to compile it , set up its database then run a demo site in nopcommerce .

After setup the demo site for nopcommerce it started exploring its source code architecture and learn more about it from its developer's documentation

I have previously done a ecommerce website in Nopcommerce but at that time i used Nopcommerce 1.9 version that was asp.net version of nopcommerce Now i am going to use Nopcommerce 3.2 and this version is really cool . It is MVC version of nopcommerce using Linq queries instead of using sql and using Razor view engines and more flexibility . Actually I liked this version .

Important Links that i have studied and explored today -

Link for Nopcommerce Developer's Documentation
Nopcommerce Developer Documentation

Nopcommerce uses code first approach for each and every development component in nopcommerce so i decided to first give a look at code first approach to revise my concepts about codefirst approach

Code First Approach Documentation MVC

After Reading about code first approach of Nopcommerce I reached a Nerd Dinner app that is MVC app that uses code first approch for MVC application development
Nerd Dinner MVC App with Code First Approach

So that was it for the day i learned number of things today

Today I have explored following Modules -

1. Nopcommerce Installation v3.20 (mvc)
2. Explored Nopcommerce Developers Documentation
3. Exploring Nopcommerce Architecture
4. Explored Nopcommerce Theme CUstomization and Creating Own Theme
5. Exploring Plugin Development Documentation
     -- Created simple hello world Plugin

Next I will explore create plugins with database access and various nopcommerce inbuilt modules

Import dbf database in sqlserver

Steps For Importing Dbf database into Sql Server

There is no appropriate way or direct way to do that but nothing is impossible so there is workaround that can be done to perform this task . Here is the steps :-

• First we have to convert the .dbf format data into .csv format data
• Then depending on table structure Create the Table in database one by one 
• Then using import bulk options of sql server that accept the data in csv format we can easily insert data in sql server

Convert DBF to CSV

DBF Converter allows you convert a single dbf file or folder with dbf files to csv format from GUI or command line.

1. Select the dbf file or foder with dbf files for batch conversion.
2. Select the output csv file or folder for csv files for batch conversion.

Download Software 



3. Preview, select options for sorting, filtering data (if necessary)
You can also select/unselect columns, set order for columns.


4. Select options for csv format:columns delimiter, rows delimiter (if necessary), and click "Finish"

Import CSV File Into SQL Server Using Bulk Insert

 CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Steps For Importing CSV in Sql server 

• Create The table whose data we are going to import into database

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

where TestData is database name and CSVTest is table name

• I assume your .Csv file lies in c:\csvtest.txt and it contains data like this
  
  1,James,Smith,19750101
  2,Meggie,Smith,19790122
  3,Robert,Smith,20071101
  4,Alex,Smith,20040202
  
  
  
• 
  
  Then Run the following script in the query windows of sql server . Make sure your paths given are correct to .csv file
  
  Code
  
  BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

Snapshot
  
  
  
  
  
  
  • 
    
  
  

How To Use Sqlite Database With Csharp

Sqlite Database is the best way of providing portability to our database . Sqlite database is well know for the conditions when developers don't want to mess up with installation of database on the client machine . In case of Desktop applications or Web Applications We often require database . It does not matter any database we use We need to install it .

We can use database for application by install it on Client's machine that may be some messy work that take some time and could be problematic and The Second condition is to host the database to Database Server and client is given the access to that Database But that may include some Usage or disk cost of Database Server that could be costly as the database goes on increasing day by day

So solution to these type of problems is SQLite Database . Actually Sqlite Database is very popular database used in Handheld or mobile applications . The main purpose of using Sqlite Database for these conditions is that Sqlite Database Simply includes the All sql queries that are used to create table in between the application source code and it create a file of database with .mdf extension on client's machine in given location and all data is saved to that location so it can be easily backed up by employee by using simple method of uploading this database file to his / her Mail Account or save it in somewhere else .

Download the Sqlite DLL And sourcecode that will be required in this Application 

Download System.Data.Sqlite Dll File
DOWNLOAD SOURCECODE ZIP FILE


 
Adding System.Data.Sqlite Dll File To Visual Studio

1. Open Visual Studio and Create a New C# Desktop Application Project by giving it a legal name .
2. Now In Right Side you can see Solution Explorer 
3. In Solution Explorer Right Click on Reference and Click on Add Reference
   
   
4. Now Click on Browse and then Click on Browse button to choose Dll file from your computer where you have downloaded it .
   
   
5. Now click on OK after adding dll file .
   
   
6. Now Dll file is successfully Added
7. Now Finally Add Namespace Using System.Data.Sqlite in top line of your form's .Cs file

Adding Data_Connection Class

Data_connection class will be used to give location where the Sqlite Database .mdf file will be created We are creating class for this as it will be used in number of location that is called code reuse

Steps To Add Class :- 

1. Right click on Project's folder in solution explorer 
2. In pop-up menu click on Add Then click on Class
3. Now a Window appear give a name like Data_connection.cs and Hit ok
4. Now add the code similar to following code to the class :-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SQLite;
using System.Data;
using System.Windows.Forms;
namespace useSqlitecsharp
{
    class Data_connection
    {
        public string datalocation()
        {
            string dir = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            return "Data Source=" + dir + "\\DBgeeksprogrammings.mdf;";
        }

    }
}
5. Now Save all

Adding Code for Creating Table , Inserting Data and Showing Inserted Data

Now we are going to create table using csharp code by passing queries into the code then we pass insert query then we show the result
Add the code similar to following code to your form  :-

Main Terms Used in Code

1. SQLiteConnection :- This is class that is used to establish connection to the sqlite database for creating table , inserting data or to pass any query to database we must first establish connection to database and open the port for connection this is accomplished by making object of this class

2. SQLcommand :- This class is used to pass the sql queries . This requires the Sqlconnect object to be passed also to open to tell the command which instance of database is used for executing the queries that are passed to it .

3. SqlDataAdapter :- This class is used to store the result of executed query and hold to put it in some other variable or datatable


ERROR INFORMATION :- It may be possible that you may go through an error that always exist when we you are attempting first time with sqlite database The error details is shown in image below and Its solution is given in link below


CLICK HERE FOR ERROR SOLUTION
ERROR SOLUTION

Csharp ( C# ) CODE  :-


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace useSqlitecsharp
{
    public partial class Form1 : Form
    {
        Data_connection dbobject = new Data_connection();
        SQLiteConnection SQLconnect = new SQLiteConnection();
        public Form1()
        {
            InitializeComponent();
        }
     
        private void button1_Click(object sender, EventArgs e)
        {
            SQLiteCommand SQLcommand = new SQLiteCommand();
            SQLcommand = SQLconnect.CreateCommand();
            SQLcommand.CommandText = "CREATE TABLE IF NOT EXISTS "+textBox1.Text+"( Username TEXT, Password TEXT);";
            SQLcommand.ExecuteNonQuery();
            SQLcommand.Dispose();

            MessageBox.Show("Table Created");
        }



        private void Form1_Load(object sender, EventArgs e)
        {
            SQLconnect.ConnectionString = dbobject.datalocation();
            SQLconnect.Open();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd = SQLconnect.CreateCommand();
            cmd.CommandText = "insert into " + textBox1.Text + " (Username,Password)  values (@username,@password)";
            cmd.Parameters.AddWithValue("@username", textBox2.Text);
            cmd.Parameters.AddWithValue("@password", textBox3.Text);
            cmd.ExecuteNonQuery();
            cmd.Dispose();

            MessageBox.Show("Data Inserted");
        }



        private void button3_Click(object sender, EventArgs e)
        {
            SQLiteCommand cmd = new SQLiteCommand("select * from "+textBox1.Text , SQLconnect);
            SQLiteDataAdapter da = new SQLiteDataAdapter();
            DataTable dt = new DataTable();
            da.SelectCommand = cmd;
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dataGridView1.DataSource = dt;
            }
            else
            {
                MessageBox.Show("No Data Exist in Table");
            }
        }

    }
}


























































How To use Crystal Reports in Asp.net

Download zip code file  for Crystal Report Demo App
Download Code For How To use Crystal Reports in Asp.net

Setting Up Sql Server Database 

1. Open you Sql Server Management Studio
2. Connect to SQl Server
3. Then  in Right panel Right click on Databases and Click on New Database
4. Now A New Dialog Box Appears 
5. Fill Database name: TestReports and Hit Enter . Now Table will be successfully created
   
   
6. Now in the Query window execute the following scripts to create table
   
   
7. After successfull creation of tables insert some dummy data in tables using following query code
   
   
8. Now Our Database work is over Now we go to Visual Studio for code

Now Do Code in Visual Studio
Start your Visual Studio .Click on New project choose Asp.net Web Form  Application and Give a appropriate name and choose location and Hit Enter .

Getting Dataset Ready For Report

1. Right click on Project Folder in solution Explorer and click on Add then in further sub-menu click on New item
   
   
2. Now a Popup window appears scroll and choose DataSet give name "Customers" and Click on Ok to add DataSet
3. Now Customers Dataset will be added in solution explorer . Now Double click on it 
4. It will open DataSet Designer Now right click on and create new DataTable 
5. After Creating DataTable it will be empty by default now we will add column to it similar to our column in table in database 
6. Right click on DataTable that we have created and Create new Column and give it name try to give the name similar to name of columns in Table in Database

Adding Crystal Report

1. Right click on Project Folder in solution Explorer and click on Add then in further sub-menu click on New item 
2. Now a Popup window appears scroll and choose Crystal Report give it name and Click on Ok
   
   
3. Now Crystal Report Gallery window popup after some time 
4. Now choose "Using the Report Wizard"  and click ok
   
   
5. Now a window will popup and tell you to choose your dataset that we have created in above steps
6. Choose your Datatable under Customers DataSet and Click ok
7. Now your Dataset fields will be added on Field Explorer . Field explorer is available on left side of visual studio window
8. In Field explorer under Database Expert you all datatable fields will be available
   
   
9. you can drag these fields to crystal Report Details Section When you add them in  Details Section Its Header will automatically added to Page Header Section
10. you can do some design of your Crystal Report like shown below :-

Designing Form

1. Now  we will add a Aspx form that will be used to view our Crystal Report
2. Right click on Project Folder in solution Explorer and click on Add then in further sub-menu click on New item 
3. Now a Popup window appears scroll and choose WebForm give it name and Click on Ok  
4. Now delete the existing code Add Following Code To Aspx Form

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <center>
    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" Width="1000" />
            </center>
    </form>
</body>
</html>




Adding Csharp ( C# ) Code

1. Now go to Csharp code of New Added Webform and Add code similar to code below :-
2. Add this code under your partial class -- 



protected void Page_Load(object sender, EventArgs e)
    {
        ReportDocument crystalReport = new ReportDocument();
        crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"));
        Customers dsCustomers = GetData("select * from Testtable");
        crystalReport.SetDataSource(dsCustomers);
        CrystalReportViewer1.ReportSource = crystalReport;
    }

    private Customers GetData(string query)
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        SqlCommand cmd = new SqlCommand(query);
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;

                sda.SelectCommand = cmd;
                using (Customers dsCustomers = new Customers())
                {
                    sda.Fill(dsCustomers, "DataTable1");
                    return dsCustomers;
                }
            }
        }
    }


Getting Started and Deploy Asp.net App with Appharbor -- Cloud Platform as a service

CLOUD - So first thing we need to know here is what is a Cloud ? and how it is useful to us to use Cloud. Cloud is long network of interconnected computers that allow the avaliability of data over internet all the time .Cloud help to make our data secure and allow the user to run its application on cloud with full scalability and availaibility . We say it provides avalaibility of 100% because Cloud follows the approach of Replication of data to the other nodes to make ensuring the full availaibility of data even when the server that is actually assumed to have data or application is not availaible .

If we are accessing an Application on Cloud and at that particular time that server is down so how our Application Deployed on Server could be accessed Cloud Computing gives answer of this question By Cloud Computing when we deploy our application on cloud then cloud computing will replicate your application to other connected nodes also so that your application will be availabile when one of node is off and unable to provide the access to your application

AppHarbor is a fully hosted .NET Platform as a Service. AppHarbor can deploy and scale any standard .NET application to the cloud. AppHarbor is easy and simple to use . It provides number of additional addons for database and other applications to add it to your application. Here I am going to tell all the procedure to create and deploy asp.net application to appharbor cloud service.

Step1

Start your Browser and Put this Url in your address bar and Navigate to AppHarbor Website :- https://appharbor.com/ and Hit Enter

Step2
Click on  Get started Button . Then do simple signup procedure .Then Login to your AppHarbor Account.

Step3
After login Click on Your Applications Menu item on Top Menu Bar and Create a New application by supplying a name for the application Then Finally click on Create New Button

Step4
Now New application is successfully created on cloud . Now for deploying a .Net application with Sql Server database We require to add Sql server as a Addon to Our cloud App

Step5
Now click on Add Ons on sidebar Menu Then Scroll down and find Sql Server and Click on it.

Step6
Now SQL  SERVER Add details are shown Click on Free Install button . Then SQL SERVER add on will be installed for your application to use sql server database with .net application in cloud

Step7
Now on SQL SERVER Installation Success screen click on  sql server

Step8
Then click on Go To  Sql Server . Now your sql server Instance Details will be shown . The most important thing we need is connection string of database instance

Step9
Copy this connection string we will require it in Further steps

Step10
Now open your Visual Studio Command Prompt . It will be availaible in Microsoft visual studio 2010/2012 folder , if not found you can comment here on this post

Step11
Write the following command on this :-
 aspnet_regsql -A all -C "place your copied connection string here"     Now hit enter

 

Step12
Now Open Visual studio Click on File Click on New Project Choose ASP.NET WEB APPLICATION give it a Name and give proper save path like I give "C:/WindowsApplication3" and Hit Ok

 

Step13
Now Visual Studio will Present you with practice project already having some coded files .

Step14
Now open Web.Release.config file Uncomment the connectionstring tag and Replace the name of connection add tag to  "ApplicationServices" and Paste the connection we have copied earlier here and finally Press Ctrl + s to save all content . Now all work with visual studio is done.  Just  Run you application on localhost to know its working fine or not and it also compile all the code that is must required to deploy app on appharbor

 Step15
Now Open Git Bash If you have not installed it yet then wait for next article in which i will tell how to install Git Bash

Step16
After opening Git Bash Navigate to directory where you have saved your .net project like I have saved it in "C:/WebApplication3" . Use this command to go to this directory

cd c:/WebApplication3             Then Hit enter



Step17
Now Run following command to initialize Git Repository
 git init



Step18
Run following command to add repository to Git
git add .



Step19
Run following command to commit your data to Git repository
git commit -m "Added Project To Git:"



Step20
Sometime It may ask you for your Email Address pass it using following command
git config --global user.email "yourmail@example.com"

Step21
Now Clone your git Repository for that we require Repository Url for that Go to Appharbor.com then click on your applications Then Click on Application you have created Then in Sidebar click on   Repository Url then it will be automatically copied to your clipboard

 Step22
Now run the following command  :-
git remote add appharbor "paste copied url here"    ...........then hit enter


Step23
Now push your data or application to cloud or Git Repository by using following command
git push appharbor master

Step24
Now it will ask for password then enter password of your appharbor account and Hit Enter Now it will copy all files to Git Repository



Step25
Now your Application is successfully deployed to cloud To check it online you can first refresh your webpage where you have opened Appharbor account Then go to application we have created on appharbor and click on Hostnames and you can see a url there click on it or open it in new  tab it will show your website that you have uploaded