Connection Class

#region userConnectionString
public static string userConnectionString
{

get
{
return (ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

}


}
#endregion

#region ConnectionProcedure

public static SqlCommand ConnectionProcedure()
{

SqlConnection cn = new SqlConnection(UserEntity.userConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cn.Open();
cmd.CommandType = CommandType.StoredProcedure;
return (cmd);



}

#endregion

#region FetchUserData
public DataTable fetchUserData(string userEmail, string userPassword, string Storeprocedurename)
{
try
{
SqlCommand cmd = UserEntity.ConnectionProcedure();
cmd.CommandText = Storeprocedurename;
cmd.Parameters.AddWithValue("@USER_EMAIL", userEmail);
cmd.Parameters.AddWithValue("@USER_PASSWORD", userPassword);

//SqlParameter addoutprm = new SqlParameter("@COOUT", "1");
//addoutprm.Direction = ParameterDirection.Output;
//cmd.Parameters.Add(addoutprm);

SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp.Fill(dt);


if (dt != null && dt.Rows.Count > 0)
{
return dt;
}
else
{
return null;
}

}
catch (Exception ex)
{
return null;
}
}
#endregion

#region deleteUserRecords

public int deleteUserRecords(int userId, string storeProcedure)
{
int result = 0;
try
{
SqlCommand cmd = UserEntity.ConnectionProcedure();
cmd.CommandText = storeProcedure;
cmd.Parameters.AddWithValue("@USER_ID", userId);
SqlParameter prm = new SqlParameter("@POUTRESULT", 0);
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm);
result = cmd.ExecuteNonQuery();

int r = Convert.ToInt32(prm.Value);

if (result > 0)
{
return r;
}
else
{
return 0;
}
}
catch (Exception ex)
{
return 0;

}



}


#endregion

#region UpdateUserData

public int UpdateUserData(int userId, string userName, string passWord, string procedureName)
{
try
{
int result = 0;
SqlCommand cmd = UserEntity.ConnectionProcedure();
cmd.CommandText = procedureName;
cmd.Parameters.AddWithValue("@USER_ID", userId);
cmd.Parameters.AddWithValue("@USER_NAME", userName);
cmd.Parameters.AddWithValue("@USER_PASSWORD", passWord);
SqlParameter prm = new SqlParameter("@POUTRESUT", 9);
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm);
result = cmd.ExecuteNonQuery();

if (result > 0)
{
return result;
}
else
{
return 0;
}

}
catch (Exception ex)
{
return 0;

}


}
#endregion