Dataview filter for null values in a column using C#

Load data to DataTable from backend And Filter from Code behind

DataTable dt2= null,
  DataTable dt3= null,

strFilter = "ColumnName IS NOT NULL";
                        DataView dv = dt2.AsDataView();
                        dv.RowFilter = strFilter;
                        dt3= dv.ToTable();
                        dt3.AcceptChanges();

Bind data to gridview with JQuery or JSON in asp.net

In Default.aspx page

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
<script type="text/javascript">
         function Disp(obj) {
            // debugger;
            $.ajax({
                type: "POST",
                contentType: "application/json; charset=utf-8",
                url: "Default.aspx/BindDatatable",
                data: '{ID:"' + obj + '"}',
                dataType: "json",
                success: function (data) {
                    // debugger;
                    if (data.d.length == 0) {
                      
                    }
                    else {                      
                        $("#ctl00_CPHBody_gvDisplay").show();                               
                         $("#ctl00_CPHBody_gvDisplay tr:has(td)").remove();                      
                        for (var i = 0; i < data.d.length; i++) {
                            $("#ctl00_CPHBody_gvDisplay").append("<tr><td width='35%'>" + data.d[i].Code + "</td><td width='45%'>" + data.d[i].Description + "</td><td width='30%'>" + data.d[i].Date + "</td><td width='20%'>" + data.d[i].Seq + "</td></tr>");
                        }
                           }
                },
                error: function (result) {
                    alert("Error");
                }
            });
        }

    <asp:Button ID="btnAdd" runat="server" Text="Add" OnClientClick="Disp(2)" />
    <table width="100%">
                <tr>
                    <td>
                        <asp:GridView ID="gvDisplay" runat="server">
                            <HeaderStyle CssClass="tdheader" />
                            <RowStyle CssClass="trwhite" />
                        </asp:GridView>
                    </td>
                </tr>
            </table>

In Default.aspx.cs file
[WebMethod]
        public static UserDetails[] BindDatatable(string ID)
        {
            DataTable dt = new DataTable();
            List<UserDetails> details = new List<UserDetails>();
            if (ID != "undefined")
            {
                dt = "fetch data from database";
            }
            foreach (DataRow dtrow in dt.Rows)
            {
                UserDetails user = new UserDetails();
                user.FundCode = dtrow["Code"].ToString();
                user.FundDescription = dtrow["Description"].ToString();
                user.FundDate = dtrow["Date"].ToString();
                user.Seq = dtrow["Seq"].ToString();
                details.Add(user);
            }
            return details.ToArray();

        }
        public class UserDetails
        {
            public string Code { get; set; }
            public string Description { get; set; }
            public string Date { get; set; }
            public string Seq { get; set; }
        }

Adding Rows Dynamically to GridView in asp.net

Dynamically we can rows to gridview in asp.net please follow below code

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

           DataTable dt = new DataTable();

            DataRow dr = null;
            dt.Columns.Add(new DataColumn("Column Name", typeof(DataType)));
            dt.Columns.Add(new DataColumn(" Column Name ", typeof( DataType )));

            dt.Columns.Add(new DataColumn(" Column Name ", typeof( DataType )));

            dt.Columns.Add(new DataColumn(" Column Name ", typeof( DataType )));

            dr = dt.NewRow();
            GridBind();
}
}



private void GridBind()
    {
        DataSet ds = new DataSet();
        SqlConnection con = new SqlConnection("Connection string");
        SqlDataAdapter dap = new SqlDataAdapter("Store Procedure", con);
        dap.SelectCommand.CommandType = CommandType.StoredProcedure;
        dap.Fill(ds);
        grd.DataSource = ds;
        grd.DataBind();

}


protected void btnAdd_Click(object sender, EventArgs e)
    {   
        AddNewRowToGrid();        
    }


  private void AddNewRowToGrid()
    {
        int rowIndex = 0;
        if (ViewState["CurrentTable"] != null)
        {

            DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];

            DataRow drCurrentRow = null;

            if (dtCurrentTable.Rows.Count > 0)
            {
                DataTable dt2 = new DataTable();
                SqlConnection con = new SqlConnection("Connectionstring");
                SqlCommand cmd = new SqlCommand("spName", con);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                cmd.CommandType = CommandType.StoredProcedure;
                adp.Fill(ds);
                dt2 = ds.Tables[0];

                for (int i = 0; i <= dtCurrentTable.Rows.Count-1; i++)
                {

                    //extract the TextBox values
                 TextBox box0 = (TextBox)grd.Rows[rowIndex].Cells[0].FindControl("ControlName");             
                  TextBox box1 = (TextBox) grd.Rows[rowIndex].Cells[1].FindControl("ControlName");
                              
              DropDownList  ddl =(DropDownList) grd.Rows[rowIndex].FindControl("ControlName");                 
                     ddl  .DataSource = dt2;
                     ddl  .DataValueField = "value";
                     ddl  .DataTextField = "Text";
                     ddl  .DataBind();
                     ddl  .Items.Insert(0, new ListItem("-- Select --", "-1"));         
                    TextBox box3 = (TextBox) grd.Rows[rowIndex].Cells[3].FindControl("ControlName");         
                    drCurrentRow = dtCurrentTable.NewRow();           
             
                    dtCurrentTable.Rows[i]["ColumnName"] = box0.Text;

                    dtCurrentTable.Rows[i][" ColumnName "] = box1.Text;

                    dtCurrentTable.Rows[i][" ColumnName "] = ddl.SelectedItem.Text;

                    dtCurrentTable.Rows[i][" ColumnName "] = box3.Text;

                    rowIndex++;
                }
               //

                dtCurrentTable.Rows.InsertAt(drCurrentRow, 0);

                ViewState["CurrentTable"] = dtCurrentTable;

                grd.DataSource = dtCurrentTable;
                 grd .EditIndex = 0;             
                 grd .DataBind();
            }
        }

        else
        {

            Response.Write("ViewState is null");

        }
        //Set Previous Data on Postbacks

        SetPreviousData();

    }
    private void SetPreviousData()
    {
        int rowIndex = 0;
        BindDropDownlist();
        if (ViewState["CurrentTable"] != null)
        {

            DataTable dt = (DataTable)ViewState["CurrentTable"];

            if (dt.Rows.Count > 0)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //extract the TextBox values
                 TextBox box0 = ( TextBox  )grd.Rows[rowIndex].Cells[0].FindControl("ControlName");             
                 TextBox  box1 = ( TextBox  )grd.Rows[rowIndex].Cells[1].FindControl(" ControlName ");           
                TextBox  box2 = ( TextBox  )grdAdmin.Rows[rowIndex].Cells[2].FindControl("ControlName ");               
               TextBox  box3 = ( TextBox  )grdAdmin.Rows[rowIndex].Cells[2].FindControl(" ControlName ");
                 
                    if (i != 0)
                    {
            DropDownList ddl = (DropDownList)grd.Rows[rowIndex].FindControl("ControlName");
                        ddlAdminList.SelectedItem.Text = box2.Text;
                    }
               
                    box0.Text = dt.Rows[i]["ColumnName"].ToString();
                    box1.Text = dt.Rows[i][" ColumnName "].ToString();

                    box2.Text = dt.Rows[i][" ColumnName "].ToString();

                    box3.Text = dt.Rows[i][" ColumnName "].ToString();             
                    rowIndex++;

                }

            }

        }

    }

Data bind Dropdownlist in asp.net

Fill drop down list dynamically using c# in asp.net.We need to write method inside IspostBack condition to avoid multiple times binding.Below Example using Dictionary collection to fill asp.net dropdownlist,we can fetch data from database as well.

protected void Page_Load(object sender, EventArgs e)

   {

       firstName = "vinod";

       if (!IsPostBack)

       {

           fillDropdownlist();

       }

   }

   public void fillDropdownlist()

   {

       Dictionary<int, string> obj = new Dictionary<int, string>();

       obj.Add(1, "vinod");

       obj.Add(2, "hari");

       

       DropDownList1.DataValueField = "Key";

       DropDownList1.DataTextField = "Value";

       DropDownList1.DataSource = obj;

       DropDownList1.DataBind();

     DropDownList1.Items.Insert(0, "--Select--");

   }

    City : 

    <asp:DropDownList ID="DropDownList1" runat="server">                                   

    </asp:DropDownList>  

          

 

              

Data Base connection in ado.net

A SqlConnection is an object, just like any other C# object.  Most of the time, you just declare and instantiate the SqlConnection.
Ado.net  will contain following steps.

1. Instantiate the SqlConnection.
2. Open the connection.
3. Pass the connection to other ADO.NET objects.
4. Perform database operations with the other ADO.NET objects.
5. Close the connection.

using (SqlConnection connection = new SqlConnection(connectionString))
       {
           // Create the Command and Parameter objects.
           SqlCommand command = new  SqlCommand("Employee_Insert" 

, connection);
           command.Parameters.AddWithValue("@CityName", txtCity.Text);
           command.Parameters.AddWithValue("@Name", txtName.Text);
           command.CommandType = CommandType.StoredProcedure; 
           try{
               connection.Open();
               command.ExecuteNonQuery();
           }
           catch (SqlException ex){
               throw ex;
           }
           catch (Exception ex){
               throw ex;
           }
           finally{
               connection.Close();
           }
       } 

Database connection in ado.net