Monday, 16 September 2013

Auto Numbering in Grid View in Asp.net


Add S.No column in grid view 

                         
                            <asp:TemplateField HeaderText="SNO">
                               <ItemTemplate>
                                   <%# Container.DataItemIndex + 1 %>
                                </ItemTemplate>
                            </asp:TemplateField>                    
                       

Output:

                 




Posted by at No comments:

Friday, 13 September 2013

Convert Excel From SQL Data Base in C# Windows Application

Step 1:

First, you need to add Microsoft.Office.Interop.Excel as a reference 




Step 2:

private void GridDesign()
{
            dgvAdd.Columns.Add("docno", "Doc_No");
            dgvAdd.Columns.Add("cname", "Consignee");
            dgvAdd.Columns.Add("phone", "Phone_No");
            dgvAdd.Columns.Add("pin", "Pin_Code");
}

Step 3:

namespace Data_Entry_Application
{
    public partial class Report : Form
    {        
        string uid = "";
        int srows = 0;
        string conn = ConfigurationSettings.AppSettings["constr"];
        public Report()
        {
            InitializeComponent();
            GridDesign();
        }

Step 4:

Convert Excel Button Coding

private void butExcel_Click(object sender, EventArgs e)
{
            dgvAdd.Rows.Clear();
             SqlConnection con = new SqlConnection(conn);
            con.Open();
            if (radioAll.Checked == true)
            {
                string fdate = "";
                string tdate = "";

                string find = "";
                if (radioPall.Checked == true)
                {
                    find = "select DocNo,Consignee,PhoneNo,Pincode from BluStar_Details order by Pincode";
                }
                else if (radioPdate.Checked == true)
                {
                    int year1 = dtpdate1.Value.Date.Year;
                    int month1 = dtpdate1.Value.Date.Month;
                    int day1 = dtpdate1.Value.Date.Day;
                    fdate = datetimeconvert(year1, month1, day1);

                    int year2 = dtpDate2.Value.Date.Year;
                    int month2 = dtpDate2.Value.Date.Month;
                    int day2 = dtpDate2.Value.Date.Day;
                    tdate = datetimeconvert(year2, month2, day2);

                    find = "select DocNo,Consignee,PhoneNo,Pincode from BluStar_Details where (DocDate BETWEEN '" + fdate + "' AND '" + tdate + "') order by Pincode";
                }
                SqlCommand cmd1234 = new SqlCommand(find, con);
                SqlDataReader dr = cmd1234.ExecuteReader();
                while (dr.Read())
                {
                    string val = dr["DocNo"].ToString();
                    string val1 = dr["Consignee"].ToString();
                    string val2 = dr["PhoneNo"].ToString();
                    string val3 = dr["Pincode"].ToString();
                    dgvAdd.Rows.Add(val, val1, val2, val3);
                }
                dr.Close();          


                if (dgvAdd.Rows.Count > 0)
                {
                    Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                    Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                    Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                    app.Visible = true;

                    try
                    {
                        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
                        worksheet.Name = "Exported from DataGridView";

                        for (int i = 1; i < dgvAdd.Columns.Count + 1; i++)
                        {
                            worksheet.Cells[1, i] = dgvAdd.Columns[i - 1].HeaderText;
                        }

                        for (int i = 0; i < dgvAdd.Rows.Count - 1; i++)
                        {
                            for (int j = 0; j < dgvAdd.Columns.Count; j++)
                            {
                                worksheet.Cells[i + 2, j + 1] = dgvAdd.Rows[i].Cells[j].Value.ToString();
                            }
                        }

                        string fileName = String.Empty;
                        saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*";
                        saveFileExcel.FilterIndex = 2;
                        saveFileExcel.RestoreDirectory = true;

                        if (saveFileExcel.ShowDialog() == DialogResult.OK)
                        {
                            fileName = saveFileExcel.FileName;

                            workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                        }
                        else
                            return;

                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        app.Quit();
                        workbook = null;
                        app = null;
                    }
                }
                else
                {
                    MessageBox.Show("No Records");
                }
Posted by at No comments:

Friday, 6 September 2013

Using String Builder to Bulk Insert and Update Data's in Asp.net


//Design gridview
  DataTable dt = new DataTable();
  DataRow dr = null;

  dt.Columns.Add(new DataColumn("CustName", typeof(string)));
  dt.Columns.Add(new DataColumn("CustPosition", typeof(string)));
  dt.Columns.Add(new DataColumn("CustCity", typeof(string)));
  dt.Columns.Add(new DataColumn("CustState", typeof(string)));

  gvCustomer.DataSource = dt;
  gvCustomer.DataBind();


//Add button Insert to gridview
private void Insert()
{ 
      DataTable dt = new DataTable();
      DataRow dr = null;
      dr = dt.NewRow();
      dr["CustName"] = txtName.Text.ToString();
      dr["CustPosition"] = txtPos.Text.ToString();
      dr["CustCity"] = txtCity.Text.ToString();
      dr["CustState"] = txtState.Text.ToString();
      dt.Rows.Add(dr);
      gvCustomer.DataSource = dt;
      gvCustomer.DataBind();
}

//Save button using string builder

 protected void btnSave_Click(object sender, EventArgs e)
 {
   StringBuilder sb = new StringBuilder();
   sb.Append("<root>"); 
   for (int i = 0; i < gvCustomer.Rows.Count; i++)
   {    
    string Name= gvCustomer.Rows[i].Cells[0].Text.ToString();
    string Position = gvCustomer.Rows[i].Cells[1].Text.ToString();
    string City = gvCustomer.Rows[i].Cells[2].Text.ToString();
    string State= gvCustomer.Rows[i].Cells[3].Text.ToString();
   
    if(Name != "")
      sb.Append("<row Name='" + Name + "' Position='" + Position + "' City='" +         City + "' State='" + State + "'/>");
   }
   sb.Append("</root>");

  string conStr = WebConfigurationManager.
  ConnectionStrings["BlogConnectionString"].ConnectionString;
  SqlConnection con = new SqlConnection(conStr);
  //InsertCustomer as Stored Procedure
  SqlCommand cmd = new SqlCommand("InsertCustomer", con);
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@XMLCustomer", sb.ToString());
 try
 {
   using (con)
   {
     con.Open();
     cmd.ExecuteNonQuery();
   }

   lblError.Text = "Record(s) Inserted successfully";
   lblError.ForeColor = System.Drawing.Color.Green;
 }
 catch (Exception ex)
 {
  lblError.Text = "Error Occured";
  lblError.ForeColor = System.Drawing.Color.Red;
  }
 }


//Stored Procedure Insert Query in Sql server
CREATE PROCEDURE [dbo].[InsertCustomer]
(
 @XMLCustomer XML
)
AS
BEGIN

      INSERT INTO CUSTOMER
             (CustName,CustPosition,CustCity,CustState)             
      SELECT
            TempCustomer.Item.value('@Name', 'VARCHAR(50)'),
            TempCustomer.Item.value('@Position', 'VARCHAR(50)'),
            TempCustomer.Item.value('@City', 'VARCHAR(50)'),
            TempCustomer.Item.value('@State', 'VARCHAR(50)')
      FROM @XMLCustomer.nodes('/root/row') AS TempCustomer(Item)

RETURN 0
END

//Upadate button Same Process of Save button differnt stored procedure
//Stored Procedure Update Query in Sql server

CREATE PROCEDURE [dbo].[UpdateCustomer]
(
 @XMLCustomer XML
)
AS
BEGIN

      UPDATE Customer
            SET CustName=TempCustomer.Item.value('@Name', 'VARCHAR(50)'),
            CustPosition=TempCustomer.Item.value('@Position', 'VARCHAR(50)'),
            CustCity=TempCustomer.Item.value('@City', 'VARCHAR(50)'),
            CustState=TempCustomer.Item.value('@State', 'VARCHAR(50)')
      FROM @XMLCustomer.nodes('/root/row') AS TempCustomer(Item)
      WHERE CustID=TempCustomer.Item.value('@CustID', 'INT')

RETURN 0
END


 




Posted by at No comments:

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.














Posted by



at





No comments:
  


































        

      









Subscribe to:
Posts (Atom)