Popular Posts

There was an error in this gadget

Oct 22, 2013

Exporting to Excel in Crystal Reports .NET



Export crystal report to excel in asp.net 

ReportDocument crystalReport;

protected void Page_Load(object sender, EventArgs e)
    {
       
        crystalReport = new ReportDocument();
        crystalReport.Load(Server.MapPath("GenReportMaster.rpt"));
       
        //Set DataBase Login Info
        crystalReport.SetDatabaseLogon("sa", "Nopass123", @"BD-DAC-014", "lamolinsDB");
        string year = (Request.QueryString["year"] != "") ? Request.QueryString["year"] : "";
        string month = (Request.QueryString["month"] != "") ? Request.QueryString["month"] : "";
        string zoneString = Request.QueryString["zone"];
        string zone = (zoneString.Equals("All")) ? "" : zoneString;
        string branchString = Request.QueryString["branch"];
        string branch = (branchString.Equals("All")) ? "" : branchString;
        string brand = (Request.QueryString["brand"]!= "") ? Request.QueryString["brand"] : "";




        crystalReport.SetParameterValue("@month", month);
        crystalReport.SetParameterValue("@year", year);
        crystalReport.SetParameterValue("@zone", zone);
        crystalReport.SetParameterValue("@branch", branch);
        crystalReport.SetParameterValue("@brand", brand);
        CwrVatCrystalReportViewer.Visible = true;
        CwrVatCrystalReportViewer.DisplayGroupTree = false;
        CwrVatCrystalReportViewer.HasPageNavigationButtons = true;
        CwrVatCrystalReportViewer.HasCrystalLogo = false;
        CwrVatCrystalReportViewer.HasDrillUpButton = false;
        CwrVatCrystalReportViewer.HasSearchButton = false;
        CwrVatCrystalReportViewer.HasViewList = false;
        CwrVatCrystalReportViewer.HasToggleGroupTreeButton = false;
        CwrVatCrystalReportViewer.HasZoomFactorList = false;
        CwrVatCrystalReportViewer.ToolbarStyle.Width = new Unit("750px");


        CwrVatCrystalReportViewer.ReportSource = crystalReport;
       
    }



Convert to excel:

protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            ExportOptions CrExportOptions;

            DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
            ExcelFormatOptions CrFormatTypeOptions = new ExcelFormatOptions();
            CrDiskFileDestinationOptions.DiskFileName = "c:\\report.xls";
            CrExportOptions = crystalReport.ExportOptions;
            CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
            CrExportOptions.ExportFormatType = ExportFormatType.Excel;
            CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
            CrExportOptions.FormatOptions = CrFormatTypeOptions;
            crystalReport.Export();
            messageLabel.Text = "Download excel successful...";
        }
        catch (Exception ex)
        {
            messageLabel.Text = "Download excel failed..."+ex.ToString();
        }

    }



Prevent websites from injection in asp.net






string queryStr = "select email, fullname, from t_employee where username = user_name ='" + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";
 
To prevent from query injection we can change it by using stored procedures or 
parameterised TSQL
 

string queryStr = "select email, fullname from t_employee where user_name = @USER and user_date between @startdate and @enddate'";

cmd.Parameters.Add("@USER", SqlDbType.NVarChar, 100);  -- Assuming type and size
cmd.Parameters["@USER"].Value = username
cmd.Parameters.Add("@startdate ", SqlDbType.DateTime);
cmd.Parameters["@startdate "].Value = startdate ;
cmd.Parameters.Add("@enddate", SqlDbType.DateTime);
cmd.Parameters["@enddate"].Value = enddate;

Oct 7, 2013

Store Excel Data To DataTable And Copy To SQLServer Database using SqlBulkCopy


This method stores the data to datatable and shows to GridView.

private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
        string conStr="";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();

        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath);
        GridView1.DataSource = dt;
        GridView1.DataBind();

        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();
        BulkInsertDataTable(“databaseTable”, dt);


 }

This method insert the data from datatable to sqlserver.

public bool BulkInsertDataTable(string tableName, DataTable dataTable)
    {
        bool isSuccuss;
        string connString = ConfigurationManager.ConnectionStrings["db_connection_string"].ConnectionString;
        SqlConnection SqlConnectionObj = new SqlConnection(connString);
       
        try
        {  
            SqlConnectionObj.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionObj, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.WriteToServer(dataTable);
            isSuccuss = true;
        }
        catch (Exception ex)
        {
            isSuccuss = false;
        }
        finally
        {
            SqlConnectionObj.Close();
        }
        return isSuccuss;
    }