Thursday, 5 September 2013

Insert,delete and update using LinQ in Asp.net

Step 1:
Create a new Web application project:


(or)

File --> New Website


Step 2:

Create a Web Form:
<asp:Button ID="btnView" runat="server" Text="View" OnClick="btnView_Click" />
    <asp:Button ID="btnAddNew" runat="server" Text="Add New" OnClick="btnAddNew_Click" />
    <table id="tblForm" runat="server">
        <tr>
            <td>Country Name:</td>
            <td><asp:TextBox ID="txtCountryName" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Country Code:</td>
            <td><asp:TextBox ID="txtCountryCode" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnSave" runat="server" Text="Add" 
  OnClick="btnSave_Click" />
                <asp:Button ID="btnUpdate" runat="server" Text="Update" 
  OnClick="btnUpdate_Click" />
                <asp:Button ID="btnCancel" runat="server" Text="Cancel" 
  OnClick="btnCancel_Click" />
            </td>
        </tr>
    </table>
    <table id="tblGrid" runat="server">
        <tr>
            <td><asp:Label ID="lblID" runat="server" Visible="false"></asp:Label>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" 
  OnRowCommand="GridView1_RowCommand">
                    <Columns>
                        <asp:BoundField DataField="Countryid" HeaderText="ID" />
                        <asp:BoundField DataField="CountryName" 
   HeaderText="Country Name" />
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:LinkButton ID="lnkEdit" runat="server" 
    CommandArgument='<%# Eval("Countryid")%>'
                                    CommandName="cmdEdit" Text="Edit"></asp:LinkButton>
                                  ---  
                                <asp:LinkButton ID="lnkDelete" runat="server" 
    CommandArgument='<%# Eval("Countryid")%>'
                                    CommandName="cmdDelete" Text="Delete">
        </asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
            </td>
        </tr>
    </table>

Step 3:

Add a new item – Linq to SQL classes:

Step 4:

Create a database table -- tblCountry:
CREATE TABLE [dbo].[tblCountry](
 [Countryid] [int] IDENTITY(1,1) NOT NULL,
 [CountryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [CountryCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [Status] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 
 CONSTRAINT [PK_tblCountry] PRIMARY KEY CLUSTERED 
(
 [Countryid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Step 5:

Connect the database to the project:

Step 6:

Select the table for Linq table integration:
To change a .dbml Name

Step 7:

Set database connection string in web.config file:
<appSettings>
  <add key="constr" value="Data Source = machinename\SQLEXPRESS;
 Initial Catalog=test;User ID=tuser; password=tpass"/>
</appSettings>

Step 8:

Write Insert record method:
protected void btnSave_Click(object sender, EventArgs e)
        {
            using(DLCountryiesDataContext countries = new DLCountryiesDataContext())
            {
                tblCountry country = new tblCountry
                 {
                     CountryName = txtCountryName.Text.Trim(),
                     CountryCode = txtCountryCode.Text.Trim(),
                     Status = "Active"
                 };
                countries.Connection.ConnectionString = 
  System.Configuration.ConfigurationManager.AppSettings["constr"];
                countries.tblCountries.InsertOnSubmit(country);
                countries.SubmitChanges();
                ViewData();
            }
        }

Step 9:

Write method View Records in Gridview:
private void ViewData()
        {
            ClearForm();
            DLCountryiesDataContext db = new DLCountryiesDataContext();
            db.Connection.ConnectionString = 
  System.Configuration.ConfigurationManager.AppSettings["constr"];
            var country =
                from c in db.tblCountries
                select c;

            GridView1.DataSource = country;
            GridView1.DataBind();
        }

Step 10:

Write methods for edit and delete records:
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "cmdEdit")
            {
                ClearForm();
                DLCountryiesDataContext db = new DLCountryiesDataContext();
                db.Connection.ConnectionString = 
  System.Configuration.ConfigurationManager.AppSettings["constr"];
                var country =
                    from c in db.tblCountries
                    where (c.Countryid == Convert.ToInt32(e.CommandArgument.ToString()))
                    select c;
                lblID.Text = e.CommandArgument.ToString();
                foreach (tblCountry cntry in country)
                {
                    txtCountryName.Text = cntry.CountryName;
                    txtCountryCode.Text = cntry.CountryCode;
                }
                btnSave.Visible = false;
                btnUpdate.Visible = true;
                ShowForm();
} if (e.CommandName == "cmdDelete") { DLCountryiesDataContext db = new DLCountryiesDataContext(); db.Connection.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["constr"]; var country = from c in db.tblCountries where (c.Countryid == Convert.ToInt32(e.CommandArgument.ToString())) select c; db.tblCountries.DeleteAllOnSubmit(country); db.SubmitChanges(); ViewData(); } }

Step 11:

Write method for update record:
protected void btnUpdate_Click(object sender, EventArgs e)
        {
            DLCountryiesDataContext db = new DLCountryiesDataContext();
            db.Connection.ConnectionString = 
  System.Configuration.ConfigurationManager.AppSettings["constr"];

            var country = db.tblCountries.Single
  (p => p.Countryid == Convert.ToInt32(lblID.Text));
            country.CountryName = txtCountryName.Text.Trim();
            country.CountryCode = txtCountryCode.Text.Trim();
            db.SubmitChanges();
            ViewData();
        }

Step 12:

Common methods for page (it's not mandatory to use in page):
protected void btnView_Click(object sender, EventArgs e)
        {         
            ViewData();
            HideForm();
        }

        protected void btnAddNew_Click(object sender, EventArgs e)
        {
            ShowForm();
            btnSave.Visible = true;
            btnUpdate.Visible = false;            
        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {   
            ViewData();
            HideForm();
        }
        
        private void HideForm()
        {
            tblForm.Visible = false;
            tblGrid.Visible = true;
            btnView.Visible = false;
            btnAddNew.Visible = true;            
        }
        
        private void ShowForm()
        {
            tblForm.Visible = true;
            tblGrid.Visible = false;
            btnView.Visible = true;
            btnAddNew.Visible = false;
        }
        
        private void ClearForm()
        {
            txtCountryCode.Text = "";
            txtCountryName.Text = "";
        }


Step 13:

Run the project.
End.

Gridview Pagination in Asp.net

Grid View Properties:

 gridview we need to set AllowPaging="true" and PageSize="5" Properties


<asp:GridView ID="gvEmpDetails" runat="server" CssClass="Gridview" AllowPaging="true" PageSize="4"HeaderStyle-BackColor="#7779AF" HeaderStyle-ForeColor="White" DataSourceID="dsEmpdetails">

<PagerSettings Mode="NumericFirstLast" PageButtonCount="4"  
FirstPageText="First" LastPageText="Last"/> 

<PagerStyle BackColor="#7779AF" Font-Bold="true" ForeColor="White" /> 


</asp:GridView>


NextPreviousFirstLast: If we want to use this mode for paging in gridview we need to change thePagerSettings mode like this

<PagerSettings Mode="NextPreviousFirstLast" PageButtonCount="4" PreviousPageText="Previous"
NextPageText="Next" FirstPageText="First" LastPageText="Last" />


Textbox allows only Numbers with dots and spaces

Design:

<asp:Content ID="Content1" ContentPlaceHolderID="contentPlaceHolder" Runat="Server">
<script type="text/javascript">
//Numbers and dots
  function isNumberKey(evt) {
      var charCode = (evt.which) ? evt.which : event.keyCode
      if (charCode != 46 && (charCode < 48 || charCode > 57)) {
          return false;
      }
      else {
          return true;
      }
  }        

//Number and spaces
function isNumberKey1(evt) {
      var charCode = (evt.which) ? evt.which : event.keyCode
      if (charCode != 32 && (charCode < 48 || charCode > 57)) {
          return false;
      }
      else {
          return true;
      }
  }          
</script>
<asp:TextBox ID="txtMFGQty" runat="server" Height="22px" Width="224px" onkeypress= "return isNumberKey(event)"
                        MaxLength="12"></asp:TextBox>

<asp:TextBox ID="txtPhoneNo" onkeypress= "return isNumberKey1(event)" runat="server" Width="200px" MaxLength="12"></asp:TextBox>






Saved Successfully in Asp.net

ScriptManager.RegisterStartupScript(this, typeof(Page), "Alert", "<script>alert('" + "Saved Sucessfully" + "');</script>", false);

How to page load to data's in Dropdown box and Casecading dropdown boxes

Design:



Coding:


protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
                 LoadRawMeterialGroup();
        }
}

//Material Group

 private void LoadRawMeterialGroup()
    {
        DataTable dt = new DataTable();
        dt = RM.GetRawMeterialGroup();
        if (dt.Rows.Count > 0)
        {
            DrpMaterialGroup.Items.Clear();
            DrpMaterialGroup.DataSource = dt;
            DrpMaterialGroup.DataValueField = "rmgrp_code";
            DrpMaterialGroup.DataTextField = "rmgrp_name";
            DrpMaterialGroup.DataBind();
            DrpMaterialGroup.Items.Insert(0, new ListItem("", ""));
        }
    }

//Material Item Code

private void LoadMCode()
    {
        DataTable dt = new DataTable();
        dt = RM.GetRawMaterialMaster();
        if (dt.Rows.Count > 0)
        {
            DrpMCode.Items.Clear();
            DrpMCode.DataSource = dt;
            DrpMCode.DataValueField = "rm_code";
            DrpMCode.DataTextField = "rm_itemcode";
            DrpMCode.DataBind();
            DrpMCode.Items.Insert(0, new ListItem("", ""));
        }
    }

//Material Name

    private void LoadMName()
    {
        DataTable dt = new DataTable();
        dt = RM.GetRawMaterialMaster();
        if (dt.Rows.Count > 0)
        {
            DrpMName.Items.Clear();
            DrpMName.DataSource = dt;
            DrpMName.DataValueField = "rm_code";
            DrpMName.DataTextField = "rm_name";
            DrpMName.DataBind();
            DrpMName.Items.Insert(0, new ListItem("", ""));
        }
    }

To Set in properties:

AutoPostback = true;
SelectedIndexChanged = DrpMaterialGroup_SelectedIndexChanged;

//Material Group

protected void DrpMaterialGroup_SelectedIndexChanged(object sender, EventArgs e)
    {
        string dmg = DrpMaterialGroup.SelectedValue.ToString().Trim();
        int mg = 0;
        if (dmg != "")
        {
            mg = Convert.ToInt32(dmg);
        }

        if (mg > 0)
        {
            DataTable dt = new DataTable();
            dt = RM.MGGetRawMaterialMaster(mg);
            if (dt.Rows.Count > 0)
            {
                DrpMCode.Items.Clear();
                DrpMCode.DataSource = dt;
                DrpMCode.DataValueField = "rm_code";
                DrpMCode.DataTextField = "rm_itemcode";
                DrpMCode.DataBind();
                DrpMCode.Items.Insert(0, new ListItem("", ""));
            }
            else
            {
                DrpMCode.Items.Clear();
            }

            DataTable dt1 = new DataTable();
            dt1 = RM.MGGetRawMaterialMaster(mg);
            if (dt1.Rows.Count > 0)
            {
                DrpMName.Items.Clear();
                DrpMName.DataSource = dt1;
                DrpMName.DataValueField = "rm_code";
                DrpMName.DataTextField = "rm_name";
                DrpMName.DataBind();
                DrpMName.Items.Insert(0, new ListItem("", ""));
            }
            else
            {
                DrpMName.Items.Clear();
            }
        }
        else
        {
            LoadMCode();
            LoadMName();
        }
    }

//Material item Code

protected void DrpMCode_SelectedIndexChanged(object sender, EventArgs e)
    {
        string dmc = DrpMCode.SelectedValue.ToString().Trim();
        int mc = 0;
        if (dmc != "")
        {
            mc = Convert.ToInt32(dmc);
        }

        if (mc > 0)
        {
            DataTable dt = new DataTable();
            dt = RM.CodeGetRawMaterialMaster(mc);
            if (dt.Rows.Count > 0)
            {
                DrpMaterialGroup.SelectedValue = dt.Rows[0]["rm_rmgrp_Code"].ToString();
                DrpMName.SelectedValue = dt.Rows[0]["rm_code"].ToString();
                txtUom.Text = dt.Rows[0]["rm_uom_name"].ToString();  
            }
        }
        else
        {
            LoadRawMeterialGroup();
        }
    }






How to add data's to Gridview temporarily using ViewState in Asp.net


Sample Design:

Design coding:

<asp:GridView ID="grdMateriallist" runat="server" Width="981px" 
                        AutoGenerateColumns="False" onrowcommand="grdMateriallist_RowCommand" 
                        onrowdeleting="grdMateriallist_RowDeleting" 
                        onrowediting="grdMateriallist_RowEditing">
                        <Columns>
                            <asp:BoundField DataField="rmname" HeaderText="Material Name" />
                            <asp:BoundField DataField="icode" HeaderText="Item Code" />
                            <asp:TemplateField HeaderText="Material Code" Visible="False">
                                <ItemTemplate>
                                        <asp:Label ID="rmcode" runat="server" Text='<%#(Eval("rmcode")) %>'>                                                 </asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>                           
                            <asp:BoundField DataField="rmqty" HeaderText="Material Qty" />
                            <asp:BoundField DataField="uom" HeaderText="UOM" />
                            <asp:TemplateField HeaderText="Edit">
                                <ItemTemplate>
                                    <asp:LinkButton ID="Edit" runat="server" CausesValidation="false" 
                                        CommandArgument="Edit" CommandName="Edit">Edit</asp:LinkButton>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Delete">
                                <ItemTemplate>
                                    <asp:LinkButton ID="Delete" runat="server" CausesValidation="false" 
                                        CommandArgument="Delete" CommandName="Delete" 
                                        OnClientClick="return confirm('Are you sure to delete this                                                                           row?');">Delete</asp:LinkButton>
                                </ItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                    </asp:GridView>

In Page Load :

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
                GrdRawMaterial();
        }
    }

To add New Function :

private void GrdRawMaterial()
    {
        DataTable dt = new DataTable();       
        dt.Columns.Add("rmname");
        dt.Columns.Add("icode");
        dt.Columns.Add("rmcode");
        dt.Columns.Add("rmqty");
        dt.Columns.Add("uom");

        ViewState["TempTable"] = dt;
    }

To Click Add Button:

        string rmname = DrpMName.SelectedItem.Text.ToString().Trim();
        string icode = DrpMCode.SelectedItem.Text.ToString().Trim();
        string rmcode1 = DrpMName.SelectedValue.ToString().Trim();
        string uom = txtUom.Text.ToString().Trim();
        string qty1 = txtKLQuantity.Text.ToString().Trim();

        double qty = 0;
        if (qty1 != "")
        {
            qty = Convert.ToDouble(qty1);
        }
        int rmcode = 0;
        if (rmcode1 != "")
        {
            rmcode = Convert.ToInt32(rmcode1);
        }
        int rowid = -1;
        if (lblRowno.Text != "")
        {
            rowid = Convert.ToInt32(lblRowno.Text.ToString());
        }
        
        DataTable dt = new DataTable();
        DataRow dr;

        if (ViewState["TempTable"] == null)
        {            
            dt.Columns.Add("rmname");
            dt.Columns.Add("icode");
            dt.Columns.Add("rmcode");
            dt.Columns.Add("rmqty");
            dt.Columns.Add("uom");
        }
        else
        {
            dt = (DataTable)ViewState["TempTable"];
        }

        if (rowid == -1)
        {
            dr = dt.NewRow();
            dr["rmname"] = rmname;
            dr["icode"] = icode;
            dr["rmcode"] = rmcode;
            dr["rmqty"] = qty;
            dr["uom"] = uom;
            dt.Rows.Add(dr);
            ViewState["TempTable"] = dt;

            grdMateriallist.DataSource = (DataTable)ViewState["TempTable"];
            grdMateriallist.DataBind();
            materiallistclear();
        }
        else
        {
            dr = dt.Rows[rowid];
            dr["rmname"] = rmname;
            dr["icode"] = icode;
            dr["rmcode"] = rmcode;
            dr["rmqty"] = qty;
            dr["uom"] = uom;
            dt.AcceptChanges();
            ViewState["TempTable"] = dt;

            grdMateriallist.DataSource = (DataTable)ViewState["TempTable"];
            grdMateriallist.DataBind();
            materiallistclear();
        }


To Edit and Delete Link Coding:

protected void grdMateriallist_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Edit")
        {
            GridViewRow row = (GridViewRow)((Control)e.CommandSource).Parent.Parent;
            int RowIndex = row.RowIndex;
            lblRowno.Text = RowIndex.ToString();

            Label idc = (Label)grdMateriallist.Rows[row.RowIndex].Cells[2].FindControl("rmcode");
            DrpMName.SelectedValue = idc.Text;            
            txtKLQuantity.Text = grdMateriallist.Rows[RowIndex].Cells[3].Text.ToString().Trim();
            txtUom.Text = grdMateriallist.Rows[RowIndex].Cells[4].Text.ToString().Trim();
            butAdd.Text = "Change";
        }
        else if (e.CommandName == "Delete")
        {
            GridViewRow row = (GridViewRow)((Control)e.CommandSource).Parent.Parent;
            int RowIndex = row.RowIndex;  
           
            DataTable dt = new DataTable();  

            dt = (DataTable)ViewState["TempTable"];
            dt.Rows[RowIndex].Delete();
            grdMateriallist.DataSource = dt;
            grdMateriallist.DataBind();
            materiallistclear();
        }
    }
    protected void grdMateriallist_RowEditing(object sender, GridViewEditEventArgs e)
    {

    }
    protected void grdMateriallist_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    }

After Save Button To Destroy a Viewstate:

private void GrdBranchDesignclear()
    {
        DataTable dt = new DataTable();        
        dt.Columns.Add("rmname");
        dt.Columns.Add("icode");
        dt.Columns.Add("rmcode");
        dt.Columns.Add("rmqty");
        dt.Columns.Add("uom");

        ViewState["TempTable"] = dt;

        grdMateriallist.DataSource = "";
        grdMateriallist.DataBind();
    } 


Output:





Tuesday, 27 August 2013

Export To PDF File(Crystal Report To Pdf file) in windows application

//Saved Temptable data's After below a Query to use Export to pdf file
//To create pdf file not in C:\\(Security problems are faced).

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

            ReportDocument cryRpt;
            cryRpt = new ReportDocument();
            cryRpt.Load("F:/WindowsProjects/TASS_ItemCode/IBS_STOCK/IBS_STOCK/CRLedger.rpt");
            crystalReportViewer1.ReportSource = cryRpt;
            crystalReportViewer1.Refresh();  
            try
            {
                ExportOptions CrExportOptions;
                DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
                PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
                CrDiskFileDestinationOptions.DiskFileName = "D:\\test.pdf";
                CrExportOptions = cryRpt.ExportOptions;
                {
                    CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                    CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
                    CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
                    CrExportOptions.FormatOptions = CrFormatTypeOptions;
                }
                cryRpt.Export();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }