DataListControl Continuing..

  public void FillDataList()
        {
            Test1BL oTest1BL = new Test1BL();
            dlistTest.DataSource = oTest1BL.GetDetails();
            dlistTest.DataBind();          
        }

        protected void dlistTest_EditCommand(object source, DataListCommandEventArgs e)
        {          
            dlistTest.EditItemIndex = e.Item.ItemIndex;
            FillDataList();
        }

        protected void dlistTest_UpdateCommand(object source, DataListCommandEventArgs e)
        {
            int ID = Convert.ToInt32(((Label)e.Item.FindControl("lblID")).Text);
            string Name = ((TextBox)e.Item.FindControl("txtName")).Text;
            int Age = Convert.ToInt32(((TextBox)e.Item.FindControl("txtAge")).Text);

            Test1BL oTest1BL = new Test1BL();
            oTest1BL.UpdateData(ID, Name, Age);
            dlistTest.EditItemIndex = -1;
            FillDataList();
        }
protected void dlistTest_CancelCommand(object source, DataListCommandEventArgs e)
        {
            dlistTest.EditItemIndex = -1;
            FillDataList();
        }

        protected void dlistTest_DeleteCommand(object source, DataListCommandEventArgs e)
        {
            int ID = Convert.ToInt32(((Label)e.Item.FindControl("lblIDDelete")).Text);
            Test1BL oTest1BL = new Test1BL();
            oTest1BL.DeleteData(ID);
            dlistTest.EditItemIndex = -1;
            FillDataList();
        }

DataList Control ASPX

<asp:DataList ID="dlistTest" runat="server" Style="width: 30%" AlternatingItemStyle-BackColor="AliceBlue" BorderWidth="2px" OnEditCommand="dlistTest_EditCommand"
onupdatecommand="dlistTest_UpdateCommand" oncancelcommand="dlistTest_CancelCommand"
 ondeletecommand="dlistTest_DeleteCommand">
 <HeaderTemplate>
 <table style="width: 100%">
<tr><td style="width: 40%">Name </td><td style="width: 40%">Age</td>
<td style="width: 20%"></td></tr></table>
 </HeaderTemplate>
<ItemTemplate>
 <table style="width: 100%"> <tr> <td style="width: 30%">
 <asp:Label runat="server" ID="lblName" Text='<%#Eval("Name")%>'></asp:Label>
 </td><td style="width: 30%">
 <asp:Label runat="server" ID="lblAge" Text='<%#Eval("Age")%>'></asp:Label></td>
<td style="width: 40%">
<asp:Label runat="server" ID="lblIDDelete" Text='<%#Eval("ID")%>' ></asp:Label>
<asp:LinkButton runat="server" ID="lbtnEdit" CommandName="Edit">Edit</asp:LinkButton>&nbsp;&nbsp;
<asp:LinkButton runat="server" ID="lbtnDelete" CommandName="Delete">Delete</asp:LinkButton>
 </td></tr></table>
</ItemTemplate>
<EditItemTemplate>
 <table style="width: 100%">
 <tr><td style="width: 10%">Name</td>
<td style="width: 20%">
<asp:TextBox runat="server" ID="txtName" Text='<%#Bind("Name")%>' Width="60px"></asp:TextBox>
</td>
<td style="width: 10%">Age </td>
<td style="width: 20%">
<asp:TextBox runat="server" ID="txtAge" Text='<%#Bind("Age")%>' Width="60px"></asp:TextBox>
</td>
<td style="width: 40%">
<asp:Label runat="server" ID="lblID" Text='<%#Eval("ID")%>' ></asp:Label></td>
</tr><tr><td></td><td></td>
<td colspan="3">
<asp:Button runat="server" ID="btnUpdate" CommandName="Update" Text="Update" />
 <asp:Button runat="server" ID="btnCancel" CommandName="Cancel" Text="Cancel" />
</td></tr></table>
</EditItemTemplate>
</asp:DataList>

Declaring OUT parameter in SQL and to retrive it

public int CheckalreadyExists(string Name)
        {
            param = new SqlParameter[2];
            param[0] = new SqlParameter("@Name", Name);
            param[1] = new SqlParameter("@IsFound", SqlDbType.Int);
            param[1].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteDataset(oDBConnection.ConString(), CommandType.StoredProcedure, "SPR_CheckalreadyExists", param);
            return Convert.ToInt32(param[2].Value.ToString());
        }

CREATE PROCEDURE SPR_CheckalreadyExists(
    @ID INT,   
    @IsFound TINYINT OUT
)
AS
BEGIN
    IF EXISTS(SELECT ID FROM StudentTable WHERE Name = @Name )
        BEGIN
            SET @IsFound = 1
        END
    ELSE
        BEGIN
            SET @IsFound = 0
        END
END

Repeater Control Sample 2

STEP 1
<asp:Repeater runat="server" ID="reptrID2">
            <HeaderTemplate>
                Data :
            </HeaderTemplate>
            <ItemTemplate>
                <%#DataBinder.Eval(Container.DataItem,"Name") %>
                (<%#DataBinder.Eval(Container.DataItem,"Age") %>)
            </ItemTemplate>
            <SeparatorTemplate>
                ,
            </SeparatorTemplate>
</asp:Repeater>

STEP 2
Just bind it....
public void FillRepeater2()
 {
    Test1BL oTest1BL = new Test1BL();
    reptrID2.DataSource = oTest1BL.GetDetails();
    reptrID2.DataBind();
  }

Repeater Control Sample 1

STEP 1
<asp:Repeater ID="reptrID" runat="server">
            <HeaderTemplate>
                <table>
                    <tr><th>Name</th><th>Age</th></tr>
            </HeaderTemplate>
            <ItemTemplate>
                <tr><td><%#DataBinder.Eval(Container.DataItem,"Name") %></td>
                    <td><%#DataBinder.Eval(Container.DataItem,"Age") %></td></tr>
            </ItemTemplate>
            <FooterTemplate>
                </table>
            </FooterTemplate>
        </asp:Repeater>
STEP 2
Just Bind it to DB
public void FillRepeater()
 {
    Test1BL oTest1BL = new Test1BL();
    reptrID.DataSource = oTest1BL.GetDetails();
    reptrID.DataBind();
 }

Generate random characters

public string GetRandomPasswordUsingGUID(int length)
        {
            // Get the GUID
            string guidResult = System.Guid.NewGuid().ToString();
            // Remove the hyphens
            guidResult = guidResult.Replace("-", string.Empty);
            // Make sure length is valid
            if (length <= 0 || length > guidResult.Length)
                throw new ArgumentException("Length must be between 1 and " + guidResult.Length);
            // Return the first length bytes
            return guidResult.Substring(0, length);
        }

Save file onto a folder(on solution) while upload

 //To Save the file
string test = Server.MapPath("~/Files/");
FileUpload1.PostedFile.SaveAs(test + FileUpload1.PostedFile.FileName);
//To delete the file
//add System.IO namespace for the "File" attribute
File.Delete(Server.MapPath("~/Files/" + FileUpload1.PostedFile.FileName));




<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Button" onclick="btnUpload_Click" />

Save and Retreive Cookie

Reference : http://msdn.microsoft.com/en-us/library/ms178194.aspx

MODE1: To single cookie value
STEP1: Save
          //METHOD 1
            /*
            HttpContext.Current.Response.Cookies["Test123"].Value = Name;
            HttpContext.Current.Response.Cookies["Test123"].Expires = DateTime.Now.AddDays(2);
             */
            //METHOD 2
              
            HttpCookie oCookie = new HttpCookie("Test321");
            oCookie.Value = Name;
            oCookie.Expires = DateTime.Now.AddDays(2);
            HttpContext.Current.Response.Cookies.Add(oCookie);STEP2: Retreive
//METHOD 1
            /*
            string Name = "";
            if (HttpContext.Current.Request.Cookies["Test123"] != null)
            {
                Name = HttpContext.Current.Server.HtmlEncode(HttpContext.Current.Request.Cookies["Test123"].Value);
            }
            return Name;
             */
            //METHOD 2
            string Name = "";
            if (HttpContext.Current.Request.Cookies["Test321"] != null)
            {
                HttpCookie oCookie = HttpContext.Current.Request.Cookies["Test321"];
                Name = HttpContext.Current.Server.HtmlEncode(oCookie.Value);
            }
            return Name;
MODE2: more than one cookie value
STEP1: Save
            HttpCookie oCookie = new HttpCookie("abc123");
            oCookie.Values["Name"] = Name;
            oCookie.Values["Pwd"] = Pwd;
            oCookie.Expires = DateTime.Now.AddDays(2);
            HttpContext.Current.Response.Cookies.Add(oCookie);
STEP2: Retreive
           string Name = "";
            string Pwd = "";
            string result = "";
            if (HttpContext.Current.Request.Cookies["abc123"] != null)
            {
                System.Collections.Specialized.NameValueCollection oCollection;
                oCollection = HttpContext.Current.Request.Cookies["abc123"].Values;

                Name = oCollection["Name"];
                Pwd = oCollection["Pwd"];
                result = "<table><tr><td>" + Name + "</td><td>" + Pwd + "</td></tr></table>";
            }
            return result;

XML Insert

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Sareesh
-- Create date: 28 March 2012
-- Description:    here @TestXML root node is /STHome
--    and declare ID,TestValue same name as in the xml format

--var TestXML = "<STHome>";
--TestXML = TestXML + "<Home>";
--TestXML = TestXML + "<ID>" + $(this).val() + "</ID>";                                               
--TestXML = TestXML + "<TestValue>" + "" + "</TestValue>";                  
--TestXML = TestXML + "</Home>";
--TestXML = STHomeXML + "</STHome>";
-- =============================================
CREATE PROCEDURE SPR_TEST_Insert(
    @TestID INT,
    @TestXML XML
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @XmlDocumentHandle int
    DECLARE @ID INT
    DECLARE @TestValue VARCHAR(2000)
   
    -- reads the xml then parses the text using the MSXML parser,
    -- and provides the parsed document in a state ready for consumption
    EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @TestXML

    -- I create a cursor to iterate over the developer items in the XML file 
    -- OPENXML allows access to XML data as though it is a relational rowset
    DECLARE TestCursor CURSOR FOR
    SELECT ID, TestValue
    FROM OPENXML (@XmlDocumentHandle, '/STHome/Home',2)
    WITH (ID INT, TestValue VARCHAR(2000))
   
    OPEN DeficitCursor 
    FETCH NEXT FROM DeficitCursor INTO @DeficitID, @DeficitValue  
    WHILE @@FETCH_STATUS = 0
        BEGIN
              INSERT INTO TESTTABLE VALUES (@ID, @TestValue, @TestID)
              FETCH NEXT FROM TestCursor INTO @ID, @TestValue
        END

    CLOSE TestCursor
    DEALLOCATE TestCursor
   
    EXEC sp_xml_removedocument @XmlDocumentHandle
END
GO

Handling wih DataView for filter dataset values

we have a dataset
Dataset dsTest = oTestDL.GetTestData(ID);

if we want to use values in between a range from dataset we can use like this

DataView dvTest = dsTest .Tables[0].DefaultView;
dvTest .RowFilter = "TestID > 10 AND TestID < 18";
  foreach (DataRowView drv in dvTest)
     {
          string TestValues = drv[3].ToString();
    }

Convert to any Datetime format in c#

string StartDate = DateTime.Now.ToShortDateString();

string CStartDate = DateTime.ParseExact(CertStartDate, "dd/MM/yyyy",null).ToString("yyyy-MM-dd");


you can convert dd/MM/yyyy to any other format by specifiying like ToString("yyyy-MM-dd")

StoredProcedure

//SP  with out parameter
//to create SP
CREATE PROCEDURE SP_TestNoParam

AS
BEGIN   
    SELECT * FROM TBL_Details
END   
   
//to drop SP  
 drop procedure SP_TestNoParam
 //to view SP
 sp_helptext SP_TestNoParam
 //to execute SP
 SP_TestNoParam

-------------------
//SP with parameters

CREATE PROCEDURE SP_Test(
                        @P_ID INT                       
                        )
    AS
    BEGIN
        SELECT * FROM TBL_Details WHERE ID=@P_ID
    END 

 SP_Test 2


-----------------------
//insert SP
CREATE PROCEDURE SP_TestInsert(                           
                            @P_Name VARCHAR(100),
                            @P_Age INT
                              )
AS
BEGIN
    DECLARE @P_ID INT
    SET @P_ID=(SELECT ISNULL(MAX(ID),0) FROM TBL_Details) + 1
    INSERT INTO TBL_Details VALUES(@P_ID,@P_Name,@P_Age)
END

//caling SP
SP_TestInsert 'Test123', 33
-----------------------------------
    public void DataConnectionSP(string query,string name,int age)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True;Pooling=False";
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = query;
            com.Parameters.Add(new SqlParameter("@P_Name",name));
            com.Parameters.Add(new SqlParameter("@P_Age",age));
            com.CommandType = CommandType.StoredProcedure;

            com.ExecuteNonQuery();
        }

 -------------------------------

//In grid rowCommand put

string query = "SP_TestInsert";
oGridDL.DataConnectionSP(query,txtName.Text.ToString(),Convert.ToInt32(txtAge.Text));

Grid EmptyRow Fill

<Gridview>
<column>
</column>

 <EmptyDataTemplate>
            <table>
            <tr>
          
            <td><asp:Label ID="Label1" runat="server" Text="Name"></asp:Label></td>
            <td><asp:Label ID="Label2" runat="server" Text="Age"></asp:Label></td>
            <td><asp:Label ID="Label3" runat="server" Text="Place"></asp:Label></td>
            <td><asp:Label ID="Label4" runat="server" Text="Dob"></asp:Label></td>
             <td><asp:Label ID="Label6" runat="server" Text="Add"></asp:Label></td>
            </tr>
            <tr>
            <td><asp:TextBox ID="txtNameE" runat="server"></asp:TextBox></td>
            <td><asp:TextBox ID="txtAgeE" runat="server"></asp:TextBox></td>
            <td><asp:TextBox ID="txtPlaceE" runat="server"></asp:TextBox></td>
            <td><asp:TextBox ID="txtDOBE" runat="server" ></asp:TextBox></td>
            <td><asp:ImageButton runat="server" ID="imgAddE" ImageUrl="Images/add.gif" CommandName="InsertEmpty"/></td>
            </tr>
            </table>
            </EmptyDataTemplate>

</Gridview>

-----------------------------------

aspx.cs


 protected void gvwtest_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "Insert")
            {
                TextBox txtName = gvwtest.FooterRow.FindControl("txtName") as TextBox;
                TextBox txtAge = gvwtest.FooterRow.FindControl("txtAge") as TextBox;
                TextBox txtPlace = gvwtest.FooterRow.FindControl("txtPlace") as TextBox;
                TextBox txtDOB = gvwtest.FooterRow.FindControl("txtDOB") as TextBox;
              

                string query = "INSERT INTO TBL_UserDetails VALUES((SELECT ISNULL(MAX(Id),0) as maxid FROM TBL_UserDetails)+1,'" + txtName.Text + "','" + txtAge.Text + "','" + txtPlace.Text + "','" + txtDOB.Text + "')";
                DataConnection(query, 0);
                FillUserDetails();
            }
            else if (e.CommandName == "InsertEmpty")
            {
                TextBox txtName = gvwtest.Controls[0].Controls[0].FindControl("txtNameE") as TextBox;
                TextBox txtAge = gvwtest.Controls[0].Controls[0].FindControl("txtAgeE") as TextBox;
                TextBox txtPlace = gvwtest.Controls[0].Controls[0].FindControl("txtPlaceE") as TextBox;
                TextBox txtDOB = gvwtest.Controls[0].Controls[0].FindControl("txtDOBE") as TextBox;

                string query = "INSERT INTO TBL_UserDetails VALUES((SELECT ISNULL(MAX(Id),0) as maxid FROM TBL_UserDetails)+1,'" + txtName.Text + "','" + txtAge.Text + "','" + txtPlace.Text + "','" + txtDOB.Text + "')";
                DataConnection(query, 0);
                FillUserDetails();
            }
        }

DisplayMessage Function in aspx

private void DisplayMessage(string strMsg)
  {
      string strScript = "<script langauge=javascript type=text/javascript> alert('" + strMsg + "')</script>";
      ClientScript.RegisterClientScriptBlock(this.GetType(), "", strScript);
  }

Grid RowDatabound and PageIndexing

protected void gvwtest_RowDataBound(object sender, GridViewRowEventArgs e)
 {
     if (e.Row.RowType == DataControlRowType.DataRow)
     {
     ImageButton ibtnDelete = e.Row.FindControl("ibtnDelete") as ImageButton;
     ibtnDelete.Attributes.Add("onclick", "return(confirm('Are you sure you want to delete the Name?'))");
     }

 }




 protected void gvwtest_PageIndexChanging(object sender, GridViewPageEventArgs e)
  {
      gvwtest.PageIndex = e.NewPageIndex;
      FillGrid();
 }

Grid RowUpdate

 protected void gvwtest_RowUpdating(object sender, GridViewUpdateEventArgs e)
  {
      TextBox txtEditName = gvwtest.Rows[e.RowIndex].FindControl("txtEditName") as TextBox;
      if (txtEditName.Text == "")
     {
     DisplayMessage("Please Enter Name");
     return;
     }
     Test_Info oTest_Info = new Test_Info();
     ImageButton ibtnUpdate = gvwtest.Rows[e.RowIndex].FindControl("ibtnUpdate") as ImageButton;

     oTest_Info.Id = Convert.ToInt32(ibtnUpdate.CommandArgument);
     oTest_Info.Name = txtEditName.Text.Trim();
    
     Test_BL oTest_BL = new Test_BL();
     int Result=oTest_BL.UpdateName(oTest_Info.Id,oTest_Info.Name);
     if (Result == 1)
     {
     DisplayMessage("Updated successfully");
     }
     else
     {
     DisplayMessage("Same  Name Already Exist");
     }
     gvwtest.EditIndex = -1;
     FillGrid();
}

Grid RowEdit and CancelEdit

protected void gvwtest_RowEditing(object sender, GridViewEditEventArgs e)
{
    Label lblName = gvwtest.Rows[e.NewEditIndex].FindControl("lblName") as Label;
    gvwtest.EditIndex = e.NewEditIndex;
    FillGrid();
    TextBox txtEditName = gvwtest.Rows[e.NewEditIndex].FindControl("txtEditName") as TextBox;
    txtEditName.Text = lblName.Text;
}



protected void gvwtest_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
 {
     gvwtest.EditIndex = -1;
     FillGrid();
 }

Grid RowDelete

protected void gvwtest_RowDeleting(object sender, GridViewDeleteEventArgs e)
 {
     Test_BL oTest_BL = new Test_BL();
     ImageButton ibtnDelete = gvwtest.Rows[e.RowIndex].FindControl("ibtnDelete") as ImageButton;
     int Id = Convert.ToInt32(ibtnDelete.CommandArgument);
     oTest_BL.DeleteName(Id);
     FillGrid();
     DisplayMessage("Deleted Successfully");
 }

Grid RowCommand

protected void gvwtest_RowCommand(object sender, GridViewCommandEventArgs e)
{

    if (e.CommandName == "Insert")
    {
    TextBox txtName = gvwtest.FooterRow.FindControl("txtName") as TextBox;
    if (txtDepartmentName.Text == "")
    {
        DisplayMessage("Please Enter Name");
        return;
    }
    //create object from Bussiness layer
    Test_BL oTest_BL = new Test_BL();
    int maxid = oTest_BL.GetMaxId() + 1;
    lblMaxId.Text = maxid.ToString();
    lblMaxId.Visible = false;
   
    Test_Info oTest_Info = new Test_Info();
    oTest_Info.Id = Convert.ToInt32(lblMaxId.Text);

    oTest_Info.Name = txtName.Text.Trim();

       int Result = oTest_BL.insertName(oTest_Info.Id,oTest_Info.Name);

       if (Result == 1)
       {
       DisplayMessage("Saved successfully");
       }
       else
    {
        DisplayMessage("Same  Name Already Exist");
    }
   
    //Filling gridview once again after insert
    FillGrid();

    }

}

Fill Grid

protected void FillGrid()
{
    Test_BL oTest_BL = new Test_BL();
    DataSet Dts = oTest_BL.GetAllNames();
    if (Dts.Tables[0].Rows.Count == 0)
    {
    Dts.Tables[0].Rows.Add(Dts.Tables[0].NewRow());
    gvwtest.DataSource = Dts;
    gvwtest.DataBind();
    gvwtest.Rows[0].Visible = false;
    }
    else
    {
    gvwtest.DataSource = Dts;
    gvwtest.DataBind();
    }

}