Using LINQ to SQL Template - Part Two

This post has reference to part one of this Article.

Create a new Class with the name "MyLinq". We are going to use the DataContextFactory Class which we created in Part One. Also assume in our database we have Table with name "User" and with the following Definition.

Read More

Using LINQ to SQL Template - Part One

LINQ to SQL is a component of the .NET Framework version 3.5. Click to learn more about LINQ to SQL

First of all, in your Project or Website, add a new item from the "LINQ to SQL" template. This will create a file with extention ".dbml" and code behind files. Now from Server Explorer, drag and drop the required Tables from the DB. At this point, we will be able to save the Server Connection String to connect to the Database automatically. If its a Web application, it will be saved in web.config and for Windows Project, it will be saved in Settings.settings file.

Read More

How to Convert Datatable to XML programmatically

Lets assume our requirement is to create a Data Table and convert it to XML format. Use the following block code for your purpose.

DataSet myDS = new DataSet();

DataTable dtMyTable = new DataTable("preview");

DataColumn myCol0 = new DataColumn("facility");

myCol0.DataType = System.Type.GetType("System.String");

myCol0.MaxLength = 256;
myCol0.AllowDBNull = true;

DataColumn myCol1 = new DataColumn("doctype");
myCol1.DataType = System.Type.GetType("System.String");
myCol1.MaxLength = 256;
myCol1.AllowDBNull = true;

dtMyTable.Columns.Add(myCol0);
dtMyTable.Columns.Add(myCol1);
dtMyTable.AcceptChanges();

DataRow myNewRow = dtMyTable.NewRow();
myNewRow["facility"] = "MyFacility Works Great!";
myNewRow["doctype"] = "MyDocType Field Does Too!";
dtMyTable.Rows.Add(myNewRow);
dtMyTable.AcceptChanges();

myDS.Tables.Add(dtMyTable);

myDS.WriteXml(Server.MapPath("~") + "\\sample.xml");

Read More

Guide to Excel Reporting with MS SQL data using Crystal Report

First of all you need to create a New Data Set object from the Add new Item. Inside the Data set you need to create a new Table Adapter which will Fill data to a Data Table.

Then you need to create a new Crystal Report File from Add new Item. In the Crystal report, from the Field Explorer -> Database Fields, now you will able to see the created Data Table with all the Column Elements selected.

Select the Column Elements needed, and add them to your Crystal Report and arrange them to your specific need. Using Crystal Reports->Design->Printer Setup, you can adjust the size of the Crystal Report according to your need. Also to make it a template for Excel, you need to Suppress the Page Footer from the Section Expert.

Then the below given Block of code is used for Excel Reporting.

try {

TempDataSetTableAdapters.TempTableAdapter temp = new MyProject.TempDataSetTableAdapters.TempTableAdapter();
TempDataSet.TempDataTable tempTable = new TempDataSet.TempDataTable();

DataTable DT = tempTable;
temp.Fill(tempTable);

ReportDocument rpt = new ReportDocument();
rpt.Load(Server.MapPath("~") + "\\TempReport.rpt");
rpt.SetDataSource(DT);

Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();

rpt.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "Report");

}

catch (Exception ex){

}

Read More