.NET Interview questions for experiences by another blogger Pavan

http://pawantechit.blogspot.in/2013/03/interview-question-for-3-6-year.html

Generate PDF file in MVC using itextsharp dll

//pdf file Generate Code
                Document pdfDoc = new Document(PageSize.A4, 25, 10, 10, 10);
                PdfWriter pdfWriter = PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
                PdfDestination pdfDest = new PdfDestination(PdfDestination.XYZ, 0, pdfDoc.PageSize.Height, 0.99f);
                pdfDoc.Open();

               
                Font font11 = FontFactory.GetFont("ARIAL", 11);
                Font font12 = FontFactory.GetFont("ARIAL", 11,Font.UNDERLINE, iTextSharp.text.BaseColor.RED);
                Font font20 = FontFactory.GetFont("ARIAL", 20);

                iTextSharp.text.Image jpg = iTextSharp.text.Image.GetInstance(Server.MapPath("~/Images/BBClogo.jpg"));
                jpg.ScaleToFit(121F, 131F);
                jpg.Alignment = Image.ALIGN_RIGHT;
                pdfDoc.Add(jpg);

                Paragraph text = new Paragraph(20f, "Commercial Invoice", font20);
                text.Alignment = Element.ALIGN_CENTER;
                pdfDoc.Add(text);

                PdfPTable pdftable1 = new PdfPTable(4);

                float[] widthstab1 = new float[] { 1f, 2f, 1f, 2f };
                pdftable1.SetWidths(widthstab1);
                pdftable1.HorizontalAlignment = 1;
                pdftable1.SpacingBefore = 20f;
                pdftable1.SpacingAfter = 10f;


                PdfPCell cell = new PdfPCell();
                pdftable1.AddCell("Sender’s name:");
                pdftable1.AddCell(objInternationalBookModels.senderName);
                pdftable1.AddCell("Invoice No:");
                pdftable1.AddCell("           ");
                pdftable1.AddCell("Sender’s Address:");


                var pCollAdd = new Paragraph();

                if (objInternationalBookModels.collectionCompany != "" && objInternationalBookModels.collectionCompany != null)
                {
                    pCollAdd.Add(objInternationalBookModels.collectionCompany + "," + "\n");
                }
                if (objInternationalBookModels.collectionaddress1 != "" && objInternationalBookModels.collectionaddress1 != null)
                {
                    pCollAdd.Add(objInternationalBookModels.collectionaddress1 + "," + "\n");
                }
                if (objInternationalBookModels.collectionaddress2 != "" && objInternationalBookModels.collectionaddress2 != null)
                {
                    pCollAdd.Add(objInternationalBookModels.collectionaddress2 + "," + "\n");
                }
                if (objInternationalBookModels.collectiontown != "" && objInternationalBookModels.collectiontown != null)
                {
                    pCollAdd.Add(objInternationalBookModels.collectiontown + "," + "\n");
                }
                if (objInternationalBookModels.collectionpostcode != "" && objInternationalBookModels.collectionpostcode != null)
                {
                    pCollAdd.Add(objInternationalBookModels.collectionpostcode + "," + "\n");
                }
                if (objInternationalBookModels.CountryName != "" && objInternationalBookModels.CountryName != null)
                {
                    pCollAdd.Add(objInternationalBookModels.CountryName);
                }

                PdfPCell celsendadd = new PdfPCell(pCollAdd);
                celsendadd.Colspan = 2;
                pdftable1.AddCell(celsendadd);

                PdfPTable nested = new PdfPTable(2);
                nested.AddCell("HAWB:");
                nested.AddCell(objInternationalBookModels.HAWBNumber);
                nested.AddCell("Date:");
                nested.AddCell(objInternationalBookModels.collectionDate);
                nested.AddCell("Pieces:");
                nested.AddCell(objInternationalBookModels.totalNoofpieces.ToString());
                nested.AddCell("Weight:");
                nested.AddCell(objInternationalBookModels.totalweight.ToString());
                nested.AddCell("Currency:");
                nested.AddCell("GBP");
                PdfPCell nesthousing = new PdfPCell(nested);
                nesthousing.Padding = 0f;
                pdftable1.AddCell(nesthousing);

                pdftable1.AddCell("Consignee Name:");
                PdfPCell celDelConname = new PdfPCell(new Phrase(objInternationalBookModels.deliveryContactname));
                celDelConname.Colspan = 3;
                pdftable1.AddCell(celDelConname);

                pdftable1.AddCell("Delivery Address:");

                var pDelAdd = new Paragraph();
                if (objInternationalBookModels.deliveryCompany != "" && objInternationalBookModels.deliveryCompany != null)
                {
                    pDelAdd.Add(objInternationalBookModels.deliveryCompany + "," + "\n");
                }
                if (objInternationalBookModels.deliveryaddress1 != "" && objInternationalBookModels.deliveryaddress1 != null)
                {
                    pDelAdd.Add(objInternationalBookModels.deliveryaddress1 + "," + "\n");
                }
                if (objInternationalBookModels.deliveryaddress2 != "" && objInternationalBookModels.deliveryaddress2 != null)
                {
                    pDelAdd.Add(objInternationalBookModels.deliveryaddress2 + "," + "\n");
                }
                if (objInternationalBookModels.deliverytown != "" && objInternationalBookModels.deliverytown != null)
                {
                    pDelAdd.Add(objInternationalBookModels.deliverytown + "," + "\n");
                }
                if (objInternationalBookModels.deliverypostcode != "" && objInternationalBookModels.deliverypostcode != null)
                {
                    pDelAdd.Add(objInternationalBookModels.deliverypostcode + "," + "\n");
                }
                if (objInternationalBookModels.deliveryCountryName != "" && objInternationalBookModels.deliveryCountryName != null)
                {
                    pDelAdd.Add(objInternationalBookModels.deliveryCountryName);
                }

                PdfPCell celdeladd = new PdfPCell(pDelAdd);
                celdeladd.Colspan = 3;
                pdftable1.AddCell(celdeladd);

                pdftable1.AddCell("Telephone:");
                pdftable1.AddCell(objInternationalBookModels.deliveryContactNo);
                pdftable1.AddCell("Shipped via:");
                pdftable1.AddCell(objInternationalBookModels.SupplierName);
                pdfDoc.Add(pdftable1);

                if (objInternationalBookModels.lstContentdetails != null && objInternationalBookModels.lstContentdetails.Count > 0)
                {
                    PdfPTable pdftable2 = new PdfPTable(4);
                    float[] widthstab2 = new float[] { 1f, 2f, 1f, 2f };
                    pdftable2.SetWidths(widthstab2);
                    pdftable2.HorizontalAlignment = 1;
                    pdftable2.SpacingBefore = 20f;
                    // pdftable2.SpacingAfter = 20f;
                    Decimal totalShipmentValue = 0;
                    pdftable2.AddCell("Quantity");
                    pdftable2.AddCell("Contents");
                    pdftable2.AddCell("Item Value");
                    pdftable2.AddCell("Total Value");


                    for (int i = 0; i < objInternationalBookModels.lstContentdetails.Count; i++)
                    {
                        pdftable2.AddCell(objInternationalBookModels.lstContentdetails[i].quantity.ToString());
                        pdftable2.AddCell(objInternationalBookModels.lstContentdetails[i].content.ToString());
                        pdftable2.AddCell(objInternationalBookModels.lstContentdetails[i].totalvalue.ToString());
                        Decimal TotalXValue = objInternationalBookModels.lstContentdetails[i].quantity * objInternationalBookModels.lstContentdetails[i].totalvalue;
                        pdftable2.AddCell(TotalXValue.ToString());

                        totalShipmentValue = totalShipmentValue + TotalXValue;
                    }
                    pdfDoc.Add(pdftable2);



                    PdfPTable pdfTotalValue = new PdfPTable(4);
                    float[] widthsT = new float[] { 1f, 2f, 1f, 2f };
                    pdfTotalValue.SetWidths(widthsT);
                    pdfTotalValue.HorizontalAlignment = 1;

                    BaseColor basecol = new BaseColor(System.Drawing.Color.Red);
                    Font fontTotalvalue = FontFactory.GetFont("ARIAL", 12, basecol);
                    PdfPCell cellTotalValue = new PdfPCell(new Phrase("Total Shipment Value", fontTotalvalue));
                    cellTotalValue.Colspan = 3;
                    cellTotalValue.HorizontalAlignment = Element.ALIGN_RIGHT;
                    pdfTotalValue.AddCell(cellTotalValue);
                    pdfTotalValue.AddCell(totalShipmentValue.ToString());

                    pdfDoc.Add(pdfTotalValue);
                }

                if (objInternationalBookModels.producttype != null && objInternationalBookModels.producttype == "Document")
                {
                    PdfPTable pdftable3 = new PdfPTable(4);
                    float[] widths = new float[] { 1f, 2f, 1f, 2f };
                    pdftable3.SetWidths(widths);
                    pdftable3.HorizontalAlignment = 1;
                    pdftable3.SpacingBefore = 20f;
                    //pdftable3.SpacingAfter = 20f;

                    pdftable3.AddCell("Quantity");
                    pdftable3.AddCell("Contents");
                    pdftable3.AddCell("Item Value");
                    pdftable3.AddCell("Total Value");
                    pdftable3.AddCell("                    ");
                    pdftable3.AddCell("                    ");
                    pdftable3.AddCell("                    ");
                    pdftable3.AddCell("                    ");

                    pdftable3.AddCell("                    ");
                    pdftable3.AddCell("                    ");
                    pdftable3.AddCell("                    ");
                    pdftable3.AddCell("                    ");

                    pdfDoc.Add(pdftable3);


                    PdfPTable pdfTotalValueDoc = new PdfPTable(4);
                    float[] widthsT = new float[] { 1f, 2f, 1f, 2f };
                    pdfTotalValueDoc.SetWidths(widthsT);
                    pdfTotalValueDoc.HorizontalAlignment = 1;

                    BaseColor basecol = new BaseColor(System.Drawing.Color.Red);
                    Font fontTotalvalue = FontFactory.GetFont("ARIAL", 12, basecol);
                    PdfPCell cellTotalValue = new PdfPCell(new Phrase("Total Shipment Value", fontTotalvalue));
                    cellTotalValue.Colspan = 3;
                    cellTotalValue.HorizontalAlignment = Element.ALIGN_RIGHT;
                    pdfTotalValueDoc.AddCell(cellTotalValue);
                    pdfTotalValueDoc.AddCell("                        ");
                    pdfDoc.Add(pdfTotalValueDoc);

                }

                PdfPTable pdftable4 = new PdfPTable(2);
                float[] widthstab4 = new float[] { 1f, 2f };
                pdftable4.SetWidths(widthstab4);
                pdftable4.HorizontalAlignment = 1;

                pdftable4.SpacingBefore = 20f;
                pdftable4.SpacingAfter = 30f;
                pdftable4.AddCell("Senders Vat No:");
                pdftable4.AddCell("               ");
                pdftable4.AddCell("Reason For Export:");
                pdftable4.AddCell("                  ");
                pdftable4.AddCell("ECO Licence");
                pdftable4.AddCell("               ");
                pdftable4.AddCell("Terms of Delivery:");
                pdftable4.AddCell("               ");
                pdfDoc.Add(pdftable4);


                //Chunk chnk = new Chunk("I declare the above information is true and correct to the best of my knowledge.\n", font11);
                //chnk.SetUnderline(0.1f, -2f);
                Paragraph text2 = new Paragraph("I declare the above information is true and correct to the best of my knowledge.\n", font11);
                pdfDoc.Add(text2);

                Paragraph text5 = new Paragraph("Signed for on behalf of the shipper.\n", font11);
                pdfDoc.Add(text5);
                Paragraph text3 = new Paragraph("Name (in print):\n\n\n", font11);
                pdfDoc.Add(text3);
                Paragraph text4 = new Paragraph("Signature:", font11);
                pdfDoc.Add(text4);

                PdfAction pdfAct = PdfAction.GotoLocalPage(1, pdfDest, pdfWriter);
                pdfWriter.SetOpenAction(pdfAct);

                pdfWriter.CloseStream = false;
                pdfDoc.Close();
                Response.Buffer = true;
                Response.ContentType = "Application/pdf";
                Response.AddHeader("content-disposition", "attachment;filename=Commercial_Invoice_template_2014.pdf");
                Response.Cache.SetCacheability(HttpCacheability.NoCache);
                Response.Write(pdfDoc);
                Response.End();

Blogger Buzz: Blogger integrates with Amazon Associates

Blogger Buzz: Blogger integrates with Amazon Associates

Replication in SQL Server

Replication
- process of distributing data from one database to another on the same server or servers connected through LAN or the internet.
- synchronize data between databases that may be at remote location.
- maintain copies of database at more than one locations.
- It can be used for load balancing of server by maintaining area specific data independently and replicate later to have single version of data

Types of Replication

1. Snapshot Replication
2. Merge Replication
3. Transactional Replication

Snapshot Replication
- Snapshot of entire data is copied from publisher to the subscriber's database on regular
interval.
- This kind of replication is used for replicating data that doesn’t change frequently.
- It is used where the source can manage with out of date data.
- This replication is considered when amount of data to be replicated is small.

Merge Replication
- This allows both publisher and subscriber to work independently, online or offline and merges
the changes later.
- Here changes are track on both publisher and subscriber and then merged.

Transactional Replication
- This kind is used when changes are frequent.
- The replication agent monitors the changes at the publisher and distributes the changes to the
subscribers.
- Its required where up to date data is required.

Cursors in SQL Server

Cursors
-It is a special programming construct that allows data to be manipulated on a row-by-row basis.
-They point to a certain location within a record set and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time.
-A cursor is used to access the result set stored in the memory on execution of a query.


Steps to use Transact-SQL Cursor.

Declare the cursor,
Open the cursor,
Fetch record row by row,
Close cursor,
Deallocate cursor.


Example of a cursor

Declare @EmpId int
Declare curEmp CURSOR READ_ONLY FOR SELECT EmpId FROM Employee
Open curEmp
Fetch next from curEmp into @EmpId
While @@FETCH_STATUS = 0
Begin
Print @EmpId
Fetch next from curEmp into @EmpId
End
Close curEmp
Deallocate curEmp


Different types of cursors

1. DYNAMIC: It reflects changes happened on the table while scrolling through the row.

2. STATIC: It works on snapshot of record set and disconnects from the server. This kind
doesn’t reflects changes happened on the table while scrolling through the row.

3.KEYSET: In this kind, new record is not reflected, but data modification can be seen.
Define the cursor lock types.

SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.


Disadvantages/limitation of the cursor
- consume network resources.
- Close cursor when it is not required.
- You shouldn’t forget to deallocate cursor after closing it.
- Cursor requires a network roundtrip each time it fetches a record.

Avoid cursors
- Using the SQL while loop
- User defined functions
- Using CTE(Common Table Expression) Using WITH Clause.
-Temporary table
- Table variable
- You should fetch least number of records.
- You should use FORWARD ONLY option when there is no need to update rows.

Differences between User Defined Functions and Stored Procedures

User Defined Functions

1 UDF are simpler to invoke than Stored Procedures from inside another SQL statement.
2 SP's are Complex to Invoke.
3 UDF's must be prefaced with the owner name, DBO in this case. Not mandatory.
4 When call function then the parameter must transmit full.
5 A UDF is always used to return a value or a table object.
6 Function return type could be scalar or table or table values(SQL Server).
7 Function returns only one value at a time
8 We can call the functions in SQL statements (select max(sal) from emp)
9 Function do not return the images, text.
10 Functions are used for computations.
11 Functions MUST return a value.
12 Function parameters are always IN.
13 UDF can be used in the SQL Statements anywhere in the WHERE/HAVING/SELECT section.
14 UDF's only can have input parameters.


Stored Procedures

1 Stored Procedure is pre compiled execution plan Functions are not precompiled.
2 Stored procedure returns always integer value by default zero.
3 Stored Procedure returns more than one value at a time.
4 We cannot call Stored Procedures in SQL Statements.
5 Function do not return the images, text. Stored Procedure returns all.
6 Procedures can be used for performing business logic.
7 Functions MUST return a value. Procedures need not be.
8 Function parameters are always IN, no OUT is possible Stored procedures can have input and output parameters.
9 Stored procedures cannot be used WHERE/HAVING/SELECT section .

Normalization and DeNormalization,fulltext definition functions,find index, non index tables in sql server

Normalization

In Relational database design,the process of organizing data to minimize redundancy is called Normalization.

DeNormalization

It is the process of optimize the performance of a database by adding redundant data.

Different Normalization Forms

1 NF

Eliminate Repeating groups

2NF

Eliminate Redundant Data

3NF

Eliminate Columns which are not dependent on key column

BCNF

Eliminate non-trivial dependencies in candidate key attribute then separate it

4NF

Isolate independent multiple relationship

5NF

Isolate semantically related multiple relationship(Separate Many-Many Relationship)

ONF

A Model is limited as express object role model

DKNF

A model free from all modification anomalies


Trick to find all index tables in a database

SELECT NAME FROM SYS.TABLES WHERE OBJECTPROPERTY(OBJECT_ID,'ISINDEXED')=1


Trick to find all non-index tables in a database


SELECT NAME FROM SYS.TABLES WHERE OBJECTPROPERTY(OBJECT_ID,'ISINDEXED')=0


Fulltext definition functions with example


SELECT EMP_NAME FROM EMPLOYEE , CONTAINSTABLE(EMPLOYEE,EMP_NAME,'CHANDR') AS TabName


SELECT EMP_NAME FROM EMPLOYEE WHERE FREETEXT(EMP_NAME,'CHAN')

SELECT EMP_NAME FROM EMPLOYEE WHERE CONTAINS(EMP_NAME,'CHAN')

SELECT RANK()OVER(ORDER BY Description),Description FROM EMPLOYEE

How to find current version of the sql server

SELECT @@VERSION

Remove duplicate records from table in sql server

Query shows how to select duplicate records with SL No.


SELECT ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME) SLNO,EMP_NAME FROM EMPLOYEE



Query for remove duplicate records from table


WITH REMDUPREC
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME) SLNO,EMP_NAME FROM EMPLOYEE
)

DELETE FROM REMDUPREC WHERE SLNO>1

delete from MyTable
where uniqueField not in
(select min(uniqueField) from MyTable T2
where T2.dupField=MyTable.dupField)

OPENQUERY AND OPENROWSET IN SQL SERVER

OPENQUERY: Used to execute the query passed on the linked server.

Syntax: OPENQUERY (Linked_server_identifier, query).

It can also be refernced from a FROM clause of selecte query.

e.g. Select * from OPENQUERY (Oracleserver, ‘select fname, FROM Employee);

OPENROWSET: Used to access tables in a linked server. It can be used one time for accessing remote data by using OLE DB. It includes all the information required to access the remote data.

Syntax:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

Advantages and disadvantages of views in sql server

Advantages of view

1. Data Security

2. Simplicity

3. No Space

4. Hide data and columns from base tables

5. You can easily use a view to create new information out of existing data, without changing the (legacy) database structure

6. Complex queries can be stored in the from of view

Disadvantages of view

1. Querying from view takes more time than directly querying from the table

2. It will have to do the query a new, every time it is opened

3. when table is not there view will not work or view become inactive

4. View can be create only if there is SELECT Permission on its base table

5. views can not derived the data from temp tables

6.Query defining view can not include ORDER BY,COMPUTE,INTO Keyword

7.You can not define full text index definitions on views