How To Create SSRS Reports In Sql Server 2008 Step By Step Process

How To Create SSRS Reports In Sql Server 2008 Step By Step Process

To Create the SSRS Reports in Sql Server 2008 Follow these Steps :

1.Click Start  --->Select The Sql Server Bussiness Intelligence Development Studio

                    
2.  Click--> File -->Select New Project

                      
3.In the Projects Types Click Business Intelligence project ----> select Report Server Project---> ok

                                      
 4. In the Solution Explorer Select Report---> Right Click --->Add---->New Item--->Ok

                                        
5. In this Select Report

                                  


6. Click New--->DataSet--->Double click on that You Will Get Like Below :
                                             
                                  


7.             In the Data source Click New ---> Give The Credentials Of Your Data Base And Server Name:  
                                          
                                     

8.

                                    
 9.For Writing Any Query Click Query Designer like Below: You Can Write Your Own Queries Here And Write Where Condition Also It will Act As Parameter
                                      
                                  

10. In this Query Designer Select Add table option.
                                                                    

 

11. In this Select Table or procedure ,View What Ever You want For Report Out put :

12. From the Toolbox Select Table Option And Drag it in the Design Page :
  
                              

13.In that Select All the Fields What Ever We want for Report :


                                          

14. If we  Want Insert Image , Select Image property From the Toolbox  then Give the Proper Path From system  Or Data Base :

15. Click the Preview Page To View The Output Report :

 

How to Use Updateprogress control in asp.net for Image Loading

The following is the CSS for Image Loading using Updateprogress control

#overlay {

    position: fixed;

    z-index: 99;

    top: 0px;

    left: 0px;

    background-color: #f8f8f8;

    width: 100%;

    height: 100%;

    filter: Alpha(Opacity=90);

    opacity: 0.9;

    -moz-opacity: 0.9;

}           

#theprogress {

    background-color: #fff;

    border:1px solid #ccc;

    padding:10px;

    width: 300px;

    height: 55px;

    line-height:30px;

    text-align: center;

    filter: Alpha(Opacity=100);

    opacity: 1;

    -moz-opacity: 1;

}

#modalprogress {

    position: absolute;

    top: 40%;

    left: 50%;

    margin: -11px 0 0 -150px;

    color: #990000;

    font-weight:bold;

    font-size:14px;

}

Image for  Loading:

The following is the code for updateprogress control in asp.net

  <asp:UpdateProgress ID="prgLoadingStatus" runat="server" DynamicLayout="true" DisplayAfter="1" AssociatedUpdatePanelID="Updatepanel1">

    <ProgressTemplate>

        <div id="overlay">

            <div id="modalprogress">

                <div id="theprogress">

                    <asp:Image ID="imgWaitIcon" runat="server" ImageAlign="AbsMiddle" ImageUrl="~/Images/loading.gif" />

                    Please wait...

                </div>

            </div>

        </div>

    </ProgressTemplate>

</asp:UpdateProgress>

How to Create User and Give Roles in sql server 2008

How to Create User and Give  Roles in sql server 2008 :

In this article we will discuss on how to create Logins and give database level roles in SQL Server and how to assign these roles to the specific users.

So in this scenario we will create two logins one with admin role and one with readonly tester role.
So let’s start with starting the SQL Server Management Studio.

Step 1 - Start the SQL Server Management Studio and login into it using the admin credentials.

Step 2 -Expand the Security->Logins Section



Step 3 -Right Click on the Logins folder and select New Login option. Enter the login details and then click ok.



Step 4 - Similarly create another login named Tester.

Step 5 -Next expand the Database at which you want to create roles.
                                        
Step 6 - Next right click on Database roles and select new database role, give the role name, go to securables tab click on search, in the popup window select the All objects of the types option click ok, Select database as the object type and click ok. Now set the permissions to the role at the database level as shown below and then click ok
                                       

                                                                 

                                                            
Step 7 -Now go back to the server logins double click on the created Tester login and goto user mapping tab select the db for which we have set up roles [SampleDB] then map the login to the tester role and click ok.
                                          

                                                
Step 8 - So we are done with the tester role and mapping the tester user to tester role.

                                                         

                          





                                                     

                                                    
Now similarly double click on the Admin login and goto user mapping tab , select the sample db, and select the inbuilt db_accessadmin and db_owner role and click ok.

Now just refresh the server. Login using the tester user and try to perform all the Select, create, insert and other operations and you will see that you will have only the select [readonly] permission on the sampleDB.

Same way login using the Admin user login and perform you will see that you can perform all the activities.
Thats it..
Hope this article will help you all ..

How to Create Views in sql Server 2008

View:
_____

       A view is a virtual table that consists of columns from one or more tables.
 Though it is similar to a table, it is stored in the database.
 It is a query stored as an object. Hence, a view is an object that derives its data
 from one or more tables. These tables are referred to as base or underlying tables.


Views ensure the security of data by restricting access to the following data:
______________________________________________________________________________

    Specific rows of the tables.
    Specific columns of the tables.
    Specific rows and columns of the tables.
    Rows fetched by using joins.
    Statistical summary of data in a given tables.
    Subsets of another view or a subset of views and tables.



Some common examples of views are:
____________________________________

    A subset of rows or columns of a base table.
    A union of two or more tables.
    A join of two or more tables.
    A statistical summary of base tables.
    A subset of another view, or some combination of views and base table.


Creating Views:
___________________________

A view can be created by using the CREATE VIEW statement.


Syntax:
_______

CREATE VIEW view_name
[(column_name[,column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

Where:

      view_name specifies the name of the view and must follow the rules for identifiers.

      column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement.

      WITH ENCRYPTION encrypts the text for the view in the syscomments table.

      AS specifies the actions that will be performed by the view.

      select_statement specifies the SELECT Statement that defines a view. The view may use the data contained in other views and tables.

      WITH CHECK OPTION forces the data modification statements to fulfill the criteria given in the SELECT statement defining the view. It also ensures that the data is visible after the modifications are made permanent.


Example:
--------------

    CREATE VIEW vwCustomer
    AS
    SELECT CustomerId, Company Name, Phone
    FROM Customers



Altering Views:
---------------------

You can modify a view without dropping it. This ensures that the permission on the view is also not lost. You can modify a view without affecting its dependent objects, such as triggers and stored procedures.

You modify a view using the ALTER VIEW statement.


Syntax:
_______

       ALTER VIEW view _name [column_ name)]
       [WITH ENCRYPTION]
       AS select_statement
       [WITH CHECK OPTION]



Dropping Views:
________________

You can drop a view from a database by using the DROP VIEW statement. When a view is dropped, it has no effect on the underlying tables. Dropping a view removes its definition and all the permissions assigned to it. Furthermore, if you query any views that reference a dropped view, you receive an error message. However, dropping a table that references a view does not drop the view automatically. You must drop it explicitly.

Syntax:
-----------

DROP VIEW view_name



Renaming Views:
_______________

You can rename a view without having to drop it. This ensures that the permissions on the view are not lost.

The guidelines for renaming a view are as follows:

    The view must be in the current database.
    The new name for the view must be followed by the rules for identifiers.
    A view can be renamed only by its owner.
    A view can also be renamed by the owner of the database.
    A view can be renamed by using the sp_rename system stored procedure.

Syntax:
--------

Sp_rename_old_viewname, new_viewname

      Where:

           old_viewname is the view that needs to be renamed.

            new_viewname is the new name of the view.

Example:
_________

           Sp_rename vwCutomers vwCustomerDetails

           Renames vwCutomers to vwCustomerDetails.

Stored procedures in sql server 2008

Stored Procedure in sql server:
--------------------------------

 Definition: 
----------------
               Stored procedures are precompiled database queries that improve the security,
efficiency and usability of database client/server applications. Developers specify a
stored procedure in terms of input and output variables. They then compile the code on
the database platform and make it available to aplication developers for use in other
environments, such as web applications. All of the major database platforms, including
Oracle, SQL Server and MySQL support stored procedures. The major benefits of this
technology are the substantial performance gains from precompiled execution, the
reduction of client/server traffic, development efficiency gains from code reuse and
abstraction and the security controls inherent in granting users permissions on specific
stored procedures instead of the underlying database tables.

Syntax:
___________
CREATE PROCEDURE PROCEDURE_NAME(PARAMETERS/ARGUMENTS PASSING)
AS
BEGIN

     //INSERT OR UPDATE OR DELETE STATEMENTS//
END

EXAMPLE FOR INSERT AND UPDATE PROCEDURES:
----------------------------------------------------------------------
CREATE  procedure [dbo].[ActivityInsert]
(
@Type varchar(50),
@EmpId varchar(50),
@Category varchar(10),
@Outline Varchar(200),
@Narration Varchar(MAX),
@Remarks Varchar(200),
@Checked Varchar(50),
@Verified Varchar(50),
@Date date

)
as
begin
if(@Type='Insert')
begin
insert into ABSL_ActivityReport(EmpId,Category,Outline,Narration,Remarks,Checked,Verified,Date,CreatedDate)
values(@EmpId,
@Category,
@Outline,
@Narration,
@Remarks,
@Checked,
@Verified,
@Date,
GETDATE())
end
else
begin
update ABSL_ActivityReport set  Category=@Category,Outline=@Outline,Narration=@Narration,Remarks=@Remarks,Checked=@Checked,
Verified=@Verified,Date=@Date,ModifiedDate=GETDATE() where EmpId=@EmpId
end

end




EXAMPLE FOR DELETE PROCEDURE:
_____________________________________

Create  procedure [dbo].[CarParking_Delete]
(
  @Id int
)
as
begin
Delete from CarParking_Create where Id=@Id
end




How to Execute Procedures :
-----------------------------------------

EXEC PROCEDURE_NAME VALUES

Example:

     EXEC CarParking_Delete 1
   
          Here CarParking_Delete is Name of the Procedure and 1 is the id which is
Deleted By us.Means which row we want to delete from the front end.

How to store and Retrieve pdf files in sqlserver 2008 using asp .net

Follow the Code Which is Help How to Store /Retrieve pdf files in DataBase :

Write This code  in Default.aspx page:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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">
    <div>
        <table cellpadding="0" cellspacing="0" align="center" width="600">
            <tr>
                <td height="30" colspan="2">
                    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
                </td>
            </tr>
          
            <tr>
                <td height="30">
                    Select Your PDF File
                </td>
                <td>
                    <asp:FileUpload ID="FileUpload1" runat="server" />
                </td>
            </tr>
            <tr>
                <td height="30" colspan="2" align="center">
                    <asp:Button ID="Button1" runat="server" Text="Upload" onclick="Button1_Click" />
                </td>
            </tr>
            <tr>
                <td height="30" colspan="2" align="center">
                    <asp:GridView ID="GridView1" runat="server" DataKeyNames="ID"
                        AutoGenerateColumns="false"
                        onselectedindexchanged="GridView1_SelectedIndexChanged">
                        <Columns>
                            <asp:TemplateField HeaderText="ID">
                                <ItemTemplate>
                                    <%#Eval("ID")%>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="PDF File Name">
                                <ItemTemplate>
                                    <asp:HyperLink Target="_blank" ID="HyperLink1" runat="server" NavigateUrl=' <%# "~/Default2.aspx?ID=" + Eval("ID", "{0:d}")%> '><%#Eval("PDFNAME")%></asp:HyperLink>                                  
                                </ItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                    </asp:GridView>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

Write This code  in Default.aspx.cs page:   

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
    SqlCommand sqlcmd = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dt = new DataTable();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            LoadGrid();
        }
        Label1.Text = "";
    }
    void LoadGrid()
    {
        sqlcon.Open();
        sqlcmd = new SqlCommand("select * from PDFUPLOAD",sqlcon);
        da = new SqlDataAdapter(sqlcmd);
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
        sqlcon.Close();
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //PDF Upload Code to SQL SERVER database table
        if (FileUpload1.HasFile)
        {
            Stream fs = default(Stream);
            fs = FileUpload1.PostedFile.InputStream;
            BinaryReader br1 = new BinaryReader(fs);
            byte[] pdfbytes = br1.ReadBytes(FileUpload1.PostedFile.ContentLength);
            sqlcon.Open();
            SqlCommand sqlcmd = new SqlCommand("insert into PDFUPLOAD(PDFNAME,PDFCONTENT) values (@pdfname,@pdf)", sqlcon);
            sqlcmd.Parameters.Add("@pdfname", FileUpload1.FileName);
            sqlcmd.Parameters.Add("@pdf", pdfbytes);
            sqlcmd.ExecuteNonQuery();
            sqlcon.Close();
            Label1.Text = "Successfully pdf upload to SQL Server database.";
            LoadGrid();
        }       
    }
}

Write This code  in Default2.aspx page:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!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">
    <div>
   
    </div>
    </form>
</body>
</html>

Write This code  in Default2.aspx.cs page:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
    SqlCommand sqlcmd = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dt = new DataTable();
    string qstr;
    byte[] b = null;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            qstr = Request.QueryString["ID"];
            //Read PDF file from DATABASE table pdf field
            SqlCommand sqlcmd = new SqlCommand("Select PDFCONTENT from pdfupload where ID='" + qstr + "'", sqlcon); //use condition to retrieve particulatr PDF
            sqlcon.Open();
            da = new SqlDataAdapter(sqlcmd);
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                b = ((byte[])dt.Rows[0][0]);
                //Collect Bytes from database and write in Webpage
                Response.ContentType = "application/pdf";
                Response.BinaryWrite(b);
            }
        }
    }
}

How to Create Triggers in sql server 2008

What is a trigger?
________________________________

                Trigger is a Special kind of Stored Procedure that is invoked whenever an
Attempt is made to modify the data in the table the trigger will protects

                 A table Can have three trigger actions :
                        1.Update,
                        2.Insert,
                        3.Delete

Only Table owners can create and drop triggers for the table.Trigger is Attached to a table or View
and is fired only when an Insert,Update,Delete Occurs,While stored procedure executes at any time
while is is called.

     Triggers are Mainly divided in to Two Categories:

      1. After trigger(FOR trigger) -----(AFTER INSERT,AFTER UPDATE,AFTER DELETE)
      2. Instead of Triggers


A simple Syntax For triggers:
________________________________

             Create Trigger Trigger_Name
             ON (Table or view)
             AFTER Insert,Update,Delete
             AS
           
               DECLARE
                 //initialize parameters if any required like (@Empid int;) //
             
             BEGIN


               // Insert ,Update ,Delete Stamt....//
                 
              PRINT('AFTER InSERT TRIGGER FIRED')
            
              END
               GO

Trigger Example For After Insert Trigger:
------------------------------------------

         First Create a Table With the Name product Having the columns like Below

        Create Table Product
        (
          Product_id int,
          productName varchar(50),
          Address varchar(50)
        )
        





Trigger for After Insert :
_______________________________________

                    CREATE  TRIGGER Product_trigger
                    ON  dbo.Product
                    AFTER INSERT
                    AS
                    DECLARE
                    @Product_id int,
                    @productName varchar(50),
                    @Address varchar(50)
                    BEGIN
                Insert into  Product (Product_id,ProductName,Address)values(@Product_id,@ProductName,@Address)

                    END
                    GO


                 In the Above dbo.Product is a table Name And  Product_trigger is Trigger Name.


How to Eanble/Disable Trigger Associated Table:
________________________________________________

           Alter Table Table_Name  ENABLE/DISABLE Trigger ALL   -----(It is For All Tables In a Data Base)


            Alter Table Table_Name ENABLE/DISABLE Trigger Trigger_Name ( It is only For A Particuler
                                                                           Trigger Assosiated Table )



              Ex: Alter Table Product ENABLE Trigger Product_trigger


Difference Between AFTER TRIGGERS AND INSTEAD TRIGGERS :
_________________________________________________________

    1.  Instead Triggers Only single Implementation is possible on table .Where as After Triggers Support a multiple triggers to a single Table
  
    2. Insted Triggers Applied on View .where As After Triggers Cant be Applied on View.