Check if User in Account Table exists in another table using C# in ASP.Net

Last Reply on Apr 27, 2016 07:11 AM By dharmendr

Posted on Apr 27, 2016 05:32 AM

I want to check if a user in User table is found either in UserName Column or in FriendUserName Column in UserFollow table and if yes display lable found, But if no display lable notfound. This code will be excuted onse a user logs in the connection code

 

protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["UserName"] != null && Session["UserName"].ToString() != string.Empty)
        {


            string userName = Session["UserName"].ToString();
            if (!this.IsPostBack)
            {
                FormView1follow.DataSource = GetData(userName);
                FormView1follow.DataBind();
            }
        }
    }
    private DataTable GetData(string userName)
    {
        DataTable dt = new DataTable();
       
        string constr = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;

        string query = "SELECT u.UserName,u.FriendUserName,u.FollowStatus,u.Status,u.Status2 ,up.UserName,up.Name FROM USERFollow as u, User3 as up WHERE up.UserName = '" + userName + "' OR u.FriendUserName='" + userName + "'";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();
                //     cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName", Request.QueryString["Id"].ToString());
                //     cmd.Parameters.AddWithValue("@FriendUserName", Session["UserName"].ToString());
                cmd.Parameters.AddWithValue("@FriendUserName", Request.QueryString["Id"].ToString());
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(dt);
                }
                con.Close();
            }
        }

        return dt;
    }

HTML

 

<asp:FormView ID="FormView1follow" runat="server" OnDataBound="OnDataBound" Width="100%">
        <ItemTemplate>
     
            <asp:HiddenField ID="hfUserName" runat="server" Value='<%# Eval("UserName") %>' />
            <asp:HiddenField ID="hfFriendsUserName" runat="server" Value='<%# Eval("FriendUserName") %>' />
            <asp:HiddenField ID="hfFollowStatus" runat="server" Value='<%# Eval("FollowStatus") %>' />
            <asp:LinkButton ID="btnfollowFollow" runat="server" ToolTip="follow user" CssClass=" btn btn-twitter fa fa-user" Font-Bold="True" >
                <i class=""  style="margin-right:2px" ></i>  <asp:Label ID="lblFollow" runat="server" Text='<%# Eval("Status") %>' />
            </asp:LinkButton>&nbsp; <asp:LinkButton ID="Unbtnfollow" runat="server" ToolTip="follow user" CssClass="  btn btn-twitter fa fa-user-plus" Font-Bold="True">
                <i class=""  style="margin-right:2px" ></i>  <asp:Label ID="lblUnFollow" runat="server" Text='<%# Eval("Status2") %>' />
            </asp:LinkButton></ItemTemplate></asp:FormView></div>

 

Posted on Apr 27, 2016 06:12 AM Modified on one year ago

Hi micah,

I have created a sample. You need to change as per your requirement.

SQL

CREATE PROC UserExistance
    @UserName VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS(SELECT UserName FROM User3 WHERE Username = @Username)
    BEGIN
        IF EXISTS(SELECT UserName FROM USERFollow WHERE UserName = @UserName OR FriendUserName = @UserName)
        BEGIN
            SELECT 'Exist' AS UserStatus -- User Exist in follower table.
        END
        ELSE
        BEGIN
            SELECT 'NotExist' AS UserStatus -- User Not Exist in follower table.
        END
    END
    ELSE
    BEGIN
        SELECT 'User not activated / invalid user' AS UserStatus -- User not activated / invalid user in User Table.
    END    
END

--EXEC UserExistance 'Mic22'
--EXEC UserExistance 'ClassLady'
--EXEC UserExistance 'dharmendra'

HTML

<div>
    <asp:Label ID="lblFound" Text="Found" runat="server" Visible="false" />
    <asp:Label ID="lblNotFound" Text="NotFound" runat="server" Visible="false" />
    <asp:Label ID="lblInvalidUser" Text="Invalid User" runat="server" Visible="false" />
</div>

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.QueryString["UserName"] != null)
    {
        if (!IsPostBack)
        {
            DataTable dt = GetData(Request.QueryString["UserName"]);
            if (dt.Rows.Count > 0)
            {
                string userStatus = dt.Rows[0]["UserStatus"].ToString().ToLower();
                if (userStatus == "exist")
                {
                    lblFound.Visible = true;
                }
                else if (userStatus == "notexist")
                {
                    lblNotFound.Visible = true;
                }
                else
                {
                    lblInvalidUser.Visible = true;
                }
            }
        }
    }
}

private DataTable GetData(string userName)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "UserExistance";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserName", userName);
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                sda.Fill(dt);
            }
            con.Close();
        }
    }

    return dt;
}

Screenshot


Posted on Apr 27, 2016 06:30 AM

you didnt show how you displayed it on lable, you only showed procedure