Sunday, 20 March 2016

How to generate create table script for all tables in SQL Server database?

(1) Get all tables scripts.

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
                when 'sql_variant' then ''
                when 'text' then ''
                when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
                else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name               = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM       information_schema.key_column_usage kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name        NOT IN ('dtproperties')

(2) Get all Stored Procedure.

SELECT STUFF(S.script, CHARINDEX(P.name, S.script, 1), 0, N'sp_helptext')
  FROM sys.procedures AS P
       CROSS APPLY

       (SELECT OBJECT_DEFINITION(P.object_id) AS script) AS S

(3) Get all views.

SELECT STUFF(S.script, CHARINDEX(P.name, S.script, 1), 0, N'sp_helptext')
  FROM sys.views AS P
       CROSS APPLY
       (SELECT OBJECT_DEFINITION(P.object_id) AS script) AS S

(4) Get All UserDefined Function.

SELECT name AS function_name,SCHEMA_NAME(schema_idAS schema_name,type_descFROM sys.objectsWHERE type_desc LIKE '%FUNCTION%';
     
     
       

Tuesday, 6 October 2015

SSRS in Asp.net

Step 1: Create Dataset

SSRS1.gif

Step 2: Create and Bind .rdlc file with Dataset.

SSRS2.gif

Step 3: Add ReportViewer and call your .rdlc file.

SSRS3.gif

So your .aspx file looks like this.
<asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <rsweb:reportviewer ID="ReportViewer1" runat="server" Font-Names="Verdana" 
    Font-Size="8pt" InteractiveDeviceInfos="(Collection)" 
    WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="90%">
        <LocalReport ReportPath="Reports\bal.rdlc">
        </LocalReport>
    </rsweb:reportviewer>

So your .cs file looks like this.

using System.Drawing;
using Microsoft.Reporting.WebForms;
 private void BindReportViewer()
        {

            dsbal m = new dsbal();
            da = new SqlDataAdapter("select * from vwShowBalance", con);
            da.Fill(m, m.Tables[0].TableName);
            ReportDataSource rds = new ReportDataSource("DataSet1", m.Tables[0]);

            this.ReportViewer1.LocalReport.DataSources.Clear();
            this.ReportViewer1.LocalReport.DataSources.Add(rds);
            this.ReportViewer1.LocalReport.Refresh();
            
        }

Tuesday, 22 September 2015

Login failed for user iis apppool default apppool System.Data.Sqlclient.Sqlexception

Introduction

I am here highlighting the issue System.Data.Sqlclient.Sqlexception:Login failed for user iis apppool \default apppool commonly encountering while working with ASP.Net application with IIS. I have been working on web application. Everything was going fine but suddenly I got the error“System.Data.SqlClient.SqlException was unhandled by user code:  Login failed for user 'IIS APPPOOL\DefaultAppPool’” and database connection couldn't open.

Error Description:

System.Data.SqlClient.SqlException was unhandled by user code:  Login failed for user 'IIS APPPOOL\DefaultAppPool

Below I have provided steps to resolve this issue.

Step 1: Go to Run (Win+R Shortcut) and then type “inetmgr” and hit “OK” button.
Step 2: Now you are in the IIS and now expand left pane and click on the “Application Pools
Login failed for user iis apppool
IIS (Application Pool)


Step 3: Now you can select “DefaultAppPool” and click on “Advance Settings”.

Step 4: Now open “Advance Settings” window and scroll down window to “Process Model”. Under the “Process Model” section select “Identity” property. Select “LocalSystem” from drop down list. "Local Service" works the computer on local system & over the local computer.

Application Pool Identity
Application Pool Identity
Step 5: Process Model Element was introduced with IIS 7.0 
Step 6: "ApplicationPoolIdentity" runs under dynamically created application pools. identity account. "ApplicationPoolIdentity" pool access the resources from "IIS AppPool\<AppPool>", It has the great feature to administrator who can impose security privileges to end users. This is run mostly for remotely or public accessing the website.

ApplicationPoolIdentity
ApplicationPoolIdentity

Step 7: Now Click on "OK" button, you have to take optional Step 4 or step 6 to run your application smoothly.

OK

Summary

Given above steps will provide you complete helpline, how to resolve the error of “Login failed for user 'IIS APPPOOL\DefaultAppPool’”. I hope it will work to resolve your problem perfectly.

Saturday, 12 September 2015

Reading a word document using C#

We may have used FileStream to read text from a text file but not the same way for getting text from a word document.

We have to use a Microsoft COM component called "Microsoft Word 9.0 object library" which provides classes and methods to read from a word document.
We have to use Word.ApplicationClass to have access to the word application.

Open the word document in memory, copy all the content to the clipboard and then we can take the data from the clipboard.

The code required is given below:
Word.ApplicationClass wordApp=new ApplicationClass();
object file=path;
object nullobj=System.Reflection.Missing.Value;  
Word.Document doc = wordApp.Documents.Open(
ref file, ref nullobj, ref nullobj,
                                      ref nullobj, ref nullobj, ref nullobj,
                                      ref nullobj, ref nullobj, ref nullobj,
                                      ref nullobj, ref nullobj, ref nullobj);
doc.ActiveWindow.Selection.WholeStory();
doc.ActiveWindow.Selection.Copy();
IDataObject data=Clipboard.GetDataObject();
txtFileContent.Text=data.GetData(DataFormats.Text).ToString();
doc.Close();

Friday, 24 July 2015

Using Checkbox in Combo Box in windows application

Using checkbox in combo box is very much similar to using any other control. Here we are going to use data template to define the visual tree of each and individual items of the combo box. When defining the visual tree we are going to place text box along with check box inside the panel. Here we are using Stack panel and select horizontal orientation of stack panel.
Here is a XAML code of this.
  1: <Window x:Class="CheckBox.Window1"
  2:     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  3:     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  4:     Title="Checked Box in Combo Box" Height="300" Width="300">
  5:     <Grid>
  6:         <Grid.RowDefinitions>
  7:             <RowDefinition/>
  8:             <RowDefinition/>
  9:         </Grid.RowDefinitions>
 10:         
 11:         <ComboBox Name="cmb" Margin="5" Height="35" >
 12:             <ComboBox.ItemTemplate>
 13:                 <DataTemplate>
 14:                     <StackPanel Orientation="Horizontal">
 15:                         <CheckBox Margin="5" IsChecked="{Binding IsVisited}"/>
 16:                         <TextBlock Margin="5" Text="{Binding CityName}"/>
 17:                     </StackPanel>
 18: 
 19:                 </DataTemplate>
 20:             </ComboBox.ItemTemplate>
 21:         </ComboBox>
 22:     </Grid>
 23: </Window>
 24: 
We are defining one data structure with one Boolean and one string data type to store information about how which city we have already visited. Then we will display that information in side the combo box. Here is a C# code of the project.
  1: using System;
  2: using System.Collections.Generic;
  3: using System.Linq;
  4: using System.Text;
  5: using System.Windows;
  6: using System.Windows.Controls;
  7: using System.Windows.Data;
  8: using System.Windows.Documents;
  9: using System.Windows.Input;
 10: using System.Windows.Media;
 11: using System.Windows.Media.Imaging;
 12: using System.Windows.Navigation;
 13: using System.Windows.Shapes;
 14: 
 15: namespace CheckBox
 16: {
 17:     /// <summary>
 18:     /// Interaction logic for Window1.xaml
 19:     /// </summary>
 20:     public partial class Window1 : Window
 21:     {
 22:         public Window1()
 23:         {
 24:             InitializeComponent();
 25: 
 26:             List<TripInfo> tripList = new List<TripInfo>();
 27: 
 28:             tripList.Add(new TripInfo(false, "Miami"));
 29:             tripList.Add(new TripInfo(true, "Boston"));
 30:             tripList.Add(new TripInfo(true, "Los Angeles"));
 31:             tripList.Add(new TripInfo(true, "Houston"));
 32:             tripList.Add(new TripInfo(false, "Dallas"));
 33:             tripList.Add(new TripInfo(false, "Atlantic City"));
 34:             tripList.Add(new TripInfo(true, "Chicago"));
 35: 
 36:             cmb.ItemsSource = tripList;
 37:         }
 38:     }
 39: 
 40:     public class TripInfo
 41:     {
 42:         public TripInfo(bool isVisited, string cityName)
 43:         {
 44:             IsVisited = isVisited;
 45:             CityName = cityName;
 46:         }
 47: 
 48:         public Boolean IsVisited
 49:         { get; set; }
 50: 
 51:         public String CityName
 52:         { get; set; }
 53:     }
 54: }
 55: 
Here is the output of this program.
CheckBoxComboBox

Tuesday, 21 July 2015

Restfull Service in WCF For Call from URL

Overview of REST

REST stands for Representational State Transfer. This is a protocol for exchanging data over a distributed environment. The main idea behind REST is that we should treat our distributed services as a resource and we should be able to use simple HTTP protocols to perform various operations on that resource.
When we talk about the Database as a resource we usually talk in terms of CRUD operations. i.e. Create, Retrieve, Update and Delete. Now the philosophy of REST is that for a remote resource all these operations should be possible and they should be possible using simple HTTP protocols.
Now the basic CRUD operations are mapped to the HTTP protocols in the following manner:
  • GET: This maps to the R(Retrieve) part of the CRUD operation. This will be used to retrieve the required data (representation of data) from the remote resource.
  • POST: This maps to the U(Update) part of the CRUD operation. This protocol will update the current representation of the data on the remote server.
  • PUT: This maps to the C(Create) part of the CRUD operation. This will create a new entry for the current data that is being sent to the server.
  • DELETE: This maps to the D(Delete) part of the CRUD operation. This will delete the specified data from the remote server.
so if we take an hypothetical example of a remote resource that contain a database of list of books. The list of books can be retrieved using a URL like:
www.testwebsite.com/books
To retrieve any specific book, lets say we have some ID that we can used to retrieve the book, the possible URL might look like:
www.testwebsite.com/books/1
Since these are GET requests, data can only be retrieved from the server. To perform other operations, if we use the similar URI structure with PUTPOST or DELETE operation, we should be able to create, update and delete the resource form the server. We will see how this can be done in implementation part.
Note: A lot more complicated queries can be performed using these URL structures. we will not be discussing the complete set of query operations that can be performed using various URL patterns.

Using the code

Now we can create a simple WCF service that will implement all the basic CRUD operations on some database. But to make this WCF service REST compatible we need to make some changes in the configuration, service behaviors and contracts. Let us see what WCF service we will be creating and then we will see how we can make useful over the REST protocol.

creating REST enabled ServiceContract

We will create Books table and will try to perform CRUD operations on this table. 

To perform the Database operations within the service lets use Entity framework. This can very well be done by using ADO.NET calls or some other ORM but I chose entity framework. (please refer this to know about entity framework:  An Introduction to Entity Framework for Absolute Beginners[^]). The generated Entity will look like following. 

Now the service contract will contain functions for CRUD operations. Let us create the ServiceContract for this service:
[ServiceContract]
public interface IBookService
{
    [OperationContract]
    List<Book> GetBooksList();

    [OperationContract]
    Book GetBookById(string id);

    [OperationContract]
    void AddBook(string name);

    [OperationContract]
    void UpdateBook(string id, string name);

    [OperationContract]
    void DeleteBook(string id);
}
Right now this is a very simple service contract, to indicate that individual operations can be called using REST protocol, we need to decorate the operations with additional attributes. The operations that are to be called on HTTP GET protocol, we need to decorate them with the WebGet attribute. The operations that will be called by protocols, like POST, PUT, DELETE will be decorated with WebInvoke attribute.

Understanding UriTemplate

Now before adding these attributes to these operations let us first understand the concept of UriTemplate.UriTemplate is a property of WebGet and WebInvoke attribute which will help us to map the parameter names coming from the HTTP protocol with the parameter names of ServiceContract. For example, if someone uses the following URI:
localhost/testservice/GetBookById/2
We need to map this first parameter with the id variable of the function. this can be done using theUriTemplate. Also, we can change the function name specifically for the URI and the name of URI function name will be mapped to the actual function name i.e. if we need to call the same URL as:
localhost/testservice/Book/2
then we can do that by specifying the UriTemplate for the operation as:
[OperationContract]
[WebGet(UriTemplate  = "Book/{id}")]
Book GetBookById(string id);
Following the same lines, let us define the UriTemplate for other methods too.
[ServiceContract]
public interface IBookService
{
    [OperationContract]
    [WebGet]
    List<Book> GetBooksList();

    [OperationContract]
    [WebGet(UriTemplate  = "Book/{id}")]
    Book GetBookById(string id);

    [OperationContract]
    [WebInvoke(Method = "GET",UriTemplate = "AddBook/{name}")]
    void AddBook(string name);

    [OperationContract]
    [WebInvoke(Method = "GET",UriTemplate = "UpdateBook/{id}/{name}")]
    void UpdateBook(string id, string name);

    [OperationContract]
    [WebInvoke(Method = "GET",UriTemplate = "DeleteBook/{id}")]
    void DeleteBook(string id);
}

Implementing the Service  

Now the service implementation part will use the entity framework generated context and entities to perform all the respective operations.
public class BookService : IBookService
{
    public List<Book> GetBooksList()
    {
        using (SampleDbEntities entities = new SampleDbEntities())
        {
            return entities.Books.ToList();
        }
    }

    public Book GetBookById(string id)
    {
        try
        {
            int bookId = Convert.ToInt32(id);

            using (SampleDbEntities entities = new SampleDbEntities())
            {
                return entities.Books.SingleOrDefault(book => book.ID == bookId);
            }
        }
        catch
        {
            throw new FaultException("Something went wrong");
        }
    }

    public void AddBook(string name)
    {
        using (SampleDbEntities entities = new SampleDbEntities())
        {
            Book book = new Book { BookName = name };
            entities.Books.AddObject(book);
            entities.SaveChanges();
        }
    }

    public void UpdateBook(string id, string name)
    {
        try
        {
            int bookId = Convert.ToInt32(id);

            using (SampleDbEntities entities = new SampleDbEntities())
            {
                Book book = entities.Books.SingleOrDefault(b => b.ID == bookId);
                book.BookName = name;
                entities.SaveChanges();
            }
        }
        catch
        {
            throw new FaultException("Something went wrong");
        }
    }

    public void DeleteBook(string id)
    {
        try
        {
            int bookId = Convert.ToInt32(id);

            using (SampleDbEntities entities = new SampleDbEntities())
            {
                Book book = entities.Books.SingleOrDefault(b => b.ID == bookId);
                entities.Books.DeleteObject(book);
                entities.SaveChanges();
            }
        }
        catch
        {
            throw new FaultException("Something went wrong");
        }
    }
}

Restful WCF service Configuration

Now from the ServiceContract perspective the service is ready to serve the REST request but to access this service over rest we need to do some changes in the service behavior and binding too.
To make the service available over REST protocol the binding that needs to be used is the webHttpBinding. Also, we need to set the endpoint's behavior configuration and define the webHttp parameter in theendpointBehavior. So our resulting configuration will look something like:

Test the service


Now to test the service we will simply run the service and use the URLs to retrieve the data. let see this for ourGET operations in action.
 
And now testing the query to get a single record
 
And so we have seen that we received the response in the browser itself in form of XML. We can use this service without even consuming it by adding a service reference by using the URLs and HTTP protocols.
Note: Here I am not demonstrating the other operations for POST, PUT and DELETE but they are fairly straight forwards and a simple HTML page sending the data using the required protocol with the specified parameter names will perform the operation.

Using JSON

We can also change the Response and Request format to use JSON instead of XML. To do this we need to specify properties of the WebInvoke attribute.
  • RequestFormat: By default its value is WebMessageFormat.XML. to change it to JSON format, it needs to be set to WebMessageFormat.Json.
  • ResponseFormat: By default its value is WebMessageFormat.XML. to change it to JSON format, it needs to be set to WebMessageFormat.Json.
Let us create one more operation in our service contract called as GetBooksNames and will apply theResponseFormat as Json for this method.
[OperationContract]
[WebGet(ResponseFormat=WebMessageFormat.Json]
List<string> GetBooksNames();
The response will now appear in the JSON format. 

And now we have a WCF REST service ready with us.