Insert record in database using 3-Tier Architecture asp.net

Insert data in database using 3 Tier Architecture .
In this article i am explaining you how to insert record in database using 3-Tier Architecture.You can create separate project for Business and data access layer or also can keep in App_code folder.

This is aspx.cs code.Here code communicate through Business Layer.

protected void btnSubmit_Click(object sender, EventArgs e)
{
objClientCls.ClName = txtCoName.Text;
objClientCls.AddLine1 = txtAddLine1.Text;
objClientCls.AddLine2 = txtAddLine2.Text;
try
{
if (objClientCls.ClId == 0)
{
string Msg = objClBLL.InsertClMaster(objClientCls);
lblMessage.Text = Msg;
Clear();
}
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
finally
{
objClBLL = null;
}
}

Business Layer Code

public string InsertClMaster(ClMasterCls objClMasterCls)
{
try
{
return objClMasterDAL.Insert(objClMasterCls);
}
catch
{
throw;
}
finally
{
objClMasterDAL = null;
}
}

Data Acess layer code
public string Insert(ClMasterCls objClMasterCls)
{
try
{
conn.Open();
string CreateMsg = Convert.ToString(SqlHelper.ExecuteScalar(conn, "usp_InsertClMaster", objClMasterCls.ClId, objClMasterCls.ClName, objClMasterCls.AddLine1
, objClMasterCls.AddLine2));
return Msg;
}
catch
{
throw;
}
finally
{
conn.Close();
}
}

Thanks & Regards
Santosh Singh

Read More

Export Gridview to Excel and PDF file using asp.net and c#

In this article i am explaining you how to display report using aspx page.Exporting Gridview to excel and PDF report is one of the most common requirement in real world.So i have written this code,you can customize ConvertDataInPdf method according to your requirement.

aspx code.
<asp:GridView ID="GridView1" runat="server" EnableTheming="False" OnRowDataBound="GridView1_RowDataBound"
Width="100%">
<HeaderStyle CssClass="ReportHeader" Width="80px" /
<RowStyle CssClass="ReportText" />
<PagerStyle CssClass="ReportHeader" />
<AlternatingRowStyle CssClass="ReportText" />
<EmptyDataRowStyle CssClass="ReportText" />
<EmptyDataTemplate>
<span style="font-size: 11pt; color: #ff0033"><strong>No Records ........!!</strong></span>
</EmptyDataTemplate>
<Columns>
<asp:BoundField HeaderText="Sr. No." />
</Columns>
</asp:GridView>

.cs code
string conStr = FetchConnection.FetchConnectionString().ToString();
void GridViewEmp()
{
DataSet Ds = new DataSet();
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
string str = "SELECT * from EmployeeTbl"
Ds = SqlHelper.ExecuteDataset(conStr, CommandType.Text, str);
GridView1.DataSource = Ds;
GridView1.DataBind();
}

protected void lbExportInExcel_Click1(object sender, ImageClickEventArgs e)
{
string attachment = "attachment; filename=" + "Emp Report" + ".xls";
Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
reportName.RenderControl(htmlWrite);
rowInfo.RenderControl(htmlWrite);
rowGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

protected void btnPdf_Click(object sender, ImageClickEventArgs e)
{
PrintAndExport exportDataInPdf = new PrintAndExport();
string reportName = "Employee Loan Report" + " ( " +lbl.Text+ " ) ";
string OtherInfo = "Company Name: " + ddlCompany.SelectedItem.Text + "\t\t\t" + "Emp Status: " + ddlStatus.SelectedItem.Text;
DataTable dt = ExportData();
exportDataInPdf.ConvertDataInPdf(dt, reportName, OtherInfo);
}


public void ConvertDataInPdf(DataTable dtExportInPdf, string reportName, string OtherInfo)
{
try
{
float CompanyNameSize = 11;
float ReportNameSize = 9;
float HeaderTextSize = 7;
float ReportTextSize = 6;

int totalWidth = 0;
int tableWidthPercent = 100;
int[] widths = new int[dtExportInPdf.Columns.Count];

for (int h = 0; h < dtExportInPdf.Columns.Count; h++) //Data table header column width
{
string strWidth = dtExportInPdf.Columns[h].ToString();
widths[h] = strWidth.Length;
}

foreach (DataRow dr in dtExportInPdf.Rows) //Data table column width
{
int[] colItemWidth = new int[dtExportInPdf.Columns.Count];
for (int i = 0; i < dtExportInPdf.Columns.Count; i++) //Data table max item width
{
if (dr[i].ToString().Length > 20)
{
colItemWidth[i] = 20;
}
else if (dr[i].ToString().Length < 3)
{
colItemWidth[i] = 3;
}
else
{
colItemWidth[i] = dr[i].ToString().Length;
}

if (colItemWidth[i] > widths[i])
{
widths[i] = colItemWidth[i];
}
}
}
for (int h = 0; h < dtExportInPdf.Columns.Count; h++)
{
totalWidth += widths[h];
}
Document pdfDoc = null;
if (totalWidth > 0 && totalWidth <= 110)
{
pdfDoc = new Document(PageSize.A4, 20, 20, 20, 20);
}
else if (totalWidth > 110 && totalWidth <= 160)
{
pdfDoc = new Document(PageSize.A4.Rotate(), 20, 20, 20, 20);
}
else if (totalWidth > 160 && totalWidth <= 250)
{
HeaderTextSize = 6;
ReportTextSize = 5;
pdfDoc = new Document(PageSize.LEGAL.Rotate(), 20, 20, 20, 20);

}
else if (totalWidth > 250 && totalWidth <= 300)
{
CompanyNameSize = 9;
ReportNameSize = 7;
HeaderTextSize = 6;
ReportTextSize = 5;
pdfDoc = new Document(PageSize.B1, 20, 20, 20, 20);
}
else if (totalWidth > 300)
{
CompanyNameSize = 9;
ReportNameSize = 7;
HeaderTextSize = 6;
ReportTextSize = 5;
pdfDoc = new Document(PageSize.B1.Rotate(), 20, 20, 20, 20);
}

// Creates a PdfPTable with column count of the table equal to no of columns of the datatable or gridview or gridview datasource.
PdfPTable pdfTable = new PdfPTable(dtExportInPdf.Columns.Count);
pdfTable.WidthPercentage = tableWidthPercent;
pdfTable.HeaderRows = 4; // Sets the first 4 rows of the table as the header rows which will be repeated in all the pages.

#region PDFHeader
PdfPTable headerTable = new PdfPTable(3); // Creates a PdfPTable with 3 columns to hold the header in the exported PDF.
byte[] logo = (byte[])System.Web.HttpContext.Current.Session["Logo"];
iTextSharp.text.Image imgLogo = iTextSharp.text.Image.GetInstance(logo);
imgLogo.ScaleToFit(80f, 40f);//Resize image depend upon your need
imgLogo.SpacingBefore = 0f;//Give space before image
imgLogo.SpacingAfter = 1f;//Give some space after the image
PdfPCell clLogo = new PdfPCell(imgLogo);// Creates a PdfPCell which accepts a phrase as a parameter.
clLogo.Border = PdfPCell.NO_BORDER;// Sets the border of the cell to zero.
clLogo.HorizontalAlignment = Element.ALIGN_LEFT;// Sets the Horizontal Alignment of the PdfPCell to left.
clLogo.VerticalAlignment = Element.ALIGN_MIDDLE;

// Creates a phrase to hold the application name at the left hand side of the header.
Phrase phApplicationName = new Phrase("" + System.Web.HttpContext.Current.Session["CompanyName"] + "", FontFactory.GetFont("Arial", CompanyNameSize, iTextSharp.text.Font.NORMAL));
PdfPCell clApplicationName = new PdfPCell(phApplicationName);// Creates a PdfPCell which accepts a phrase as a parameter.
clApplicationName.Border = PdfPCell.NO_BORDER;// Sets the border of the cell to zero.
clApplicationName.HorizontalAlignment = Element.ALIGN_CENTER;// Sets the Horizontal Alignment of the PdfPCell to left.
clApplicationName.VerticalAlignment = Element.ALIGN_MIDDLE;

// Creates a phrase to show the current date at the right hand side of the header.
Phrase phDate = new Phrase(DateTime.Now.Date.ToString("dd/MM/yyyy"), FontFactory.GetFont("Arial", 7, iTextSharp.text.Font.NORMAL));
PdfPCell clDate = new PdfPCell(phDate);// Creates a PdfPCell which accepts the date phrase as a parameter.
clDate.HorizontalAlignment = Element.ALIGN_RIGHT;// Sets the Horizontal Alignment of the PdfPCell to right.
clDate.Border = PdfPCell.NO_BORDER;// Sets the border of the cell to zero.
clDate.VerticalAlignment = Element.ALIGN_MIDDLE;

headerTable.AddCell(clLogo);
headerTable.AddCell(clApplicationName);// Adds the cell which holds the application name to the headerTable.
headerTable.AddCell(clDate);// Adds the cell which holds the date to the headerTable.
headerTable.DefaultCell.Border = PdfPCell.NO_BORDER;// Sets the border of the headerTable to zero.

// Creates a PdfPCell that accepts the headerTable as a parameter and then adds that cell to the main PdfPTable.
PdfPCell cellHeader = new PdfPCell(headerTable);
cellHeader.VerticalAlignment = Element.ALIGN_TOP;
cellHeader.Border = PdfPCell.NO_BORDER;
cellHeader.Colspan = dtExportInPdf.Columns.Count;// Sets the column span of the header cell to noOfColumns.
pdfTable.AddCell(cellHeader);// Adds the above header cell to the table.
#endregion PDFHeader

//Creates a phrase for a new line.
Phrase phSpace1 = new Phrase("\n");
PdfPCell clSpace1 = new PdfPCell(phSpace1);
clSpace1.Border = PdfPCell.BOTTOM_BORDER;
clSpace1.BorderWidth = 1;
clSpace1.BorderColor = iTextSharp.text.Color.DARK_GRAY;
clSpace1.Colspan = dtExportInPdf.Columns.Count;
pdfTable.AddCell(clSpace1);

// Creates a phrase to hold the report name.
Phrase phHeader = new Phrase("" + reportName + "", FontFactory.GetFont("Arial", ReportNameSize, iTextSharp.text.Font.NORMAL));
PdfPCell clHeader = new PdfPCell(phHeader);
clHeader.Colspan = dtExportInPdf.Columns.Count;
clHeader.Border = PdfPCell.NO_BORDER;
clHeader.HorizontalAlignment = Element.ALIGN_CENTER;
clHeader.VerticalAlignment = Element.ALIGN_MIDDLE;
clHeader.PaddingTop = 5;
clHeader.PaddingBottom = 2;
pdfTable.AddCell(clHeader);

//Create Phrage to hold other informations
Phrase phOtherInfo = new Phrase(OtherInfo, FontFactory.GetFont("Arial", ReportTextSize, iTextSharp.text.Font.NORMAL));
PdfPCell cellOtherInfo = new PdfPCell(phOtherInfo);
cellOtherInfo.Colspan = dtExportInPdf.Columns.Count;
cellOtherInfo.Border = Element.ALIGN_LEFT;
cellOtherInfo.PaddingBottom = 10;
pdfTable.AddCell(cellOtherInfo);

PdfWriter.GetInstance(pdfDoc, System.Web.HttpContext.Current.Response.OutputStream);
string strFooter = "Copyright © 2010 By Cnergee. Page:";
Phrase phCopyright = new Phrase(strFooter, FontFactory.GetFont(FontFactory.TIMES_ROMAN, 8, iTextSharp.text.Font.NORMAL));
Phrase phPageNo = new Phrase("", FontFactory.GetFont(FontFactory.TIMES_ROMAN, 8, iTextSharp.text.Font.NORMAL));
HeaderFooter footer = new HeaderFooter(phCopyright, phPageNo);
//footer.Alignment = Element.ALIGN_LEFT;
footer.Alignment = Element.ALIGN_RIGHT;
footer.Border = iTextSharp.text.Rectangle.TOP_BORDER;
footer.GrayFill = 10;
pdfDoc.Footer = footer;

pdfDoc.Open();
Font font8 = FontFactory.GetFont("ARIAL Narrow", 7);
if (dtExportInPdf != null)
{
pdfDoc.Header = null;
//Create header for pdf table
string cloName = null;
Phrase ph = null;
for (int i = 0; i < dtExportInPdf.Columns.Count; i++)
{
cloName = dtExportInPdf.Columns[i].ColumnName;
if (dtExportInPdf.Columns.Count > 0)
{
ph = new Phrase(cloName, FontFactory.GetFont("Arial", HeaderTextSize, iTextSharp.text.Font.BOLD));
}
else
{
ph = new Phrase(cloName, FontFactory.GetFont("Arial", HeaderTextSize, iTextSharp.text.Font.BOLD));
}
pdfTable.AddCell(ph);
}

//Add data into the pdf table
for (int rows = 0; rows < dtExportInPdf.Rows.Count; rows++)
{
ph = null;
PdfPCell pCell = null;
for (int column = 0; column < dtExportInPdf.Columns.Count; column++)
{
ph = new Phrase(dtExportInPdf.Rows[rows][column].ToString(), FontFactory.GetFont("Arial", ReportTextSize, iTextSharp.text.Font.NORMAL));
pCell = new PdfPCell(ph);
if (dtExportInPdf.Columns[column].ColumnName == "SrNo" || dtExportInPdf.Columns[column].ColumnName == "Sr.No." || dtExportInPdf.Columns[column].ColumnName == "Sr. No." || dtExportInPdf.Columns[column].ColumnName == "Code" || dtExportInPdf.Columns[column].ColumnName == "EmpCode" || dtExportInPdf.Columns[column].ColumnName == "EmployeeCode")
{
pCell.HorizontalAlignment = Element.ALIGN_CENTER;
}
else if (dtExportInPdf.Columns[column].ColumnName == "Amount")
{
pCell.HorizontalAlignment = Element.ALIGN_RIGHT;
}
else if (dtExportInPdf.Columns[column].ColumnName == "Date" || dtExportInPdf.Columns[column].ColumnName == "From" || dtExportInPdf.Columns[column].ColumnName == "To")
{
pCell.HorizontalAlignment = Element.ALIGN_CENTER;
}
else
{
pCell.HorizontalAlignment = Element.ALIGN_LEFT;
}
pdfTable.AddCell(pCell);
}
pdfTable.SetWidths(widths);
}
pdfTable.SpacingBefore = 15f; // Give some space after the text or it may overlap the table
pdfDoc.Add(pdfTable); // add pdf table to the document
}
pdfDoc.Close();

string pdfFileName = reportName;
reportName = reportName.Replace(" ", "");
System.Web.HttpContext.Current.Response.ContentType = "application/pdf";
System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename= " + pdfFileName + ".pdf");
System.Web.HttpContext.Current.Response.Write(pdfDoc);

System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
}
catch (DocumentException de)
{
System.Web.HttpContext.Current.Response.Write(de.Message);
}
catch (IOException ioEx)
{
System.Web.HttpContext.Current.Response.Write(ioEx.Message);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write(ex.Message);
}
}

public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the
//specified ASP.NET server control at run time.
}

Thanks & Regards
Santosh

Read More

Find tables that contain a certain field in database

Some times user need to get information about a table field,means how many table contains this field. A table field eg 'xyz' have relationship with many table and you want to see all table.Write below query.

select * from information_schema.columns where column_name = 'xyz'

Thanks & Regards
Santosh

Read More