Monday, 19 August 2013

How to Insert, Edit, Update and Delete Data in DataGridView using Windows Form C#.Net

To implement this concept you need to follow the below steps :

Step1 :
First you need to design a table in Sql Database to insert and retrieve the records in database.

Step2:
On the File menu, click New Project. Select Windows Forms Application as your project type.
Design the Form using controls from Toolbox.

Step3:
Now open the Form.cs page and write the following source code.

Form.cs Code :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;

namespace savedatatomdf
{
    public partial class Form1 : Form
    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Sqlcon"].ConnectionString);
        public Form1()
        {
            InitializeComponent();
            Bind();

        }

        private void Clear()
        {
            txtName.Text = string.Empty;
            txtLocation.Text = string.Empty;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("Insert Into Test_Data(Name,Location) Values (@Name,@Location)", con);
            cmd.Parameters.AddWithValue("Name", txtName.Text);
            cmd.Parameters.AddWithValue("Location", txtLocation.Text);
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Inserted sucessfully");
            Bind();
            Clear();
        }

        private void Bind()
        {
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Test_Data", con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            con.Close();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            SqlCommand delcmd = new SqlCommand();
            if (dataGridView1.Rows.Count > 1 && dataGridView1.SelectedRows[0].Index != dataGridView1.Rows.Count - 1)
            {
                delcmd.CommandText = "DELETE FROM Test_Data WHERE ID=" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";
                con.Open();
                delcmd.Connection = con;
                delcmd.ExecuteNonQuery();
                con.Close();
                dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                MessageBox.Show("Row Deleted");
            }
            Bind();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("Update Test_Data set Name=@Name,Location=@Location Where(Name=@Name)", con);
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Location", txtLocation.Text);
            cmd.ExecuteNonQuery();
            MessageBox.Show("updated......");

            con.Close();
            Bind();
            Clear();
        }

        private void btnEdit_Click_1(object sender, EventArgs e)
        {
            int i;
            i = dataGridView1.SelectedCells[0].RowIndex;
            txtName.Text = dataGridView1.Rows[i].Cells[1].Value.ToString();
            txtLocation.Text = dataGridView1.Rows[i].Cells[2].Value.ToString();
        }
    }
}



Step4:
Now build the Solution and Debug it for the output.



OUTPUT:






Tuesday, 13 August 2013

Gridview Sorting and Paging in asp.net C#

Design:

<asp:GridView ID="gdvRecords" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AllowPaging="True" PageSize="5" OnPageIndexChanging="gdvRecords_PageIndexChanging" AllowSorting="True" OnSorting="gdvRecords_Sorting">  </asp:GridView>





Paging:

     protected void gdvRecords_PageIndexChanging(object sender, GridViewPageEventArgs e)
     {
            GridView grdsample = (GridView)sender; //Get Current Gridview Object
            grdsample.PageIndex = e.NewPageIndex; //Set Page Index

            SqlConnection sqlcon = new SqlConnection(onnectionString);
            SqlCommand sqlcmdinsert = new SqlCommand("select * from Table",sqlcon);
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlcmdinsert);         
            sqlda.Fill(dsAllData);
            gdvRecords.DataSource = dsAllData.Tables[0];
            gdvRecords.DataBind();
     }

Sorting:
   private const string ASCENDING = " ASC";
   private const string DESCENDING = " DESC";

   public SortDirection GridViewSortDirection
   {
        get
        {
            if (ViewState["sortDirection"] == null)
                ViewState["sortDirection"] = SortDirection.Ascending;

                return (SortDirection)ViewState["sortDirection"];
          }

            set { ViewState["sortDirection"] = value; }
   }


        private void SortGridView(string sortExpression, string direction)
        {
            DataTable dt;
            if (chkAll.Checked == true)
            {
                bindAllData();
                dt = dsAllData.Tables[0];
            }
            else
            {
                bindSearchData();
                dt = dsSearch.Tables[0];
            }
            //  You can cache the DataTable for improving performance
          

            DataView dv = new DataView(dt);
            dv.Sort = sortExpression + direction;

            gdvRecords.DataSource = dv;
            gdvRecords.DataBind();
        }

        protected void gdvRecords_Sorting(object sender, GridViewSortEventArgs e)
        {
            string sortExpression = e.SortExpression;

            if (GridViewSortDirection == SortDirection.Ascending)
            {
                GridViewSortDirection = SortDirection.Descending;
                SortGridView(sortExpression, DESCENDING);
            }
            else
            {
                GridViewSortDirection = SortDirection.Ascending;
                SortGridView(sortExpression, ASCENDING);
            }
        }


Monday, 12 August 2013

Generate excel from datatable



 public static void ExportGridView(string fileName, DataTable A_objData)
        {
            try
            {
                DataTable objDataTable = A_objData;
                string attachment = string.Format("attachment; filename={0}", fileName);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.AddHeader("content-disposition", attachment);
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                System.Text.StringBuilder strOrderList = new System.Text.StringBuilder(1000);
                string strTest = "";
                int iColumnCount = A_objData.Columns.Count;
                for (int iIndex = 0; iIndex < iColumnCount; iIndex++)
                    strTest = strTest + objDataTable.Columns[iIndex].ToString() + "\t";
                strTest = strTest + "\n";
                strOrderList.Append(strTest);

                foreach (DataRow rows in objDataTable.Rows)
                {
                    strOrderList.Append(GetTabSeperatedString(rows, iColumnCount));
                }
                byte[] info = new System.Text.UTF8Encoding(true).GetBytes(strOrderList.ToString());

                HttpContext.Current.Response.Buffer = true;
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.ClearHeaders();
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
                HttpContext.Current.Response.BinaryWrite(info);
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private static string GetTabSeperatedString(DataRow A_objDataTableRow, int A_iColumnCount)
        {
            string strRowValue = "";
            string strColumnValue = "";

            for (int iIndex = 0; iIndex < A_iColumnCount; iIndex++)
            {
                strColumnValue = A_objDataTableRow[iIndex].ToString();
                strColumnValue = strColumnValue.Replace("\n", " ");
                strColumnValue = strColumnValue.Replace("\r", " ");
                strColumnValue = strColumnValue.Replace("\t", " ");
                if (strColumnValue == "&nbsp;")
                    strColumnValue = " ";
                strRowValue += strColumnValue + "\t";
            }
            strRowValue += "\n";
            return strRowValue;
        }

Html 5 Registration page


//Styles


 <style>
        .signup-box {
            background: rgb(241, 241, 241);
            margin: 0px;
            padding: 25px;
            border: 1px solid rgb(229, 229, 229);
        }

        .inputFields {
            border-top-width: 1px;
            border-right-width: 1px;
            border-bottom-width: 1px;
            border-left-width: 1px;
            border-top-style: solid;
            border-left-style: solid;
            border-top-color: #bdc7d8;
            border-right-color: #bdc7d8;
            border-bottom-color: #bdc7d8;
            border-left-color: #bdc7d8;
            border-top-left-radius: 5px;
            border-top-right-radius: 5px;
            padding-top: 8px;
            padding-left: 10px;
            padding-bottom: 8px;
            padding-right: 10px;
            font-size: 18px;
        }

        .btnSave {
            color: #6BA64A;
            background-color: #6BA64A;
            border: 1px solid;
            border-radius: 5px 5px 5px 5px;
            color: rgb(255, 255, 255);
            cursor: pointer;
            display: inline-block;
            letter-spacing: 1px;
            position: relative;
            text-shadow: 0px 1px 2px rgba(0, 0, 0, 0.5);
            background: -moz-linear-gradient(center top, rgb(103, 174, 85), rgb(87, 136, 67)) repeat scroll 0% 0% rgb(105, 167, 78);
            box-shadow: 0px 1px 1px rgb(164, 227, 136) inset;
            border-color: rgb(59, 110, 34) rgb(59, 110, 34) rgb(44, 81, 21);
            font-size: 19px;
            line-height: 126%;
            min-width: 194px;
            padding: 7px 20px;
            text-align: center;
            font-family: 'Freight Sans Bold' !important;
            font-weight: normal !important;
            text-rendering: optimizelegibility;
        }

    </style>





<div class="signup-box" style="width:500px;border-top-right-radius:10px;border-top-left-radius:10px;border-bottom-left-radius:10px;border-bottom-right-radius:10px">

       <asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label>
      <br />
  <fieldset>
  <legend><strong></strong></legend>
      <table>
         <tr>
            <td><input class="inputFields" type="text" runat="server" id="txtFirstName" placeholder="First Name" ></input>
            </td>
            <td style="margin-left:20px"><input type="text" runat="server" class="inputFields" id="txtLastName" placeholder="Last Name" ></input></td>
         </tr>
         <tr>
             <td colspan="2"><input style="width:430px"  type="text" runat="server" id="txtEducation" class="inputFields" placeholder="Education" ></input>
             </td>
         </tr>
         <tr>
            <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtDesignation" class="inputFields" placeholder="Designation" ></input>
            </td>
         </tr>
     </table>
  </fieldset>
       <br />
   <fieldset>
      <legend><strong></strong></legend>
        <table>
            <tr>      
                <td colspan="2"><input style="width:430px" class="inputFields" type="text" runat="server" id="txtcompanyname" placeholder="Company Name" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input style="width:430px"  type="text" runat="server" id="txtemailcompany" class="inputFields" placeholder="Email id (company)" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtemailpersonal" class="inputFields" placeholder="Email id (Personal)" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtweburl" class="inputFields" placeholder="Web URL" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtsector" class="inputFields" placeholder="Sector" ></input>
                </td>
            </tr>
        </table>
 </fieldset>
 <br />
  <fieldset>
      <legend><strong></strong></legend>
        <table>
            <tr>      
                <td colspan="2"><input style="width:430px" class="inputFields" type="text" runat="server" id="txtAddress1" placeholder="Address 1" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input style="width:430px"  type="text" runat="server" id="txtAddress2" class="inputFields" placeholder="Address 2" ></input>
                </td>
            </tr>
            <tr>
           
                <td colspan="2"><input style="width:430px"  type="text" runat="server" id="txtcountry" class="inputFields" placeholder="Country" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input style="width:430px"  type="text" runat="server" id="txtstate" class="inputFields" placeholder="State" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input style="width:430px"  type="text" runat="server" id="txtcity" class="inputFields" placeholder="City" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtPinCode" class="inputFields" placeholder="Pin Code" ></input>
             
            </tr>
        </table>
 </fieldset>
      <br />
 <fieldset>
      <legend><strong></strong></legend>
        <table>
            <tr>      
                <td colspan="2"><input style="width:430px" class="inputFields" type="text" runat="server" id="txtteloffice" placeholder="Tel - Office" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input style="width:430px"  type="text" runat="server" id="txtExt" class="inputFields" placeholder="Ext" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtDirectNum" class="inputFields" placeholder="Direct Number" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtMobileNum" class="inputFields" placeholder="Mobile Number" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtMobileNum2" class="inputFields" placeholder="Mobile number 2" ></input>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="text" style="width:430px" runat="server" id="txtFax" class="inputFields" placeholder="Fax" ></input>
                </td>
            </tr>
        </table>
 </fieldset>
  <br />
      <table>
          <tr>
              <td> <asp:Button ID="btnSave" runat="server" CssClass="btnSave" Text="Save" OnClick="btnSave_Click" /></td>
              <td> <asp:Button ID="btnGenerateReport" runat="server" CssClass="btnSave" Text="Generate Report" OnClick="btnGenerateReport_Click" /></td>
          </tr>
      </table>
  </div>